Запросы

В данном разделе показано, как получить данные из базы данных.

Обзор

Процесс получения данных из базы данных называется запросом. В SQL для создания запросов используется команда SELECT. Общий вид команды SELECT следующий:

[WITH запросы_with]
SELECT список_выборки
FROM табличное_выражение
[определение_сортировки]

Далее будут подробно описаны список выборки, табличное выражение и определение сортировки. Запросы WITH являются расширенной функцией, поэтому рассматриваются в последнюю очередь.

Простейший запрос имеет следующий вид:

SELECT * FROM table1;

Если существует таблица с именем table1, то вышеупомянутая команда извлечёт все строки с содержимым всех столбцов из table1. (Метод выдачи результата зависит от клиентского приложения. Например, программа qsql отобразит на экране таблицу ASCII-art, хотя клиентские библиотеки могут извлекать отдельные значения из результата запроса). В качестве списка выборки указан символ * он означает, что в результате выполнения запроса вернутся все столбцы табличного выражения. В списке выборки можно также указать подмножество доступных столбцов или выполнить вычисления с использованием столбцов. Например, если в table1 есть столбцы с именами a, b и c (и, возможно, другие), вы можете выполнить следующий запрос:

SELECT a, b + c FROM table1;

(тут считается, что b и c числовые типы данных). См. раздел Списки выборки для более подробной информации.

FROM table1 — это простейший вид табличного выражения, который читает только одну таблицу. В общем случае табличные выражения могут быть сложными комбинациями базовых таблиц, соединений и подзапросов. Но вы также можете не использовать табличное выражение и использовать только команду SELECT в качестве калькулятора:

SELECT 3 * 4;

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

SELECT random();

Табличные выражения

Табличное выражение вычисляет таблицу. Оно содержит предложение FROM, за которым могут следовать WHERE, GROUP BY и HAVING. Простые табличные выражения просто ссылаются на конкретную (базовую) таблицу на диске, но в более сложных выражениях можно модифицировать или комбинировать базовые таблицы различными способами.

Необязательные предложения WHERE, GROUP BY и HAVING в табличных выражениях задают последовательность преобразований, выполняемых с данными, полученными в предложении FROM. В результате этих преобразований создается виртуальная таблица, строки которой передаются в список выборки для вычисления выходных строк запроса.

Условие FROM

Предложение FROM формирует таблицу из одной или нескольких ссылок на таблицы, разделённых запятыми.

FROM ссылка_на_таблицу [, ссылка_на_таблицу [, ...]]

Ссылка на таблицу может быть именем таблицы (возможно, с именем схемы) или производной таблицей (подзапрос, конструкция JOIN или их сложные комбинации). Если в предложении FROM указано более одной ссылки на таблицу, таблицы соединяются перёкрестно (то есть формируется декартово произведение их строк; см. ниже). Результатом списка FROM является промежуточная виртуальная таблица, которая затем может преобразовываться с помощью WHERE, GROUP BY и HAVING, и, в итоге, является результатом общего табличного выражения.

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

Соединение таблиц

Соединённая таблица — это таблица, полученная из двух других (реальных или производных) таблиц в соответствии с правилами соединения. Возможны внутренние, внешние и перекрёстные соединения. Общий синтаксис соединения таблиц:

T1 тип_соединения T2 [ условие_соединения ]

Соединения всех типов могут быть объединены в цепочку или вложены друг в друга - любая из таблиц или даже обе таблицы T1 и T2 могут быть уже соединёнными таблицами. Для указания точного порядка соединения предложения JOIN можно заключать в скобки. При отсутствии скобок предложения JOIN обрабатываются слева направо.

Типы соединения
Перекрёстное соединение (CROSS JOIN):
T1 CROSS JOIN T2
  • Для каждой возможной комбинации строк из таблиц T1 и T2 (декартово произведение) объединённая таблица будет содержать строку, состоящую из всех столбцов таблицы T1 за которыми следуют все столбцы таблицы T2. Если таблицы имеют N и M строк соответственно, объединенная таблица будет иметь размер N * M строк.

  • FROM T1 CROSS JOIN T2 эквивалентно FROM T1 INNER JOIN T2 ON TRUE (см. ниже). А также эквивалентно FROM T1, T2.

Заметка
Последний вариант не полностью эквивалентен первым двум, если в объединении указано более двух таблиц, т.к. JOIN связывает более плотно, чем запятая. Например, условие FROM T1 CROSS JOIN T2 INNER JOIN T3 ON condition не совпадает с условием FROM T1, T2 INNER JOIN T3 ON condition поскольку condition может ссылаться на T1 в первом случае, но не во втором.

Квалифицированные соединение (Qualified joins)
T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 ON boolean_expression
T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 USING ( join column list )
T1 NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2
  • Слова INNER и OUTER являются необязательными во всех типах соединений. INNER — это значение по умолчанию; LEFT, RIGHT и FULL подразумевают внешнее соединение.

  • Условие соединения указывается в предложении ON или USING или неявно словом NATURAL. Условие соединения определяет, какие строки из двух исходных таблиц считаются «совпадающими», как подробно описано ниже.

  • Возможные типы квалифицированного соединения:

    • INNER JOIN - каждая строка R1 из T1 в объединенной таблице соединена со строкой из T2, которая удовлетворяет условию соединения с R1.

    • LEFT OUTER JOIN - сначала выполняется внутреннее соединение. Затем в результирующую таблицу добавляются все строки T1, которым не соответствует ни одной строки T2. Вместо значений T2 вставляются NULL. В итоге, в результирующей таблице всегда будет присутствовать хотя бы одна строка T1.

    • RIGHT OUTER JOIN - сначала выполняется внутреннее соединение. Затем в результирующую таблицу добавляются все строки T2, которым не соответствует ни одной строки T1. Вместо значений T1 вставляются NULL. Это соединение обратно левому: В итоге, в результирующей таблице всегда будет присутствовать хотя бы одна строка T2.

    • FULL OUTER JOIN - сначала выполняется внутреннее соединение. Затем в результирующую таблицу добавляются все строки T1, которым не соответствует ни одной строки T2 (вместо значений T2 вставляются NULL). И все строки T2, которым не соответствует ни одной строки T1 (вместо значений T1 вставляются NULL).

  • Предложение ON является наиболее общим видом условия соединения: тут указываются выражения логического типа, аналогично предложению WHERE. Две строки из T1 и T2 совпадают, если выражение ON возвращает true.

  • Предложение USING это сокращение, которое используется, когда обе стороны соединения используют одинаковые имена столбцов. Оно принимает разделённый запятыми список имен общих столбцов и формирует условие соединения с равенством этих столбцов. Например, соединение T1 и T2 с помощью USING (a, b) создает условие ON T1.a = T2.a AND T1.b = T2.b.

  • Кроме того, использование JOIN USING оставляет только уникальные столбцы: нет необходимости печатать оба сопоставленных столбца, т.к. они имеют одинаковые значения. В противоположности JOIN ON, который выводит все столбцы из T1 и затем все столбцы из T2, JOIN USING создает один столбец для каждой из перечисленных пар столбцов (в указанном порядке), за которыми следуют все оставшиеся столбцы из T1, а затем все оставшиеся столбцы из T2.

  • Наконец, NATURAL — это сокращенная форма USING: он формирует список USING состоящий из всех имен столбцов, которые появляются в обеих входных таблицах. Как и при использовании USING, эти столбцы появляются только один раз в результирующей таблице. Если нет общих имён столбцов, NATURAL JOIN ведет себя как JOIN ... ON TRUE, создавая кросс-объединение между таблицами.

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

