SELECT#

Синтаксис#

[ WITH SESSION [ name = expression [, ...] ]
[ WITH [ FUNCTION udf ] [, ...] ]
[ 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 см. в разделе pattern recognition в предложении FROM.

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

Описание использования table functions см. в разделе table functions.

а join_type — один из следующих вариантов:

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

а grouping_element — один из следующих вариантов:

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

Описание#

Извлекает строки из нуля или более таблиц.

Предложение WITH SESSION#

Предложение WITH SESSION позволяет задавать значения session properties и catalog session properties, применимые только к обработке текущего statement SELECT. Заданные значения переопределяют любую другую конфигурацию и настройки session properties. Несколько свойств разделяются запятыми.

Следующий пример переопределяет глобальное configuration property query.max-execution-time с помощью session property query_max_execution_time, чтобы уменьшить время до 2h. Он также переопределяет catalog property iceberg.query-partition-filter-required из каталога example, который использует Коннектор Iceberg, устанавливая catalog session property query_partition_filter_required в true:

WITH
  SESSION
    query_max_execution_time='2h',
    example.query_partition_filter_required=true
SELECT *
FROM example.default.thetable
LIMIT 100;

Предложение WITH FUNCTION#

Предложение WITH FUNCTION позволяет определить список Inline пользовательские функции, доступных для использования в оставшейся части запроса.

Следующий пример объявляет и использует две inline UDFs:

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!

Дополнительные сведения об UDFs в целом, inline UDFs, всех поддерживаемых statements и примерах см. в UDF - Пользовательские функции.

Предложение WITH#

Предложение WITH определяет именованные relations для использования внутри запроса. Оно позволяет выравнивать вложенные запросы или упрощать подзапросы. Например, следующие запросы эквивалентны:

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;

Это также работает с несколькими подзапросами:

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;

Кроме того, relations внутри предложения WITH могут образовывать цепочки:

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;

Warning

В настоящее время SQL для предложения WITH будет inline-подставлен в каждом месте использования именованной relation. Это означает, что если relation используется более одного раза и запрос недетерминированный, результаты могут каждый раз отличаться.

Предложение WITH RECURSIVE#

Предложение WITH RECURSIVE — вариант предложения WITH. Оно определяет список запросов для обработки, включая рекурсивную обработку подходящих запросов.

Warning

Эта возможность является экспериментальной. Используйте ее только если понимаете возможные сбои запросов и влияние рекурсивной обработки на вашу нагрузку.

Рекурсивный WITH-query должен иметь форму UNION из двух relations. Первая relation называется recursion base, а вторая — recursion step. Trino поддерживает рекурсивные WITH-queries с одной рекурсивной ссылкой на WITH-query изнутри запроса. Имя T запроса T может один раз упоминаться в предложении FROM relation recursion step.

Следующий листинг показывает простой пример, демонстрирующий часто используемую форму одного запроса в списке:

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

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

  • recursive base дает 1

  • первая рекурсия дает 1 + 1 = 2

  • вторая рекурсия использует результат первой и добавляет единицу: 2 + 1 = 3

  • третья рекурсия использует результат второй и снова добавляет единицу: 3 + 1 = 4

  • четвертая рекурсия прекращается, поскольку n = 4

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

  • финальный statement выполняет операцию суммирования этих элементов с итоговым результатом 10

Типы возвращаемых столбцов берутся из base relation. Поэтому требуется, чтобы типы в step relation могли быть приведены к типам base relation.

Предложение RECURSIVE применяется ко всем запросам в списке WITH, но не все они обязаны быть рекурсивными. Если WITH-query не соответствует описанным выше правилам или не содержит рекурсивной ссылки, он обрабатывается как обычный WITH-query. Column aliases обязательны для всех запросов в рекурсивном списке WITH.

В дополнение к ограничениям предложения WITH, из-за следования стандарту SQL и особенностей реализации применяются следующие ограничения:

  • поддерживаются только одноэлементные рекурсивные циклы. Как и в обычных WITH-queries, разрешены ссылки на предыдущие запросы в списке WITH. Ссылки на последующие запросы запрещены.

  • использование outer joins, set operations, предложения limit и других конструкций не всегда допускается в step relation

  • глубина рекурсии фиксирована, по умолчанию равна 10 и не зависит от фактических результатов запроса

Глубину рекурсии можно настроить с помощью session property max_recursion_depth. При изменении значения учитывайте, что размер query plan растет квадратично относительно глубины рекурсии.

Предложение SELECT#

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

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

Квантификаторы ALL и DISTINCT определяют, включаются ли дублирующиеся строки в result set. Если указан аргумент ALL, включаются все строки. Если указан аргумент DISTINCT, в result set включаются только уникальные строки. В этом случае каждый выходной столбец должен иметь тип, допускающий сравнение. Если ни один аргумент не указан, по умолчанию используется ALL.

Select expressions#

Каждое select_expression должно иметь одну из следующих форм:

expression [ [ AS ] column_alias ]

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

relation.*

*

В случае expression [ [ AS ] column_alias ] определяется один выходной столбец.

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

В случае relation.* в result set включаются все столбцы relation. В этом случае column aliases не допускаются.

В случае * в result set включаются все столбцы relation, определенной запросом.

В result set порядок столбцов совпадает с порядком их указания в select expressions. Если select expression возвращает несколько столбцов, они упорядочены так же, как в исходной relation или выражении row type.

Если указаны column aliases, они переопределяют любые существующие имена столбцов или полей row:

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#

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

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

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

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

Предложения GROUP BY могут группировать вывод по именам входных столбцов, которые не присутствуют в выводе statement select. Например, следующий запрос создает counts строк для таблицы customer, используя входной столбец mktsegment:

SELECT count(*) FROM customer GROUP BY mktsegment;

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

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

Сложные операции grouping#

Trino также поддерживает сложные aggregations с помощью синтаксиса GROUPING SETS, CUBE и ROLLUP. Этот синтаксис позволяет выполнять анализ, который требует aggregation по нескольким наборам столбцов в одном запросе. Сложные операции grouping не поддерживают grouping по выражениям, составленным из входных столбцов. Допускаются только имена столбцов.

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

AUTO#

Когда указан AUTO, engine Trino автоматически определяет grouping columns, вместо того чтобы требовать их явного перечисления. В этом режиме любой столбец в списке SELECT, который не является частью aggregate function, неявно считается grouping column.

Этот пример запроса вычисляет общий account balance по market segment. Предложение AUTO выводит mktsegment как grouping key, поскольку он не используется ни в одной aggregate function, например sum.

SELECT mktsegment, sum(acctbal) FROM shipping GROUP BY AUTO;

 mktsegment |       _col1
------------+--------------------
 BUILDING   |          1444587.8
 MACHINERY  |         1296958.61
 HOUSEHOLD  |         1279340.66
 FURNITURE  |          1265282.8
 AUTOMOBILE | 1395695.7200000004
(5 rows)

GROUPING SETS#

Grouping sets позволяют указать несколько списков столбцов для grouping. Столбцы, не входящие в данный подсписок grouping columns, устанавливаются в 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 из нескольких запросов GROUP BY:

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 (GROUPING SETS, CUBE или ROLLUP) читает из базового источника данных только один раз, тогда как запрос с UNION ALL читает базовые данные три раза. Поэтому запросы с UNION ALL могут создавать несогласованные результаты, когда источник данных недетерминированный.

CUBE#

Оператор CUBE создает все возможные grouping sets, то есть power set, для заданного набора столбцов. Например, запрос:

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#

Оператор ROLLUP создает все возможные промежуточные итоги для заданного набора столбцов. Например, запрос:

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), ());

