OVER, PARTITION BY, ROW_NUMBER, RANK, LAG, LEAD, фреймы
Оконные функции — это мощный инструмент SQL, который позволяет выполнять вычисления над набором строк, связанных с текущей строкой, не сворачивая результат в одну строку. Это ключевое отличие от обычных агрегатных функций.
💡 Главная идея: каждая строка сохраняет свою индивидуальность, но при этом получает доступ к данным из других строк окна.
функция OVER (
PARTITION BY столбцы_группировки
ORDER BY столбцы_сортировки
фрейм
)| Компонент | Описание | Обязательный |
|---|---|---|
| функция | Оконная функция (ROW_NUMBER, SUM, LAG и др.) | ✅ Да |
| OVER | Ключевое слово, определяющее окно | ✅ Да |
| PARTITION BY | Разбивает результат на группы (окна) | ❌ Нет |
| ORDER BY | Сортирует строки внутри окна | ❌ Нет* |
| фрейм | Определяет подмножество строк для вычисления | ❌ Нет |
*Требуется для функций, чувствительных к порядку (LAG, LEAD, FIRST_VALUE и др.)
Исходные данные:
┌─────────┬────────────┬────────┐
│ name │ department │ salary │
├─────────┼────────────┼────────┤
│ Alice │ IT │ 100000 │
│ Bob │ IT │ 80000 │
│ Carol │ Sales │ 90000 │
│ Dave │ Sales │ 70000 │
│ Eve │ IT │ 60000 │
└─────────┴────────────┴────────┘
PARTITION BY department → Разбиение на окна:
┌─────────────────────────────────────────┐
│ Окно: IT │
│ ┌─────────┬────────────┬────────┐ │
│ │ Alice │ IT │ 100000 │ │
│ │ Bob │ IT │ 80000 │ │
│ │ Eve │ IT │ 60000 │ │
│ └─────────┴────────────┴────────┘ │
└─────────────────────────────────────────┘
┌─────────────────────────────────────────┐
│ Окно: Sales │
│ ┌─────────┬────────────┬────────┐ │
│ │ Carol │ Sales │ 90000 │ │
│ │ Dave │ Sales │ 70000 │ │
│ └─────────┴────────────┴────────┘ │
└─────────────────────────────────────────┘
ORDER BY salary DESC → Сортировка внутри каждого окна:
IT: Alice (100k) → Bob (80k) → Eve (60k)
Sales: Carol (90k) → Dave (70k)
Результат с ROW_NUMBER():
┌─────────┬────────────┬────────┬────────────┐
│ name │ department │ salary │ row_number │
├─────────┼────────────┼────────┼────────────┤
│ Alice │ IT │ 100000 │ 1 │
│ Bob │ IT │ 80000 │ 2 │
│ Eve │ IT │ 60000 │ 3 │
│ Carol │ Sales │ 90000 │ 1 │
│ Dave │ Sales │ 70000 │ 2 │
└─────────┴────────────┴────────┴────────────┘
Это критически важный момент для понимания оконных функций.
SELECT city, COUNT(*) AS cnt
FROM users
GROUP BY city;Что происходит:
Результат:
┌─────────┬─────┐
│ city │ cnt │
├─────────┼─────┤
│ Moscow │ 2 │
│ London │ 2 │
│ Paris │ 1 │
└─────────┴─────┘
SELECT name, city,
COUNT(*) OVER (PARTITION BY city) AS city_count
FROM users;Что происходит:
Результат:
┌─────────┬─────────┬────────────┐
│ name │ city │ city_count │
├─────────┼─────────┼────────────┤
│ Alice │ Moscow │ 2 │
│ Bob │ Moscow │ 2 │
│ Carol │ London │ 2 │
│ Dave │ London │ 2 │
│ Eve │ Paris │ 1 │
└─────────┴─────────┴────────────┘
┌─────────────────────────────────────────────────────────────┐
│ АГРЕГАТНАЯ ФУНКЦИЯ │
├─────────────────────────────────────────────────────────────┤
│ SELECT city, AVG(salary) │
│ FROM employees │
│ GROUP BY city; │
│ │
│ Вход: 1000 строк → Выход: 10 строк (по одной на город) │
│ ❌ Нельзя увидеть имена сотрудников │
│ ❌ Нельзя сравнить зарплату сотрудника со средней в городе │
└─────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────┐
│ ОКОННАЯ ФУНКЦИЯ │
├─────────────────────────────────────────────────────────────┤
│ SELECT name, city, salary, │
│ AVG(salary) OVER (PARTITION BY city) AS avg_city │
│ FROM employees; │
│ │
│ Вход: 1000 строк → Выход: 1000 строк │
│ ✅ Видны все сотрудники │
│ ✅ Можно сравнить: salary / avg_city │
└─────────────────────────────────────────────────────────────┘
Эти функции присваивают каждой строке номер (ранг) в соответствии с её позицией в окне.
Присваивает уникальные последовательные номера начиная с 1.
SELECT
name,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;┌─────────┬────────┬───────────┐
│ name │ salary │ row_num │
├─────────┼────────┼───────────┤
│ Alice │ 100000 │ 1 │
│ Bob │ 100000 │ 2 │ ← Одинаковая зарплата, но номер 2
│ Carol │ 80000 │ 3 │
│ Dave │ 70000 │ 4 │
└─────────┴────────┴───────────┘
Применение:
Присваивает ранги с пропусками при одинаковых значениях.
SELECT
name,
salary,
RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;┌─────────┬────────┬──────┐
│ name │ salary │ rank │
├─────────┼────────┼──────┤
│ Alice │ 100000 │ 1 │
│ Bob │ 100000 │ 1 │ ← Одинаковый ранг
│ Carol │ 80000 │ 3 │ ← Пропуск: нет ранга 2
│ Dave │ 70000 │ 4 │
└─────────┴────────┴──────┘
Почему пропуск? После двух строк с рангом 1, следующая позиция — 3.
Применение:
Присваивает ранги без пропусков при одинаковых значениях.
SELECT
name,
salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;┌─────────┬────────┬────────────┐
│ name │ salary │ dense_rank │
├─────────┼────────┼────────────┤
│ Alice │ 100000 │ 1 │
│ Bob │ 100000 │ 1 │ ← Одинаковый ранг
│ Carol │ 80000 │ 2 │ ← Нет пропуска
│ Dave │ 70000 │ 3 │
└─────────┴────────┴────────────┘
Применение:
SELECT
name,
salary,
ROW_NUMBER() OVER w AS row_num,
RANK() OVER w AS rank,
DENSE_RANK() OVER w AS dense_rank
FROM employees
WINDOW w AS (ORDER BY salary DESC);┌─────────┬────────┬─────────┬──────┬────────────┐
│ name │ salary │ row_num │ rank │ dense_rank │
├─────────┼────────┼─────────┼──────┼────────────┤
│ Alice │ 100000 │ 1 │ 1 │ 1 │
│ Bob │ 100000 │ 2 │ 1 │ 1 │
│ Carol │ 100000 │ 3 │ 1 │ 1 │ ← Трое с одинаковой ЗП
│ Dave │ 80000 │ 4 │ 4 │ 2 │ ← RANK: 4, DENSE: 2
│ Eve │ 70000 │ 5 │ 5 │ 3 │
└─────────┴────────┴─────────┴──────┴────────────┘
WITH ranked_employees AS (
SELECT
name,
department,
salary,
DENSE_RANK() OVER (
PARTITION BY department
ORDER BY salary DESC
) AS dept_rank
FROM employees
)
SELECT name, department, salary
FROM ranked_employees
WHERE dept_rank <= 3;Позволяют обращаться к данным из других строк относительно текущей.
SELECT
date,
sales,
LAG(sales, 1) OVER (ORDER BY date) AS prev_sales
FROM daily_sales;Параметры:
LAG(столбец, offset, default_value)offset — на сколько строк назад (по умолчанию 1)default_value — значение если строки нет (по умолчанию NULL)┌────────────┬───────┬────────────┐
│ date │ sales │ prev_sales │
├────────────┼───────┼────────────┤
│ 2026-01-01 │ 100 │ NULL │ ← Нет предыдущей
│ 2026-01-02 │ 150 │ 100 │
│ 2026-01-03 │ 120 │ 150 │
│ 2026-01-04 │ 180 │ 120 │
│ 2026-01-05 │ 200 │ 180 │
└────────────┴───────┴────────────┘
SELECT
date,
sales,
LEAD(sales, 1) OVER (ORDER BY date) AS next_sales
FROM daily_sales;┌────────────┬───────┬────────────┐
│ date │ sales │ next_sales │
├────────────┼───────┼────────────┤
│ 2026-01-01 │ 100 │ 150 │
│ 2026-01-02 │ 150 │ 120 │
│ 2026-01-03 │ 120 │ 180 │
│ 2026-01-04 │ 180 │ 200 │
│ 2026-01-05 │ 200 │ NULL │ ← Нет следующей
└────────────┴───────┴────────────┘
SELECT
date,
sales,
sales - LAG(sales, 1, 0) OVER (ORDER BY date) AS change,
ROUND(
100.0 * (sales - LAG(sales, 1) OVER (ORDER BY date))
/ LAG(sales, 1) OVER (ORDER BY date),
2
) AS change_pct
FROM daily_sales;┌────────────┬───────┬────────┬─────────────┐
│ date │ sales │ change │ change_pct │
├────────────┼───────┼────────┼─────────────┤
│ 2026-01-01 │ 100 │ 100 │ NULL │
│ 2026-01-02 │ 150 │ +50 │ +50.00% │
│ 2026-01-03 │ 120 │ -30 │ -20.00% │
│ 2026-01-04 │ 180 │ +60 │ +50.00% │
│ 2026-01-05 │ 200 │ +20 │ +11.11% │
└────────────┴───────┴────────┴─────────────┘
SELECT
date,
sales,
LAG(sales, 1) OVER (ORDER BY date) AS prev_1d,
LAG(sales, 7) OVER (ORDER BY date) AS prev_7d,
LEAD(sales, 1) OVER (ORDER BY date) AS next_1d,
LEAD(sales, 7) OVER (ORDER BY date) AS next_7d
FROM daily_sales;Применение:
Возвращает первое значение в окне.
SELECT
name,
salary,
FIRST_VALUE(name) OVER (
ORDER BY salary DESC
) AS highest_paid,
FIRST_VALUE(salary) OVER (
ORDER BY salary DESC
) AS highest_salary
FROM employees;┌─────────┬────────┬───────────────┬────────────────┐
│ name │ salary │ highest_paid │ highest_salary │
├─────────┼────────┼───────────────┼────────────────┤
│ Alice │ 100000 │ Alice │ 100000 │
│ Bob │ 90000 │ Alice │ 100000 │
│ Carol │ 80000 │ Alice │ 100000 │
│ Dave │ 70000 │ Alice │ 100000 │
└─────────┴────────┴───────────────┴────────────────┘
Возвращает последнее значение в окне, но требует явного указания фрейма!
SELECT
name,
salary,
LAST_VALUE(name) OVER (
ORDER BY salary DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS lowest_paid
FROM employees;┌─────────┬────────┬───────────────┐
│ name │ salary │ lowest_paid │
├─────────┼────────┼───────────────┤
│ Alice │ 100000 │ Dave │
│ Bob │ 90000 │ Dave │
│ Carol │ 80000 │ Dave │
│ Dave │ 70000 │ Dave │
└─────────┴────────┴───────────────┘
Фрейм по умолчанию для оконных функций:
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
Это означает: «от начала окна до текущей строки».
Без явного фрейма (НЕПРАВИЛЬНО):
┌─────────┬────────┬─────────────────────────┐
│ name │ salary │ LAST_VALUE (ошибочно) │
├─────────┼────────┼─────────────────────────┤
│ Alice │ 100000 │ Alice ← до текущей │
│ Bob │ 90000 │ Bob ← до текущей │
│ Carol │ 80000 │ Carol ← до текущей │
│ Dave │ 70000 │ Dave ← до текущей │
└─────────┴────────┴─────────────────────────┘
С явным фреймом (ПРАВИЛЬНО):
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
┌─────────┬────────┬─────────────────────────┐
│ name │ salary │ LAST_VALUE (верно) │
├─────────┼────────┼─────────────────────────┤
│ Alice │ 100000 │ Dave ← всё окно │
│ Bob │ 90000 │ Dave ← всё окно │
│ Carol │ 80000 │ Dave ← всё окно │
│ Dave │ 70000 │ Dave ← всё окно │
└─────────┴────────┴─────────────────────────┘
Возвращает N-ное значение в окне (PostgreSQL 15+):
SELECT
name,
salary,
NTH_VALUE(name, 2) OVER (
ORDER BY salary DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS second_highest
FROM employees;Любая агрегатная функция может стать оконной!
SELECT
name,
salary,
SUM(salary) OVER () AS total_salary,
AVG(salary) OVER () AS avg_salary,
MIN(salary) OVER () AS min_salary,
MAX(salary) OVER () AS max_salary,
COUNT(*) OVER () AS employee_count
FROM employees;┌─────────┬────────┬──────────────┬────────────┬────────────┬────────────┬────────────────┐
│ name │ salary │ total_salary │ avg_salary │ min_salary │ max_salary │ employee_count │
├─────────┼────────┼──────────────┼────────────┼────────────┼────────────┼────────────────┤
│ Alice │ 100000 │ 340000 │ 85000 │ 70000 │ 100000 │ 4 │
│ Bob │ 90000 │ 340000 │ 85000 │ 70000 │ 100000 │ 4 │
│ Carol │ 80000 │ 340000 │ 85000 │ 70000 │ 100000 │ 4 │
│ Dave │ 70000 │ 340000 │ 85000 │ 70000 │ 100000 │ 4 │
└─────────┴────────┴──────────────┴────────────┴────────────┴────────────┴────────────────┘
SELECT
name,
salary,
ROUND(
100.0 * salary / SUM(salary) OVER (),
2
) AS pct_of_total
FROM employees;┌─────────┬────────┬───────────────┐
│ name │ salary │ pct_of_total │
├─────────┼────────┼───────────────┤
│ Alice │ 100000 │ 29.41% │
│ Bob │ 90000 │ 26.47% │
│ Carol │ 80000 │ 23.53% │
│ Dave │ 70000 │ 20.59% │
└─────────┴────────┴───────────────┘
SELECT
name,
department,
salary,
AVG(salary) OVER (
PARTITION BY department
) AS dept_avg_salary,
salary - AVG(salary) OVER (
PARTITION BY department
) AS diff_from_avg
FROM employees;┌─────────┬────────────┬────────┬─────────────────┬──────────────────┐
│ name │ department │ salary │ dept_avg_salary │ diff_from_avg │
├─────────┼────────────┼────────┼─────────────────┼──────────────────┤
│ Alice │ IT │ 100000 │ 80000 │ +20000 │
│ Bob │ IT │ 80000 │ 80000 │ 0 │
│ Eve │ IT │ 60000 │ 80000 │ -20000 │
│ Carol │ Sales │ 90000 │ 80000 │ +10000 │
│ Dave │ Sales │ 70000 │ 80000 │ -10000 │
└─────────┴────────────┴────────┴─────────────────┴──────────────────┘
Фрейм определяет подмножество строк внутри окна, над которыми выполняется вычисление.
{ROWS | RANGE} BETWEEN <начало> AND <конец>| Ключевое слово | Описание |
|---|---|
UNBOUNDED PRECEDING | От первой строки окна |
UNBOUNDED FOLLOWING | До последней строки окна |
CURRENT ROW | Текущая строка |
N PRECEDING | N строк перед текущей |
N FOLLOWING | N строк после текущей |
ROWS — работает с физическими строками:
-- 3 строки перед текущей + текущая
ROWS BETWEEN 3 PRECEDING AND CURRENT ROWRANGE — работает с логическими значениями (требует ORDER BY):
-- Все строки в диапазоне 7 дней до текущей даты
RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROWSELECT
date,
sales,
AVG(sales) OVER (
ORDER BY date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS moving_avg_7d
FROM daily_sales;┌────────────┬───────┬───────────────┐
│ date │ sales │ moving_avg_7d │
├────────────┼───────┼───────────────┤
│ 01.01 │ 100 │ 100.0 │ ← только 1 строка
│ 02.01 │ 150 │ 125.0 │ ← 2 строки
│ 03.01 │ 120 │ 123.3 │ ← 3 строки
│ 04.01 │ 180 │ 137.5 │ ← 4 строки
│ 05.01 │ 160 │ 142.0 │ ← 5 строк
│ 06.01 │ 140 │ 141.7 │ ← 6 строк
│ 07.01 │ 200 │ 150.0 │ ← 7 строк (полное окно)
│ 08.01 │ 220 │ 167.1 │ ← 7 строк (скользит)
└────────────┴───────┴───────────────┘
SELECT
date,
sales,
SUM(sales) OVER (
ORDER BY date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM daily_sales;┌────────────┬───────┬───────────────┐
│ date │ sales │ running_total │
├────────────┼───────┼───────────────┤
│ 01.01 │ 100 │ 100 │
│ 02.01 │ 150 │ 250 │
│ 03.01 │ 120 │ 370 │
│ 04.01 │ 180 │ 550 │
│ 05.01 │ 200 │ 750 │
└────────────┴───────┴───────────────┘
Дано 10 строк, текущая строка — 5:
UNBOUNDED PRECEDING AND CURRENT ROW
├───────────────┤ ← фрейм
● ● ● ● [5] 6 7 8 9 10
3 PRECEDING AND CURRENT ROW
├───────┤ ← фрейм
1 2 3 [4] [5] 6 7 8 9 10
CURRENT ROW AND 2 FOLLOWING
├───────┤ ← фрейм
1 2 3 4 [5] [6] [7] 8 9 10
UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
├───────────────────────────┤ ← фрейм (всё окно)
● ● ● ● [5] ● ● ● ● ●
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
├───────┤ ← фрейм
1 2 3 4 [5] 6 7 8 9 10
Разбивает строки окна на N примерно равных групп.
NTILE(N) OVER (ORDER BY столбец)SELECT
name,
salary,
NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;┌─────────┬────────┬──────────┐
│ name │ salary │ quartile │
├─────────┼────────┼──────────┤
│ Alice │ 100000 │ 1 │ ← Верхние 25%
│ Bob │ 95000 │ 1 │
│ Carol │ 90000 │ 1 │
│ Dave │ 85000 │ 2 │ ← 25-50%
│ Eve │ 80000 │ 2 │
│ Frank │ 75000 │ 2 │
│ Grace │ 70000 │ 3 │ ← 50-75%
│ Henry │ 65000 │ 3 │
│ Ivy │ 60000 │ 4 │ ← Нижние 25%
│ Jack │ 55000 │ 4 │
└─────────┴────────┴──────────┘
-- Децили (10 групп)
SELECT
name,
salary,
NTILE(10) OVER (ORDER BY salary DESC) AS decile
FROM employees;
-- Процентили (100 групп)
SELECT
name,
salary,
NTILE(100) OVER (ORDER BY salary DESC) AS percentile
FROM employees;-- Топ-3 покупателя в каждом городе
WITH ranked_users AS (
SELECT
u.name,
u.city,
SUM(o.total_amount) AS total_spent,
ROW_NUMBER() OVER (
PARTITION BY u.city
ORDER BY SUM(o.total_amount) DESC
) AS rn
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name, u.city
)
SELECT name, city, total_spent
FROM ranked_users
WHERE rn <= 3;SELECT
month,
revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_month,
ROUND(
100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
/ LAG(revenue) OVER (ORDER BY month),
2
) AS growth_pct
FROM (
SELECT
DATE_TRUNC('month', order_date)::date AS month,
SUM(total_amount) AS revenue
FROM orders
GROUP BY 1
) monthly_revenue
ORDER BY month;┌────────────┬──────────┬────────────┬─────────────┐
│ month │ revenue │ prev_month │ growth_pct │
├────────────┼──────────┼────────────┼─────────────┤
│ 2026-01-01 │ 1000000 │ NULL │ NULL │
│ 2026-02-01 │ 1200000 │ 1000000 │ +20.00% │
│ 2026-03-01 │ 1150000 │ 1200000 │ -4.17% │
│ 2026-04-01 │ 1400000 │ 1150000 │ +21.74% │
└────────────┴──────────┴────────────┴─────────────┘
-- Оставить только первую запись для каждого email
WITH ranked AS (
SELECT
id,
email,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY created_at
) AS rn
FROM users
)
DELETE FROM users
WHERE id IN (
SELECT id FROM ranked WHERE rn > 1
);-- Найти продажи, отклоняющиеся более чем на 2 стандартных отклонения
WITH stats AS (
SELECT
date,
sales,
AVG(sales) OVER () AS avg_sales,
STDDEV(sales) OVER () AS stddev_sales
FROM daily_sales
)
SELECT
date,
sales,
avg_sales,
stddev_sales,
ROUND((sales - avg_sales) / stddev_sales, 2) AS z_score
FROM stats
WHERE ABS((sales - avg_sales) / stddev_sales) > 2;-- Ранг внутри ранга: топ-3 отдела в каждой категории
WITH ranked AS (
SELECT
category,
department,
SUM(revenue) AS total_revenue,
RANK() OVER (
PARTITION BY category
ORDER BY SUM(revenue) DESC
) AS category_rank
FROM sales
GROUP BY category, department
)
SELECT *
FROM ranked
WHERE category_rank <= 3;SELECT
stage,
COUNT(*) AS users_count,
ROUND(
100.0 * COUNT(*) / FIRST_VALUE(COUNT(*)) OVER (
ORDER BY stage_order
),
1
) AS conversion_pct
FROM (
SELECT
stage,
CASE stage
WHEN 'visit' THEN 1
WHEN 'signup' THEN 2
WHEN 'trial' THEN 3
WHEN 'purchase' THEN 4
END AS stage_order,
COUNT(*) AS cnt
FROM user_funnel
GROUP BY stage
) sub
ORDER BY stage_order;┌──────────┬───────────────┬─────────────────┐
│ stage │ users_count │ conversion_pct │
├──────────┼───────────────┼─────────────────┤
│ visit │ 10000 │ 100.0% │
│ signup │ 3000 │ 30.0% │
│ trial │ 1500 │ 15.0% │
│ purchase │ 500 │ 5.0% │
└──────────┴───────────────┴─────────────────┘
✅ Используйте именованные окна для переиспользования:
-- Плохо: повторяющееся определение окна
SELECT
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn,
SUM(salary) OVER (PARTITION BY dept ORDER BY salary) AS running_sum
FROM employees;
-- Хорошо: одно определение окна
SELECT
ROW_NUMBER() OVER w AS rn,
SUM(salary) OVER w AS running_sum
FROM employees
WINDOW w AS (PARTITION BY dept ORDER BY salary DESC);✅ Фильтруйте после вычисления оконной функции:
-- Невозможно: WHERE с оконной функцией
SELECT * FROM (
SELECT name, ROW_NUMBER() OVER (ORDER BY salary) AS rn
FROM employees
) WHERE rn <= 10; -- ✅ Правильно
-- Нельзя: SELECT ... WHERE ROW_NUMBER() OVER ... <= 10 ❌✅ Избегайте лишних PARTITION BY:
-- Медленнее: лишнее партиционирование
SELECT
name,
SUM(salary) OVER (PARTITION BY 1) AS total -- ❌
-- Быстрее: без PARTITION BY
SELECT
name,
SUM(salary) OVER () AS total -- ✅PostgreSQL использует индексы для:
CREATE INDEX idx_employees_dept_salary
ON employees (department, salary DESC);
-- Запрос будет использовать индекс:
SELECT
name,
department,
salary,
ROW_NUMBER() OVER (
PARTITION BY department
ORDER BY salary DESC
) AS rn
FROM employees;| Ошибка | Решение |
|---|---|
| LAST_VALUE возвращает текущую строку | Добавьте ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING |
| NULL в LAG/LEAD для первой/последней строки | Используйте третий параметр: LAG(col, 1, 0) |
| Неправильный порядок в ранжировании | Проверьте ORDER BY в OVER() |
| Медленные запросы на больших данных | Добавьте индексы по PARTITION BY и ORDER BY |
✅ Идеально подходят:
❌ Лучше альтернативы:
| Что нужно? | Функция |
|---|---|
| Уникальные номера | ROW_NUMBER() |
| Ранг с пропусками | RANK() |
| Ранг без пропусков | DENSE_RANK() |
| Предыдущее значение | LAG() |
| Следующее значение | LEAD() |
| Первое в окне | FIRST_VALUE() |
| Последнее в окне | LAST_VALUE() + фрейм |
| Разбить на группы | NTILE(N) |
| Сумма/среднее в окне | SUM()/AVG() OVER() |
| Накопительный итог | SUM() OVER (ORDER BY ...) |
| Скользящее среднее | AVG() OVER (ROWS BETWEEN N PRECEDING AND CURRENT ROW) |
-- Всё окно
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
-- От начала до текущей строки (по умолчанию)
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
-- От текущей до конца
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
-- N строк перед текущей
ROWS BETWEEN N PRECEDING AND CURRENT ROW
-- Скользящее окно N строк
ROWS BETWEEN (N-1) PRECEDING AND CURRENT ROWОконные функции — это мощнейший инструмент аналитики в SQL. Они позволяют:
Практикуйтесь на реальных данных, и оконные функции станут вашим любимым инструментом!
Следующий шаг: Проверьте знания в разделе вопросов по оконным функциям!
Вопросы ещё не добавлены
Вопросы для этой подтемы ещё не добавлены.