Рассмотрим вышеизложенное на примерах. Пусть у нас есть таблицы t1:

num | name
-----+------
  1 | a
  2 | b
  3 | c

и t2:

num | value
-----+-------
  1 | xxx
  3 | yyy
  5 | zzz

Для различных их соединений мы получим следующие результаты:

=> SELECT * FROM t1 CROSS JOIN t2;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   1 | a    |   3 | yyy
   1 | a    |   5 | zzz
   2 | b    |   1 | xxx
   2 | b    |   3 | yyy
   2 | b    |   5 | zzz
   3 | c    |   1 | xxx
   3 | c    |   3 | yyy
   3 | c    |   5 | zzz
(9 rows)

=> SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   3 | c    |   3 | yyy
(2 rows)

=> SELECT * FROM t1 INNER JOIN t2 USING (num);
 num | name | value
-----+------+-------
   1 | a    | xxx
   3 | c    | yyy
(2 rows)

=> SELECT * FROM t1 NATURAL INNER JOIN t2;
 num | name | value
-----+------+-------
   1 | a    | xxx
   3 | c    | yyy
(2 rows)

=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |   3 | yyy
(3 rows)

=> SELECT * FROM t1 LEFT JOIN t2 USING (num);
 num | name | value
-----+------+-------
   1 | a    | xxx
   2 | b    |
   3 | c    | yyy
(3 rows)

=> SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   3 | c    |   3 | yyy
     |      |   5 | zzz
(3 rows)

=> SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |   3 | yyy
     |      |   5 | zzz
(4 rows)

Условие соединения, указанное с помощью ON может также содержать условия, которые не имеют прямого отношения к соединению. Это может быть полезным для некоторых запросов, но должно быть тщательно продумано. Например:

=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |     |
(3 rows)

Обратите внимание, что аналогичное условие в WHERE приводит к иному результату:

=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num WHERE t2.value = 'xxx';
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
(1 row)

Это связано с тем, что условие, указанное в предложение ON, обрабатывается до соединения, а условие, указанное в WHERE, обрабатывается после соединения. Это не имеет значения для внутренних объединений, но очень важно для внешних объединений.

Псевдонимы таблиц и столбцов

Таблицам и сложным ссылкам на таблицы можно дать временные имена, по которым можно обращаться к таблицам в запросе. Это временное имя называется псевдонимом таблицы.

Псевдоним можно создать с помощью ключевого слова AS:

FROM table_reference AS alias

Или:

FROM table_reference alias

Ключевое слово AS является необязательным. alias может быть любым идентификатором.

Стандартно псевдонимы создаются в качестве коротких идентификаторов для длинных имён таблиц, чтобы улучшить читаемость запросов. Например:

SELECT * FROM some_very_long_table_name s JOIN another_fairly_long_name a ON s.id = a.num;

Псевдоним становится новым именем таблицы в текущем запросе - после объявления псевдонима нельзя сослаться на таблицу по исходному имени. Следующий пример недопустим:

SELECT * FROM my_table AS m WHERE my_table.a > 5;    -- wrong

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

SELECT * FROM people AS mother JOIN people AS child ON mother.id = child.mother_id;

Кроме того, псевдоним обязателен, если вы используете подзапрос (см. раздел Подзапросы).

В случае неоднозначных объявлений псевдонимов стоит использовать скобки. В следующем примере первому оператору присваивается псевдоним b, второму экземпляру my_table, но второй оператор присваивает псевдоним результату соединения:

SELECT * FROM my_table AS a CROSS JOIN my_table AS b ...
SELECT * FROM (my_table AS a CROSS JOIN my_table) AS b ...

Также псевдонимы можно давать столбцам таблицы, а не только самим таблицам:

FROM table_reference [AS] alias ( column1 [, column2 [, ...]] )

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

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

SELECT a.* FROM my_table AS a JOIN your_table AS b ON ...

но запрос:

SELECT a.* FROM (my_table AS a JOIN your_table AS b ON ...) AS c

выдаст ошибку, т.к. псевдоним таблицы a не доступен вне псевдонима c.

Подзапросы

Подзапросы, которые образуют таблицу, должны быть заключены в круглые скобки и должны иметь псевдоним (как в разделе Псевдонимы таблиц и столбцов). Например:

FROM (SELECT * FROM table1) AS alias_name

Этот пример эквивалентен FROM table1 AS alias_name. Более интересные случаи, которые нельзя свести к простому соединению, возникают, когда подзапрос включает в себя группировку или агрегирующие функции.

Подзапрос также может быть списком VALUES:

FROM (VALUES ('anne', 'smith'), ('bob', 'jones'), ('joe', 'blow'))
     AS names(first, last)

Опять же, тут требуется псевдоним таблицы. Указывать псевдонимы столбцам списка VALUES необязательно, но это хорошая практика. Для получения дополнительной информации см. раздел Списки VALUES.

Табличные функции

