CTE, рекурсивные CTE, подзапросы и GROUPING SETS

Аналитический SQL редко умещается в один плоский SELECT. Реальный запрос — это слои: посчитать промежуточный набор, опереться на него, агрегировать, сравнить несколькими разрезами. SQL даёт для этого структурные средства, и этот урок собирает четыре из них: CTE как способ именовать промежуточные результаты, рекурсивные CTE для иерархий, подзапросы и их виды, и расширения GROUP BY — GROUPING SETS, CUBE, ROLLUP — для многомерной агрегации одним запросом.

CTE: именованный промежуточный результат

CTE (Common Table Expression, обобщённое табличное выражение) — это именованный подзапрос, объявленный в начале запроса через WITH и используемый дальше как таблица.

WITH big_orders AS (
  SELECT custkey, totalprice
  FROM tpch.sf1.orders
  WHERE totalprice > 200000
)
SELECT custkey, count(*) AS cnt, sum(totalprice) AS total
FROM big_orders
GROUP BY custkey
ORDER BY total DESC
LIMIT 5;

CTE решает читаемость. Без WITH тот же запрос пришлось бы писать вложенным подзапросом в FROM, и при двух-трёх уровнях вложенности он становится нечитаемым «изнутри наружу». CTE раскладывает запрос на именованные шаги, которые читаются сверху вниз, как программа.

В одном WITH объявляют несколько CTE через запятую, и последующий может ссылаться на предыдущий — получается конвейер:

WITH
  staged AS (SELECT custkey, totalprice FROM tpch.sf1.orders WHERE orderstatus = 'F'),
  per_customer AS (SELECT custkey, sum(totalprice) AS rev FROM staged GROUP BY custkey)
SELECT custkey, rev FROM per_customer WHERE rev > 1000000;

Важное уточнение «до железа»: CTE в Trino — это не материализация и не кэш. Распространённое заблуждение: «CTE вычислится один раз, результат сохранится». На деле CTE для оптимизатора — это объявление логического подзапроса. Оптимизатор волен встроить его тело в место использования (inline) и решить заново, как его исполнять. Если один CTE используется дважды, по умолчанию его логика может быть вычислена дважды. CTE — инструмент структурирования и читаемости, а не способ «посчитать один раз». Когда повторное вычисление дорого, результат стоит явно материализовать во временную таблицу.

Рекурсивный CTE: обход иерархий

Обычный CTE ссылается на то, что объявлено до него. Рекурсивный CTE умеет ссылаться на самого себя — и это открывает обход иерархических структур: дерево сотрудников и руководителей, граф категорий, цепочка комплектующих. Объявляется через WITH RECURSIVE.

Рекурсивный CTE состоит из двух частей, соединённых UNION ALL:

• Anchor (база) — стартовый набор строк, не ссылающийся на сам CTE.
• Recursive (рекурсивный шаг) — запрос, который ссылается на CTE и вычисляет следующий «уровень» от уже полученных строк.

Исполнение: сначала считается anchor, затем рекурсивная часть применяется к результату предыдущей итерации, добавляя новые строки, и так пока очередная итерация не перестанет давать строки.

WITH RECURSIVE numbers(n) AS (
  SELECT 1                       -- anchor: старт
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 10   -- recursive: шаг
)
SELECT n FROM numbers;

 n
----
  1
  2
 ...
 10

Anchor дал строку n = 1. Рекурсивный шаг от 1 дал 2, от 2 дал 3, и так до условия n < 10, которое останавливает процесс. Получился ряд от 1 до 10.

Реальное применение — иерархия. Развернуть дерево «сотрудник -> руководитель» с уровнем вложенности:

WITH RECURSIVE org(emp, mgr, lvl) AS (
  SELECT emp, mgr, 1
  FROM employees
  WHERE mgr IS NULL                 -- anchor: верхушка иерархии
  UNION ALL
  SELECT e.emp, e.mgr, org.lvl + 1
  FROM employees e
  JOIN org ON e.mgr = org.emp       -- recursive: спуск на уровень ниже
)
SELECT emp, lvl FROM org ORDER BY lvl;

Подзапросы и их виды

Подзапрос — это запрос внутри запроса. По месту и роли подзапросы делятся на несколько видов, и различать их полезно, потому что исполняются они по-разному.

Скалярный подзапрос возвращает ровно одну строку и один столбец — одно значение — и используется там, где ожидается значение:

SELECT custkey, totalprice
FROM tpch.sf1.orders
WHERE totalprice > (SELECT avg(totalprice) FROM tpch.sf1.orders);

