Функции и операторы: агрегатные и оконные функции

SQL — это не только SELECT ... FROM ... WHERE. Аналитическая мощь языка — в функциях, которые превращают строки в показатели. Trino несёт большую библиотеку функций, и для аналитики важны три их класса: скалярные, агрегатные и оконные. Третий класс — оконные функции — джуниоры понимают хуже всего, а именно он отличает аналитика, который умеет в SQL, от того, кто умеет только GROUP BY.

Этот урок — про различие трёх классов, про устройство конструкции OVER, про ranking-функции и про то, что происходит внутри движка, когда вы пишете оконный запрос.

Три класса функций: сколько строк на входе и на выходе

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

Скалярная функция работает построчно и не схлопывает строки: lower('ПРИВЕТ') даёт 'привет' в той же строке. Агрегатная — схлопывает: GROUP BY собирает строки в группы, и sum отдаёт одно число на группу, строки группы исчезают из результата.

Оконная функция — самое интересное. Она, как агрегатная, смотрит на множество строк (окно), но, в отличие от агрегатной, не схлопывает их: каждая строка остаётся в результате, и функция приписывает ей значение, посчитанное по её окну. Это даёт то, чего GROUP BY дать не может: показатель по группе рядом с детальными строками этой группы.

Агрегатные функции и фильтрация групп

Кратко зафиксируем агрегатные, прежде чем углубиться в оконные. Базовый набор — count, sum, avg, min, max. Кроме них Trino несёт полезные специальные агрегаты: approx_distinct(x) — приближённый счёт уникальных значений на структуре HyperLogLog (на порядки дешевле точного count(DISTINCT) по памяти), array_agg(x) — собирает значения группы в массив, map_agg(k, v) — собирает пары в MAP, approx_percentile(x, p) — приближённый перцентиль.

Главное правило, которое путают: фильтр по результату агрегата ставится в HAVING, а не в WHERE. WHERE отсекает строки до группировки, HAVING — отсекает группы после агрегации.

SELECT orderstatus, count(*) AS cnt, sum(totalprice) AS revenue
FROM tpch.sf1.orders
WHERE orderdate >= DATE '1995-01-01'   -- фильтр строк ДО группировки
GROUP BY orderstatus
HAVING count(*) > 100000;              -- фильтр групп ПОСЛЕ агрегации

 orderstatus |  cnt   |    revenue
-------------+--------+----------------
 O           | 365045 | 5.51e11
 F           | 360757 | 5.43e11

Оконные функции: устройство OVER

Оконная функция — это функция плюс конструкция OVER, которая описывает окно. OVER состоит из трёх частей, и каждая отвечает за свой вопрос:

функция() OVER (
  PARTITION BY ...   -- на какие группы делим строки
  ORDER BY ...       -- в каком порядке внутри группы
  ROWS BETWEEN ...   -- frame: какие строки группы входят в окно для текущей строки
)

PARTITION BY разбивает строки на партиции — независимые группы, внутри которых считается функция. Партиция оконной функции — аналог группы GROUP BY, но строки не схлопываются. Без PARTITION BY вся таблица — одна партиция.

ORDER BY задаёт порядок строк внутри партиции. Для ranking-функций он обязателен — ранг определяется именно порядком. Для накопительных вычислений (running total) он задаёт, в каком направлении накапливать.

Frame (ROWS/RANGE BETWEEN) — самая тонкая часть. Frame определяет, какое подмножество строк партиции входит в окно для конкретной текущей строки. Окно бывает не статичным на всю партицию, а скользящим: «текущая строка и две предыдущие», «все строки от начала партиции до текущей».

Frame по умолчанию, когда есть ORDER BY, — RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: от начала партиции до текущей строки. Именно поэтому sum(x) OVER (ORDER BY d) даёт накопительный итог, а не общую сумму партиции: для каждой строки окно — это «всё, что было по порядку до неё включительно».

Ranking-функции

