Продолжаю публикацию расширенных транскриптов лекционного курса "PostgreSQL для начинающих", подготовленного мной в рамках "Школы backend-разработчика" в "Тензоре".

В этой лекции углубимся в расширенные возможности команды SELECT : как можно "сложить" и "вычесть" выборки (UNION/INTERSECT/EXCEPT), или запомнить их и использовать повторно (даже в рекурсивных запросах), что дают оконные функции (WINDOW) и соединения (JOIN).

Как обычно, для предпочитающих смотреть и слушать, а не читать - доступна видеозапись:

Краткий путеводитель:

1. Основы SQL

2. Простые SELECT

3. Сложные SELECT

4. Анализ запросов

5. Индексы

6. Транзакции

7. Блокировки

---

Операции над множествами

По сути, любая выборка, которую мы научились создавать и как-то минимально обрабатывать на прошлой лекции, представляет из себя некоторое множество записей.

Поэтому сегодняшнюю лекцию мы и начнем с разбора операций, которые над этими множествами можно совершать. Стандарт SQL допускает 3 таких операции: объединение (UNION), пересечение (INTERSECT) и исключение (EXCEPT).

Самой простой из них, конечно, является операция объединения:

Ничего сложного в ее использовании в запросе нет: "SELECT слева - SELECT справа", если верить документации. Но на практике, обычно, эти SELECT оказываются не "слева-справа", а "сверху-снизу" от ключевого слова UNION - так гораздо удобнее графически воспринимать сложные запросы:

  VALUES
    (1, 2)
  , (1, 2)
UNION
  VALUES
    (3, 4)
  , (1, 2);

UNION ALL vs UNION [DISTINCT]

Как видно из описания, существует две формы объединения:

• UNION ALL, которая просто "склеивает" две выборки, никак не вмешиваясь в их содержимое

  Тут можно заметить, что UNION ALL всегда в PostgreSQL выводит сначала записи первой выборки, а потом второй, причем никак не изменяя их порядка. Такое поведение не описано в стандарте, а реализовано by design, что открывает возможности для оптимизации выполнения запросов с использованием UNION ALL + LIMIT.

• UNION "просто" (ключевое слово DISTINCT тут можно безболезненно опустить), который "уникализирует" записи результирующей выборки - фактически X UNION Y, это "синтаксический сахар" для

  SELECT DISTINCT
    *
  FROM
    (
      X
    UNION ALL
      Y
    ) T;

  Тут важно понимать, что "уникализация" производится для всей результирующей выборки, а не только для второго блока. Например, в этом примере запись (1, 2), повторяющаяся уже в первой выборке, останется в единственном экземпляре:

"Несовпадающие" столбцы

При объединении выборок, как и при любой другой операции над множествами, важно помнить три момента.

Первый касается тех разработчиков, которые любят "клеить" текст запроса где-то в коде приложения - это неправильная "свертка" nil/null/undefined-значений, которая допускает пропуск части столбцов. В этом случае вы рискуете получить разное количество столбцов в объединяемых выборках, что приведет к ошибке:

ERROR:  each UNION query must have the same number of columns

Но даже если вы не пропустили NULL-значение, а сгенерировали текст запроса корректно, это вовсе не означает, что он отработает без ошибки - ведь "нетипизованный" NULL по умолчанию приведется к типу text, а типы каждой пары столбцов объединяемых выборок должны совпадать, иначе...

ERROR:  UNION types integer and text cannot be matched

Но даже если вы правильно указали тип каждого NULL, но использовали UNION-форму (без ALL) - опять можете получить сюрприз, поскольку все NULL-значения одинаковы при уникализации, хотя в точке генерации это могли быть разные значения - например, null и undefined.

Пересечения (INTERSECT) и исключения (EXCEPT)

Раз есть объединение множеств, то должно же быть и пересечение и исключение. И оно есть, и описывается точно так же, как и UNION:

Разница разве что в вычислении количества "совпадающих" записей при ALL-операции:

-- m - экземпляров записи в первой выборке, n - во второй
UNION ALL     -> sum(m, n)
INTERSECT ALL -> min(m, n)
EXCEPT ALL    -> max(m - n, 0)