Подзапрос в IN / NOT IN возвращает столбец значений, с которым сверяется выражение:

SELECT * FROM tpch.sf1.customer
WHERE custkey IN (SELECT custkey FROM tpch.sf1.orders WHERE totalprice > 400000);

EXISTS / NOT EXISTS проверяет сам факт наличия хотя бы одной строки в подзапросе — возвращает BOOLEAN, само содержимое строк не важно.

Ключевое различие — коррелированный подзапрос против некоррелированного. Некоррелированный не зависит от внешнего запроса: его можно вычислить один раз. Коррелированный ссылается на столбцы внешней строки — он логически «пересчитывается» для каждой строки внешнего запроса:

-- коррелированный: подзапрос ссылается на o из внешнего запроса
SELECT o.orderkey, o.totalprice
FROM tpch.sf1.orders o
WHERE o.totalprice > (
  SELECT avg(o2.totalprice) FROM tpch.sf1.orders o2 WHERE o2.custkey = o.custkey
);

Здесь подзапрос для каждого заказа считает среднее по заказам того же клиента — ссылка o2.custkey = o.custkey и делает его коррелированным. Наивно это «N подзапросов на N строк». Но оптимизатор Trino применяет decorrelation: он переписывает коррелированный подзапрос в join или агрегацию, исполняемые единым набором операторов, без построчного прогона. Понимать корреляцию стоит именно поэтому: коррелированный подзапрос — это удобная запись, которую движок старается превратить в эффективный join; но не каждую корреляцию удаётся decorrelate, и тогда запрос будет дорогим.

GROUPING SETS, CUBE, ROLLUP: многомерная агрегация

Обычный GROUP BY группирует по одному фиксированному набору столбцов. Но отчёт часто требует несколько разрезов сразу: выручка по регионам, по категориям, по парам регион-категория, и общий итог — всё в одном результате. Наивно это UNION ALL из нескольких GROUP BY, каждый из которых заново сканирует таблицу. Расширения GROUP BY решают это одним проходом.

GROUPING SETS — явно перечисляет наборы столбцов группировки. Один запрос даёт результат сразу по всем перечисленным наборам:

SELECT region, category, sum(amount) AS total
FROM sales
GROUP BY GROUPING SETS ((region, category), (region), (category), ());

Этот запрос вернёт строки четырёх видов: по паре (region, category), только по region, только по category, и () — общий итог по всей таблице. В строках, где столбец не участвует в группировке, на его месте стоит NULL.

ROLLUP — сокращение для иерархии «справа налево»: ROLLUP(a, b, c) эквивалентен GROUPING SETS ((a,b,c),(a,b),(a),()). Это для естественных иерархий: год -> квартал -> месяц, страна -> регион -> город. ROLLUP(country, region, city) даёт детализацию по городам, промежуточные итоги по регионам, по странам и общий итог.

CUBE — все возможные комбинации столбцов: CUBE(a, b) эквивалентен GROUPING SETS ((a,b),(a),(b),()). Это для полной перекрёстной таблицы, когда нужны все разрезы без иерархии.

Чтобы отличить настоящий NULL в данных от NULL, который расширение поставило на месте неучаствующего столбца, есть функция grouping(col) — она возвращает 0, если столбец участвовал в группировке этой строки, и 1, если нет.

Почему это «до железа» важно. GROUPING SETS считает все разрезы за один проход по данным: таблица сканируется один раз, а агрегаты для разных наборов накапливаются параллельно. Альтернатива через UNION ALL из отдельных GROUP BY сканировала бы таблицу столько раз, сколько разрезов. На большой таблице разница — кратная.

Попробуй сам

На песочнице курса (Trino 481):

1. Перепишите вложенный запрос в CTE. Возьмите SELECT custkey, cnt FROM (SELECT custkey, count(*) AS cnt FROM tpch.sf1.orders GROUP BY custkey) WHERE cnt > 20; и оформите его через WITH. Объясните, почему версия с CTE читаемее, и почему при этом CTE не означает «вычислить и сохранить».

2. Напишите рекурсивный CTE, порождающий степени двойки до 1024: anchor — 1, рекурсивный шаг — удвоение с условием остановки. Укажите явно, какая строка является anchor, а какая — recursive, и что именно остановит рекурсию.

3. На таблице tpch.sf1.orders выполните SELECT orderstatus, orderpriority, count(*) FROM orders GROUP BY ROLLUP (orderstatus, orderpriority);. Найдите в результате строки промежуточных итогов и общий итог, объясните, где стоят NULL и почему, и сформулируйте, сколько раз была просканирована таблица.