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

7.2. Табличные выражения
Назад	Наверх	Глава 7. Запросы	Начало	Далее

---

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

7.2.1. Предложение FROM

7.2.2. Предложение WHERE

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

7.2.4. GROUPING SETS, CUBE и ROLLUP

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

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

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

7.2.1. Предложение FROM

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

FROM table_reference [, table_reference [, ...]]

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

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

Вместо того чтобы писать ONLY перед именем таблицы, вы можете написать * после имени таблицы, чтобы явно указать, что включены потомки таблицы. Нет реальной причины использовать этот синтаксис больше, потому что поиск потомков таблиц теперь всегда является поведением по умолчанию. Однако он поддерживается для совместимости с более старыми версиями.

7.2.1.1. Joined Tables

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

T1 join_type T2 [ join_condition ]

Соединения всех типов могут быть объединены вместе или вложены: как 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. Таким образом, объединенная таблица всегда имеет по крайней мере одну строку для каждой строки в T1.

RIGHT OUTER JOIN

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

FULL OUTER JOIN

Сначала выполняется внутреннее соединение. Затем, для каждой строки в T1, которая не удовлетворяет условию соединения с любой строкой в T2, добавляется объединенная строка с пустыми значениями в столбцах T2. Также, для каждой строки T2, которая не удовлетворяет условию соединения с любой строкой в T1, добавляется объединенная строка с пустыми значениями в столбцах T1.

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

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

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

Для создания псевдонима таблицы напишите

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;

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

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

это допустимый SQL, но:

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

не является допустимым; псевдоним таблицы a не виден за пределами псевдонима c.

7.2.1.3. Подзапросы

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

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 является необязательным, но является хорошей практикой. Дополнительную информацию см. в разделе Раздел 7.7.

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

Табличные функции - это функции, которые производят набор строк, состоящих из базовых типов данных (скалярных типов) или составных типов данных (строк таблицы). Они используются как таблица, представление или подзапрос в разделе 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. Этот столбец нумерует строки результирующего набора функции, начиная с 1. (Это обобщение синтаксиса стандарта SQL для UNNEST ... WITH ORDINALITY). По умолчанию, столбец с порядковым номером называется ordinality, но можно назначить ему другое имя с помощью предложения AS.

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

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

Если не указан тег table_alias, то имя функции используется в качестве имени таблицы; в случае конструкции 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 без параметров OUT. Когда такая функция используется в запросе, ожидаемая структура строки должна быть указана в самом запросе, чтобы система могла знать, как разбирать и планировать запрос. Синтаксис выглядит следующим образом:

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, вместо списка псевдонимов столбцов после ROWS FROM() может быть записан список column_definition.

Рассмотрим следующий пример:

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

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

В этом примере используется ROWS FROM:

SELECT *
FROM ROWS FROM
    (
        json_to_recordset('[{"a":40,"b":"foo"},{"a":"100","b":"bar"}]')
            AS (a INTEGER, b TEXT),
        generate_series(1, 3)
    ) AS x (p, q, s)
ORDER BY p;

  p  |  q  | s
-----+-----+---
  40 | foo | 1
 100 | bar | 2
     |     | 3

Он объединяет две функции в одну целевую FROM. json_to_recordset() указано вернуть два столбца, первый integer и второй text. Результат generate_series() используется напрямую. Предложение ORDER BY сортирует значения столбца как целые числа.

7.2.1.5. Подзапросы 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;

Это не особенно полезно, так как оно имеет точно такой же результат, как более традиционный

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

LATERAL is primarily useful when the cross-referenced column is necessary for computing the row(s) to be joined. A common application is providing an argument value for a set-returning function. For example, supposing that vertices(polygon) returns the set of vertices of a polygon, we could identify close-together vertices of polygons stored in a table with:

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;

7.2.2. Предложение WHERE

Синтаксис WHERE предложения выглядит так:

WHERE search_condition

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

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

Примечание

Условие соединения внутреннего соединения может быть записано как в предложении WHERE, так и в предложении JOIN. Например, эти табличные выражения эквивалентны:

FROM a, b WHERE a.id = b.id AND b.val > 5

и

FROM a INNER JOIN b ON (a.id = b.id) WHERE b.val > 5

или, возможно, даже:

FROM a NATURAL JOIN b WHERE b.val > 5

Какой из них использовать - это в основном вопрос стиля. Синтаксис JOIN в предложении FROM, вероятно, не такой переносимый для других систем управления базами данных SQL, хотя он присутствует в стандарте SQL. Для внешних соединений нет выбора: они должны выполняться в предложении FROM. Предложение ON или USING внешнего соединения не эквивалентно предложению 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 также является именем столбца в исходной входной таблице подзапроса. Но квалификация имени столбца добавляет ясность, даже когда это не требуется. Этот пример показывает, как область именования столбцов внешнего запроса распространяется на его внутренние запросы.

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

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

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

Предложение 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 - это агрегатная функция, которая вычисляет одно значение для всей группы. Более подробную информацию о доступныхагрегатных функциях можно найти в Раздел 9.21.

Подсказка

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

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

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(...)), которое представляет сумму продаж продукта. Для каждого продукта запрос возвращает сводную строку о всех продажах продукта.

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

В строгом SQL GROUP BY может группировать только по столбцам исходной таблицы, но Tantor SE расширяет это, позволяя 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.

7.2.4. GROUPING SETS, CUBE и ROLLUP

Более сложные операции группировки, чем описанные выше, возможны с использованием концепции групповых наборов. Данные, выбранные с помощью предложений 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 в результирующих строках для группировочных наборов, в которых эти столбцы не появляются. Чтобы отличить, из какой группировки получена конкретная выходная строка, см. Таблица 9.62.

Для указания двух распространенных типов группировочного набора предоставляется сокращенная запись. Предложение в форме

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

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

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

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

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

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

Если в одном 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)
)

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

GROUP BY ROLLUP (a, b), ROLLUP (a, c)

эквивалентно

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

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

GROUP BY DISTINCT ROLLUP (a, b), ROLLUP (a, c)

эквивалентно

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

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

Примечание

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

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

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

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

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

---

Назад	Наверх	Далее
7.1. Обзор	Начало	7.3. Списки выбора