То есть пересечение (INTERSECT) оставляет только те записи, которые присутствуют и в первой, и во второй выборке, а исключение (EXCEPT) - только те, которые есть в первой, но не во второй.

При этом UNION, INTERSECT и EXCEPT могут произвольным образом комбинироваться в одном запросе. Чтобы понять порядок их вычисления, проще всего соотнести их с арифметическими операциями:

UNION     -> + -> low          A UNION B INTERSECT C EXCEPT D
EXCEPT    -> - -> low          A + B * C - D
INTERSECT -> * -> high         A UNION (B INTERSECT C) EXCEPT D

Common Table Expression, CTE (WITH)

Мы уже научились выборки создавать, объединять, пересекать, то надо же их как-то научиться запоминать для возможности повторного использования. Для этого в SQL используется ключевое слово WITH, которое реализует функционал обобщенных табличных выражений:

Такой запоминаемой под определенным именем выборкой может быть результат как "обычных" SELECT, TABLE или VALUES, так и INSERT, UPDATE или DELETE (с PostgreSQL 16 еще добавился MERGE) в RETURNING-форме.

При этом имена столбцам выборки можно задавать как "внутри", так и "снаружи":

Изнутри генерирующего запроса можно ссылаться на уже описанные выше по тексту CTE, то есть можно получить своеобразную суперпозицию g(f(x)):

WITH f AS (
  TABLE x -- это обращение к реальной таблице
)
, g AS (
  TABLE f -- это уже обращение к сформированной CTE
)
TABLE g;

Рекурсивные CTE (WITH RECURSIVE)

Другой вариант описания CTE - рекурсивный (в этом случае к WITH необходимо добавить ключевое слово RECURSIVE), когда генерирующий выборку запрос может ссылаться не только на предыдущие CTE, но и "на самого себя":

В этом случае он состоит из нерекурсивной выборки-"затравки", оператора UNION ALL (или просто UNION, но его внутри рекурсии стоит использовать с осторожностью) и шага рекурсии с условием продолжения - под ним.

На каждом следующем шаге рекурсии такой запрос получает "на вход" (под именем "своей" CTE) результат генерации записей предыдущего сегмента, пока этот результат непустой, или затравочную выборку - для первого шага:

Важно понимать, что хоть какое-то условие (по наличию записей, их количеству, счетчику шагов или времени выполнения) должно ограничивать продолжение формирования выборки, иначе есть риск получить бесконечно выполняющийся запрос.

Потому что если мы забудем где-то ограничить нашу рекурсию, то можем очень быстро вспомнить легенду о зернах на клетках шахматной доски, потому что нет никаких ограничений на количество записей в сегменте, и оно легко может расти экспоненциально, запросто поглощая любой доступный объем памяти на сервере:

Оконные функции (WINDOW / OVER)

Следующая полезная "фича" PostgreSQL заключается в поддержке возможностей оконных функций - выполняемых по глобально определенному в рамках SELECT-запроса с помощью WINDOW-блока "окну" или по определенному локально с помощью ключевого слова OVER:

По сути, оконные функции являются гибридом группировки и рекурсии - с одной стороны, они позволяют весь набор записей разделить на группы (PARTITION BY) и работать с каждым изолированно, с другой - последовательно накапливать данные или "заглядывать" в предыдущие значения, если задан их порядок (ORDER BY) на определенное количество записей "вперед/назад" (RANGE/ROWS/GROUPS).

Оконные функции вместо рекурсии

Следующий пример демонстрирует возможность замены рекурсивного вычисления последовательности использованием оконных функций для вычисления "треугольных" чисел:

Здесь в определении локального "окна" (в OVER-блоке) мы указали только лишь порядок (ORDER BY) обработки записей. Это означает, что все записи выборки будут отнесены в одну группу и для каждой записи функция (в нашем случае - sum) вычисляется на множестве записей от первой до текущей:

То есть использование OVER(ORDER BY i) эквивалентно вот такому определению "рамки":

