Табличное выражение вычисляет таблицу. Табличное выражение содержит 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и T2with 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ссылаются в подзапросах. Уточнение c1as 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 ),
( )
)Отдельные элементы предложения CUBEor 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 комментариев