Курс «PostgreSQL для начинающих»: #2 — Простые SELECT19.12.2023 08:30

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

Сегодня поговорим о самых простых, но важных, возможностях команды SELECT, наиболее часто используемой при работе с базами данных — формировании выборок (VALUES), их ограничении (LIMIT/OFFSET/FETCH), фильтрации (WHERE/HAVING), сортировке (ORDER BY), уникализации (DISTINCT) и группировке (GROUP BY).

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

Лекция 2: Простые SELECT (видео)

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

  1. Основы SQL

  2. Простые SELECT

  3. Сложные SELECT

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

  5. Индексы

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

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

На прошлой лекции мы остановились на том, что прочитали из таблицы все данные. И выяснили, что на SQL сделать это очень просто — не надо писать ни циклов, ни итераторов, достаточно всего лишь: 

SELECT * FROM <имя_таблицы>;

Впрочем, можно даже не писать SELECT * FROM, потому что есть команда TABLE, которая делает то же самое — безусловно вычитывает из таблицы строки со всеми полями в них: 

TABLE <имя_таблицы>;

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

Но если вдруг кому-то начало казаться, что SELECT — это просто, то это совсем не так, это достаточно сложно, ведь SELECT — самая богатая по количеству функционала команда, которая только есть в SQL:

Богатство возможностей SELECT

Богатство возможностей SELECT

Но если вы почему-то думаете, что это все — какие-то модные нововведения в связи с развитием поддержки расширений стандарта SQL, то это тоже не так.

Всех нововведений на этом слайде, относительно наиболее старой поддерживаемой сейчас версии PostgreSQL 11 всего два: опция WITH TIES, добавленная в версии PostgreSQL 13 возможность неразрывного захвата группы «одинаковых» записей (что удобно для «постраничной навигации», например), и расширение GROUP BY DISTINCT, представленное в PostgreSQL 14.

Если ориентироваться на типичный цикл мажорной версии PostgreSQL раз в год, а в октябре этого года вышла версия 16, то синтаксис, фактически, не менялся за последние 5 лет.

VALUES

Но давайте наше рассмотрение функционала команды SELECT вообще не с нее, с совсем другой команды — VALUES.

Если погрузиться глубоко в исходники PostgreSQL, то VALUES, как и TABLE, это такой маленький SELECT, только с куцым функционалом:

VALUES - это маленький SELECT

VALUES — это маленький SELECT

Из всего многообразия возможностей SELECT, для VALUES оставлено всего лишь 4, как это в русской документации называется, «предложения» (в оригинале — «clause»). Что они позволяют делать, мы и рассмотрим, для начала.

Вообще, команда VALUES позволяет вручную определить некоторую выборку, ее формат, и из чего она будет состоять: через запятую внутри скобок — значения полей записи, вне скобок — перечисление самих записей:

Определение выборки с помощью VALUES

Определение выборки с помощью VALUES

Заодно, она самостоятельно задает имена столбцов (column1, column2, … и далее по порядку) и их типы — если мы не указали специально тип значения в поле, то будет выбран наиболее подходящий из трех стандартных вариантов: integer, numeric или text.

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

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

VALUES (1), ('1');
-- 1
-- 1
VALUES (1), ('a');
-- ERROR:  invalid input syntax for type integer: "a"

Еще одна проблема может поджидать любителей генерировать текст запроса в коде, если ваша стандартная функция свертки массива значений полей нехорошо обходится с NULL или undefined — вы рискуете получить или пустоту вместо значения, или меньше необходимого количества столбцов, что одинаково приведет к ошибке выполнения на стороне сервера СУБД: 

VALUES (1,2),(3,);
-- ERROR:  syntax error at or near ")"
VALUES (1,2),(3);
-- ERROR:  VALUES lists must all be the same length

Помимо статичных значений, в VALUES могут также использоваться NULL-значения, числовые значения в экспоненциальной форме и результаты вычисления выражений: 

VALUES
  (1, 2.5e+0, 'al' || 'pha')
, (2 + 2, NULL, 'beta');
column1 | column2 | column3
integer | numeric | text
      1 |     2.5 | alpha
      4 |         | beta

Здесь мы видим два выражения: 2 + 2 и 'al' || 'pha'. Причем второе, с оператором ||, который во многих привычных языках программирования означает «логическое ИЛИ», в PostgreSQL — конкатенация строк.

