SELECT

Синтаксис

[ WITH FUNCTION sql_routines ]
[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT ] select_expression [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY [ ALL | DISTINCT ] grouping_element [, ...] ]
[ HAVING condition]
[ WINDOW window_definition_list]
[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
[ ORDER BY expression [ ASC | DESC ] [, ...] ]
[ OFFSET count [ ROW | ROWS ] ]
[ LIMIT { count | ALL } ]
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES } ]

где from_item одно из

table_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]

from_item join_type from_item
  [ ON join_condition | USING ( join_column [, ...] ) ]

table_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
  MATCH_RECOGNIZE pattern_recognition_specification
    [ [ AS ] alias [ ( column_alias [, ...] ) ] ]

Описание выражения MATCH_RECOGNIZE смотрите в документе MATCH_RECOGNIZE.

TABLE (table_function_invocation) [ [ AS ] alias [ ( column_alias [, ...] ) ] ]

Описание использования табличных функций смотрите в документе Табличные функции.

и join_type одно из

[ INNER ] JOIN
LEFT [ OUTER ] JOIN
RIGHT [ OUTER ] JOIN
FULL [ OUTER ] JOIN
CROSS JOIN

и grouping_element одно из

()
expression
GROUPING SETS ( ( column [, ...] ) [, ...] )
CUBE ( column [, ...] )
ROLLUP ( column [, ...] )

Описание

Возвращает данные из таблиц и представлений.

Выражение WITH FUNCTION

Позволяет задать список inline SQL routines, доступных для запроса. Например:

WITH 
  FUNCTION hello(name varchar)
    RETURNS varchar
    RETURN format('Hello %s!', 'name'),
  FUNCTION bye(name varchar)
    RETURNS varchar
    RETURN format('Bye %s!', 'name')
SELECT hello('Finn') || ' and ' || bye('Joe');
-- Hello Finn! and Bye Joe!

См. SQL routines.

Выражение WITH

Определяет общее табличное выражение (common table expression, CTE), которое может быть использовано несколько раз в запросе. Например, следующие запросы эквивалентны:

SELECT a, b
FROM (
  SELECT a, MAX(b) AS b FROM t GROUP BY a
) AS x;

WITH x AS (SELECT a, MAX(b) AS b FROM t GROUP BY a)
SELECT a, b FROM x;

Запрос может содержать несколько CTE:

WITH
  t1 AS (SELECT a, MAX(b) AS b FROM x GROUP BY a),
  t2 AS (SELECT a, AVG(d) AS d FROM y GROUP BY a)
SELECT t1.*, t2.*
FROM t1
JOIN t2 ON t1.a = t2.a;

Последующие CTE могут ссылаться на предыдущие:

WITH
  x AS (SELECT a FROM t),
  y AS (SELECT a AS b FROM x),
  z AS (SELECT b AS c FROM y)
SELECT c FROM z;

Предупреждение

В общем случае каждое вхождение одного и того же CTE будет выполнено независимо от других. Если результат CTE недетерминирован, результаты выполнения одного и того же CTE в одном запросе могут отличаться.

Выражение WITH RECURSIVE

Позволяет выполнять рекурсивные CTE.

Предупреждение

Функциональность доступна в экспериментальном режиме. Некоторые запросы могут возвращать некорректный результат. Использование рекурсивных CTE может приводить к существенному росту нагрузки на кластер.

Рекурсивное выражение WITH должно быть сформировано как UNION двух подзапросов. Первый подзапрос называется базой рекурсии, второй шагом рекурсии.

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

Пример рекурсивного выражения:

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

VALUES (1) определяет базу рекурсии. SELECT n + 1 FROM t WHERE n < 4 определяет шаг рекурсии. Выполнение данного запроса происходит следующим образом:

• база рекурсии возвращает 1

• первый шаг рекурсии возвращает 1 + 1 = 2

• второй шаг рекурсии возвращает 2 + 1 = 3

• третий шаг рекурсии возвращает: 3 + 1 = 4