Комбинирование нескольких grouping expressions#

Несколько grouping expressions в одном запросе интерпретируются с семантикой cross-product. Например, следующий запрос:

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 определяют, создают ли дублирующиеся grouping sets отдельные выходные строки. Это особенно полезно, когда в одном запросе комбинируется несколько сложных grouping sets. Например, следующий запрос:

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

создаются только уникальные grouping sets:

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),
    ()
);

Квантификатор set по умолчанию — ALL.

Операция GROUPING#

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

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

Чтобы вычислить результирующий bit set для конкретной строки, bits назначаются столбцам-аргументам, причем самый правый столбец является младшим битом. Для заданного grouping bit устанавливается в 0, если соответствующий столбец включен в grouping, и в 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)

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

Предложение HAVING#

Предложение HAVING используется совместно с aggregate functions и предложением GROUP BY, чтобы управлять выбором групп. Предложение HAVING исключает группы, не удовлетворяющие заданным условиям. HAVING фильтрует группы после вычисления групп и aggregates.

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

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#

Предложение WINDOW используется для определения именованных window specifications. На определенные именованные window specifications можно ссылаться в предложениях 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 definitions предложения WINDOW может содержать одну или несколько именованных window specifications следующей формы:

window_name AS (window_specification)

Window specification состоит из следующих компонентов:

  • Существующее имя window, которое ссылается на именованную window specification в предложении WINDOW. Window specification, связанная с указанным именем, является основой текущей specification.

  • Partition specification, которая разделяет входные строки на разные partitions. Это аналогично тому, как предложение GROUP BY разделяет строки на разные группы для aggregate functions.

  • Ordering specification, которая определяет порядок обработки входных строк window function.

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

