SQL: оконные функции

Что такое оконная функция

Оконная функция выполняет вычисление по связанным строкам, но, в отличие от GROUP BY, не объединяет их. Поэтому рядом с каждой исходной строкой можно показать место, среднее по группе, предыдущее значение или накопительный итог.

функция() OVER (
    PARTITION BY группа
    ORDER BY порядок
    ROWS BETWEEN граница_начала AND граница_конца
)

Три части окна

• PARTITION BY делит строки на независимые группы.
• ORDER BY задаёт порядок расчёта внутри группы.
• ROWS BETWEEN определяет, какие соседние строки участвуют в расчёте.

Нумерация и рейтинг

• ROW_NUMBER() даёт каждой строке уникальный номер.
• RANK() даёт одинаковым значениям одно место и оставляет пропуски.
• DENSE_RANK() даёт одинаковые места без пропусков.

RANK() OVER(
    PARTITION BY class_name
    ORDER BY score DESC
)

Расчёты по группе

Функции SUM, AVG, MIN, MAX и COUNT можно использовать с OVER(). Без PARTITION BY расчёт выполняется по всей таблице, а с ним — отдельно для каждой группы.

Предыдущая и следующая строки

LAG(value, 1) OVER(ORDER BY day)
LEAD(value, 1) OVER(ORDER BY day)

LAG смотрит назад, LEAD — вперёд. Второй аргумент задаёт расстояние в строках.

Рамка окна

AVG(score) OVER(
    ORDER BY test_number
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)

Такое окно включает текущую и две предыдущие строки. Для накопительного итога используйте начало UNBOUNDED PRECEDING.

Первое и последнее значения

FIRST_VALUE возвращает первое значение в порядке окна. Для LAST_VALUE обычно нужно явно расширить рамку до UNBOUNDED FOLLOWING, иначе последней может считаться текущая строка.

Как выбрать функцию

• «номер», «по счёту» — ROW_NUMBER;
• «место», «одинаковый результат» — RANK или DENSE_RANK;
• «предыдущий» или «следующий» — LAG или LEAD;
• «с начала до текущего» — агрегат с ORDER BY;
• «последние три строки» — рамка ROWS BETWEEN.

Перед запросом ответьте на четыре вопроса: что считать, для кого считать отдельно, в каком порядке расположить строки и какие строки включить в окно.