В большинстве материалов по SQL обсуждаются знакомые всем конструкции — SELECT, JOIN, WHERE, группировки, простые индексы. Но когда вы обрабатываете сложные данные, оптимизируете производительность или строите аналитические отчёты, ограничиваться только базовыми операторами — значит добровольно усложнять себе жизнь. В SQL есть мощные, но редко упоминаемые функции, которые помогают решить задачи элегантно и эффективно.

В этой статье, основанной на личных экспериментах и наблюдениях, мы рассмотрим три таких инструмента: PIVOT/UNPIVOT, CROSS APPLY/LATERAL и частичные (filtered) индексы.

PIVOT и UNPIVOT: когда строки становятся столбцами и наоборот

Операторы PIVOT и UNPIVOT это сами по себе вендорские операторы. В PostgreSQL их нет, нужен crosstab из tablefunc. С их помощью можно преобразовать таблицу, поворачивая данные:

• PIVOT превращает уникальные значения столбца в отдельные столбцы, при этом выполняя агрегацию по оставшимся колонкам. В официальной документации Microsoft PIVOT описывается как средство, которое вращает таблицу, превращая уникальные значения одного столбца в несколько столбцов выходного набора.

• 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.