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

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

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

Основной синтаксис

FUNCTION() OVER (PARTITION BY col ORDER BY col ROWS/RANGE frame)

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

ROW_NUMBER()

Уникальный номер каждой строки.

SELECT salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;

RANK()

Ранг с пропусками при одинаковых значениях.

SELECT salary, RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

DENSE_RANK()

Ранг без пропусков.

SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

PERCENT_RANK()

Относительный ранг (0-1).

NTILE(n)

Делит на N равных групп.

SELECT salary, NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees;

Функции смещения

LAG() и LEAD()

Предыдущее и следующее значение.

SELECT date, revenue, LAG(revenue) OVER (ORDER BY date) AS prev
FROM sales;

FIRST_VALUE() и LAST_VALUE()

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

SELECT salary, FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY hire_date)
FROM employees;

Агрегирующие функции с OVER

SUM с окном (running total)

SELECT date, revenue, SUM(revenue) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative
FROM sales;

AVG для скользящего среднего

SELECT date, revenue, AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7days
FROM sales;

COUNT над окном

SELECT customer_id, COUNT(*) OVER (PARTITION BY customer_id) AS customer_orders
FROM orders;

Типы окон

ROWS (физические строки)

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW — текущая и 2 выше
ROWS UNBOUNDED PRECEDING AND CURRENT ROW — от начала до текущей
ROWS UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING — все строки

RANGE (логические значения)

RANGE BETWEEN INTERVAL 1 DAY PRECEDING AND CURRENT ROW

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

Топ 3 продукта в каждой категории

WITH ranked AS (
    SELECT category, product_name, sales,
           ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rank
    FROM products
)
SELECT * FROM ranked WHERE rank <= 3;

Процент от общей суммы

SELECT employee_id, salary,
       ROUND(100.0 * salary / SUM(salary) OVER (), 2) AS percentage
FROM employees;

Разница от среднего по отделу

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

Кумулятивный доход

SELECT date, revenue,
       SUM(revenue) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative
FROM sales
ORDER BY date;

Месячный рост

SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_month,
       ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month) * 100, 2) AS growth_percent
FROM monthly_sales;

Дублирующиеся email

WITH email_count AS (
    SELECT email, COUNT(*) OVER (PARTITION BY email) AS cnt
    FROM users
)
SELECT DISTINCT email FROM email_count WHERE cnt > 1;

Производительность

Оконные функции обычно быстрее чем самоприсоединения и коррелированные подзапросы.

Оптимизация:

• Индекс на PARTITION BY колонке
• Индекс на ORDER BY колонке
• EXPLAIN ANALYZE для анализа

Поддержка БД

• PostgreSQL: полная поддержка
• MySQL 8.0+: поддерживает
• SQL Server: поддерживает
• SQLite 3.25+: поддерживает
• Oracle: поддерживает

Значение для System Analyst

System Analyst должен:

• Понимать когда использовать оконные функции
• Рекомендовать эффективные подходы для аналитики
• Оптимизировать сложные аналитические запросы
• Документировать требования для аналитики