Табличные функции — это функции, создающие набор строк, состоящий из базовых типов данных (скалярных типов) или составных типов данных (строк таблицы). Они используются как таблицы, представления или подзапросы в предложении FROM. Столбцы, возвращаемые табличными функциями, могут быть включены в предложения SELECT, JOIN или WHERE так же, как столбцы таблиц, представлений или подзапросов.

Табличные функции также могут быть соединены с использованием синтаксиса ROWS FROM. Результаты будут возвращены в параллельных столбцах, количество строк в результате будет равно наибольшему из результатов функций, а результаты с меньшим количеством строк дополнятся значениями NULL.

function_call [WITH ORDINALITY] [[AS] table_alias [(column_alias [, ... ])]]
ROWS FROM( function_call [, ... ] )
[WITH ORDINALITY] [[AS] table_alias [(column_alias [, ... ])]]

Если указано предложение WITH ORDINALITY, то в результирующие столбцы функции будет добавлен дополнительный столбец типа bigint с пронумерованными строками результатов. (Это обобщение стандартного синтаксиса SQL для UNNEST ... WITH ORDINALITY). По умолчанию данный столбец называется ordinality, но ему может быть присвоено другое имя с помощью предложения AS.

В специальную табличную функцию UNNEST передается массив с любым количеством параметров. Функция возвращает соответствующее количество столбцов, как если бы UNNEST (см. раздел Функции и операторы массива) был вызван для каждого параметра по отдельности и объединен с использованием конструкции ROWS FROM.

UNNEST( array_expression [, ... ] ) [WITH ORDINALITY] [[AS] table_alias [(column_alias [, ... ])]]

Если table_alias не указан, то в качестве имени таблицы используется имя функции, а в случае c конструкцией ROWS FROM() используется имя первой функции.

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

Несколько примеров:

CREATE TABLE foo (fooid int, foosubid int, fooname text);

CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;

SELECT * FROM getfoo(1) AS t1;

SELECT * FROM foo
    WHERE foosubid IN (
                        SELECT foosubid
                        FROM getfoo(foo.fooid) z
                        WHERE z.fooid = foo.fooid
                      );

CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);

SELECT * FROM vw_getfoo;

В некоторых случаях полезно определить табличную функцию, возвращающую различные наборы столбцов для разных вариантов вызова. Для этого необходимо объявить табличную функцию как возвращающую псевдотип record. Когда такая функция используется в запросе, ожидаемая структура строк должна быть указана в самом запросе, чтобы система могла знать, как анализировать запрос и составить план запроса. Этот синтаксис выглядит так:

function_call [AS] alias (column_definition [, ... ])
function_call AS [alias] (column_definition [, ... ])
ROWS FROM( ... function_call AS (column_definition [, ... ]) [, ... ] )

Если синтаксис ROWS FROM() не используется, список column_definition заменяет список псевдонимов, который в противном случае можно было бы добавить в предложение FROM; имена в определениях столбцов служат псевдонимами. При использовании ROWS FROM() список column_definition может быть добавлен к каждой функции отдельно; или если существует только одна функция и нет предложения WITH ORDINALITY, список column_definition можно записать вместо списка псевдонимов после ROWS FROM().

Посмотрим на этот пример:

SELECT *
    FROM dblink('dbname=mydb', 'SELECT proname, prosrc FROM pg_proc')
      AS t1(proname name, prosrc text)
    WHERE proname LIKE 'bytea%';

Функция dblink (часть модуля dblink) выполняет удаленный запрос. Она объявлена, как возвращающая record, т.к. он может использоваться для любого запроса. Здесь фактический набор столбцов необходимо указать в вызывающем запросе, чтобы анализатор запроса знал, например, как преобразовать *.

Подзапросы LATERAL

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

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

LATERAL элемент может появиться на верхнем уровне в списке FROM или в дереве JOIN. В последнем случае он может также ссылаться на любые элементы, левой части дерева JOIN, к которому он присоединен с правой стороны.

Когда элемент FROM содержит LATERAL, запрос выполняется следующим образом: для каждой строки элемента FROM со столбцами подзапроса, или набора строк из нескольких элементов FROM, содержащих столбцы, вычисляется LATERAL со значениеми столбцов этой строки или набора строк. Затем результирующие строки соединяются как обычно со строками, из которых они были вычислены. И процедура повторяется для всех строк или наборов строк из исходной таблицы.

Обычный пример LATERAL:

SELECT * FROM foo, LATERAL (SELECT * FROM bar WHERE bar.id = foo.bar_id) ss;

Тут использование LATERAL не имеет смысла, т.к. тот же результат можно получить более простым способом:

SELECT * FROM foo, bar WHERE bar.id = foo.bar_id;

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

SELECT p1.id, p2.id, v1, v2
FROM polygons p1, polygons p2,
     LATERAL vertices(p1.poly) v1,
     LATERAL vertices(p2.poly) v2
WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;

Этот запрос можно также записать так:

SELECT p1.id, p2.id, v1, v2
FROM polygons p1 CROSS JOIN LATERAL vertices(p1.poly) v1,
     polygons p2 CROSS JOIN LATERAL vertices(p2.poly) v2
WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;

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

Часто очень удобно использовать LEFT JOIN с LATERAL, чтобы исходные строки появлялись в результате, даже если подзапрос LATERAL не возвращает для них строк. Например, если функция get_product_names() возвращает названия продуктов, выпускаемых конкретным производителем, но при этом некоторые производители в настоящее время не выпускают продукты, мы можем узнать это следующим образом:

SELECT m.name
FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true
WHERE pname IS NULL;

Условие WHERE

Синтаксис WHERE:

WHERE search_condition

где search_condition - любое выражение значения (см. раздел Выражения значения), которое возвращает значение типа boolean.

После обработки предложения FROM каждая строка производной виртуальной таблицы проверяется на соответствие условиям в WHERE. Если результат условия равен true, строка сохраняется в выходной таблице, в противном случае (т. е. если результат false или null), она отбрасывается. В условии поиска обычно указывается хотя бы один столбец из таблицы, созданной в предложении FROM; это не обязательно, но иначе предложение WHERE будет бесполезным.

Вот несколько примеров использования WHERE:

SELECT ... FROM fdt WHERE c1 > 5

SELECT ... FROM fdt WHERE c1 IN (1, 2, 3)

SELECT ... FROM fdt WHERE c1 IN (SELECT c1 FROM t2)

