Какие оконные функции в SQL ты знаешь?

Оконные функции (Window Functions) в SQL выполняют вычисления над набором строк, связанных с текущей строкой, без свёртки результата в одну строку на группу (в отличие от GROUP BY). Они определяются с помощью предложения OVER().

Основные категории оконных функций:

1. Агрегатные функции как оконные:

   • SUM(), AVG(), COUNT(), MIN(), MAX(), STDDEV().
   • Позволяют вычислить агрегат в рамках «окна» (например, нарастающий итог).

     SELECT 
     employee_id,
     department_id,
     salary,
     AVG(salary) OVER(PARTITION BY department_id) AS avg_department_salary
     FROM employees;

2. Функции ранжирования:

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

     SELECT 
     product_id,
     category,
     price,
     ROW_NUMBER() OVER(PARTITION BY category ORDER BY price DESC) as price_rank_in_category,
     RANK() OVER(ORDER BY price DESC) as global_price_rank_with_gaps
     FROM products;

3. Функции смещения (доступа к соседним строкам):

   • LAG(column, offset, default) — возвращает значение из строки, находящейся на offset строк перед текущей.
   • LEAD(column, offset, default) — возвращает значение из строки, находящейся на offset строк после текущей.
   • FIRST_VALUE(column) — возвращает первое значение в окне.
   • LAST_VALUE(column) — возвращает последнее значение в окне.

     SELECT 
     date,
     revenue,
     LAG(revenue, 1, 0) OVER(ORDER BY date) AS revenue_previous_day,
     revenue - LAG(revenue, 1, 0) OVER(ORDER BY date) AS daily_growth
     FROM sales;

4. Функции статистического распределения:

   • CUME_DIST() — относительный ранг строки (доля строк со значением <= текущему).
   • PERCENT_RANK() — относительный ранг строки в диапазоне от 0 до 1.

Ключевые части предложения OVER():

• PARTITION BY — разбивает данные на независимые окна/секции (аналог GROUP BY, но без свёртки).
• ORDER BY — определяет порядок строк внутри окна, что критично для нарастающих итогов, ранжирования и смещения.
• ROWS/RANGE BETWEEN — определяет границы окна относительно текущей строки (например, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW для скользящего среднего по 3 строкам).