Каждый компонент window необязателен. Если window specification не задает window partitioning, ordering или frame, эти компоненты берутся из window specification, на которую ссылается existing window name, или из другой window specification в цепочке ссылок. Если existing window name не указано или ни одна из referenced window specifications не содержит компонент, используется значение по умолчанию.

Set operations#

UNION, INTERSECT и EXCEPT являются set operations. Эти предложения используются, чтобы объединять результаты более чем одного statement select в один result set:

query UNION [ALL | DISTINCT] [CORRESPONDING] query

query INTERSECT [ALL | DISTINCT] [CORRESPONDING] query

query EXCEPT [ALL | DISTINCT] [CORRESPONDING] query

Аргумент ALL или DISTINCT управляет тем, какие строки включаются в итоговый result set. Если указан аргумент ALL, включаются все строки, даже если они идентичны. Если указан аргумент DISTINCT, в объединенный result set включаются только уникальные строки. Если ни один аргумент не указан, по умолчанию используется DISTINCT.

Несколько set operations обрабатываются слева направо, если порядок явно не задан скобками. Кроме того, INTERSECT имеет более высокий приоритет, чем EXCEPT и UNION. Это означает, что A UNION B INTERSECT C EXCEPT D эквивалентно A UNION (B INTERSECT C) EXCEPT D.

Предложение UNION#

UNION объединяет все строки из result set первого запроса со строками из result set второго запроса. Ниже приведен пример одного из самых простых предложений UNION. Он выбирает значение 13 и объединяет этот result set со вторым запросом, выбирающим значение 42:

SELECT 13
UNION
SELECT 42;

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

Следующий запрос демонстрирует разницу между UNION и UNION ALL. Он выбирает значение 13 и объединяет этот result set со вторым запросом, который выбирает значения 42 и 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)

CORRESPONDING сопоставляет столбцы по имени, а не по позиции:

SELECT * FROM (VALUES (1, 'alice')) AS t(id, name)
UNION ALL CORRESPONDING
SELECT * FROM (VALUES ('bob', 2)) AS t(name, id);

 id | name
----+-------
  1 | alice
  2 | bob
(2 rows)

SELECT * FROM (VALUES (DATE '2025-04-23', 'alice')) AS t(order_date, name)
UNION ALL CORRESPONDING
SELECT * FROM (VALUES ('bob', 123.45)) AS t(name, price);

 name
-------
 alice
 bob
(2 rows)

Предложение INTERSECT#