SELECT ... FROM fdt WHERE c1 IN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10)

SELECT ... FROM fdt WHERE c1 BETWEEN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10) AND 100

SELECT ... FROM fdt WHERE EXISTS (SELECT c1 FROM t2 WHERE c2 > fdt.c1)

fdt — это таблица, полученная в предложении FROM. Строки, которые не удовлетворяют условию поиска WHERE, удаляются из fdt. Обратите внимание на использование скалярных подзапросов в качестве выражений значений. Как и любой другой запрос, подзапросы могут использовать сложные табличные выражения. Обратите также внимание на то, как fdt используется в подзапросах. Определение c1 как fdt.c1 необходимо только в том случае, если в таблице подзапроса также имеется столбецc1. Но уточнение имени столбца добавляет читаемости, даже когда оно не нужно. В этом примере показано, как область именования столбцов внешнего запроса распространяется на его подзапросы.

Предложения GROUP BY и HAVING

После прохождения фильтра WHERE строки полученной таблицы могут быть сгруппированы с использованием предложения GROUP BY и затем сгруппированные строки могут быть отфильтрованы с помощью предложения HAVING.

SELECT select_list
    FROM ...
  [WHERE ...]
    GROUP BY grouping_column_reference [, grouping_column_reference]...

Предложение GROUP BY группирует строки таблицы по всем перечисленным в GROUP BY столбцам. Порядок, в котором перечислены столбцы, неважен. Смысл заключается в объединении каждого набора строк, имеющих общие значения, в одну строку группы, которая представляет все строки в группе. Это делается для устранения избыточности в результирующих данных и/или вычисления агрегированных функций, которые применяются к этим группам. Например:

=> SELECT * FROM test1;
 x | y
---+---
 a | 3
 c | 2
 b | 5
 a | 1
(4 rows)

=> SELECT x FROM test1 GROUP BY x;
 x
---
 a
 b
 c
(3 rows)

Во втором примере нельзя записать SELECT * FROM test1 GROUP BY x, потому что не существует единственного значения для y которое можно было бы связать с каждой группой. Сгруппированные столбцы можно использовать в списке выборки, т.к. они имеют единое значение в каждой группе.

В целом, если таблица сгруппирована, то столбцы, которые не перечислены в GROUP BY можно использовать только в агрегатных выражениях. Например:

=> SELECT x, sum(y) FROM test1 GROUP BY x;
 x | sum
---+-----
 a |   4
 b |   5
 c |   2
(3 rows)

Здесь sum - агрегатная функция, которая вычисляет одно значение для всей группы. Более подробную информацию о видах агрегатных функций можно найти в разделе Агрегатные функции.

Группировка без агрегатных выражений по сути выдает набор различающихся значений столбцов. Тот же результат можно получить используя предложение DISTINCT (см. раздел Выбор уникальных записей).

Ещё один пример, который вычисляет общую сумму продаж по каждому продукту (а не общую сумму продаж всех продуктов):

SELECT product_id, p.name, (sum(s.units) * p.price) AS sales
    FROM products p LEFT JOIN sales s USING (product_id)
    GROUP BY product_id, p.name, p.price;

В этом примере столбцы product_id, p.name и p.price должны находиться в предложении GROUP BY, поскольку на них есть ссылки в списке выборки запросов (но см. ниже). Столбец s.units не обязательно должен быть в списке GROUP BY поскольку он используется только в агрегатном выражении (sum(...)), которое представляет количество проданных единиц. Для каждого продукта запрос возвращает итоговую строку обо всех его продажах.

Если бы в таблице продуктов product_id являлся бы первичным ключом, то в примере выше достаточно было бы группировки по product_id, поскольку название и цена функционально зависят от идентификатора продукта, и название и цена однозначно определяются по идентификатору продукта.

В стандарте SQL GROUP BY может группировать только по столбцам исходной таблицы, но QHB расширяет эту возможность, разрешая GROUP BY группировать по столбцам в списке выборки. Также допустима группировка по выражениям вместо имен столбцов.

Если таблица была сгруппирована с использованием GROUP BY, но вам нужны только определенные группы, можно отфильтровать группы с использованием HAVING, работающего аналогично WHERE. Синтаксис:

SELECT select_list FROM ... [WHERE ...] GROUP BY ... HAVING boolean_expression

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

Пример:

=> SELECT x, sum(y) FROM test1 GROUP BY x HAVING sum(y) > 3;
 x | sum
---+-----
 a |   4
 b |   5
(2 rows)

=> SELECT x, sum(y) FROM test1 GROUP BY x HAVING x < 'c';
 x | sum
---+-----
 a |   4
 b |   5
(2 rows)

Более реалистичный пример:

SELECT product_id, p.name, (sum(s.units) * (p.price - p.cost)) AS profit
    FROM products p LEFT JOIN sales s USING (product_id)
    WHERE s.date > CURRENT_DATE - INTERVAL '4 weeks'
    GROUP BY product_id, p.name, p.price, p.cost
    HAVING sum(p.price * s.units) > 5000;

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

Если в запросе есть вызовы агрегатных функций, но нет предложения GROUP BY, то данные все равно будут сгруппированы в результате будет отдельная строка группы (или, возможно, ни одной строки, если эта строка затем отсекается условиями из HAVING). То же самое верно, если запрос содержит предложение HAVING, но не содержит агрегатных функций или предложения GROUP BY.

GROUPING SETS, CUBE и ROLLUP

Более сложные операции группировки, нежели описанные выше, возможны с применением концепции наборов группирования.

GROUPING SETS

Данные, выбранные с помощью предложений FROM и WHERE, группируются по отдельности для каждого заданного набора группирования, агрегатные функции вычисляются для каждой группы как для простых предложений GROUP BY, а затем возвращаются результаты. Например:

=> SELECT * FROM items_sold;
 brand | size | sales
-------+------+-------
 Foo   | L    |  10
 Foo   | M    |  20
 Bar   | M    |  15
 Bar   | L    |  5
(4 rows)

=> SELECT brand, size, sum(sales) FROM items_sold GROUP BY GROUPING SETS ((brand), (size), ());
 brand | size | sum
-------+------+-----
 Foo   |      |  30
 Bar   |      |  20
       | L    |  15
       | M    |  35
       |      |  50