OVER(
  ORDER BY i                -- определение порядка записей
  ROWS BETWEEN              -- ключевые слова "рамки":
    UNBOUNDED PRECEDING AND -- ... от самого начала
    CURRENT ROW             -- ... до текущей записи
)

А вот без заданной сортировки OVER() использует по умолчанию совсем другую "рамку", что может вызвать проблемы у неопытного разработчика:

OVER(
  ROWS BETWEEN
    UNBOUNDED PRECEDING AND -- ... от самого начала
    UNBOUNDED FOLLOWING     -- ... до самого конца
)

Более полно с возможностями определений "рамки" можно ознакомиться в документации.

Совместное вычисление по разным "окнам"

В отличие от группировки, которая всю выборку делит на "кучки" по единому правилу, оконные функции могут использовать каждая свои правила сегментирования и даже доопределять параметры "окна", описанного в запросе глобально:

В этом примере мы разбили всю выборку по целочисленному остатку от деления i на 5 (если мы делим в SQL целое на целое, то и результатом будет целое) и отсортировали по значению.

Затем, с помощью функции row_number мы пронумеровали, начиная с 1, каждую запись внутри каждой группы, а вот сумму мы вычислили по "рамке" от предыдущей строки до следующей, исключив текущую (EXCLUDE).

Такой подход может быть полезен, например, если вам надо заполнить пропуски на основании соседних данных.

Оконные функции вместо группировки

Использование оконных функций хоть и похоже на использование агрегатных функций при группировке, но дает значительно больше возможностей.

Если при группировке исходные данные теряются (группы "схлапываются" до единственной строки результата), а правила сегментирования должны быть едины для всех агрегатов, то оконные функции и исходные данные не трогают, и правила могут быть свои для каждой:

Впрочем, с помощью оконных функций можно достичь того же результата, что и при группировке - достаточно использовать DISTINCT ON по всему набору ключей сегментирования (PARTITION BY):

Расширенные возможности агрегатных функций (GROUP BY)

Раз уж мы снова вспомнили про группировку данных, то используемые при ней агрегатные функции имеют существенно больше возможностей, чем мы рассмотрели в прошлой лекции:

На примере функции string_agg, склеивающей текстовые строки в одну, мы можем увидеть, как работает сортировка (ORDER BY), уникализация значений (DISTINCT) и фильтрация (FILTER) строк:

Сортирующие и гипотезирующие функции (WITHIN GROUP)

Но если для обычных агрегирующих функций сортировка является дополнительной возможностью, то для некоторых, определяющих значение на определенном месте выборки или порядковое место, соответствующее значению, задание этого самого порядка обязательно в блоке WITHIN GROUP(ORDER BY ...).

В настоящий момент PostgreSQL поддерживает 3 сортирующие функции:

... и 4 гипотезирующие функции:

Наборы группирования (GROUPING SETS)

Еще одна полезная возможность - группировка одной и той же исходной выборки сразу по нескольким комбинациям ключей - наборам группирования (GROUPING SETS).

Подробно в рамках этой лекции останавливаться на ней не буду, поскольку о ее пользе можно почитать в моей недавней статье SQL HowTo: итоги по строкам и столбцам «в одно действие».

Исходная выборка (FROM)

Мы уже выяснили, что источник данных для обработки должен находиться во FROM-блоке SELECT-запроса.

Мы уже попробовали варианты, когда там используется реальная таблица базы, выборка как результат вложенного запроса, CTE и даже генерирующая функция.

И прежде чем затронуть наиболее сложную тему соединений, немного отвлечемся как раз на функции.

Нумерация строк результата функции (WITH ORDINALITY)

В одном из предыдущих примеров мы использовали функцию generate_series для формирования выборки из арифметической последовательности значений, а оконную функцию row_number - для порядковой нумерации этих самых полученных значений.

Но именно для этой задачи такой подход является избыточным - можно просто добавить столбец с порядковым номером с помощью ключевого слова WITH ORDINALITY:

При необходимости можно сразу же переименовать столбцы результата, включая и порядковый номер. Если этого не сделать, его имя будет назначено автоматически как ordinality.

Соединения (JOIN)

