В большинстве материалов по SQL обсуждаются знакомые всем конструкции — SELECT, JOIN, WHERE, группировки, простые индексы. Но когда вы обрабатываете сложные данные, оптимизируете производительность или строите аналитические отчёты, ограничиваться только базовыми операторами — значит добровольно усложнять себе жизнь. В SQL есть мощные, но редко упоминаемые функции, которые помогают решить задачи элегантно и эффективно.
В этой статье, основанной на личных экспериментах и наблюдениях, мы рассмотрим три таких инструмента: PIVOT/UNPIVOT, CROSS APPLY/LATERAL и частичные (filtered) индексы.
PIVOT и UNPIVOT: когда строки становятся столбцами и наоборот
Операторы PIVOT и UNPIVOT это сами по себе вендорские операторы. В PostgreSQL их нет, нужен crosstab из tablefunc. С их помощью можно преобразовать таблицу, поворачивая данные:
PIVOTпревращает уникальные значения столбца в отдельные столбцы, при этом выполняя агрегацию по оставшимся колонкам. В официальной документации MicrosoftPIVOTописывается как средство, которое вращает таблицу, превращая уникальные значения одного столбца в несколько столбцов выходного набора.UNPIVOTделает обратную операцию — превращает столбцы в строки. Документация говорит, что оператор поворачивает столбцы таблицы в значения столбца».
Но при этом повторное использование PIVOT и UNPIVOT в одном запросе может негативно влиять на производительность. Поэтому нужно понимание, где эти операторы дают выигрыш, а где лучше использовать другие техники.
Представим таблицу sales со столбцами product_id, month, amount. Нужно построить отчёт, где каждой строке соответствует товар, а в столбцах — продажи по месяцам. На чистом SQL это реализуется через PIVOT:
SELECT *
FROM (
SELECT product_id, month, amount
FROM sales
) AS src
PIVOT
(
SUM(amount)
FOR month IN ([Jan], [Feb], [Mar])
) AS pvt;Запрос превратит значения месяца в отдельные столбцы и посчитает сумму продаж. Раньше можно было писать подобные отчёты через серию CASE WHEN month = 'Jan' THEN amount ELSE 0 END, что выглядело громоздко. PIVOT делает код компактей.
Часто приходится расправлять таблицы, где в одной строке хранятся несколько однотипных значений. Например, таблица UserColors имеет колонки FavoriteColor1, FavoriteColor2, FavoriteColor3. Чтобы превратить её в нормализованный вид (одна строка — один цвет), существует оператор UNPIVOT:
SELECT UserID, FavoriteColor
FROM UserColors
UNPIVOT
(
FavoriteColor FOR ColorCol IN (FavoriteColor1, FavoriteColor2, FavoriteColor3)
) AS u;Однако синтаксис UNPIVOT может показаться неинтуитивным. На сайте MSSQLTips отмечают, что для UNPIVOT приходится придумывать виртуальное имя столбца, и эта история так себе, особенно когда результат должен содержать исходный ключ. В такой ситуации хорошо работает оператор CROSS APPLY с VALUES, который фактически выполняет ту же задачу, но проще читается:
SELECT u.UserID, v.FavoriteColor
FROM UserColors AS u
CROSS APPLY (
VALUES (u.FavoriteColor1),
(u.FavoriteColor2),
(u.FavoriteColor3)
) AS v(FavoriteColor);CROSS APPLY — это недооценённая возможность, одна из частых её применений — симуляция коррелированного подзапроса или разворот столбцов в строки. При этом запрос выполняет один проход по таблице и не содержит «магического» имени столбца, как в UNPIVOT.
CROSS APPLY и LATERAL: нестандартные соединения для сложных подзапросов
Оператор CROSS APPLY в SQL Server впервые появился в 2005 году, но до сих пор его редко используют. Он выполняет повторное вычисление подзапроса для каждой строки внешней таблицы. На MSSQLTips отмечают, что с помощью CROSS APPLY можно эмулировать коррелированный подзапрос, например, находить последний пост автора.
SELECT a.AuthorID, a.AuthorName, p.PostID
FROM Authors AS a
CROSS APPLY
(
SELECT MAX(PostID) AS PostID
FROM Posts
WHERE AuthorID = a.AuthorID
) AS p;Кроме того, CROSS APPLY позволяет строить конструкторы строк через VALUES, превращая несколько колонок в рядах.
В PostgreSQL аналогом CROSS APPLY является ключевое слово LATERAL. Документация Crunchy Data объясняет, что LATERAL позволяет подзапросу обращаться к столбцам таблиц из верхнего уровня запроса. Например, чтобы для каждой записи из accounts выбрать последнюю покупку, используется:
SELECT
accounts.id,
accounts.name,
last_purchase.*
FROM accounts
INNER JOIN LATERAL (
SELECT *
FROM purchases
WHERE account_id = accounts.id
ORDER BY created_at DESC
LIMIT 1
) AS last_purchase ON TRUE;Без LATERAL этот запрос выдал бы ошибку «invalid reference to FROM‑clause entry», поскольку подзапрос не видит внешнюю таблицу. LATERAL решает проблему и делает код читаемым.
LATERAL часто используют для работы с массивами и JSON. LATERAL позволяет распаковывать элементы JSON‑массива и фильтровать их по условию. Например, чтобы получить все адреса в Калифорнии из колонки addresses типа jsonb, можно написать:
SELECT
accounts.id,
accounts.name,
addr.value->>'state' AS state,
addr.value->>'city' AS city
FROM accounts,
LATERAL jsonb_array_elements(accounts.addresses) AS addr
WHERE addr.value->>'state' = 'California';Однако следует помнить, что APPLY/LATERAL работают как коррелированный цикл — подзапрос выполняется для каждой строки родительской таблицы. Для больших наборов данных это может оказаться медленнее, чем использование GROUP BY или предварительных агрегатов. Поэтому всегда проверяйте планы выполнения и экспериментируйте с вариантами.
Частичные и filtered‑индексы
Частичный индекс в Postgres содержит только строки, удовлетворяющие предикату. Его используют когда точно знают, какая подвыборка горячая. Оптимизатор использует такой индекс не потому, что текст предиката совпал, а когда из логики запроса можно вывести истинность предиката индекса.
-- Журнал обращений: исключаем внутреннюю /24
CREATE INDEX access_log_client_ip_ext
ON access_log (client_ip)
WHERE NOT (client_ip << inet '192.168.100.0/24');
-- Запрос, который триггерит индекс
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM access_log
WHERE NOT (client_ip << inet '192.168.100.0/24')
AND ts >= now() - interval '7 days';Если продукт читает почти всегда незавершённые заказы, индекс на этот срез уменьшит размер в разы и снимет нагрузку с записи.
-- Горячая подвыборка: «в работе»
CREATE INDEX orders_in_progress_idx
ON orders (customer_id, created_at DESC)
WHERE status = 'in_progress';В SQL Server filtered-индекс даёт те же плюсы, но добавляется параметризация. Когда значение приходит через параметр, планировщик может предпочесть скан, потому что не уверен в селективности. Для hotpath запросов помогает встраивание параметров в план.
-- Только один тип операций держим в индексе
CREATE INDEX IX_T ON dbo.StockItemTransactions(StockItemID)
WHERE TransactionTypeID = 12;
-- Встроить параметр в план на конкретный запуск
SELECT StockItemID, Quantity
FROM dbo.StockItemTransactions
WHERE TransactionTypeID = @tt
OPTION (RECOMPILE);Если запросов много и значения предсказуемы, иногда проще строить несколько узких filtered-индексов под топовые значения.
Вывод
Вместо того чтобы изобретать сложные конструкции, стоит знать эти возможности и использовать их там, где это действительно приносит пользу. Попробуйте применить хотя бы одну из описанных техник в своём проекте — и вы увидите, насколько быстрее могут стать ваши запросы, а код красивее.
Когда в базе царит хаос, SQL остаётся единственным инструментом, который позволяет навести порядок — но простых SELECT и парочки JOIN быстро перестаёт хватать. Если в работе регулярно приходится сводить данные, искать ошибки или проверять корректность вводимых значений, без дополнительных приёмов вы тратите лишние часы и нервы. Эти бесплатные уроки помогут закрыть самые болезненные пробелы и научат работать с данными уверенно:
8 сентября в 20:00 — SQL объединяет: как подружить таблицы между собой
23 сентября в 20:00 — SQL против бардака в данных: поиск по шаблону и регулярные выражения
Готовы проверить свои знания по SQL? Пройдите вступительное тестирование и узнайте, насколько уверенно вы себя чувствуете в теме.
Чтобы оставаться в курсе актуальных технологий и трендов, подписывайтесь на Telegram-канал OTUS.