(5 rows)

В каждом внутреннем списке GROUPING SETS можно перечислить ноль (пустой столбец) или более столбцов или выражений, которые интерпретируется так же, как если бы они были указаны непосредственно в предложении GROUP BY. Пустой набор группирования означает, что все строки собраны в одну группу (которая выводится, даже если не было входных строк), как описано выше для агрегатных функций без предложения GROUP BY.

Ссылки на группирующие столбцы или выражения заменяются значениями NULL в результирующих строках тех наборов группирования, в которых эти столбцы не появляются. Чтобы распознавать, результатом какого группирования стала конкретная строка вывода, см. таблицу Группировка операций.

Существуют более краткие записи для указания двух распространенных видов наборов группирования.

ROLLUP

Предложение формы

ROLLUP ( e1, e2, e3, ... )

представляет собой заданный список выражений и всех префиксов списка, включая пустой список, т. е. оно равнозначно:

GROUPING SETS (
    ( e1, e2, e3, ... ),
    ...
    ( e1, e2 ),
    ( e1 ),
    ( )
)

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

CUBE

Предложение формы

CUBE (e1, e2, ...)

представляет заданный список и все его возможные подмножества (т. е. степенное множество). Таким образом,

CUBE (a, b, c)

равносильно

GROUPING SETS (
    ( a, b, c ),
    ( a, b    ),
    ( a,    c ),
    ( a       ),
    (    b, c ),
    (    b    ),
    (       c ),
    (         )
)

Отдельными элементами предложения CUBE или ROLLUP могут быть либо отдельные выражения, либо вложенные списки элементов в скобках. В последнем случае списки обрабатываются как цельные единицы, участвующие в создании отдельных наборов группирования. Например:

CUBE ((a, b), (c, d))

равнозначно

GROUPING SETS (
    ( a, b, c, d ),
    ( a, b       ),
    (       c, d ),
    (            )
)

и

ROLLUP (a, (b, c), d)

равнозначно

GROUPING SETS (
    ( a, b, c, d ),
    ( a, b, c    ),
    ( a          ),
    (            )
)

Конструкции CUBE и ROLLUP могут либо использоваться непосредственно в предложении GROUP BY, либо вкладываться внутрь GROUPING SETS. Если одно предложение GROUPING SETS вложено в другое, результат будет таким же, как если бы все элементы внутреннего GROUPING SETS были записаны непосредственно во внешнем.

Если в одном предложении GROUP BY указано несколько элементов группирования, то окончательный список является прямым произведением этих элементов. Например:

GROUP BY a, CUBE (b, c), GROUPING SETS ((d), (e))

равнозначно

GROUP BY GROUPING SETS (
    (a, b, c, d), (a, b, c, e),
    (a, b, d),    (a, b, e),
    (a, c, d),    (a, c, e),
    (a, d),       (a, e)
)

Примечание
Конструкция (a, b) обычно распознается в выражениях как конструктор строки. Но в предложении GROUP BY это не распространяется на верхние уровни выражений, и (a, b) анализируется как список выражений, как описано выше. Если по какой-то причине вам нужен конструктор строки в выражении группировки, используйте ROW(a, b).

Обработка оконных функций

Если запрос содержит какие-либо оконные функции (см. раздел Руководство по оконным функциям, раздел Оконные функции и раздел Вызовы оконных функций), эти функции вычисляются после каждой группировки, агрегации и фильтрации HAVING. То есть, если запрос использует какие-либо агрегатные функции, предложеия GROUP BY или HAVING, тогда строки, видимые оконными функциями, являются сгруппированными строками, а не исходными строками таблицы из FROM/WHERE.

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

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

Списки выборки

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

Элементы списка выборки

Простейший тип списка выборки — это * который выдает все столбцы, созданные табличным выражением. В общем случае список выборки

  • это список выражений значения через запятую, (как определено в разделе Выражения значения). Например, список имён столбцов:
SELECT a, b, c FROM ...

Имена столбцов a, b и c это либо фактические имена столбцов таблиц, перечисленных в предложении FROM, либо псевдонимы, описанные в соотвествии с разделом Псевдонимы таблиц и столбцов. Пространство имён в списке выборки то же самое, что и в предложении WHERE, если не было группировки, в этом случае оно совпадает с пространством имён в предложении HAVING.

Если в нескольких таблицах существует столбец с одинаковым именем, то необходимо указать имя таблицы:

SELECT tbl1.a, tbl2.a, tbl1.b FROM ...

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

SELECT tbl1.*, tbl2.a FROM ...

Для получения подробной информации о типе записи table_name.* см. раздел Использование составных типов в запросах.

Если в списке выборки используется произвольное выражение значения, концептуально в результирующую таблицу добавляется новый виртуальный столбец в. Выражение значения вычисляется для каждой возвращаемой строки, подменяя ссылки на соответствующие значения из колонок. Однако выражение в запросе не обязательно должно ссылаться на данные, указываемые в предложении FROM, оно также может представлять собой простое арифметическое действие или иное действие возвращающее скалярную величину.

Переименование столбцов

Записям в списке выборки могут быть назначены имена для последующей обработки, например, для использования в предложении ORDER BY или для отображения клиентским приложением. Например:

SELECT a AS value, b + c AS sum FROM ...

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

Ключевое слово AS можно опустить, если имя нового столбца не является ключевым словом QHB (см. раздел Ключевые слова SQL). Чтобы избежать случайного совпадения с ключевым словом, вы можете заключить имя столбца в кавычки. Например, VALUE является ключевым словом, поэтому такой вариант выдаст ошибку:

SELECT a value, b + c AS sum FROM ...

но можно использовать такой вариант:

SELECT a "value", b + c AS sum FROM ...

Для защиты от случайных совпадений с ключевыми словами, которые могут появиться в будущем рекомендуется либо всегда писать AS либо использовать кавычки.

Переименование столбцов, описанное в этом разделе отличается от именования, описанного в предложении FROM (см. раздел Псевдонимы таблиц и столбцов). Можно переименовать столбец дважды, но имя, указанное в списке выборки, будет иметь приоритет.

Выбор уникальных записей

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

SELECT DISTINCT select_list ...

(Чтобы включить поведение по умолчанию и отобразить все строки можно использовать ключевое слово ALL.)

