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

Да ты посмотри, какие они хитрожопые, эти оконные функции! Это ж просто пиздец, как удобно, когда тебе надо посчитать что-то по группе, но при этом все строки на месте остаются, не сворачиваются в одну, как с обычным GROUP BY. Представь: ты видишь каждую зарплату, а рядом — среднюю по отделу. Красота, ёпта!

Вот смотри, основные банды, про которые надо знать:

1. Агрегатчики, которые стали оконными. Это наши старые знакомые: SUM(), AVG(), COUNT(). Только теперь они не давят всё в одну кашу, а считают своё дело в рамках «окошка». Например, нарастающий итог — это же просто песня, а не функция!

   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 корзинок. Типа, «разбей мне всех сотрудников на 4 группы по зарплате». Получатся квантильчики.

     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) — оглядывается назад, на указанное количество строк. «А что там вчера было?»
   • LEAD(column, offset, default) — смотрит вперёд. «А что там завтра будет?»
   • 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;

     Видишь? Мы взяли выручку за вчера (LAG), вычли её из сегодняшней и получили прирост. Элегантно, блядь, до безобразия!

4. Статистические зануды. CUME_DIST() и PERCENT_RANK(). Ну, эти нужны, когда тебе надо не просто ранжировать, а понять, в каком процентиле находится строка. Полезно, но мозг иногда вывихивают.

А теперь, ёпта, самое главное — как этим всем рулить? Всё решает волшебная штука OVER().

В ней три основных рычага:

• PARTITION BY — это как GROUP BY, только для окон. Разбивает таблицу на независимые куски (отделы, категории, годы). В каждом таком куске своё окошко и свои вычисления.
• ORDER BY — а это порядок внутри окошка. Критически важен! Без него нарастающий итог не посчитать, и LAG с LEAD будут себя странно вести. Это как сказать: «Стройся по зарплате, от большей к меньшей, и теперь давай считай».
• ROWS/RANGE BETWEEN — вот это уже высший пилотаж. Тут ты указываешь границы окна. Например, «смотри на две строки перед текущей и на одну после». Идеально для скользящего среднего! Если не указать, по умолчанию окно может вести себя неочевидно, особенно с LAST_VALUE() — так что тут надо глаза держать открытыми.

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