INTERSECT возвращает только строки, которые находятся в result sets и первого, и второго запросов. Ниже приведен пример одного из самых простых предложений INTERSECT. Он выбирает значения 13 и 42 и объединяет этот result set со вторым запросом, выбирающим значение 13. Поскольку 42 присутствует только в result set первого запроса, оно не включается в итоговые результаты:

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

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

CORRESPONDING сопоставляет столбцы по имени, а не по позиции:

SELECT * FROM (VALUES (1, 'alice')) AS t(id, name)
INTERSECT CORRESPONDING
SELECT * FROM (VALUES ('alice', 1)) AS t(name, id);

 id | name
----+-------
  1 | alice
(1 row)

Предложение EXCEPT#

EXCEPT возвращает строки, которые находятся в result set первого запроса, но отсутствуют во втором. Ниже приведен пример одного из самых простых предложений EXCEPT. Он выбирает значения 13 и 42 и объединяет этот result set со вторым запросом, выбирающим значение 13. Поскольку 13 также присутствует в result set второго запроса, оно не включается в итоговый результат:

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

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

CORRESPONDING сопоставляет столбцы по имени, а не по позиции:

SELECT * FROM (VALUES (1, 'alice'), (2, 'bob')) AS t(id, name)
EXCEPT CORRESPONDING
SELECT * FROM (VALUES ('alice', 1)) AS t(name, id);

 id | name
----+------
  2 | bob
(1 row)

Предложение ORDER BY#

Предложение ORDER BY используется для сортировки result set по одному или нескольким выходным выражениям:

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

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

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

В следующем примере предложение применяется только к statement select.

INSERT INTO some_table
SELECT * FROM another_table
ORDER BY field;

Поскольку таблицы в SQL по своей природе неупорядочены, предложение ORDER BY в этом случае не дает различий, но негативно влияет на производительность выполнения всего statement insert, поэтому Trino пропускает операцию сортировки.

Другой пример, где предложение ORDER BY избыточно и не влияет на результат всего statement, — вложенный запрос:

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

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

Предложение OFFSET#

Предложение OFFSET используется для отбрасывания некоторого числа начальных строк из result set:

OFFSET count [ ROW | ROWS ]

Если присутствует предложение ORDER BY, предложение OFFSET вычисляется поверх отсортированного result set, и набор остается отсортированным после отбрасывания начальных строк:

SELECT name FROM nation ORDER BY name OFFSET 22;

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

В противном случае отбрасываемые строки произвольны. Если count, указанный в предложении OFFSET, равен размеру result set или превышает его, итоговый результат пуст.

Предложение LIMIT или FETCH FIRST#

Предложение LIMIT или FETCH FIRST ограничивает число строк в result set.

LIMIT { count | ALL }

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

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

SELECT orderdate FROM orders LIMIT 5;

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

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

Предложение FETCH FIRST поддерживает ключевые слова FIRST или NEXT, а также ключевые слова ROW или ROWS. Эти ключевые слова эквивалентны, и их выбор не влияет на выполнение запроса.

Если count не указан в предложении FETCH FIRST, по умолчанию используется 1:

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)

Для предложения FETCH FIRST аргумент ONLY или WITH TIES управляет тем, какие строки включаются в result set.

Если указан аргумент ONLY, result set ограничивается точным числом начальных строк, определенным count.

Если указан аргумент WITH TIES, требуется наличие предложения ORDER BY. Result set состоит из того же набора начальных строк и всех строк из той же peer group, что и последняя из них (“ties”), определенной порядком в предложении ORDER BY. Result set отсортирован:

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)

TABLESAMPLE#

Доступно несколько методов sample:

BERNOULLI

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

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

SYSTEM

Этот sampling method разделяет таблицу на логические сегменты данных и выполняет sample таблицы на этом уровне детализации. Этот sampling method либо выбирает все строки из конкретного сегмента данных, либо пропускает его на основе сравнения sample percentage и случайного значения, вычисленного во время выполнения.