Две строки считаются разными, если они отличаются хотя бы одним значением столбца, при этом значения NULL считаются одинаковыми.

Можно явно определить какие строки считать разными:

SELECT DISTINCT ON (expression [, expression ...]) select_list ...

Здесь expression — это выражение значения, которое вычисляется для всех строк. Набор строк, для которых все выражения равны, считаются дубликатами, и в результате сохраняется только первая строка набора. Обратите внимание, «первая строка» набора может быть произвольной если запрос не отсортирован или сортировка не гарантирует уникальное упорядочение строк, поступающих в фильтр DISTINCT. (Обработка DISTINCT ON происходит после сортировки ORDER BY).

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

Объединение запросов

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

query1 UNION [ALL] query2
query1 INTERSECT [ALL] query2
query1 EXCEPT [ALL] query2

query1 и query2 — это запросы, в которых могут использоваться любые функции, упомянутые ранее.

Операции над множествами могут быть вложенными и связанными, например:

query1 UNION query2 UNION query3

Этот запрос выполнится следующим образом:

(query1 UNION query2) UNION query3

Предложение UNION добавляет результат query2 к результату query1 (порядок строк не гарантирован). Кроме того, он исключает дублирующиеся строки аналогично DISTINCT, если только не указано предложение UNION ALL.

Предложение INTERSECT возвращает все строки, которые находятся в результатах обоих запросов. Дублирующиеся строки фильтруются, если не указано INTERSECT ALL.

Предложение EXCEPT возвращает все строки, которые есть в результате query1 но отсутствуют в результате query2. (Это иногда называют разницей двух запросов). Опять же, дубликаты удаляются, если не используется EXCEPT ALL.

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

Сортировка строк

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

Порядок сортировки задается с помощью предложения ORDER BY:

SELECT select_list
    FROM table_expression
    ORDER BY sort_expression1 [ASC | DESC] [NULLS { FIRST | LAST }]
           [, sort_expression2 [ASC | DESC] [NULLS { FIRST | LAST }] ...]

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

SELECT a, b FROM table1 ORDER BY a + b, c;

Если задано несколько выражений, то последующие значения позволяют отсортировать строки, которые равны по предыдущим значениям. За каждым выражением может следовать необязательное ключевое слово ASC или DESC, которое задает направление сортировки по возрастанию или убыванию, соответственно. По умолчанию используется порядок по возрастанию (ASC) При сортировке по возрастанию сначала ставятся меньшие значения, где «меньший» определяется оператором <. Аналогично, в сортировке по убыванию порядок определяется с помощью оператора1 > .

Для указания места вывода значений NULL используют предложения NULLS FIRST и NULLS LAST, которые указывают как отображать значения NULL - до или после ненулевых значений. По умолчанию значения NULL сортируются, как если бы они были больше, чем любое неNULL значение, т.е. NULLS FIRST является значением по умолчанию для порядка DESC, а NULLS LAST для ASK.

Обратите внимание, что параметры сортировки определяются для каждого столбца отдельно. К примеру, ORDER BY x, y DESC означает ORDER BY x ASC, y DESC, а не ORDER BY x DESC, y DESC.

Выражение sort_expression также может быть меткой столбца или номером итогового столбца, например:

SELECT a + b AS sum, c FROM table1 ORDER BY sum;
SELECT a, max(b) FROM table1 GROUP BY a ORDER BY 1;

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

SELECT a + b AS sum, c FROM table1 ORDER BY sum + c;

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

ORDER BY может также применяться к результату UNION, INTERSECT или EXCEPT, но в этом случае разрешается сортировать только по именам или номерам столбцов, а не по выражениям.

LIMIT и OFFSET

LIMIT и OFFSET позволяют вам получить только часть строк результирующей таблицы:

SELECT select_list
    FROM table_expression
  [ ORDER BY ... ]
  [ LIMIT { number | ALL } ] [ OFFSET number ]

Если задано численное значение LIMIT, то будет возвращено не более указанного количества строк (возможно, меньше, если сам запрос выдаст меньше строк). Указание LIMIT ALL — аналогичен варианту, когда предложение LIMIT отсутствует, а также варианту LIMIT с аргументом NULL.

OFFSET используется, чтобы пропустить заданное количество строк, прежде чем начать возвращать строки. OFFSET 0 — аналогичен варианту когда предложение OFFSET отсуствует, а также варианту OFFSET с аргументом NULL.

Если указаны и OFFSET и LIMIT, то сначала пропускается указанное количество строк OFFSET пропускаются перед началом подсчета возвращаемых строк LIMIT.

При использовании LIMIT необходимо также указывать предложение ORDER BY, чтобы обеспечить определённый порядок выдаваемых результатов, иначе мы получим непредсказуемое подмножество результирующих строк. Можно запросить от десятой до двадцатой строки, но какой порядок имелся в виду? Если вы не указали ORDER BY, порядок будет произвольным.

Оптимизатор запросов учитывает LIMIT, при построении планов запросов, поэтому для разных LIMIT и OFFSET, скорее всего, вы получите разные планы (с разными порядками строк). Таким образом, разные LIMIT/OFFSET без использования ORDER BY, будут выбирать разные подмножества результатов, приводя к противоречивым результатам. Это не ошибка, а неотъемлемое следствие того факта, что SQL не гарантирует порядка выводов результатов запроса, если порядок вывода не определен предолжением ORDER BY.

Строки, пропускаемые предложением OFFSET, все равно вычисляются на сервере, поэтому задавать большое значение OFFSET неэффективно.

Списки VALUES

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

VALUES ( expression [, ...] ) [, ...]

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

Например:

VALUES (1, 'one'), (2, 'two'), (3, 'three');

вернет таблицу из двух столбцов и трех строк. Этот пример эквивалентен следующему запросу:

SELECT 1 AS column1, 'one' AS column2
UNION ALL
SELECT 2, 'two'
UNION ALL
SELECT 3, 'three';

По умолчанию QHB назначает столбцам таблицы VALUES имена column1, column2 и т.д. . Имена столбцов не определены стандартом SQL, и разные СУБД могут именовать столбцы другим образом, поэтому хорошим стилем считается переопределение имен по умолчанию списком псевдонимов таблицы, например так:

=> SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t (num,letter);
 num | letter
-----+--------
   1 | one
   2 | two
   3 | three
(3 rows)