• процесс рекурсии прерывается, так как достигнуто условие остановки n = 4

• в качестве результата подвыражение t возвращает значения 1, 2, 3, 4

• функция SUM суммирует значения t, возвращая финальный результат 10

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

Если рекурсивное выражение сформировано не в соответствии с правилами, упомянутыми выше, или не содержит рекурсивной ссылки, оно обрабатывается как обычное CTE.

Рекурсивные выражения имеют следующие ограничения:

• допустимы только рекурсивные циклы с одним элементов

• использование LIMIT, OUTER JOIN и set-операций (UNION, INTERSECT, MINUS) в шаге рекурсии не всегда допустимо

• максимальная глубина рекурсии фиксирована и не зависит от возвращаемых запросом данных. Значение по умолчанию равно 10. Вы можете настроить глубину рекурсии с помощью свойства сессии max_recursion_depth. Увеличение глубины рекурсии приводит к квадратичному увеличению размера плана запроса.

Выражение SELECT

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

SELECT [ ALL | DISTINCT ] select_expression [, ...]

Ключевое слово ALL возвращает все результирующие строки запроса. Ключевое слово DISTINCT возвращает только уникальные строки. По умолчанию применяется режим ALL.

Выражение select_expression

Должно быть представлено в одной из следующих форм:

expression [ [ AS ] column_alias ]

row_expression.* [ AS ( column_alias [, ...] ) ]

relation.*

*

Выражение expression [ [ AS ] column_alias ] возвращает один столбец.

В случае row_expression.* [ AS ( column_alias [, ...] ) ], row_expression является произвольным выражением типа ROW. В результат запроса будут включены все столбцы выражения.

В случае relation.* все столбцы relation будут включены в результат запроса. Псевдонимы столбцов не допускаются.

В случае * все столбцы отношений запроса будут включены в результат.

Порядок столбцов в результате совпадает с порядком их указания в запросе.

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

SELECT (CAST(ROW(1, true) AS ROW(field1 bigint, field2 boolean))).* AS (alias1, alias2);

alias1 | alias2
--------+--------
     1 | true
(1 row)

В противном случае используются существующие имена:

SELECT (CAST(ROW(1, true) AS ROW(field1 bigint, field2 boolean))).*;

field1 | field2
--------+--------
     1 | true
(1 row)

При отсутствии имен, столбцам будут заданы автоматически сгенерированные значения:

SELECT (ROW(1, true)).*;

_col0 | _col1
-------+-------
    1 | true
(1 row)

