Как стать автором
Обновить
112.1
Тензор
Разработчик системы СБИС

Курс «PostgreSQL для начинающих»: #3 — Сложные SELECT

Уровень сложностиСредний
Время на прочтение12 мин
Количество просмотров43K

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

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

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

Лекция 3: Сложные SELECT (видео)

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

  1. Основы SQL

  2. Простые SELECT

  3. Сложные SELECT

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

  5. Индексы

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

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


Содержание текущей лекции:


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

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

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

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

UNION - объединение выборок
UNION - объединение выборок

Ничего сложного в ее использовании в запросе нет: "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), повторяющаяся уже в первой выборке, останется в единственном экземпляре:

UNION vs UNION ALL
UNION vs UNION ALL

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

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

Первый касается тех разработчиков, которые любят "клеить" текст запроса где-то в коде приложения - это неправильная "свертка" 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.

Осторожнее с NULL'ами!
Осторожнее с NULL'ами!

Пересечения (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, которое реализует функционал обобщенных табличных выражений:

Запоминаем выборку с помощью CTE
Запоминаем выборку с помощью CTE

Такой запоминаемой под определенным именем выборкой может быть результат как "обычных" 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).

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

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

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

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

Группировка vs оконные функции
Группировка vs оконные функции

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

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

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

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

Агрегатные функции с уникализацией, упорядочением, фильтрацией и сортирующие
Агрегатные функции с уникализацией, упорядочением, фильтрацией и сортирующие

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

Разные варианты применения string_agg
Разные варианты применения string_agg

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

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

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

Сортирующие агрегатные функции
Сортирующие агрегатные функции

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

Гипотезирующие агрегатные функции
Гипотезирующие агрегатные функции
Определение значений нескольких персентилей сразу и ранга конкретного значения
Определение значений нескольких персентилей сразу и ранга конкретного значения

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

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

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

Функции в исходной выборке (FROM)

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

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

Возможности FROM
Возможности FROM

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

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

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

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

WITH ORDINALITY вместо row_number
WITH ORDINALITY вместо row_number

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

Соединения (JOIN) и выражения подзапросов

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

Виды соединений
Виды соединений

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

CROSS JOIN

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

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

CROSS JOIN
CROSS JOIN

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

JOIN / INNER JOIN

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

INNER 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-форму:

LEFT JOIN
LEFT JOIN

Это ровно то же самое внутреннее соединение, к результатам которого мы добавили не нашедшие себе пару строки из левой выборки, заполнив остальные столбцы результата 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 - из правой, а если добавить и те, и другие - получится полное соединение.

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

Схема типов соединений: INNER JOIN в ядре каждого результата
Схема типов соединений: INNER 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 перед ней - в этом случае ее вычисление пойдет заведомо после описанных выше по тексту запроса:

Доступ к данным "предыдущей" выборки через LATERAL
Доступ к данным "предыдущей" выборки через 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
Синтаксис оператора CASE

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

CASE-switch vs CASE-if
CASE-switch vs CASE-if

coalesce

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

Синтаксис функции coalesce
Синтаксис функции coalesce

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

CASE
  WHEN valX IS NOT NULL
    THEN valX
  WHEN valY IS NOT NULL
    THEN valY
END
->
coalesce(valX, valY)
Второй CASE выполняется только при ELSE-ветке первого
Второй CASE выполняется только при ELSE-ветке первого

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

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

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


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

Теги:
Хабы:
Всего голосов 32: ↑31 и ↓1+34
Комментарии7

Публикации

Информация

Сайт
sbis.ru
Дата регистрации
Дата основания
Численность
1 001–5 000 человек
Местоположение
Россия