Синтаксически спискок VALUES равнозначен следующей записи:

SELECT select_list FROM table_expression

и может использоваться везде, где допустим SELECT. Например, вы можете использовать его как часть UNION или присоединить к нему sort_specification (ORDER BY, LIMIT и/или OFFSET). Также VALUES часто используется в качестве источника данных в команде INSERT и в качестве подзапроса.

Для получения дополнительной информации см. VALUES.

Запросы WITH (общие табличные выражения)

WITH предоставляет способ написания дополнительных операторов для использования в больших запросах. Эти операторы, которые часто называют общими табличными выражениями или CTE (Common Table Expressions), можно рассматривать как определения временных таблиц, которые существуют в рамках одного запроса. Дополнительным оператором в предложении WITH может быть SELECT, INSERT, UPDATE или DELETE, а само предложение WITH присоединяется к основному оператору (которым также может быть SELECT, INSERT, UPDATE или DELETE).

SELECT в WITH

Основное назначение SELECT в WITH - разбивать сложные запросы на простые части. Например:

WITH regional_sales AS (
    SELECT region, SUM(amount) AS total_sales
    FROM orders
    GROUP BY region
), top_regions AS (
    SELECT region
    FROM regional_sales
    WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

Этот запрос отображает итоги продаж по отдельным продуктам для топовых регионов. Предложение WITH определяет два вспомогательных оператора с именами regional_sales и top_regions, где данные, полученные в regional_sales, используются в top_regions а данные, полученные в top_regions, используются в основном запросе SELECT. Этот пример можно было бы написать без использования WITH, но нам потребовалось бы два уровня вложенных подзапросов. Вариант с WITH выглядит намного проще.

Необязательный модификатор RECURSIVE превращает WITH из простой синтаксической конструкции в функцию, выполняющую вещи невозможные в стандартном SQL. С RECURSIVE, запрос WITH может обращаться к своему собственному результату. Это видно на очень простом примере — запрос для вычисления суммы целых чисел от 1 до 100:

WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;

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

Вычисление рекурсивных запросов

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

  2. Пока рабочий стол не пуст, повторите эти шаги:

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

    2. Содержимое рабочей таблицы заменяется содержимым промежуточной таблицы, затем промежуточная таблица очищается.

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

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

WITH RECURSIVE included_parts(sub_part, part, quantity) AS (
    SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'
  UNION ALL
    SELECT p.sub_part, p.part, p.quantity
    FROM included_parts pr, parts p
    WHERE p.part = pr.sub_part
)
SELECT sub_part, SUM(quantity) as total_quantity
FROM included_parts
GROUP BY sub_part

При работе с рекурсивными запросами важно быть уверенным, что рекурсивная часть запроса в конце не будет возвращать кортежи (строки), иначе запрос будет бесконечным. Иногда, можно добиться этого используя UNION вместо UNION ALL, т.к. при этом отбрасываются строки, которые дублируют предыдущие выходные строки. Однако часто в цикле не встречаются строки, которые совпадают полностью, поэтому можно проверить только одно или несколько полей, чтобы узнать, не была ли ранее достигнута текущая точка. Стандартный метод решения таких задач состоит в том, чтобы вычислить массив уже обработанных значений. Например, рассмотрим следующий запрос, который ищет таблицу с помощью поля link:

WITH RECURSIVE search_graph(id, link, data, depth) AS (
    SELECT g.id, g.link, g.data, 1
    FROM graph g
  UNION ALL
    SELECT g.id, g.link, g.data, sg.depth + 1
    FROM graph g, search_graph sg
    WHERE g.id = sg.link
)
SELECT * FROM search_graph;

Этот запрос зацикливается, если связи link содержат циклы. Поскольку нам требуется вывод depth, простое изменение UNION ALL на UNION не поможет. Вместо этого нам нужно определить, достигали ли мы текущей строки ранее, следуя определенному пути. Мы добавляем два столбца path и cycle к результату и получаем:

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
    SELECT g.id, g.link, g.data, 1,
      ARRAY[g.id],
      false
    FROM graph g
  UNION ALL
    SELECT g.id, g.link, g.data, sg.depth + 1,
      path || g.id,
      g.id = ANY(path)
    FROM graph g, search_graph sg
    WHERE g.id = sg.link AND NOT cycle
)
SELECT * FROM search_graph;

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

В общем случае если для распознавания цикла, необходимо проверить более одного поля, можно использовать массив строк. Например, если нам нужно сравнить поля f1 и f2:

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
    SELECT g.id, g.link, g.data, 1,
      ARRAY[ROW(g.f1, g.f2)],
      false
    FROM graph g
  UNION ALL
    SELECT g.id, g.link, g.data, sg.depth + 1,
      path || ROW(g.f1, g.f2),
      ROW(g.f1, g.f2) = ANY(path)
    FROM graph g, search_graph sg
    WHERE g.id = sg.link AND NOT cycle
)
SELECT * FROM search_graph;

Опустите синтаксис ROW() в общем случае, когда нужно проверить только одно поле для распознавания цикла. Это позволяет использовать простой массив, а не массив составного типа, что повышает эффективность.

Алгоритм рекурсивного вычисления запроса выводит результаты в порядке поиска в ширину (BFS). Вы можете также получить результаты, отсортированные в порядке поиска в глубину (DFS), сделав внешний запрос ORDER BY по столбцу «path», как в примере выше.

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

WITH RECURSIVE t(n) AS (
    SELECT 1
  UNION ALL
    SELECT n+1 FROM t
)
SELECT n FROM t LIMIT 100;

В данном случае зацикливания не происходит, поскольку QHB выдает столько строк запроса WITH сколько фактически возвращает родительский запрос. Использование этого трюка в производственной среде не рекомендуется, потому что другие СУБД могут работать по-другому. Кроме того, подобный запрос не будет работать, если вы выполните внешний запрос сортировки результатов рекурсивного запроса или присоедините их к какой-либо другой таблице, потому что в таких случаях внешний запрос обычно пытается извлечь все данные запроса WITH.