Выражение GROUP BY {#select-having}

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

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

SELECT count(*), nationkey FROM customer GROUP BY 2;

SELECT count(*), nationkey FROM customer GROUP BY nationkey;

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

SELECT count(*) FROM customer GROUP BY mktsegment;

_col0
-------
29968
30142
30189
29949
29752
(5 rows)

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

Сложные операции группировки

CedrusData поддерживает сложные операции группировки GROUPING SETS, CUBE и ROLLUP. Данные операции позволяют пользователям выполнять запросы, агрегирующие нескольких наборов столбцов в одном запросе. Данные операции поддерживают только группировку по именам столбцов. Группировка с использованием других выражений не поддерживается.

Сложные операции группировки зачастую эквивалентны применению операции UNION ALL к нескольким запросам с группировкой GROUP BY, как показано в следующих примерах. Эквивалентность не гарантирована, когда источник данных агрегаций не является детерминированным.

GROUPING SETS

Ключевое GROUPING SETS позволяют указать несколько списков столбцов для группировки. Если столбец не является частью конкретного списка группируемых столбцов, ему будет присвоено значение NULL.

Рассмотрим следующую таблицу:

SELECT * FROM shipping;

origin_state | origin_zip | destination_state | destination_zip | package_weight
--------------+------------+-------------------+-----------------+----------------
California   |      94131 | New Jersey        |            8648 |             13
California   |      94131 | New Jersey        |            8540 |             42
New Jersey   |       7081 | Connecticut       |            6708 |            225
California   |      90210 | Connecticut       |            6927 |           1337
California   |      94131 | Colorado          |           80302 |              5
New York     |      10002 | New Jersey        |            8540 |              3
(6 rows)

Пример использования GROUPING SETS с тремя списками столбцов:

SELECT origin_state, origin_zip, destination_state, sum(package_weight)
FROM shipping
GROUP BY GROUPING SETS (
    (origin_state),
    (origin_state, origin_zip),
    (destination_state));

origin_state | origin_zip | destination_state | _col0
--------------+------------+-------------------+-------
New Jersey   | NULL       | NULL              |   225
California   | NULL       | NULL              |  1397
New York     | NULL       | NULL              |     3
California   |      90210 | NULL              |  1337
California   |      94131 | NULL              |    60
New Jersey   |       7081 | NULL              |   225
New York     |      10002 | NULL              |     3
NULL         | NULL       | Colorado          |     5
NULL         | NULL       | New Jersey        |    58
NULL         | NULL       | Connecticut       |  1562
(10 rows)

Предыдущий запрос эквивалентен следующему запросу с UNION ALL:

SELECT origin_state, NULL, NULL, sum(package_weight)
FROM shipping GROUP BY origin_state

UNION ALL

SELECT origin_state, origin_zip, NULL, sum(package_weight)
FROM shipping GROUP BY origin_state, origin_zip

UNION ALL

SELECT NULL, NULL, destination_state, sum(package_weight)
FROM shipping GROUP BY destination_state;

Запрос с GROUPING SETS, CUBE или ROLLUP получает данные из базового источника только один раз, в то время как запрос с UNION ALL получает данные три раза. Поэтому запросы с UNION ALL могут давать противоречивые результаты, если источник данных не является детерминированным.

CUBE

Генерирует все возможные GROUPING SETS для заданного набора столбцов.

Например, запрос:

SELECT origin_state, destination_state, sum(package_weight)
FROM shipping
GROUP BY CUBE (origin_state, destination_state);

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

SELECT origin_state, destination_state, sum(package_weight)
FROM shipping
GROUP BY GROUPING SETS (
    (origin_state, destination_state),
    (origin_state),
    (destination_state),
    ()
);

origin_state | destination_state | _col0
--------------+-------------------+-------
California   | New Jersey        |    55
California   | Colorado          |     5
New York     | New Jersey        |     3
New Jersey   | Connecticut       |   225
California   | Connecticut       |  1337
California   | NULL              |  1397
New York     | NULL              |     3
New Jersey   | NULL              |   225
NULL         | New Jersey        |    58
NULL         | Connecticut       |  1562
NULL         | Colorado          |     5
NULL         | NULL              |  1625
(12 rows)

ROLLUP

Генерирует GROUPING SET с заданным набором столбцов, а также промежуточным наборами столбцов вплоть до пустого набора, последовательно убирая по одному столбцу справа.

Например, запрос:

SELECT origin_state, origin_zip, sum(package_weight)
FROM shipping
GROUP BY ROLLUP (origin_state, origin_zip);

origin_state | origin_zip | _col2
--------------+------------+-------
California   |      94131 |    60
California   |      90210 |  1337
New Jersey   |       7081 |   225
New York     |      10002 |     3
California   | NULL       |  1397
New York     | NULL       |     3
New Jersey   | NULL       |   225
NULL         | NULL       |  1625
(8 rows)

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

SELECT origin_state, origin_zip, sum(package_weight)
FROM shipping
GROUP BY GROUPING SETS ((origin_state, origin_zip), (origin_state), ());

Комбинирование сложных операций группировки

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

SELECT origin_state, destination_state, origin_zip, sum(package_weight)
FROM shipping
GROUP BY
    GROUPING SETS ((origin_state, destination_state)),
    ROLLUP (origin_zip);

Который можно переписать как:

SELECT origin_state, destination_state, origin_zip, sum(package_weight)
FROM shipping
GROUP BY
    GROUPING SETS ((origin_state, destination_state)),
    GROUPING SETS ((origin_zip), ());

Логически эквивалентен:

SELECT origin_state, destination_state, origin_zip, sum(package_weight)
FROM shipping
GROUP BY GROUPING SETS (
    (origin_state, destination_state, origin_zip),
    (origin_state, destination_state)
);

origin_state | destination_state | origin_zip | _col3
--------------+-------------------+------------+-------
New York     | New Jersey        |      10002 |     3
California   | New Jersey        |      94131 |    55
New Jersey   | Connecticut       |       7081 |   225
California   | Connecticut       |      90210 |  1337
California   | Colorado          |      94131 |     5
New York     | New Jersey        | NULL       |     3
New Jersey   | Connecticut       | NULL       |   225
California   | Colorado          | NULL       |     5
California   | Connecticut       | NULL       |  1337
California   | New Jersey        | NULL       |    55
(10 rows)

Ключевые слова ALL и DISTINCT определяют, будут ли повторяющиеся наборы группировки создавать отдельные строки. Это особенно полезно, когда в одном запросе объединяются несколько сложных наборов группировок. Например, запрос:

SELECT origin_state, destination_state, origin_zip, sum(package_weight)
FROM shipping
GROUP BY ALL
    CUBE (origin_state, destination_state),
    ROLLUP (origin_state, origin_zip);

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

SELECT origin_state, destination_state, origin_zip, sum(package_weight)
FROM shipping
GROUP BY GROUPING SETS (
    (origin_state, destination_state, origin_zip),
    (origin_state, origin_zip),
    (origin_state, destination_state, origin_zip),
    (origin_state, origin_zip),
    (origin_state, destination_state),
    (origin_state),
    (origin_state, destination_state),
    (origin_state),
    (origin_state, destination_state),
    (origin_state),
    (destination_state),
    ()
);

Однако, если запрос использует ключевое слово DISTINCT для GROUP BY:

SELECT origin_state, destination_state, origin_zip, sum(package_weight)
FROM shipping
GROUP BY DISTINCT
    CUBE (origin_state, destination_state),
    ROLLUP (origin_state, origin_zip);

То будут возвращены только уникальные наборы группировок:

SELECT origin_state, destination_state, origin_zip, sum(package_weight)
FROM shipping
GROUP BY GROUPING SETS (
    (origin_state, destination_state, origin_zip),
    (origin_state, origin_zip),
    (origin_state, destination_state),
    (origin_state),
    (destination_state),
    ()
);

По умолчанию CedrusData использует семантику ключевого слова ALL.

GROUPING

grouping(col1, ..., colN) -> bigint

Операция группировки возвращает набор битов, преобразованный в десятичный вид, указывающий, какие столбцы присутствуют в группировке. Он должен использоваться в сочетании с GROUPING SETS, ROLLUP, CUBE или GROUP BY, и его аргументы должны точно соответствовать столбцам, указанным в соответствующих GROUPING SETS , ROLLUP, CUBE или GROUP BY.

Чтобы вычислить результирующий набор битов для конкретной строки, биты назначаются столбцам аргументов, при этом крайний правый столбец является младшим значащим битом. Для данной группы бит устанавливается в 0, если соответствующий столбец включен в группу, и в 1 в противном случае. Например, запрос:

SELECT origin_state, origin_zip, destination_state, sum(package_weight),
       grouping(origin_state, origin_zip, destination_state)
FROM shipping
GROUP BY GROUPING SETS (
    (origin_state),
    (origin_state, origin_zip),
    (destination_state)
);

origin_state | origin_zip | destination_state | _col3 | _col4
--------------+------------+-------------------+-------+-------
California   | NULL       | NULL              |  1397 |     3
New Jersey   | NULL       | NULL              |   225 |     3
New York     | NULL       | NULL              |     3 |     3
California   |      94131 | NULL              |    60 |     1
New Jersey   |       7081 | NULL              |   225 |     1
California   |      90210 | NULL              |  1337 |     1
New York     |      10002 | NULL              |     3 |     1
NULL         | NULL       | New Jersey        |    58 |     6
NULL         | NULL       | Connecticut       |  1562 |     6
NULL         | NULL       | Colorado          |     5 |     6
(10 rows)

Первая группа в приведенном результате включает только столбец origin_state и исключает столбцы origin_zip и destination_state. Набор битов, созданный для этой группы, равен 011, где старший бит представляет origin_state.

Выражение HAVING

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

SELECT count(*), mktsegment, nationkey,
       CAST(sum(acctbal) AS bigint) AS totalbal
FROM customer
GROUP BY mktsegment, nationkey
HAVING sum(acctbal) > 5700000
ORDER BY totalbal DESC;

_col0 | mktsegment | nationkey | totalbal
-------+------------+-----------+----------
 1272 | AUTOMOBILE |        19 |  5856939
 1253 | FURNITURE  |        14 |  5794887
 1248 | FURNITURE  |         9 |  5784628
 1243 | FURNITURE  |        12 |  5757371
 1231 | HOUSEHOLD  |         3 |  5753216
 1251 | MACHINERY  |         2 |  5719140
 1247 | FURNITURE  |         8 |  5701952
(7 rows)

Выражение WINDOW

Задает спецификацию оконной функции. На спецификацию оконной функции можно ссылаться в выражениях SELECT и ORDER BY. Например:

SELECT orderkey, clerk, totalprice,
      rank() OVER w AS rnk
FROM orders
WINDOW w AS (PARTITION BY clerk ORDER BY totalprice DESC)
ORDER BY count() OVER w, clerk, rnk

Запрос может содержать одну или несколько спецификаций в форме

window_name AS (window_specification)

Спецификация состоит из следующих компонентов:

• Имя спецификации оконной функции.

• Спецификация PARTITION BY, которая разделяет входные строки на партиции аналогично тому, как выражение GROUP BY разделяет строки на разные группы для агрегатных функций.

• Спецификация ORDER BY, определяющая порядок, в котором входные строки будут обработаны оконной функцией.

• Фрейм, определяющий строки, которые должны быть обработаны оконной функцией для заданной строки. Если фрейм не указан, по умолчанию используется фрейм RANGE UNBOUNDED PRECEDING (эквивалентен RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), который содержит все строки с начала партиции до текущей строки. При отсутствии ORDER BY все строки считаются эквивалентными, поэтому RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW эквивалентно BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Синтаксис фрейма поддерживает дополнительные паттерны для распознавания строк. Если паттерн указан, фрейм для конкретной строки состоит из строк, соответствующих паттерну, начиная с текущей строки. Если во фрейме указаны меры (measures) паттерна, они могут быть использованы аналогично оконным функциям. Дополнительные сведения см. в разделе Row pattern recognition in window structures.

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

SET-операции

CedrusData поддерживает операции UNION INTERSECT и EXCEPT, которые объединяют результаты двух запросов в один:

query UNION [ALL | DISTINCT] query

query INTERSECT [ALL | DISTINCT] query

query EXCEPT [ALL | DISTINCT] query

Ключевые слова ALL и DISTINCT определяют, какие строки будут включены в окончательный результирующий набор. Если указан аргумент ALL, в результат будут включены все строки. Если указан аргумент DISTINCT, в результат будут включены только уникальные строки. По умолчанию CedrusData использует семантику DISTINCT.

Обработка подзапросов происходит слева направо. Порядок обработки можно изменить с помощью скобок. INTERSECT имеет приоритет перед EXCEPT и UNION. Например, запрос A UNION B INTERSECT C EXCEPT D эквивалентен A UNION (B INTERSECT C) EXCEPT D.

Выражение UNION

Объединяет результаты двух запросов. Например:

SELECT 13
UNION
SELECT 42;

_col0
-------
   13
   42
(2 rows)

Следующий запрос демонстрирует разницу между UNION и UNION ALL. В случае UNION ALL строка со значением 13 будет возвращена дважды. В случае UNION (который эквивалентен UNION DISTINCT) строка со значением 13 будет возвращена только один раз:

SELECT 13
UNION
SELECT * FROM (VALUES 42, 13);

_col0
-------
   13
   42
(2 rows)

SELECT 13
UNION ALL
SELECT * FROM (VALUES 42, 13);

_col0
-------
   13
   42
   13
(2 rows)

Выражение INTERSECT

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

SELECT * FROM (VALUES 13, 42)
INTERSECT
SELECT 13;

_col0
-------
   13
(2 rows)

Выражение EXCEPT

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

SELECT * FROM (VALUES 13, 42)
EXCEPT
SELECT 13;

_col0
-------
  42
(2 rows)

Выражение ORDER BY

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

ORDER BY expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...]

