Табличное выражение вычисляет таблицу. Табличное выражение содержит FROM
предложение, за которым могут следовать предложения WHERE
, GROUP BY
и HAVING
. Простые табличные выражения просто ссылаются на таблицу на диске, так называемую базовую таблицу, но более сложные выражения могут использоваться для изменения или объединения базовых таблиц различными способами.
Необязательные предложения WHERE
, GROUP BY
и HAVING
в табличном выражении задают конвейер последовательных преобразований, выполняемых в таблице, полученной в FROM
предложении. Все эти преобразования создают виртуальную таблицу, предоставляющую строки, которые передаются в список выбора для вычисления выходных строк запроса.
Предложение FROM
Предложение FROM
выводит таблицу из одной или нескольких других таблиц, указанных в списке ссылок на таблицы, разделенных запятыми.
FROM table_reference [, table_reference [, ...]]
Ссылкой на таблицу может быть имя таблицы (возможно, дополненное схемой) или производная таблица, такая как подзапрос, JOIN
конструкция или их сложная комбинация. Если в предложении указано более одной ссылки на таблицу FROM
, таблицы соединяются перекрестно (то есть формируется декартово произведение их строк; см. ниже). Результатом FROM
списка является промежуточная виртуальная таблица, которая затем может быть подвергнута преобразованиям с помощью предложений WHERE
, GROUP BY
, и HAVING
и, наконец, является результатом общего табличного выражения.
Когда ссылка на таблицу называет таблицу, которая является родителем иерархии наследования таблиц, ссылка на таблицу создает строки не только этой таблицы, но и всех ее дочерних таблиц, если только ключевое слово не ONLY
предшествует имени таблицы. Однако ссылка создает только те столбцы, которые появляются в именованной таблице — любые столбцы, добавленные в подтаблицы, игнорируются.
Вместо того, чтобы писать ONLY
перед именем таблицы, вы можете написать *
после имени таблицы, чтобы явно указать, что включены дочерние таблицы. Нет никакой реальной причины использовать этот синтаксис больше, потому что поиск в таблицах-потомках теперь всегда является поведением по умолчанию. Однако он поддерживается для совместимости со старыми версиями.
Присоединенные таблицы
Соединяемая таблица — это таблица, полученная из двух других (реальных или производных) таблиц в соответствии с правилами конкретного типа соединения. Доступны внутренние, внешние и поперечные соединения. Общий синтаксис объединенной таблицы:
T1 join_type T2[ ]
join_condition
Соединения всех типов могут быть объединены в цепочку или вложены друг в друга: одно или оба , T1
и T2
могут быть объединены в таблицы. Круглые скобки можно использовать вокруг JOIN
предложений для управления порядком соединения. При отсутствии круглых скобок JOIN
предложения располагаются слева направо.
Join Types
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 TRUEFROM T1, T2
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
выражение оценивается как истинное.
Предложение USING
— это сокращение, которое позволяет вам воспользоваться конкретной ситуацией, когда обе стороны соединения используют одно и то же имя для присоединяемого столбца (столбцов). Он принимает разделенный запятыми список имен общих столбцов и формирует условие соединения, которое включает сравнение на равенство для каждого из них. Например, объединение T1
и T2
with 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
, создавая объединение между продуктами.
Чтобы собрать это вместе, предположим, что у нас есть таблицы 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
предложение, обрабатывается после объединения. Это не имеет значения для внутренних соединений, но имеет большое значение для внешних соединений.
Псевдонимы таблиц и столбцов
Временное имя может быть присвоено таблицам и сложным ссылкам на таблицы, которые будут использоваться для ссылок на производную таблицу в остальной части запроса. Это называется псевдонимом таблицы .
Чтобы создать псевдоним таблицы, напишите
FROM table_reference AS alias
или
FROM table_reference alias
Ключевое AS
слово — необязательный шум. alias
может быть любым идентификатором.
Типичным применением псевдонимов таблиц является присвоение коротких идентификаторов длинным именам таблиц, чтобы предложения соединения оставались читабельными. Например:
SELECT * FROM some_very_long_table_name s JOIN other_fairly_long_name a ON s.id = a.num;
Псевдоним становится новым именем ссылки на таблицу в отношении текущего запроса — нельзя ссылаться на таблицу по исходному имени в другом месте запроса. Таким образом, это недействительно:
SELECT * FROM my_table AS m WHERE my_table.a > 5; -- неправильный
Псевдонимы таблиц в основном предназначены для удобства записи, но их необходимо использовать при присоединении таблицы к самой себе, например:
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 ...
является действительным SQL, но:
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
списка необязательно, но это хорошая практика.
Табличные Функции
Табличные функции — это функции, создающие набор строк, состоящих либо из базовых типов данных (скалярные типы), либо из составных типов данных (табличные строки). Они используются как таблица, представление или подзапрос в FROM
предложении запроса. Столбцы, возвращаемые табличными функциями, могут быть включены в предложения SELECT
, JOIN
, или WHERE
так же, как столбцы таблицы, представления или подзапроса.
Табличные функции также можно комбинировать с использованием ROWS FROM
синтаксиса, при этом результаты возвращаются в параллельных столбцах; количество строк результатов в этом случае соответствует наибольшему результату функции, а меньшие результаты дополняются нулевыми значениями для соответствия.
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
вызывалась для каждого параметра отдельно и объединялась с помощью 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
предложения, 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
поскольку его можно использовать для любого типа запроса. Фактический набор столбцов должен быть указан в вызывающем запросе, чтобы синтаксический анализатор знал, например, до чего *
следует расширяться.
В этом примере используются 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
сортирует значения столбца как целые числа.
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
в первую очередь полезно, когда столбец с перекрестными ссылками необходим для вычисления строки (строк), которые необходимо объединить. Обычное приложение предоставляет значение аргумента для функции, возвращающей множество. Например, предположим, что vertices(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
предложения каждая строка производной виртуальной таблицы проверяется на соответствие условию поиска. Если результат условия истинен, строка сохраняется в выходной таблице, в противном случае (т. е. если результат ложен или нулевой) она отбрасывается. Условие поиска обычно ссылается по крайней мере на один столбец таблицы, сгенерированной в 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
as 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
используется для группировки тех строк в таблице, которые имеют одинаковые значения во всех перечисленных столбцах. Порядок, в котором перечислены столбцы, не имеет значения. Эффект заключается в объединении каждого набора строк, имеющих общие значения, в одну групповую строку, которая представляет все строки в группе. Это делается для устранения избыточности в выходных и/или вычислительных агрегатах, которые применяются к этим группам. Например:
=> 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
агрегатная функция, которая вычисляет одно значение для всей группы.
Вот еще один пример: он вычисляет общий объем продаж для каждого продукта (а не общий объем продаж всех продуктов):
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
, так как название и цена будут функционально зависеть от ID товара, и поэтому не будет двусмысленности о том, какое имя и значение цены возвращать для каждой группы идентификаторов продуктов.
В строгом SQL GROUP BY
можно группировать только по столбцам исходной таблицы, но PostgreSQL расширяет это, позволяя также 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
Более сложные операции группировки, чем описанные выше, возможны при использовании концепции группирующих множеств . Данные, выбранные с помощью предложений 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
предложения.
Ссылки на столбцы или выражения группировки заменяются нулевыми значениями в строках результатов для наборов группировки, в которых эти столбцы не отображаются. Чтобы определить, из какой группы получена конкретная выходная строка.
Для указания двух распространенных типов набора группировок предусмотрена сокращенная нотация. Предложение формы
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
or 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, используемого при группировании того же столбца.
Обработка оконных функций
Если запрос содержит какие-либо оконные функции, эти функции оцениваются после выполнения любой группировки, агрегации и HAVING
фильтрации. То есть, если в запросе используются какие-либо агрегаты, GROUP BY
или HAVING
, то строки, видимые оконными функциями, являются строками группы, а не исходными строками таблицы из FROM
/ WHERE
.
Когда используется несколько оконных функций, все оконные функции, имеющие синтаксически эквивалентные предложения PARTITION BY
и ORDER BY
в своих определениях окон, гарантированно будут оцениваться за один проход по данным. Поэтому они увидят один и тот же порядок сортировки, даже если ORDER BY
он не определяет порядок однозначно. Однако не дается никаких гарантий относительно оценки функций, имеющих другие PARTITION BY
спецификации ORDER BY
. (В таких случаях обычно требуется этап сортировки между проходами оценок оконной функции, и сортировка не гарантирует сохранения порядка строк, который он ORDER BY
считает эквивалентным.)
В настоящее время оконные функции всегда требуют предварительно отсортированных данных, поэтому выходные данные запроса будут упорядочены в соответствии с тем или иным из предложений оконных функций PARTITION BY
/ ORDER BY
. Однако полагаться на это не рекомендуется. Используйте явное предложение верхнего уровня ORDER BY
, если вы хотите быть уверены, что результаты отсортированы определенным образом.
0 комментариев