Как писать продвинутые шаблоны SQL-запросов

О нас: Персональный сайт Тимофея Бугаевского и компании Зетка Интерактив

Руководства: Обширная коллекция технических руководств, написанных с точки зрения опытного разработчика. Каждая статья содержит подробные объяснения, практические примеры кода и паттерны для продакшена.

Базы данных: Администрирование баз данных, оптимизация и паттерны

SQL обманчиво прост: базовые запросы даются легко, но для решения сложных бизнес-задач требуются продвинутые шаблоны. От self-join до window functions — освоение этих техник меняет ваш подход к задачам работы с данными. В этом руководстве рассматриваются ключевые продвинутые шаблоны SQL с точки зрения senior-разработчика.

Почему важен продвинутый SQL

Освоение продвинутого SQL позволяет:

Производительность: переносить логику в базу данных, где это быстрее всего
Простота: заменять код приложения одиночными запросами
Надёжность: операции на уровне базы данных атомарны
Аналитика: сложная отчётность без выгрузки данных
Эффективность: сокращать количество сетевых round-trip

Коррелированные подзапросы

Получить Top N в каждой группе

Найти верхние 15% продаж по региону и неделе:

-- Шаблон для MS Access / MySQL
SELECT *
FROM sales_by_shops_weekly AS sbs
LEFT JOIN shops_count_weekly AS sc
ON sbs.group_id = sc.group_id
AND sbs.week = sc.week
WHERE (
SELECT COUNT(*)
FROM sales_by_shops_weekly AS sbs2
WHERE sbs2.sold > sbs.sold
AND sbs2.week = sbs.week
AND sbs2.group_id = sbs.group_id
) < sc.shops_count * 15 / 100;

Этот шаблон: 1. Считает, сколько строк имеют продажи выше, чем у текущей строки 2. Оставляет строки, где это количество меньше 15% от общего числа магазинов 3. Фактически возвращает лучших исполнителей из верхних 15%

Найти строки без соответствующих связанных записей

-- Найти клиентов без заказов
SELECT c.*
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.id
);
-- Альтернатива с использованием LEFT JOIN
SELECT c.*
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Window functions

Window functions выполняют вычисления по связанным строкам, не «схлопывая» их.

Накопительные итоги

SELECT
order_date,
amount,
SUM(amount) OVER (ORDER BY order_date) AS running_total,
SUM(amount) OVER (
PARTITION BY customer_id
ORDER BY order_date
) AS customer_running_total
FROM orders;

Ранжирование внутри групп

SELECT
department,
employee_name,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;

Различия:

RANK(): пропуски после равенств (1, 1, 3)
DENSE_RANK(): без пропусков (1, 1, 2)
ROW_NUMBER(): уникальные номера (1, 2, 3)

Сравнение с предыдущими/следующими строками

SELECT
sale_date,
amount,
LAG(amount, 1) OVER (ORDER BY sale_date) AS prev_amount,
LEAD(amount, 1) OVER (ORDER BY sale_date) AS next_amount,
amount - LAG(amount, 1) OVER (ORDER BY sale_date) AS change_from_prev
FROM daily_sales;

Первый/последний в группе

SELECT DISTINCT
department,
FIRST_VALUE(employee_name) OVER (
PARTITION BY department
ORDER BY hire_date
) AS first_hire,
LAST_VALUE(employee_name) OVER (
PARTITION BY department
ORDER BY hire_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS last_hire
FROM employees;

Common Table Expressions (CTE)

CTE улучшают читаемость и позволяют выполнять рекурсивные запросы.

Базовый CTE

WITH monthly_totals AS (
SELECT
DATE_TRUNC('month', order_date) AS month,
SUM(amount) AS total
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
),
monthly_growth AS (
SELECT
month,
total,
LAG(total) OVER (ORDER BY month) AS prev_total,
(total - LAG(total) OVER (ORDER BY month)) /
LAG(total) OVER (ORDER BY month) * 100 AS growth_pct
FROM monthly_totals
)
SELECT * FROM monthly_growth
WHERE growth_pct > 10;

Рекурсивный CTE для иерархий

-- Иерархия сотрудников
WITH RECURSIVE employee_tree 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,
et.level + 1,
et.path || ' > ' || e.name
FROM employees e
INNER JOIN employee_tree et ON e.manager_id = et.id
)
SELECT * FROM employee_tree ORDER BY path;

Рекурсивный CTE для диапазонов дат

-- Сгенерировать серию дат
WITH RECURSIVE date_series AS (
SELECT DATE '2024-01-01' AS date
UNION ALL
SELECT date + INTERVAL '1 day'
FROM date_series
WHERE date < DATE '2024-12-31'
)
SELECT
ds.date,
COALESCE(SUM(o.amount), 0) AS daily_total
FROM date_series ds
LEFT JOIN orders o ON DATE(o.order_date) = ds.date
GROUP BY ds.date
ORDER BY ds.date;

Восстановление кодировки

Когда у данных есть проблемы с кодировкой (часто встречается с кириллицей или азиатским текстом):

Восстановить cp1251 из UTF-8 mojibake