Каждый expression может быть выражением или порядковым номером столбца результата запроса, начиная с единицы. Обработка выражения ORDER BY происходит после обработки выражений GROUP BY и HAVING и перед обработкой выражений OFFSET, LIMIT и FETCH FIRST. Порядок отображения NULL значений по умолчанию — NULLS LAST.

В соответствии со спецификацией SQL выражение ORDER BY влияет только на порядок записей того запроса, которому оно принадлежит. По этой причине CedrusData может игнорировать ORDER BY выражение в случаях, когда его выполнение не влияет на результат.

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

INSERT INTO some_table
SELECT * FROM another_table
ORDER BY field;

По той же причине выражение ORDER BY является избыточным в подзапросах:

SELECT *
FROM some_table
    JOIN (SELECT * FROM another_table ORDER BY field) u
    ON some_table.key = u.key;

Дополнительную информацию об оптимизации выполнения ORDER BY можно найти в блоге Trino.

Выражение OFFSET

Удаляет заданное количество ведущих записей из результата запроса:

OFFSET count [ ROW | ROWS ]

Если в запросе присутствует выражение ORDER BY, то выражение OFFSET будет применено к отсортированному результату:

SELECT name FROM nation ORDER BY name OFFSET 22;

name
----------------
UNITED KINGDOM
UNITED STATES
VIETNAM
(3 rows)