Наконец, мы добрались до самой сложной, но и самой полезной функции SQL - соединения выборок (не путать с объединением!):

Фактически, соединение говорит базе как сопоставить записи выборок слева и справа.

CROSS JOIN

И самый простой способ такого сопоставления - перекрестное соединение, оно же декартово произведение множеств (ага, снова множества), оно же прямое произведение, оно же CROSS JOIN, оно же "через запятую".

Каждая пара строк из левой (X) и правой (Y) выборок образуют X * Y строк результата, состоящих из cX + cY столбцов:

Причем некоторые из имен столбцов могут даже задублироваться, содержа при этом разные значения в одной и той же записи - поэтому SELECT * не стоит использовать при таком соединении.

JOIN / INNER JOIN

От самого простого перекрестного соединения перейдем к наиболее типичному - внутреннему. При нем сопоставляются только те пары записей, для которых выполняется условие:

По сути, INNER JOIN (впрочем, ключевое слово INNER в PostgreSQL можно опустить) можно рассматривать как результат CROSS JOIN с последующим наложением ON-условия:

X CROSS JOIN Y WHERE cond
->
X INNER JOIN Y ON cond

Подводные камни соединений

Во-первых, конечно же, NULL-значения! Если они окажутся в сопоставляемых полях, то условие от них даст тоже NULL-результат, и такие строки не будут сопоставлены.

Во-вторых, обратите внимание, что в нашем примере строка со значением a = 3 слева была в одном экземпляре, а справа таких строк было две - поэтому и в результате они у нас "умножились". А вот запись со значением a = 5 не нашла себе пару справа, и из результата исчезла.

LEFT JOIN

А чтобы все-таки сохранить такую строку "без пары" в результирующей выборке, существует левое соединение (оно же "левое внешнее", LEFT OUTER JOIN, но OUTER тоже принято опускать):

В типичной модели использования условие обычно формируется на основе совпадения значений одноименных столбцов обеих выборок. Чтобы не писать такие условия многократно, можно использовать USING-форму:

Это ровно то же самое внутреннее соединение, к результатам которого мы добавили не нашедшие себе пару строки из левой выборки, заполнив остальные столбцы результата NULL-значениями.

RIGHT JOIN

Симметрично левому, существует и правое соединение, оставляющие строки из правой выборки. Впрочем, они взаимозаменяемы с LEFT JOIN при перестановке выборок (антикоммутативны), потому RIGHT JOIN является не более чем "синтаксическим сахаром":

X RIGHT JOIN Y ON cond
->
Y LEFT JOIN X ON cond

FULL JOIN

При LEFT JOIN мы добавляли "непарные" строки из левой выборки, при RIGHT JOIN - из правой, а если добавить и те, и другие - получится полное соединение.

Проще всего представить разные типы соединений в виде графической схемы:

Но если у кого-то вдруг сложилось ощущение, что условие может быть только по равенству значений, то это не так - например, можно сопоставить записи по условию <=:

Автоформирование условия по столбцам (USING, NATURAL)

В типичной модели использования условие сопоставления обычно формируется на основе совпадения значений одноименных столбцов обеих выборок. Чтобы не писать такие условия многократно, можно использовать USING-форму:

X ??? JOIN Y ON Y.a = X.a AND Y.b = X.b
->
X ??? JOIN Y USING(a, b)

При этом упомянутые в USING столбцы будут присутствовать в результате заведомо однократно, в отличие от ON-условия.

Если же нам необходимо установить соответствие по значениям всех одноименных столбцов, можно написать еще меньше - в NATURAL-форме, без перечисления всех этих столбцов:

X(a, b, x) ??? JOIN Y(a, b, y) USING(a, b)  -> (a, b, x, y)
->
X(a, b, x) NATURAL ??? JOIN Y(a, b, y)      -> (a, b, x, y)

Порядок вычисления выборок (LATERAL)

Как мы видели на прошлой лекции, порядок вычислений что выражений, что выборок, стандарт SQL оставляет на волю внутренней реализации СУБД - поэтому обратиться из одной выборки во FROM к "предыдущей" нельзя.