Ranking-функции присваивают строкам порядковые номера или ранги внутри партиции. Их часто путают между собой — разберём различие на одном наборе данных.

SELECT name, score,
       row_number() OVER (ORDER BY score DESC) AS rn,
       rank()       OVER (ORDER BY score DESC) AS rnk,
       dense_rank() OVER (ORDER BY score DESC) AS drnk
FROM (VALUES ('A', 90), ('B', 90), ('C', 80), ('D', 70)) AS t(name, score);

 name | score | rn | rnk | drnk
------+-------+----+-----+------
 A    |    90 |  1 |   1 |    1
 B    |    90 |  2 |   1 |    1
 C    |    80 |  3 |   3 |    2
 D    |    70 |  4 |   4 |    3

Смотрите на строки A и B с одинаковым score=90. row_number дал им 1 и 2 — порядок произволен, но номера разные. rank дал обоим 1, а следующей строке — 3 (двойка «пропущена», ранг отражает, что две строки были выше). dense_rank дал обоим 1, а следующей — 2 (без разрыва). Выбор зависит от смысла: «топ-3 без дублей» — row_number; «спортивный ранг с разрывами» — rank; «уровни без пропусков» — dense_rank.

Кроме ranking есть функции навигации по окну: lag(x, n) — значение из строки на n позиций назад, lead(x, n) — на n вперёд, first_value/last_value/nth_value — значение из заданной позиции окна. lag — основной инструмент для «сравнить с предыдущим периодом»:

SELECT month, revenue,
       lag(revenue) OVER (ORDER BY month) AS prev_revenue,
       revenue - lag(revenue) OVER (ORDER BY month) AS delta
FROM (VALUES (1, 100), (2, 130), (3, 110)) AS t(month, revenue);

 month | revenue | prev_revenue | delta
-------+---------+--------------+-------
     1 |     100 |         NULL | NULL
     2 |     130 |          100 |    30
     3 |     110 |          130 |   -20

Что делает движок: оконная функция стоит дорого

Зачем понимать оконные функции «до железа». Оконная функция требует, чтобы строки одной партиции оказались на одном воркере и были там отсортированы по ORDER BY окна. Это значит: Trino добавляет в план стадию перераспределения данных по ключу PARTITION BY (exchange с хэш-партиционированием) и стадию сортировки внутри партиции.

Отсюда практические следствия. Оконная функция без PARTITION BY сгоняет всю таблицу в одну партицию — а партиция целиком обрабатывается одним воркером. Для большой таблицы это убивает параллелизм: один воркер сортирует и считает всё, остальные простаивают. Оконная функция с PARTITION BY по столбцу высокой кардинальности, наоборот, распараллеливается хорошо — много мелких партиций раскидываются по воркерам.

В EXPLAIN оконная функция видна как оператор Window, и перед ним — LocalExchange/RemoteExchange с партиционированием и сортировка:

- Window[partitionBy = [custkey], orderBy = [orderdate ASC]]
    - RemoteExchange[HASH] partitionBy custkey
        - Sort[orderdate ASC]

Попробуй сам

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

1. На таблице tpch.sf1.orders выполните: SELECT custkey, orderdate, totalprice, row_number() OVER (PARTITION BY custkey ORDER BY orderdate) AS order_seq FROM orders LIMIT 20;. Объясните, что означает order_seq для каждой строки и почему нумерация перезапускается.

2. Постройте накопительный итог: SELECT orderdate, sum(totalprice) OVER (ORDER BY orderdate) AS running_total FROM tpch.sf1.orders WHERE custkey = 1 ORDER BY orderdate;. Объясните, какой frame применился по умолчанию и почему результат — running total, а не общая сумма.

3. Возьмите EXPLAIN для запроса с row_number() OVER (PARTITION BY custkey ORDER BY orderdate) и затем для того же без PARTITION BY (просто OVER (ORDER BY orderdate)). Сравните планы: найдите оператор Window и тип exchange перед ним. Сформулируйте в двух предложениях, почему вариант без PARTITION BY хуже масштабируется.