В отсутствии выражения ORDER BY строки будут отброшены произвольным образом. Если количество отбрасываемых строк в выражении OFFSET равно или превышает количество строк в результате запроса, окончательный результат будет пустым.

Выражения LIMIT или FETCH FIRST

Ограничивают количество строк в результирующем наборе.

LIMIT { count | ALL }

FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES }

Следующий запрос получает данные из большой таблицы, и оставляет только пять записей с помощью выражения LIMIT:

SELECT orderdate FROM orders LIMIT 5;

orderdate
------------
1994-07-25
1993-11-12
1992-10-06
1994-01-04
1997-12-28
(5 rows)

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

LIMIT ALL эквивалентно отсутствию выражения LIMIT.

Выражения FIRST и NEXT эквивалентны. Ключевые слова ROW и ROWS эквивалентны.

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

SELECT orderdate FROM orders FETCH FIRST ROW ONLY;

orderdate
------------
1994-02-12
(1 row)

Если присутствует выражение OFFSET, то обработка LIMIT и FETCH FIRST будет осуществлена после обработки OFFSET:

SELECT * FROM (VALUES 5, 2, 4, 1, 3) t(x) ORDER BY x OFFSET 2 LIMIT 2;

x
---
3
4
(2 rows)

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

SELECT name, regionkey
FROM nation
ORDER BY regionkey FETCH FIRST ROW WITH TIES;