Но если очень хочется - то можно, использовав ключевое слово LATERAL перед ней - в этом случае ее вычисление пойдет заведомо после описанных выше по тексту запроса:

Единственным исключением является вызов функции во FROM - LATERAL перед ним писать можно, но бессмысленно - функции и так вычисляются в PostgreSQL после предыдущих выборок:

SELECT
  *
FROM
  (
    VALUES
      (2)
    , (3)
    , (5)
  ) T(i)
, generate_series(1, i) j; -- тут мы успешно ссылаемся на данные предыдущей выборки

Выражения подзапросов

Но, в общем-то, вложенный запрос может находиться и не во FROM-блоке, если нужен нам всего лишь для проверки какого-то условия, а не для получения данных из него.

Поэтому в SQL есть набор операторов, проверяющих выражения для подзапросов.

EXISTS

Проверяет наличие хотя бы одной строки в результирующей выборке. Например, вместо INNER JOIN, умножащего записи с одинаковыми ключами, можно использовать EXISTS-проверку:

SELECT
  *
FROM
  X
WHERE
  EXISTS(
    SELECT
      NULL
    FROM
      Y
    WHERE
      a = X.a
  );

IN/NOT IN

Оператор IN позволяет проверить наличие значения во вложенной выборке (в этом случае она должна возвращать единственный столбец):

SELECT
  *
FROM
  (
    VALUES
      (1, 1)
    , (2, 2)
  ) X(a, b)
WHERE
  a IN (
    VALUES
      (1)
    , (3)
  );

Или можно сравнивать даже целые записи (тогда должно совпадать количество столбцов):

SELECT
  *
FROM
  (VALUES
    (1, 1)
  , (2, 2)
  ) X(a, b)
WHERE
  (a, b) IN (
    VALUES
      (1, 1)
    , (1, 2)
    , (2, 1)
    , (2, 2)
  );

ANY/ALL

Ключевые слова ANY и ALL позволяют вычислить истинность указанного оператора хотя бы для какой-то строки / для каждой строки вложенной выборки:

WITH Y AS (
  VALUES(1),(2)
)
SELECT
  *
, a < ANY(TABLE Y) -- хоть кто-то меньше этого значения a?
, a < ALL(TABLE Y) -- все меньше этого значения a?
FROM
  (VALUES
    (1, 1)
  , (2, 2)
  ) X(a, b);

Если таким оператором выступает =, то = ANY эквивалентно работе оператора IN.

Управление порядком выполнения

Несмотря на невозможность "штатно" управлять ходом выполнения запроса, такая возможность все-таки существует с помощью оператора CASE и функции coalesce.

CASE

Оператор CASE может использоваться в двух вариантах:

• аналог цепочки if(...) ... else if(...) ... else if(...) ... else ..., когда на каждом шаге вычисляется значение нового выражения

• аналог switch (...) {case ...; case ...; default: ...}, когда значения оператора зависят от значений единственного выражения

В обоих случаях CASE возвращает какое-то значение. Возможно, это будет NULL, если не произошло ни одного совпадения с WHEN-условием, а опциональный ELSE-блок не описан.

coalesce

В отличие от CASE, функция coalesce просто последовательно вычисляет значения переданных в нее выражений, пока не встретит первый не-NULL'овый результат - его и возвращает:

То есть coalesce можно рассматривать как подобный CASE:

CASE
  WHEN valX IS NOT NULL
    THEN valX
  WHEN valY IS NOT NULL
    THEN valY
END
->
coalesce(valX, valY)

Обратной по смыслу к coalesce является функция nullif, принимающая значение NULL при совпадении значений пары аргументов, иначе возвращающая результат вычисления первого:

Функции минимума и максимума (least, greatest)

Поскольку min и max уже заняты в SQL в качестве имен агрегатных функций, а потребность вычисления минимального/максимального из списка переданных значений (вовсе не обязательно лишь 2) никуда не делась, для них введены специальные функции least и greatest.

---

Что ж... На этом рассказ о базовых возможностях SQL-запросов в PostgreSQL окончен, и дальше мы поговорим об оптимизации их эффективности. А пока - в бой!