Строки, выбранные при system sampling, зависят от используемого connector. Например, при использовании с Hive это зависит от того, как данные расположены в HDFS. Этот метод не гарантирует независимые sampling probabilities.

Note

Ни один из двух методов не позволяет задать детерминированные границы числа возвращаемых строк.

Примеры:

SELECT *
FROM users TABLESAMPLE BERNOULLI (50);

SELECT *
FROM users TABLESAMPLE SYSTEM (75);

Использование sampling с joins:

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

UNNEST#

UNNEST можно использовать, чтобы развернуть ARRAY или MAP в relation. Arrays разворачиваются в один столбец:

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

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

Maps разворачиваются в два столбца: key и value:

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 можно использовать в сочетании с ARRAY структур ROW, чтобы развернуть каждое поле 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)

UNNEST может иметь необязательное предложение WITH ORDINALITY; в этом случае в конец добавляется дополнительный столбец 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)

UNNEST возвращает ноль записей, когда array или map пусты:

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

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

UNNEST возвращает ноль записей, когда array или map равны null:

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

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

Обычно UNNEST используется с JOIN и может ссылаться на столбцы из relations в левой части 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 также можно использовать с несколькими аргументами; в этом случае они разворачиваются в несколько столбцов с числом строк, равным cardinality самого длинного аргумента, а остальные столбцы дополняются null:

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 предпочтителен, чтобы не потерять строку, содержащую рассматриваемое поле array или map, когда referenced columns из relations в левой части join могут быть пустыми или иметь значения 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 единственное условие, поддерживаемое текущей реализацией, — ON TRUE.

JSON_TABLE#

JSON_TABLE преобразует JSON-данные в relational table format. Как и UNNEST и LATERAL, используйте JSON_TABLE в предложении FROM statement SELECT. Дополнительные сведения см. в JSON_TABLE.

Joins#

Joins позволяют объединять данные из нескольких relations.

CROSS JOIN#

Cross join возвращает декартово произведение, то есть все комбинации двух relations. Cross joins можно задавать явно с помощью синтаксиса CROSS JOIN или указанием нескольких relations в предложении 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, может предшествовать ключевое слово LATERAL. Это позволяет им ссылаться на столбцы, предоставленные предыдущими элементами FROM.

LATERAL join может находиться на верхнем уровне в списке FROM или в любом месте внутри заключенного в скобки дерева join. Во втором случае он также может ссылаться на любые элементы, находящиеся слева от JOIN, для которого он находится справа.

Когда элемент FROM содержит cross-references LATERAL, вычисление происходит следующим образом: для каждой строки элемента FROM, предоставляющего cross-referenced columns, элемент LATERAL вычисляется с использованием значений столбцов из row set этой строки. Полученные строки обычным образом соединяются со строками, из которых они были вычислены. Это повторяется для набора строк из таблиц-источников столбцов.

LATERAL прежде всего полезен, когда cross-referenced column необходим для вычисления строк, которые нужно присоединить:

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

Квалификация имен столбцов#

Когда две relations в join имеют столбцы с одинаковым именем, ссылки на столбцы должны быть квалифицированы с помощью alias relation, если у relation есть alias, или с помощью имени relation:

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;

Подзапросы#

Подзапрос — это выражение, состоящее из запроса. Подзапрос является correlated, когда он ссылается на столбцы вне подзапроса. Логически подзапрос вычисляется для каждой строки окружающего запроса. Поэтому referenced columns будут постоянными во время каждого отдельного вычисления подзапроса.

Note

Поддержка correlated subqueries ограничена. Поддерживаются не все стандартные формы.

EXISTS#

Предикат EXISTS определяет, возвращает ли подзапрос какие-либо строки:

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

IN#

Предикат IN определяет, равны ли какие-либо значения, созданные подзапросом, заданному выражению. Результат IN следует стандартным правилам для null. Подзапрос должен создавать ровно один столбец:

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

Scalar subquery#

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

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

Note

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