name    | regionkey
------------+-----------
ETHIOPIA   |         0
MOROCCO    |         0
KENYA      |         0
ALGERIA    |         0
MOZAMBIQUE |         0
(5 rows)

Если ключевые слова ONLY и WITH TIES не указаны, будет использован режим ONLY.

TABLESAMPLE

Возвращает некоторое количество записей таблицы. Существует несколько методов выбора строк:

BERNOULLI

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

SYSTEM

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

Примечание

Ни один из двух методов не обеспечивает детерминированный результат.

Примеры:

SELECT *
FROM users TABLESAMPLE BERNOULLI (50);

SELECT *
FROM users TABLESAMPLE SYSTEM (75);

Использование TABLESAMPLE совместно с JOIN:

SELECT o.*, i.*
FROM orders o TABLESAMPLE SYSTEM (10)
JOIN lineitem i TABLESAMPLE BERNOULLI (40)
  ON o.orderkey = i.orderkey;

UNNEST

Преобразует значения типов ARRAY или MAP в табличную форму.

Пример использования UNNEST с ARRAY типом, состоящим из одной колонки:

SELECT * FROM UNNEST(ARRAY[1,2]) AS t(number);

number
--------
     1
     2
(2 rows)

Пример использования UNNEST с ARRAY типом, состоящим из нескольких колонок:

SELECT * FROM UNNEST(
        map_from_entries(
            ARRAY[
                ('SQL' , 1974),
                ('Java', 1995)
            ]
        )
) AS t(language, first_appeared_year);

language | first_appeared_year
----------+---------------------
SQL      |                1974
Java     |                1995
(2 rows)

UNNEST можно использовать в сочетании с колонками типа ROW:

SELECT *
FROM UNNEST(
        ARRAY[
            ROW('Java', 1995),
            ROW('SQL' , 1974)],
        ARRAY[
            ROW(false),
            ROW(true)]
) as t(language,first_appeared_year,declarative);

language | first_appeared_year | declarative
----------+---------------------+-------------
Java     |                1995 | false
SQL      |                1974 | true
(2 rows)

Ключевое слово WITH ORDINALITY добавляет порядковый номер записи в качестве дополнительной колонки:

SELECT a, b, rownumber
FROM UNNEST (
    ARRAY[2, 5],
    ARRAY[7, 8, 9]
     ) WITH ORDINALITY AS t(a, b, rownumber);

a   | b | rownumber
------+---+-----------
  2 | 7 |         1
  5 | 8 |         2
NULL | 9 |         3
(3 rows)

Если значение типа ARRAY или MAP является пустым, UNNEST вернет пустой результат:

SELECT * FROM UNNEST (ARRAY[]) AS t(value);

value
-------
(0 rows)

Если значение типа ARRAY или MAP равно NULL, оно не будет включено в результат:

SELECT * FROM UNNEST (CAST(null AS ARRAY(integer))) AS t(number);

number
--------
(0 rows)

UNNEST обычно используется совместно с JOIN и может ссылаться на столбцы с левой стороны JOIN:

SELECT student, score
FROM (
   VALUES
      ('John', ARRAY[7, 10, 9]),
      ('Mary', ARRAY[4, 8, 9])
) AS tests (student, scores)
CROSS JOIN UNNEST(scores) AS t(score);

student | score
---------+-------
John    |     7
John    |    10
John    |     9
Mary    |     4
Mary    |     8
Mary    |     9
(6 rows)

UNNEST может ссылаться на несколько столбцов с левой стороны JOIN:

SELECT numbers, animals, n, a
FROM (
  VALUES
    (ARRAY[2, 5], ARRAY['dog', 'cat', 'bird']),
    (ARRAY[7, 8, 9], ARRAY['cow', 'pig'])
) AS x (numbers, animals)
CROSS JOIN UNNEST(numbers, animals) AS t (n, a);

numbers  |     animals      |  n   |  a
-----------+------------------+------+------
[2, 5]    | [dog, cat, bird] |    2 | dog
[2, 5]    | [dog, cat, bird] |    5 | cat
[2, 5]    | [dog, cat, bird] | NULL | bird
[7, 8, 9] | [cow, pig]       |    7 | cow
[7, 8, 9] | [cow, pig]       |    8 | pig
[7, 8, 9] | [cow, pig]       |    9 | NULL
(6 rows)

Используйте LEFT JOIN, если вы хотите вернуть все записи с левой стороны JOIN, даже если некоторые них содержат пустые ARRAY/MAP или NULL:

