Что такое оконные функции в SQL?

Question

Что такое оконные функции в SQL?

Тип вопроса: Базы данных Вероятность: 10% Категория: Python Разработчик

Ответ

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

Ключевые особенности и преимущества:

Сохранение детализации: В отличие от GROUP BY, оконные функции возвращают столько же строк, сколько было на входе, добавляя к каждой строке вычисленное значение. Это позволяет видеть агрегированные данные вместе с детализированными.
Гибкость определения окна: Окно данных определяется с помощью предложения OVER(), которое может включать:
- PARTITION BY: Делит строки на группы (партиции), по которым будут производиться вычисления.
- ORDER BY: Определяет порядок строк внутри каждой партиции.
- ROWS/RANGE: Указывает конкретный диапазон строк относительно текущей строки в окне (например, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW).
Мощный инструмент для аналитики: Идеально подходят для ранжирования, расчета скользящих средних, кумулятивных сумм, сравнения значений с соседними строками или с агрегатами по группе.

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

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

Примеры использования:

-- 1. Ранжирование сотрудников по зарплате в каждом отделе
SELECT
    employee_id,
    department,
    salary,
    RANK() OVER(PARTITION BY department ORDER BY salary DESC) as rank_in_department
FROM
    employees;

-- 2. Расчет средней зарплаты по отделу для каждого сотрудника
SELECT
    employee_id,
    department,
    salary,
    AVG(salary) OVER(PARTITION BY department) as avg_dept_salary
FROM
    employees;

-- 3. Расчет кумулятивной суммы продаж по датам
SELECT
    sale_date,
    amount,
    SUM(amount) OVER(ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as cumulative_sum
FROM
    sales;

-- 4. Получение предыдущего значения (LAG)
SELECT
    order_id,
    order_date,
    total_amount,
    LAG(total_amount, 1, 0) OVER(ORDER BY order_date) as previous_order_amount
FROM
    orders;

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

Ответ 18+ 🔞

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

В чём, сука, соль и перец?

Детали не теряются! Это главный пиздец. Ты получаешь ВСЕ свои исходные строки, и к каждой из них прикручивается вычисленное значение. Ничего не схлопнулось, не пропало. Как будто ты каждому сотруднику в ведомость приписал среднюю зарплату по его отделу, и все довольны.
Окно — оно гибкое, как гимнастка. Определяется через OVER(). Туда можно впихнуть:
- PARTITION BY — это чтобы разбить данные на куски, как торт на день рождения. Вычисления идут в рамках каждого куска.
- ORDER BY — чтобы задать порядок, блядь, внутри этого куска торта. Кто первый, кто последний.
- ROWS/RANGE — это вообще магия, можно сказать: «считай мне от двух строк назад до текущей» или «от начала партиции и до сих пор». Мощь, ёбана!

На что они, сука, делятся?

Ранжирующие. Это те, которые нумеруют и сортируют. ROW_NUMBER() — просто номер по порядку. RANK() и DENSE_RANK() — для спортивных соревнований, где места могут делиться. NTILE() — чтобы разбить на, блядь, равные корзины, типа «отнесём этого клиента к топ-10%».
Агрегатные. Да-да, те же самые SUM, AVG, но... с прицепом! Они не сворачивают строки, а накладывают результат на каждую. Как будто каждый ученик в журнале видит не только свою оценку, но и средний балл по классу.
Функции смещения. Ну это вообще красота, чтобы смотреть по сторонам. LAG() — посмотреть, что было в предыдущей строке. LEAD() — заглянуть вперёд, на следующую. FIRST_VALUE() — увидеть самое первое значение в окне. Прямо как в кино, блядь, с перемоткой!

Смотри, как это выглядит вживую, ёпта:

-- 1. Кто в отделе самый жирный котяра по зарплате? Ранжируем, сука!
SELECT
    employee_id,
    department,
    salary,
    RANK() OVER(PARTITION BY department ORDER BY salary DESC) as rank_in_department
FROM
    employees;

-- 2. Каждому работяге покажем, насколько он беден относительно среднего по его конторе.
SELECT
    employee_id,
    department,
    salary,
    AVG(salary) OVER(PARTITION BY department) as avg_dept_salary
FROM
    employees;

-- 3. Накопительный итог продаж. Как деньги капают на счёт день за днём.
SELECT
    sale_date,
    amount,
    SUM(amount) OVER(ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as cumulative_sum
FROM
    sales;

-- 4. А что мы продали в прошлый раз? Заглянем назад одним глазком.
SELECT
    order_id,
    order_date,
    total_amount,
    LAG(total_amount, 1, 0) OVER(ORDER BY order_date) as previous_order_amount
FROM
    orders;

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