Полезным свойством запросов WITH является то, что они обычно вычисляются только один раз для родительского запроса, даже если родительский запрос или одноуровневый запрос WITH ссылается на них более одного раза. Таким образом, долгие вычисления, которые необходимы в нескольких местах, могут быть помещены в запрос WITH для ускорения работы запроса. Другим возможным применением является предотвращение нежелательных множественных вычислений функций с побочными эффектами. Однако есть и обратная сторона этой монеты - оптимизатор не может перенести ограничения из родительского запроса в запрос WITH с множественными ссылками, поскольку это может повлиять использование WITH во всех местах, когда должно повлиять только на один. Запрос WITH с множественными ссылками будет выполняться как записан и возвращать все строки, включая те которые родительский запрос может впоследствии отбросить. (Но, как упоминалось выше, вычисление может остановиться раньше, если в ссылке на запрос потребуется ограниченное количество строк).

Однако, если запрос WITH является нерекурсивным и не имеет побочных эффектов (то есть, это SELECT без зменчивых функций), его можно сложить в родительский запрос, что позволяет оптимизировать совместно два уровня запроса. По умолчанию это происходит, только если родительский запрос ссылается на запрос WITH один раз, а не несколько. Вы можете переопределить это решение, указав MATERIALIZED, чтобы вычислять запрос WITH отдельно, или указав NOT MATERIALIZED, чтобы принудительно включить его в родительский запрос. Последний вариант может привести к дублированию вычислений запроса WITH, но это все равно может оказаться выгодным, если при каждом использовании запроса WITH требуется только небольшая часть полного результата запроса WITH.

Простой пример этих правил:

WITH w AS (
    SELECT * FROM big_table
)
SELECT * FROM w WHERE key = 123;

Этот запрос WITH будет свернут в родительский и создаст тот же план выполнения, что и:

SELECT * FROM big_table WHERE key = 123;

В частности, если в таблице есть индекс по key, он, вероятно, будет использоваться для выборки строк с key = 123. С другой стороны, в:

WITH w AS (
    SELECT * FROM big_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
WHERE w2.key = 123;

запрос WITH будет материализован, создавая временную копию big_table которая затем соединяется сама с собой без использования какого-либо индекса. Этот запрос будет выполнен намного эффективнее, если он будет записан как:

WITH w AS NOT MATERIALIZED (
    SELECT * FROM big_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
WHERE w2.key = 123;

так что ограничения родительского запроса могут быть применены непосредственно к просмотрам big_table.

Следующий пример показывает случай, когда NOT MATERIALIZED нежелателен:

WITH w AS (
    SELECT key, very_expensive_function(val) as f FROM some_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.f = w2.f;

Здесь материализация запроса WITH гарантирует, что very_expensive_function вычисляется только один раз для каждой строки таблицы, а не дважды.

В приведенных выше примерах показано, что WITH используется с SELECT, но его можно присоединить также к INSERT, UPDATE или DELETE. В каждом случае он создает временную таблицу(ы), на которую можно ссылаться в основной команде.

Операторы изменения данных в WITH

Вы можете использовать операторы изменения данных (INSERT, UPDATE или DELETE) в WITH. Это позволяет вам выполнять несколько разных операций в одном запросе. Например:

WITH moved_rows AS (
    DELETE FROM products
    WHERE
        "date" >= '2010-10-01' AND
        "date" < '2010-11-01'
    RETURNING *
)
INSERT INTO products_log
SELECT * FROM moved_rows;

Этот запрос перемещает строки из products в products_log. Оператор DELETE в WITH удаляет указанные строки из products и возвращает их содержимое с помощью предложения RETURNING. А затем основной запрос читает этот вывод и вставляет его в products_log.

Изюминкой вышеприведенного примера является то, что предложение WITH присоединяется к INSERT, а не к SELECT в INSERT. Это необходимо, т.к. WITH может содержать операторы, изменяющие данные, только на верхнем уровне. Тем не менее, при этом применяются обычные правила видимости WITH, поэтому можно обратиться к выводу оператора WITH из подзапроса.

Операторы изменения данных в WITH обычно дополняются предложением RETURNING (см. раздел Возврат данных из измененных строк), как показано в примере выше. Именно результат данные предложения RETURNING, а не таблица назначения оператора изменения данных, формируют временную таблицу, на которую может ссылаться остальная часть запроса. Если в операторе изменения данных в WITH отсутствует предложение RETURNING, то оно не образует временную таблицу и не может быть использовано в остальной части запроса. Тем не менее, такой запрос, будет выполнен. Вырожденный пример:

WITH t AS (
    DELETE FROM foo
)
DELETE FROM bar;

Этот пример удалит все строки из таблиц foo и bar. Количество затронутых строк, о которых будет сообщено клиенту, будет посчитано только по строкам, удаленным из bar.

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

WITH RECURSIVE included_parts(sub_part, part) AS (
    SELECT sub_part, part FROM parts WHERE part = 'our_product'
  UNION ALL
    SELECT p.sub_part, p.part
    FROM included_parts pr, parts p
    WHERE p.part = pr.sub_part
)
DELETE FROM parts
  WHERE part IN (SELECT part FROM included_parts);

Этот запрос удалит все прямые и косвенные части продукта.

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

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

WITH t AS (
    UPDATE products SET price = price * 1.05
    RETURNING *
)
SELECT * FROM products;

внешний SELECT будет возвращать исходные цены, которые были до действия UPDATE, в то время как в

WITH t AS (
    UPDATE products SET price = price * 1.05
    RETURNING *
)
SELECT * FROM t;

внешний SELECT вернет обновлённые данные.

Многократное обновление строк в одном операторе не поддерживается. Происходит только одна из модификаций, но сложно (а иногда и невозможно) предсказать, какая именно. То же самое с удалением строки, которая обновляется в том же самом операторе: в результате выполнится только обновление. Поэтому не следует пытаться модифицировать одну строку дважды в одном выражении. В частности, избегайте написания вложенных операторов WITH которые могут повлиять на строки, измененные основным оператором или дочерним вложенным оператором. Результаты таких запросов будут непредсказуемыми.

В настоящее время любая таблица, используемая как цель оператора изменения данных в WITH не должна иметь ни условного правила, ни правила ALSO, ни правила INSTEAD которое расширяется до нескольких операторов.

1

На самом деле QHB использует класс оператора B-дерева, чтобы определить порядок сортировки для ASC и DESC. Обычно типы данных устанавливаются так, чтобы порядку сортировки соотвествовали операторы < и >, можно разработать собственный тип данных с иным поведением.