Аналитические функции

Оракл и другие БД уже достаточно давно внедрили аналитические функции. Но очень часто приходится видеть, что об этих функциях либо не знают, либо не понимают, что они могут делать.

Напомню, что же это такое, и как их использовать.

Начну с простого — «разагрегирование» агрегатных функций. Часто хочется получить полный набор данных, но в нем для каждой записи вернуть еще и сумму по какой-нибудь группе. Допустим, по клиенту. Чтобы посчитать, например, какой процент от потраченной суммы клиент потратил на конкретную покупку. В традиционном SQL это выглядит примерно так:

select a.clientID, a.amount, a.amount/b.sum_amount from orders a inner join (select clientid, sum(amount) sum_amount from orders group by clientid) b on a.clientid = b.clientid

То есть, считается сумма для каждого клиента, потом результирующая таблица перемножается с таблицей ордеров, и это может занимать достаточно долгое время, которое тратится на сканирование результатов подзапроса для каждой записи из таблицы ордеров.

В случае же использования аналитических функций ничего такого не происходит — основное достоинство аналитических функций в том, что все выполняется в один проход по таблице и никаких лишних дорогих joins не происходит.

Вот как выглядит эта конструкция

select a.clientid, a.amount, a.amount/(sum(a.amount) over (partition by a.clientid)) from orders a

И все! Волшебная конструкция over (partition by … — это замена group by, которая работает точно так же, как и group by, но результат возвращается для каждой строчки запроса.

Это самый простой и, надеюсь, понятный случай.

Дальше — «все страньше и страньше» ©

Слово «over«, оказывается, можно использовать и с пустыми скобками. То есть, агрегация по всей выборке сразу: например, если хочется посчитать сумму, заплаченную клиентом по отношению к общей сумме всех покупок —

select a.clientid, a.amount, sum(a.amount) over (partition by a.clientid)/sum(a.amount) over() from orders a

Но это — только начало.

Добавляем условие order by — например, для того, чтобы пронумеровать записи в группе:

select a.clientid, a.amount, row_number() over (partition by a.clientid order by a.purchase_date) from orders a

То есть, все записи для каждого клиента пронумерованы от 1 до … в порядке возрастания даты покупки.

Кроме полезной функции row_number(), есть еще функции first_value(), last_value(), lead(), lag() и некоторые другие, которые работают с отсортированными наборами. Lead — это следующая запись (точнее, n-ная) из отсортированного списка, lag() — предыдущая, first_value — первая запись в группе, last_value — последняя… Точнее, не последняя, а неизвестно какая, об этом — чуть позже.

Пока — простой пример:
select a.clientid, first_value(a.purchase_date) over (partition by a.client_id order by a.purchase_date), lead(a.purchase_date) over (partition by a.client_id order by a.purchase_date), lag(a.purchase_date) over (partition by a.client_id order by a.purchase_date) from orders a

Этот запрос возвращает дату первой покупки, и еще и даты предыдущей и последующей покупок. Очевидно, что для первой записи по клиенту дата предыдущей покупки будет неизвестна (NULL, «по-нашему»), и для последней записи, соответственно, дата последующей покупки будет неизвестна. Кстати, в функциях lead/lag можно указывать вторым параметром количество записей, которые надо пропустить — так, lead(a.purchase_date, 5) — вернет пятую запись от текущей.

Тут еще надо отметить, что сортировка внутри аналитических функций может отличаться от внешней сортировки в запросе, и внутри разных аналитических функций она может отличаться.

Теперь — о странном. Last_value, по неизвестной для меня причине, работает не так, как предполагает ее название. Это не последнее значение в отсортированной группе, а текущее, если не указывать дополнительных условий в аналитической функции. О них — еще чуть ниже.

А сейчас — простой трюк, как это победить — для этого надо, всего навсего, использовать first_value с сортировкой в другом порядке — то есть:

first_value(a.purchase_date) over (partition by a.client_id order by a.purchase_date desc)

Ну и, все-таки, почему же некоторые аналитические функции еще называются оконными? Потому что, кроме условий группировки и сортировки в них можно указывать интервал записей, по которым надо производить вычисления. Это, не вдаваясь в слишком глубокие дебри, конструкции типа

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, — это интервал записей от начала группы до текущей записи, или, что то же самое, просто ROWS UNBOUNDED PRECEDING, или ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING — это от текущей записи до конца группировки, или ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING — это по всей группе.

В большинстве случаев такое добавление можно не использовать, кроме (!!!) last_value — в случае с last_value добавление этой, казалось бы, необязательной конструкции приводит к тому, что last_value начинает работать правильно и показывает именно последнюю запись в группе. По «умолчанию» last_value работает в режиме ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Кому пришла в голову такая гениальная идея, я не знаю. Еще можно использовать конструкции типа
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING. То есть, вернуть все записи до текущей (не включая ее).

Для чего же это нужно? Хотя бы для всеми горячо любимого running total (накопленная сумма) вот такой запрос:

select a.clientid, sum(a.amount) over (partition by a.client_id order by a.purchase_date rows unbounded preceding) from orders a order by clientid, purchase_date

возвращает running total — для каждой записи по client_id возвращает накопленную сумму на текущую дату. И не нужно делать никаких циклов по одной записи с вычислениями этого значения.

И в конце, так сказать, на десерт — можно использовать аналитические функции поверх обычных групповых агрегатных функций — то есть, например, найти минимальную сумму или посчитать общую итоговую сумму («Итого») —

select a.clientid, sum(a.amount), sum(sum(a.amount)) over(), min(sum(a.amount()) over() from orders a group by a.clientid

этот запрос возвращает сумму, потраченную каждым клиентом, общую сумму и минимальную сумму из сумм, потраченных клиентами.

Казалось бы, уже все возможные варианты рассмотрели, но…

Оракл не только «разгруппировал» агрегатные функции и добавил сортировки и оконные условия, но и, наоборот, «сгруппировал» оконные функции — добавил функциональность first и last к агрегатным функциям — теперь стало возможно выбирать min/max в групповых операциях с учетом необходимой сортировки — ведь очень часто хочется вернуть не минимальную запись, а первую или последнюю запись в группе. Возьмем типичный пример — биржевая информация (торговля акциями) — так называемые «свечи» (candlesticks). Это — минимальное и максимальное значение цен акций, а также первое и последнее значение цен акций в каком-либо интервале времени.
Если бы у Оракла не было таких «отсортированных» значений, то пришлось бы использовать выражение типа

select distinct min(price) over (partition by <interval>), max(price) over (partition by <interval>), first_value(price) over (partition by <interval> order by time), first_value(price) over (partition by <interval> order by time desc) from trades

здесь <interval> — это условное обозначение функции расчета интервалов времени — это может быть любой интервал — секунда, минута, час, и т.д. В случае использования аналитических функций возникает противное слово distinct.

Но у Оракла есть конструкция KEEP (DENSE_RANK FIRST (или LAST) ORDER BY …) — это как раз и означает, что мы выбираем первое (последнее) значение в отсортированном наборе внутри группы.

select min(price), max(price), min(price) keep (dense_rank first order by time), max(price) keep (dense_rank last order by time) from trades group by <interval>

Надеюсь, что этот достаточно поверхностный обзор (но выстраданный, можно сказать, потом и кровью) поможет разобраться в аналитических функциях и областях их применения.