Что такое оконные функции (Window Functions) в SQL и для каких задач они применяются?

Тип вопроса: Базы данных и SQL Вероятность: 23% Категория: AQA / Automation

Ответ

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

Ключевые компоненты и синтаксис: Оконная функция определяется с помощью предложения OVER():

<функция> OVER (
    [PARTITION BY <столбцы>]
    [ORDER BY <столбцы>]
    [ROWS/RANGE <границы окна>]
)

Практические примеры применения:

  1. Агрегация без группировки (сравнение с групповой средней):

    SELECT
    employee_id,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS avg_dept_salary,
    salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees;

    Каждая строка сохраняется, но к ней добавляется средняя зарплата по её отделу.

  2. Ранжирование и нумерация (например, топ-3 товара в категории):

    SELECT
    product_id,
    category,
    sales,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rank_in_category
FROM products;

  3. Расчёт накопительных итогов и скользящих средних:

    SELECT
    order_date,
    daily_revenue,
    SUM(daily_revenue) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_revenue,
    AVG(daily_revenue) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS weekly_moving_avg
FROM revenue_stats;

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

  • Агрегатные: SUM(), AVG(), COUNT(), MIN(), MAX().
  • Ранжирующие: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE().
  • Функции смещения: LAG(), LEAD() (доступ к данным из предыдущей/следующей строки).
  • Функции распределения: PERCENT_RANK(), CUME_DIST().

Оконные функции незаменимы для сложной аналитической отчётности, расчёта KPI и подготовки данных, где требуется сохранить детализацию исходных строк.

Ответ 18+ 🔞

А, оконные функции в SQL! Ну это, блядь, просто песня, а не инструмент. Сидишь такой, пишешь запрос, и понимаешь, что тебе надо посчитать какую-нибудь хуйню вроде средней зарплаты по отделу, но чтобы все строки на месте остались, а не схлопнулись в одну. И тут ты вспоминаешь про OVER() — и жизнь налаживается, ёпта!

Представь: обычный GROUP BY — это как взять всех сотрудников отдела и выдать им одну общую справку: «Средняя зарплата — столько-то». А оконная функция — это подойти к каждому челу по отдельности, шепнуть ему на ушко эту цифру и сказать: «Вот, Вася, держи, сравнивай, насколько ты хуйло или молодец». И все исходные данные — они на месте, их нихуя не тронули!

Вот смотри, из чего эта магия состоит: Всё крутится вокруг этого самого OVER(). Туда можно намешать три основных компонента, как в хороший коктейль:

<функция> OVER (
    [PARTITION BY <столбцы>]  <!-- Разбиваешь на куски, как торт -->
    [ORDER BY <столбцы>]      <!-- Сортируешь внутри этих кусков -->
    [ROWS/RANGE <границы окна>] <!-- И говоришь, какие строки рядом считать -->
)

Ну и чтобы не быть голословным, вот тебе живые примеры, где эта хуйня реально спасает жопу:

  1. «Сравни себя с соседом по палате» (Агрегация без схлопывания): Допустим, хочешь узнать, насколько каждый работник отстаёт или перепизживает среднюю по своему отделу.

    SELECT
    employee_id,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS avg_dept_salary, <!-- Вот она, магия! -->
    salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg <!-- И сразу видно, лох или красавчик -->
FROM employees;

    Каждый чувак остаётся в своей строке, но теперь у него в кармане лежит средняя температура по больнице.

  2. «Кто тут у нас самый продающий?» (Ранжирование): Нужно вывести топ-3 товара в каждой категории. Без оконных функций пришлось бы городить огород из подзапросов, а тут — раз плюнуть.

    SELECT
    product_id,
    category,
    sales,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rank_in_category
FROM products;

    PARTITION BY category — значит, ранжирование начнётся заново для каждой категории. И всё, пиздец, задача решена.

  3. «Сколько уже накопил и какая средняя за неделю?» (Накопительные итоги): Вот это вообще мощь. Хочешь видеть, как растёт выручка день за днём и какое скользящее среднее за неделю?

    SELECT
    order_date,
    daily_revenue,
    SUM(daily_revenue) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_revenue,
    AVG(daily_revenue) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS weekly_moving_avg
FROM revenue_stats;

    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW — это и есть указание «смотри на текущую строку и на шесть предыдущих». Красота, блядь!

А какие вообще бывают эти функции? Да их, блять, овердохуища!

  • Агрегатные: Те же самые SUM(), AVG(), но применяются к окну, а не ко всей таблице.
  • Ранжирующие: ROW_NUMBER() (просто номер по порядку), RANK() (а вот тут могут быть одинаковые ранги и дырки), DENSE_RANK() (дырок не будет).
  • Функции смещения: LAG() (загляни, что было в предыдущей строке), LEAD() (посмотри, что будет в следующей). Просто находка для анализа временных рядов, в рот меня чих-пых!
  • Функции распределения: PERCENT_RANK() и прочая экзотика, но они тоже иногда нужны.

Короче, если ты делаешь аналитику, отчёты или просто хочешь не ебать мозг себе и базе кучей подзапросов — учи оконные функции. Это один из тех инструментов, после освоения которого начинаешь чувствовать себя настоящим шаманом SQL, ёпта!

© ХакСобесов, 2026
support@hacksobesov.com