Операторы в PostgreSQL

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

Имя оператора образует последовательность не более чем NAMEDATALEN-1 (по умолчанию 63) символов из следующего списка:

+ - * / < > = ~ ! @ # % ^ & | ` ?

Однако для имён операторов есть ещё несколько ограничений:

  • Сочетания символов -- и /* не могут присутствовать в имени оператора, так как они будут обозначать начало комментария.

  • Многосимвольное имя оператора не может заканчиваться знаком + или -, если только оно не содержит также один из этих символов:

    ~ !  @ # % ^ & | ` ?

    Например,  @- — допустимое имя оператора, а *- — нет. Благодаря этому ограничению,  PostgreSQL может разбирать корректные SQL-запросы без пробелов между компонентами.

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

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

VALUES (|/ 36, ||/ 125, @ -1, 'al' || 'pha');
column1          | column2          | column3 | column4
double precision | double precision | integer | text
               6 |                5 |       1 | alpha

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

VALUES (sqrt(36), cbrt(125), abs(-1), concat('al', 'pha'));

Раз мы заговорили об операторах, стоит упомянуть, что они имеют различный приоритет:

Операторы в PostgreSQL по убыванию приоритета

Операторы в PostgreSQL по убыванию приоритета

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

Порядок вывода строк (ORDER BY)

Но давайте все-таки вернемся к возможностям команды VALUES и рассмотрим первое «предложение» — ORDER BY, которое позволяет задать порядок вывода получаемых нами записей.

«Порядка от зависит многое!»

Оно состоит из списка выражений сортировки, которые применяются последовательно — то есть сначала все записи сортируются по первому выражению, при равенстве значений в первом — по второму, и так далее.

Каждое из таких выражений может быть представлено:

  • позиционным номером или именем столбца сортируемой выборки

  • некоторым выражением от одного или нескольких полей строки (столбцов выборки) с операторами и функциями

Единственное требование — лишь бы тип столбца или результата выражения поддерживал операторы линейного порядка <, <=, =, >=, >. То есть, например, boolean, числа или строки отсортировать «по порядку» можно, а двухкоординатные геометрические точки — нельзя.

Дополнительно у нас могут быть указаны ключевые слова:

  • ASC/DESC, которые позволяют определить направление сортировки — по возрастанию (по умолчанию) или убыванию соответственно

  • NULLS FIRST/NULLS LAST, определяющие положение NULL-значений раньше/позже всех остальных

Если же и после сортировки по последнему из выражений в нем оказались равные значения или если не было ORDER BY в запросе вовсе, то порядок записей не определен.

То есть, в соответствии со стандартом SQL, база вправе отдавать вам записи в любом произвольном, удобном ей с точки зрения производительности, порядке, не нарушающем указанный в запросе.

Не указали ORDER BY и надеетесь, что записи сохранят хоть какой-то порядок при следующем выполнении того же запроса?… В большинстве случаев — зря! Для базы нет никакой разницы между »эта запись у меня записана в начале таблицы, а эта — в конце, значит, она будет выведена позже! » Да и вообще, ничто не заставляет PostgreSQL читать файл таблицы «с начала» — чтение запросто может стартовать и с его «середины».

Выполним нашу команду VALUES без всякого указания ORDER BY — и, несмотря на все сказанное ранее, получаем строки ровно в том порядке, в котором их указали в запросе! Такое поведение не регламентируется стандартом, но реализовано в PostgreSQL «by design».

Однако, при наличии любой сортировки порядок данных в неперечисленных там столбцах будет произвольным. Например, при сортировке по первому столбцу «по возрастанию» можно внезапно обнаружить, что второй столбец оказался отсортированным «по убыванию».

Без сортировки и с сортировкой по первому столбцу

Без сортировки и с сортировкой по первому столбцу

Также необходимо помнить, что если у нас возникает задача «развернуть» выборку в обратном порядке, то это надо проделать со всем перечнем сортирующих выражений: 

-- прямой порядок
ORDER BY 1 ASC, 2 ASC
-- обратный порядок
ORDER BY 1 DESC, 2 DESC

При этом, если мы используем однонаправленную сортировку сразу по нескольким столбцам, их можно «объединить в кортеж» и «развернуть» сразу вместе: 

-- прямой порядок
ORDER BY (column1, column2) -- ASC по умолчанию
-- обратный порядок
ORDER BY (column1, column2) DESC

Кстати насчет порядка… Учитывая два направления сортировки и два варианта позиции NULL-значений, мы получаем 4 варианта сортировок для любого выражения: 

ORDER BY x                 -- ASC  NULLS LAST
ORDER BY x NULLS FIRST     -- ASC  NULLS FIRST
ORDER BY x DESC NULLS LAST -- DESC NULLS LAST
ORDER BY x DESC            -- DESC NULLS FIRST

… и все они разные:

ASC vs DESC / NULLS LAST vs NULLS FIRST

ASC vs DESC / NULLS LAST vs NULLS FIRST

Заметьте, что обратной к «умолчательной» ASC NULLS LAST является DESC NULLS FIRST, что бывает удивительным для начинающих разработчиков, обнаруживающих NULL'ы в самом начале «развернутой» выборки.

Сортировка строковых значений

Но что мы все про числа?… Давайте посмотрим на сортировки строк.

Если мы используем «обычную» сортировку, то порядок строк будет алфавитным, причем строчные и прописные буквы окажутся рядом. Но если воспользуемся «побайтовой» сортировкой USING ~<~, то получим весьма неожиданный результат:

Сортировка строк может быть... странной

Сортировка строк может быть… странной

Думаю, многие догадываются, что зачатки такого положения вещей были посеяны еще при портировании IBM XT и MS DOS на их советские аналоги типа ЕС-1840.

»640KB хватит всем!»

Однако, русским языком проблемы не ограничиваются и точно так же страдают поляки, немцы и многие другие народы, пишущие «с умляутами», не говоря уж про пользователей RTL-языков. Поэтому был разработан такой инструмент, как правила сортировки, которые, фактически, определяют позицию каждого символа в кодовой таблице.

Вы можете установить соответствующую кодовую таблицу на сервер PostgreSQL и использовать для сортировки. Например, установленная по умолчанию POSIX/C-кодировка может быть использована как ORDER BY column1 COLLATE "C" и даст результат аналогичный «побайтовой» ORDER BY column1 USING ~<~.

Ограничения результата

Второй блок «предложений» позволяет ограничить перечень строк в результирующей выборке: сколько штук хотим получить и с какой по счету начинать.

Ограничения выборки

Ограничения выборки

Ограничение количества строк (LIMIT)

Чтобы задать желаемое количество строк ответа (например, вы хотите выбрать из таблицы какие-то 5 строк, хотя их там миллионы), воспользуемся ключевым словом LIMIT: 

-- чтобы вернуть не более 5 строк
LIMIT 5
LIMIT '5'
-- чтобы вернуть все строки без ограничения
LIMIT NULL
LIMIT ALL
  -- ... или просто не указывать LIMIT

«Не более» означает, что даже если мы установим лимит в 5 строк для выборки, где их всего 3, то и получим только 3 — ведь базе неоткуда взять еще 2.

Смещение начала (OFFSET)

Чтобы начать получать строки не с первой по порядку, воспользуемся ключевым словом OFFSET: 

-- начинаем с 6-й строки (пропускаем 5)
OFFSET 5
OFFSET '5'
-- начинаем с самого начала
OFFSET NULL
OFFSET 0
  -- ... или просто не указывать OFFSET

Некоторые почему-то считают, что OFFSET нельзя указывать без LIMIT, но это не так: 

VALUES
  ('a')
, ('b')
, ('c')
OFFSET 1;
-- a
-- b

Понятно, что при LIMIT 0 или OFFSET ALL результат окажется заведомо пустым. Впрочем, первый вариант можно использовать, если вам необходимо получить только формат столбцов результата запроса, а вот второй вызовет ошибку: 

ERROR:  syntax error at or near "ALL"
LINE 1: OFFSET ALL;

В целом, можно вычислить, что при исходном размере выборки S строк, заданном лимите L и смещении O, мы всегда получаем min(max(S - O, 0), L) строк результата:

LIMIT и OFFSET

LIMIT и OFFSET

И такой подход позволяет достаточно просто реализовывать постраничную навигацию, если порядок выборки у вас стабилен (например, вы задали правильный однозначный и стабильный порядок с помощью ORDER BY): 

...
ORDER BY ...
LIMIT  
OFFSET

FETCH

Если изначально стандарт SQL предполагал использование только пары LIMIT и OFFSET, то с SQL:2008 появилась комбинация OFFSET и ключевого слова FETCH, которое по смыслу аналогично LIMIT с небольшими дополнениями: 

...
OFFSET 1 ROWS
FETCH FIRST 1 ROWS ONLY
-- полностью тоже самое, что
...
LIMIT 1
OFFSET 1

Тут слова ROWS и FIRST незначимы, и поддерживаются исключительно ради соответствия стандарту, а вот ONLY — ой как значимо! Именно эта форма дает результат эквивалентный LIMIT + OFFSET.

А вот вторая форма WITH TIES, появившаяся в PostgreSQL 13, заставляет базу отдавать сразу все строки неразличимого для сортировки блока, который попадает на границу выборки.

Эта возможность позволяет существенно упростить реализацию постраничной навигации:

FETCH ... WITH TIES

FETCH … WITH TIES

SELECT

Давайте, наконец, вернемся от примитивного VALUES к полноценному SELECT, который уже посложнее.

Формат результирующей выборки

Как минимум, он отличается тем, что мы описываем те столбцы, которые хотим в результирующей выборке видеть — список возвращаемых полей. Если VALUES формирует имена и определяет типы столбцов самостоятельно, то для SELECT нам необходимо хоть что-то написать самим относительно того, что пришло во FROM-части.

Это может быть некоторое:

  • выражение от столбцов из FROM,

  • имя любого из этих столбцов

  • или можем указать "*" («дай мне все столбцы из…») для части или всего FROM

В целом, если «на вход» из FROM нам пришла строка, то и «на выходе» мы получим строку соответствующую ей, как-то преобразованную указанными нами выражениями.

SELECT отображает столбцы FROM в столбцы результата

SELECT отображает столбцы FROM в столбцы результата

Хотя, FROM-части в SELECT-запросе может и не быть:

SELECT без FROM

SELECT без FROM

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

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

  • один описывает порядок, имена и типы столбцов, укацук

  • а второй содержит строки ответа в виде объектов или массивов значений полей в соответствии с порядком описания столбцов.

Либо в качестве имени столбца будет взято имя функции (random, generate_series, ...) или оператора (CASE, coalesce, nullif, ...). Функция при этом может генерировать как единственное значение, так и сразу набор строк.

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

Раз SELECT умеет принимать выборку «на вход» во FROM, а VALUES — выдавать, давайте попробуем совместить эти две команды: 

SELECT
  *
FROM (
  VALUES
    ('a', 1)
  , ('b', 2)
  , ('a', 3)
  , ('b', 4)
);
-- ERROR:  VALUES in FROM must have an alias
-- HINT:  For example, FROM (VALUES ...) [AS] foo.

Однако, это нельзя сделать просто так. Впрочем, сам PostgreSQL подсказывает нам, как исправить ошибку в запросе — присвоить алиасы для каждого элемента внутри FROM:

Нельзя просто так взять и поместить что-то во FROM - назвать надо!

Нельзя просто так взять и поместить что-то во FROM — назвать надо!

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

Впрочем, на переименованиях можно сэкономить, задав имена столбцов прямо при определении алиаса:

«Валар Моргулис»

«Валар Моргулис»

Фильтрация исходной выборки (WHERE)

Следующее «предложение» — WHERE, определяет, какие из строк исходной выборки будут обработаны и сформируют строки результата, а какие будут отброшены.

Если формат выборки определяет, какие столбцы мы получим, то WHERE — какие строки. И правила тут просты — если указанное boolean-выражение дает для строки TRUE — она попадает в выборку, если FALSE или NULL — нет.

Отбираем только соответствующие WHERE-условию строки

Отбираем только соответствующие WHERE-условию строки

Попробуем отобрать только строки со значением 'a' в поле str:

«Просеиваем» записи

Еще раз напомню, что для сравнения в SQL используется «одинарный» оператор "=".

Вычисление условий в SQL

Но как только мы добавляем еще одно условие, начинаются сложности: 

WHERE условие1 AND условие2

Если кто-то привык по императивным языкам программирования, что всегда сначала будет вычислено условие1 и только после него, в зависимости от результата, — условие2, то в SQL их ждет много сюрпризов.

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

Заметьте, что это отличается от «оптимизации» вычисления логических операторов слева направо, реализованной в некоторых языках программирования.

Как следствие, в сложных выражениях не стоит использовать функции с побочными эффектами. Особенно опасно рассчитывать на порядок вычисления или побочные эффекты в предложениях WHERE и HAVING, так как эти предложения тщательно оптимизируются при построении плана выполнения. Логические выражения (сочетания AND/OR/NOT) в этих предложениях могут быть видоизменены любым способом, допустимым законами Булевой алгебры.

Когда порядок вычисления важен, его можно зафиксировать с помощью конструкции CASE (см. Раздел 9.18). Например, такой способ избежать деления на ноль в предложении WHERE ненадёжен:

SELECT ... WHERE x > 0 AND y/x > 1.5;

Безопасный вариант:

SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;

Применяемая так конструкция CASE защищает выражение от оптимизации, поэтому использовать её нужно только при необходимости. (В данном случае было бы лучше решить проблему, переписав условие как y > 1.5*x.)

Исключение дублей (DISTINCT)

Еще одним способом сократить количество строк в результирующей выборке является их уникализация с помощью DISTINCT:

Исключаем дубли DISTINCT [ON]

Исключаем дубли DISTINCT [ON]

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

Сортировка строк при DISTINCT ON-фильтрации

Сортировка строк при DISTINCT ON-фильтрации

Напоминаю, что при отсутствии явно заданной сортировки, база вернет вам ту запись, которая удобна ей, а не вам.

Если же мы ON-выражения не указали, совпадение определяется по полному набору столбцов сразу. Из каждого набора «полных клонов» строк результирующей выборки будет оставлена ровно одна, отличающаяся от других хоть чем-то:

Полная уникализация строк

Полная уникализация строк

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

Группировка строк (GROUP BY)

Помимо DISTINCT, уникализировать строки нам может помочь «предложение» GROUP BY.

Группировка строк

Группировка строк

По описанию он очень похож на ORDER BY, там также можно указывать номера столбцов, их имена или выражения от них, но нужен он, чтобы всю нашу выборку «разложить на кучки». А потом по каждой «кучке» посчитать некоторую агрегатную функцию.

Например, это может быть количество строк в группе (count), минимальное/максимальное или среднее значение какого-то показателя (min/max/avg) или его полная сумма по группе (sum) — причем их можно считать одновременно. Или, если стандартных функций вам не хватает, можно определить свою.

Совместное вычисление нескольких агрегатов

Совместное вычисление нескольких агрегатов

Если же нам надо вычислить результат без разбиения на группы (»Моя фамилия ИТОГО! »), вы можете указать группировку по пустому набору GROUP BY () или не указывать GROUP BY вовсе.

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

SELECT i, sum(...) ... GROUP BY 1 ORDER BY 2 DESC
-- или, если само значение агрегата неважно
SELECT i ... GROUP BY 1 ORDER BY sum(...) DESC

Фильтрация сгруппированных строк (HAVING)

Раз уж мы умеем сортировать сгруппированные строки, то почему бы их и не пофильтровать? И тут на помощь нам приходит «предложение» HAVING:

Пост-фильтрация сгруппированных строк

Пост-фильтрация сгруппированных строк

По смыслу, условие HAVING эквивалентно условию WHERE, с той лишь разницей, что в условии мы уже можем использовать агрегатные функции, поскольку условие применяется к результату группировки. Кто не в курсе про HAVING, может добиться того же результата «надстройкой» еще одного уровня вложенности запроса с WHERE, но зачем писать больше?…

HAVING заменяет

HAVING заменяет «надзапрос» с WHERE

Итого, в этой лекции мы разобрали следующие возможности команды SELECT, и это оказалось все-таки не так уж сложно! 

SELECT [ DISTINCT [ ON ( выражение [, ...] ) ] ]
    [ * | выражение [ [ AS ] имя_результата ] [, ...] ]
    [ FROM элемент_FROM [, ...] ]
    [ WHERE условие ]
    [ GROUP BY элемент_группирования [, ...] ]
    [ HAVING условие ]
    [ ORDER BY выражение
       [ ASC | DESC | USING оператор ]
       [ NULLS { FIRST | LAST } ] [, ...]
    ]
    [ LIMIT { число | ALL } ]
    [ OFFSET начало [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ число ] { ROW | ROWS } { ONLY | WITH TIES } ]

А про более сложные варианты использования SELECT мы поговорим на следующей лекции.

© Habrahabr.ru