SELECT runner, checkpoint
FROM (
   VALUES
      ('Joe', ARRAY[10, 20, 30, 42]),
      ('Roger', ARRAY[10]),
      ('Dave', ARRAY[]),
      ('Levi', NULL)
) AS marathon (runner, checkpoints)
LEFT JOIN UNNEST(checkpoints) AS t(checkpoint) ON TRUE;

runner | checkpoint
--------+------------
Joe    |         10
Joe    |         20
Joe    |         30
Joe    |         42
Roger  |         10
Dave   |       NULL
Levi   |       NULL
(7 rows)

В случае использования LEFT JOIN единственным условием JOIN, поддерживаемым текущей реализацией, является ON TRUE.

Выражение JOIN

Объединяет данные из нескольких отношений.

CROSS JOIN

Возвращает декартово произведение двух отношений. CROSS JOIN может быть записан либо явно, либо путем указания нескольких отношений в выражении FROM. Следующие запросы эквивалентны:

SELECT *
FROM nation
CROSS JOIN region;

SELECT *
FROM nation, region;

Таблица nation содержит 25 строк, а таблица region содержит 5 строк, поэтому CROSS JOIN между двумя таблицами возвращает 125 строк:

SELECT n.name AS nation, r.name AS region
FROM nation AS n
CROSS JOIN region AS r
ORDER BY 1, 2;

nation     |   region
----------------+-------------
ALGERIA        | AFRICA
ALGERIA        | AMERICA
ALGERIA        | ASIA
ALGERIA        | EUROPE
ALGERIA        | MIDDLE EAST
ARGENTINA      | AFRICA
ARGENTINA      | AMERICA
...
(125 rows)

LATERAL

Позволяет ссылаться на столбцы, предоставленные предыдущими элементами FROM:

SELECT name, x, y
FROM nation
CROSS JOIN LATERAL (SELECT name || ' :-' AS x)
CROSS JOIN LATERAL (SELECT x || ')' AS y);

Разрешение имен столбцов

Если два отношения возвращают столбцы с одинаковыми именами, ссылки на такие столбцы должны содержать имя или псевдоним отношения:

SELECT nation.name, region.name
FROM nation
CROSS JOIN region;

SELECT n.name, r.name
FROM nation AS n
CROSS JOIN region AS r;

SELECT n.name, r.name
FROM nation n
CROSS JOIN region r;

Следующий запрос будет завершен с ошибкой Column 'name' is ambiguous:

SELECT name
FROM nation
CROSS JOIN region;

Подзапросы

Подзапрос — это выражение, которое возвращает отношение.

Подзапрос является коррелированным, когда он ссылается на столбцы вне подзапроса. Логически коррелированный подзапрос будет выполнен для каждой строки во внешнем запросе. Таким образом, значения столбцов строки внешнего запроса остаются неизменными на время выполнения коррелированного подзапроса для этой строки.

Примечание

CedrusData в настоящий момент поддерживает не все виды коррелированных подзапросов.

EXISTS

Возвращает TRUE, если подзапрос содержит хотя бы одну строку строки:

SELECT name
FROM nation
WHERE EXISTS (
     SELECT *
     FROM region
     WHERE region.regionkey = nation.regionkey
);

IN

Возвращает TRUE, если выражение слева входит в результат подзапроса справа. Подзапрос должен возвращать ровно один столбец:

SELECT name
FROM nation
WHERE regionkey IN (
     SELECT regionkey
     FROM region
     WHERE name = 'AMERICA' OR name = 'AFRICA'
);

Скалярные подзапросы

Скалярный подзапрос — это некоррелированный подзапрос, который возвращает не более одной строки. CedrusData вернет ошибку, если подзапрос возвращает более одной строки. Если подзапрос не возвращает ни одной строки, будет возвращено значение NULL:

SELECT name
FROM nation
WHERE regionkey = (SELECT max(regionkey) FROM region);

Примечание

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