Оконные функции, общие табличные выражения, рекурсивные запросы
Оконные функции и общие табличные выражения (CTE) — мощные инструменты для аналитических запросов. В этой теме вы научитесь выполнять сложные вычисления без группировки в одну строку.
Оконная функция выполняет вычисления над набором строк, связанных с текущей строкой, без группировки в одну строку.
SELECT
column,
aggregate_function() OVER (
PARTITION BY partition_column
ORDER BY order_column
ROWS BETWEEN ...
) AS window_column
FROM table;-- GROUP BY: одна строка на группу
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department;
-- Результат: одна строка на департамент
-- Оконная функция: все строки + агрегат
SELECT
name,
department,
salary,
AVG(salary) OVER (PARTITION BY department) as avg_salary
FROM employees;
-- Результат: все строки + средняя по департаменту в каждой строкеSELECT
name,
department,
salary,
AVG(salary) OVER (PARTITION BY department) as avg_dept_salary,
SUM(salary) OVER (PARTITION BY department) as total_dept_salary,
COUNT(*) OVER (PARTITION BY department) as emp_count,
MIN(salary) OVER (PARTITION BY department) as min_dept_salary,
MAX(salary) OVER (PARTITION BY department) as max_dept_salary
FROM employees;-- Уникальный номер строки в рамках окна
SELECT
name,
department,
salary,
ROW_NUMBER() OVER (
PARTITION BY department
ORDER BY salary DESC
) as rank_in_dept
FROM employees;
-- Результат:
-- name | department | salary | rank_in_dept
-- Alice | Sales | 5000 | 1
-- Bob | Sales | 4000 | 2
-- Carol | Sales | 3000 | 3SELECT
name,
salary,
RANK() OVER (ORDER BY salary DESC) as rank,
DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank,
ROW_NUMBER() OVER (ORDER BY salary DESC) as row_num
FROM employees;
-- Результат:
-- name | salary | rank | dense_rank | row_num
-- Alice | 5000 | 1 | 1 | 1
-- Bob | 5000 | 1 | 1 | 2 ← одинаковая зарплата
-- Carol | 4000 | 3 | 2 | 3 ← RANK пропускает 2Разница:
RANK — пропускает номера при одинаковых значениях (1, 1, 3, 4)DENSE_RANK — без пропусков (1, 1, 2, 3)ROW_NUMBER — всегда уникальные номера (1, 2, 3, 4)-- Разделение на N групп (квантилей)
SELECT
name,
salary,
NTILE(4) OVER (ORDER BY salary DESC) as quartile
FROM employees;
-- Результат: сотрудники разделены на 4 группы по зарплате
-- quartile = 1 — топ 25% по зарплате
-- quartile = 4 — нижние 25%-- Доступ к предыдущей/следующей строке
SELECT
date,
sales,
LAG(sales, 1) OVER (ORDER BY date) as prev_day_sales,
LEAD(sales, 1) OVER (ORDER BY date) as next_day_sales,
LAG(sales, 7) OVER (ORDER BY date) as week_ago_sales,
sales - LAG(sales, 1) OVER (ORDER BY date) as daily_change
FROM daily_sales;
-- Расчёт процентного изменения
SELECT
date,
sales,
ROUND(
100.0 * (sales - LAG(sales) OVER (ORDER BY date))
/ LAG(sales) OVER (ORDER BY date),
2
) as percent_change
FROM daily_sales;SELECT
name,
department,
salary,
FIRST_VALUE(salary) OVER (
PARTITION BY department
ORDER BY salary DESC
) as highest_salary,
LAST_VALUE(salary) OVER (
PARTITION BY department
ORDER BY salary DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) as lowest_salary
FROM employees;Важно: Для
LAST_VALUEнужно явно указатьROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, иначе окно будет от начала до текущей строки.
SELECT
name,
salary,
PERCENT_RANK() OVER (ORDER BY salary) as percent_rank,
CUME_DIST() OVER (ORDER BY salary) as cumulative_dist
FROM employees;
-- PERCENT_RANK: относительный ранг (0.0 до 1.0)
-- CUME_DIST: кумулятивное распределение-- Разделение на партиции (группы)
SELECT
name,
department,
salary,
AVG(salary) OVER (PARTITION BY department) as avg_salary
FROM employees;
-- Средняя считается отдельно для каждого департамента-- Накопительный итог
SELECT
date,
sales,
SUM(sales) OVER (ORDER BY date) as running_total
FROM daily_sales;
-- Результат:
-- date | sales | running_total
-- 2026-01-01 | 100 | 100
-- 2026-01-02 | 150 | 250
-- 2026-01-03 | 200 | 450-- Скользящее среднее за 7 дней
SELECT
date,
sales,
AVG(sales) OVER (
ORDER BY date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) as moving_avg_7d
FROM daily_sales;
-- Другие варианты:
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -- от начала до текущей
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING -- от текущей до конца
ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING -- ±3 строки
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING -- предыдущая и следующая-- RANGE работает со значениями, а не строками
SELECT
date,
sales,
SUM(sales) OVER (
ORDER BY date
RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW
) as weekly_total
FROM daily_sales;-- Топ-3 сотрудника по зарплате в каждом департаменте
SELECT * FROM (
SELECT
name,
department,
salary,
DENSE_RANK() OVER (
PARTITION BY department
ORDER BY salary DESC
) as rank
FROM employees
) ranked
WHERE rank <= 3;-- Месячные продажи с сравнением год к году
SELECT
year,
month,
sales,
LAG(sales, 12) OVER (ORDER BY year, month) as prev_year_sales,
ROUND(
100.0 * (sales - LAG(sales, 12) OVER (ORDER BY year, month))
/ LAG(sales, 12) OVER (ORDER BY year, month),
2
) as yoy_growth
FROM monthly_sales;-- Накопительная сумма продаж по менеджерам
SELECT
manager,
sale_date,
amount,
SUM(amount) OVER (
PARTITION BY manager
ORDER BY sale_date
) as running_total
FROM sales;-- Отклонение зарплаты от средней по департаменту
SELECT
name,
department,
salary,
AVG(salary) OVER (PARTITION BY department) as avg_salary,
salary - AVG(salary) OVER (PARTITION BY department) as diff_from_avg,
ROUND(
100.0 * (salary - AVG(salary) OVER (PARTITION BY department))
/ AVG(salary) OVER (PARTITION BY department),
2
) as percent_diff
FROM employees;-- Определение сессий по времени между событиями
WITH events_with_gaps AS (
SELECT
user_id,
event_time,
event_type,
EXTRACT(EPOCH FROM (
event_time - LAG(event_time) OVER (
PARTITION BY user_id ORDER BY event_time
)
)) as gap_seconds
FROM user_events
),
sessions AS (
SELECT
*,
CASE WHEN gap_seconds > 1800 OR gap_seconds IS NULL
THEN 1 ELSE 0
END as is_new_session
FROM events_with_gaps
),
session_ids AS (
SELECT
*,
SUM(is_new_session) OVER (
PARTITION BY user_id ORDER BY event_time
) as session_id
FROM sessions
)
SELECT * FROM session_ids;CTE — временный именованный результат запроса, доступный в основном запросе.
WITH cte_name AS (
SELECT column1, column2
FROM table
WHERE condition
)
SELECT * FROM cte_name;-- Без CTE (повторение подзапроса)
SELECT
(SELECT AVG(salary) FROM employees) as avg_salary,
(SELECT MAX(salary) FROM employees) as max_salary,
(SELECT MIN(salary) FROM employees) as min_salary;
-- С CTE (читаемее)
WITH stats AS (
SELECT AVG(salary) as avg_sal,
MAX(salary) as max_sal,
MIN(salary) as min_sal
FROM employees
)
SELECT avg_sal, max_sal, min_sal FROM stats;WITH
dept_stats AS (
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
),
emp_with_avg AS (
SELECT
e.name,
e.department,
e.salary,
d.avg_salary as dept_avg
FROM employees e
JOIN dept_stats d ON e.department = d.department
)
SELECT * FROM emp_with_avg
WHERE salary > dept_avg;WITH RECURSIVE cte_name AS (
-- Базовый случай (якорь)
SELECT initial_query
UNION ALL
-- Рекурсивный случай
SELECT recursive_query
FROM cte_name
WHERE termination_condition
)
SELECT * FROM cte_name;WITH RECURSIVE org_chart AS (
-- Якорь: генеральный директор (без начальника)
SELECT
id,
name,
manager_id,
1 as level,
name as path
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Рекурсия: подчинённые
SELECT
e.id,
e.name,
e.manager_id,
oc.level + 1,
oc.path || ' → ' || e.name
FROM employees e
JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart
ORDER BY path;WITH RECURSIVE numbers AS (
SELECT 1 as n
UNION ALL
SELECT n + 1 FROM numbers WHERE n < 100
)
SELECT * FROM numbers;WITH RECURSIVE paths AS (
-- Начальная точка
SELECT
start_node,
end_node,
ARRAY[start_node, end_node] as path,
1 as depth
FROM edges
WHERE start_node = 'A'
UNION ALL
-- Рекурсивное расширение пути
SELECT
p.start_node,
e.end_node,
p.path || e.end_node,
p.depth + 1
FROM paths p
JOIN edges e ON p.end_node = e.start_node
WHERE e.end_node != ALL(p.path) -- избегаем циклов
AND p.depth < 10 -- ограничение глубины
)
SELECT * FROM paths WHERE end_node = 'Z';WITH RECURSIVE split(id, part, rest) AS (
-- Якорь: первая часть
SELECT
id,
SPLIT_PART(tags, ',', 1) as part,
SUBSTRING(tags FROM POSITION(',' IN tags) + 1) as rest
FROM articles
UNION ALL
-- Рекурсия: следующие части
SELECT
id,
SPLIT_PART(rest, ',', 1),
SUBSTRING(rest FROM POSITION(',' IN rest) + 1)
FROM split
WHERE rest != ''
)
SELECT id, part FROM split WHERE part != '';-- Медленно: коррелированный подзапрос
SELECT
e1.name,
e1.salary,
(SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.department = e1.department) as avg_salary
FROM employees e1;
-- Быстро: оконная функция
SELECT
name,
salary,
AVG(salary) OVER (PARTITION BY department) as avg_salary
FROM employees;В PostgreSQL 12+ CTE могут быть «inlined» (оптимизированы):
-- PostgreSQL 12+: CTE может быть inlined
WITH dept_avg AS (
SELECT department, AVG(salary) as avg_sal
FROM employees GROUP BY department
)
SELECT * FROM employees e
JOIN dept_avg d ON e.department = d.department;
-- Принудительная материализация (если нужно)
WITH dept_avg AS MATERIALIZED (
...
)Теперь вы владеете оконными функциями и CTE. В следующей теме вы изучите оптимизацию запросов — анализ планов выполнения с EXPLAIN и поиск узких мест.
Вопросы ещё не добавлены
Вопросы для этой подтемы ещё не добавлены.