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

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

FROM Clause создаёт таблицу из одной или более других таблиц, которые задаются списком разделённых запятыми ссылок на таблицы:

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

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

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

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

 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)

=> 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)

=> 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)

FROM ссылка_на_таблицу AS псевдоним

FROM ссылка_на_таблицу псевдоним

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;    -- ошибка

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

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 ссылка_на_таблицу [AS] псевдоним ( колонка1 [, колонка2 [, ...]] )

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

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

FROM (SELECT * FROM table1) AS alias_name

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

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;

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

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

Синтаксис WHERE Clause следующий:

WHERE условие_поиска

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

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

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

SELECT ... FROM fdt WHERE c1 > 5

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

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

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

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

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

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

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

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

SELECT список_выборки
    FROM ...
    [WHERE ...]
    GROUP BY ссылка_на_группирумую_колонку [, ссылка_на_группирумую_колонку]...

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

=> 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 является агрегатной функцией, которая подсчитывает одно значение во всей группе. Больше информации о доступных агрегатных функциях можно найти в Section 9.18.

Здесь другой пример: он подсчитывает полные продажи для каждого продукта (вместо подсчёта полных продаж всех продуктов):

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 в данном выше примере, так как name и price должны быть функционально зависимы от product_id и таким образом не должно быть двусмысленности в отношенни того какие name и price будут соответствовать каждой группе product_id.

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

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

SELECT список_выбора FROM ... [WHERE ...] GROUP BY ... HAVING логическое_выражение

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

Ghbvth:

=> 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. Обработка оконных функций

Если запрос содержит какие-либо оконные функции (см. Section 3.5, Section 9.19 и Section 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 верхнего уровня, если вы хотите иметь уверенность, что результаты сортируются должным образом.