-- MySQL: исправить искажённый текст cp1251, сохранённый как UTF-8
SELECT
id,
CAST(
CONVERT(
CAST(CONVERT(text_column USING cp1251) AS BINARY)
USING utf8
)
AS CHAR CHARACTER SET cp1251
) COLLATE cp1251_general_ci AS fixed_text
FROM broken_table;
-- Обновить данные
UPDATE broken_table
SET text_column = CAST(
CONVERT(
CAST(CONVERT(text_column USING cp1251) AS BINARY)
USING utf8
)
AS CHAR CHARACTER SET cp1251
) COLLATE cp1251_general_ci
WHERE text_column LIKE '%Ð%'; -- Шаблон для mojibake

Поворот данных (Pivot)

Статический Pivot

-- PostgreSQL / MySQL
SELECT
product_id,
SUM(CASE WHEN month = 1 THEN amount ELSE 0 END) AS jan,
SUM(CASE WHEN month = 2 THEN amount ELSE 0 END) AS feb,
SUM(CASE WHEN month = 3 THEN amount ELSE 0 END) AS mar,
SUM(CASE WHEN month = 4 THEN amount ELSE 0 END) AS apr
FROM sales
GROUP BY product_id;
-- SQL Server с PIVOT
SELECT product_id, [1] AS jan, [2] AS feb, [3] AS mar, [4] AS apr
FROM (
SELECT product_id, month, amount
FROM sales
) AS source
PIVOT (
SUM(amount) FOR month IN ([1], [2], [3], [4])
) AS pivot_table;

Unpivot (столбцы в строки)

-- Преобразовать столбцы в строки
SELECT product_id, 'jan' AS month, jan AS amount FROM monthly_sales
UNION ALL
SELECT product_id, 'feb' AS month, feb AS amount FROM monthly_sales
UNION ALL
SELECT product_id, 'mar' AS month, mar AS amount FROM monthly_sales;
-- PostgreSQL с VALUES
SELECT
product_id,
month,
amount
FROM monthly_sales
CROSS JOIN LATERAL (
VALUES
('jan', jan),
('feb', feb),
('mar', mar)
) AS unpivot(month, amount);

Операции с JSON

PostgreSQL JSON-запросы

-- Запрос JSON-данных
SELECT
id,
data->>'name' AS name,
data->>'email' AS email,
(data->>'age')::int AS age
FROM users
WHERE data->>'status' = 'active';
-- Запрос вложенного JSON
SELECT
data->'address'->>'city' AS city,
data->'address'->>'zip' AS zip
FROM users;
-- Агрегировать в JSON
SELECT
department,
jsonb_agg(jsonb_build_object(
'name', name,
'salary', salary
)) AS employees
FROM employees
GROUP BY department;

MySQL JSON

SELECT
id,
JSON_EXTRACT(data, '$.name') AS name,
JSON_EXTRACT(data, '$.address.city') AS city
FROM users
WHERE JSON_EXTRACT(data, '$.active') = true;

Поиск «пробелов» и «островов» (Gap and Island)

Найти последовательные серии:

WITH numbered AS (
SELECT
sale_date,
amount,
sale_date - (ROW_NUMBER() OVER (ORDER BY sale_date))::int AS grp
FROM daily_sales
WHERE amount > 1000 -- Дни с высокими продажами
),
islands AS (
SELECT
grp,
MIN(sale_date) AS start_date,
MAX(sale_date) AS end_date,
COUNT(*) AS streak_length
FROM numbered
GROUP BY grp
)
SELECT * FROM islands
WHERE streak_length >= 5 -- Как минимум 5 последовательных дней
ORDER BY start_date;

Расчёт медианы

-- PostgreSQL
SELECT
department,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary
FROM employees
GROUP BY department;
-- MySQL 8+
SELECT
department,
AVG(salary) AS avg_salary,
(
SELECT AVG(sub.salary)
FROM (
SELECT salary,
ROW_NUMBER() OVER (ORDER BY salary) AS rn,
COUNT(*) OVER () AS cnt
FROM employees e2
WHERE e2.department = employees.department
) sub
WHERE sub.rn IN (FLOOR((cnt + 1) / 2), CEIL((cnt + 1) / 2))
) AS median_salary
FROM employees
GROUP BY department;

Шаблоны дедупликации

Оставить последнюю запись

-- С использованием ROW_NUMBER
WITH ranked AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY created_at DESC
) AS rn
FROM users
)
DELETE FROM users
WHERE id IN (
SELECT id FROM ranked WHERE rn > 1
);
-- С использованием self-join
DELETE u1 FROM users u1
INNER JOIN users u2
ON u1.email = u2.email
AND u1.created_at < u2.created_at;

Ключевые выводы

Window functions: мощный инструмент для аналитики без подзапросов
CTE для читаемости: разбивайте сложные запросы на шаги
Рекурсивные CTE: обрабатывайте иерархии и генерируйте серии
Коррелированные подзапросы: построчные вычисления, когда это необходимо
Осведомлённость о кодировках: кодировка базы данных имеет значение
Тестируйте производительность: EXPLAIN ANALYZE перед production

Продвинутые шаблоны SQL нередко заменяют сотни строк кода приложения одним запросом — инвестируйте время в их освоение, и ваша работа с данными преобразится.