Синтаксис SELECT

SELECT

Возвращает результат вычисления выражений, указанных после SELECT.

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

Примеры:

SELECT "Hello, world!";

SELECT 2 + 2;

FROM

Источник данных для SELECT. В качестве аргумента может принимать имя таблицы, результат другого SELECT или именованное выражение. Между SELECT и FROM через запятую указываются имена столбцов из источника или * для выбора всех столбцов.

Примеры

SELECT key FROM my_table;

SELECT * FROM
  (SELECT value FROM my_table);

$table_name = "my_table";
SELECT * FROM $table_name;

VIEW (INDEX)

Чтобы сделать запрос SELECT по вторичному индексу, используйте конструкцию:

SELECT *
    FROM TableName VIEW IndexName
    WHERE …

Примеры

  • Выбрать все поля из таблицы series по индексу views_index с условием views >= someValue:

    SELECT series_id, title, info, release_date, views, uploaded_user_id
    FROM series VIEW views_index
    WHERE views >= someValue

  • Сделать JOIN таблиц series и users c заданным полем userName по индексам users_index и name_index соответственно:

    SELECT t1.series_id, t1.title
    FROM series VIEW users_index AS t1
    INNER JOIN users VIEW name_index AS t2
    ON t1.uploaded_user_id == t2.user_id
    WHERE t2.name == userName;

WITH

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

Поддерживаются следующие значения:

  • INFER_SCHEMA — задает флаг вывода схемы таблицы. Поведение аналогично заданию прагмы yt.InferSchema, только для конкретного источника данных. Можно задать число строк для выведения (число от 1 до 1000).
  • FORCE_INFER_SCHEMA — задает флаг вывода схемы таблицы. Поведение аналогично заданию прагмы yt.ForceInferSchema, только для конкретного источника данных. Можно задать число строк для выведения (число от 1 до 1000).
  • DIRECT_READ — подавляет работу некоторых оптимизаторов и заставляет использовать содержимое таблицы как есть. Поведение аналогично заданию отладочной прагмы DirectRead, только для конкретного источника данных.
  • INLINE — указание на то, что содержимое таблицы небольшое и нужно использовать его представление в памяти для обработки запроса. Реальный объем таблицы при этом не контролируется, и если он большой, то запрос может упасть по превышению памяти.
  • UNORDERED — подавляет использование исходной сортировки таблицы.
  • XLOCK — указание на то, что нужно брать эксклюзивный лок на таблицу. Полезен, когда чтение таблицы происходит на стадии обработки метапрограммы запроса, а затем ее содержимое обновляется в основном запросе. Позволяет избежать потери данных, если между исполнением фазы метапрограммы и основной частью запроса внешний процесс успел изменить таблицу.
  • SCHEMA type — указание на то, что следует использовать указанную схему таблицы целиком, игнорируя схему в метаданных.
  • COLUMNS type — указание на то, что следует использовать указанные типы для колонок, чьи имена совпадают с именами колонок таблицы в метаданных, а также какие колонки дополнительно присутствуют в таблице.
  • IGNORETYPEV3, IGNORE_TYPE_V3 — задает флаг игнорирования type_v3 типов в таблице. Поведение аналогично заданию прагмы yt.IgnoreTypeV3, только для конкретного источника данных.

При задании подсказок SCHEMA и COLUMNS в качестве значения типа type должен быть задан тип структуры.

Примеры:

SELECT key FROM my_table WITH INFER_SCHEMA;
SELECT key FROM my_table WITH FORCE_INFER_SCHEMA="42";

$s = (SELECT COUNT(*) FROM my_table WITH XLOCK);

INSERT INTO my_table WITH TRUNCATE
SELECT EvaluateExpr($s) AS a;

SELECT key, value FROM my_table WITH SCHEMA Struct<key:String, value:Int32>;

SELECT key, value FROM my_table WITH COLUMNS Struct<value:Int32?>;

SELECT key, value FROM EACH($my_tables) WITH SCHEMA Struct<key:String, value:List<Int32>>;

WHERE

Фильтрация строк в результате SELECT по условию.

Пример

SELECT key FROM my_table
WHERE value > 0;

ORDER BY

Сортировка результата SELECT по разделенному запятыми перечню критериев сортировки. В качестве критерия может выступать значение столбца, или выражение над столбцами. Не поддерживается указание порядкового номера колонки выборки (ORDER BY N, где N - номер).

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

  • ASC — по возрастанию. Применяется по умолчанию.
  • DESC — по убыванию.

Несколько критериев сортировки будут применены слева направо.

Пример

SELECT key, string_column
FROM my_table
ORDER BY key DESC, LENGTH(string_column) ASC;

Ключевое слово ORDER BY также может использоваться в механизме оконных функций.

LIMIT и OFFSET

LIMIT ограничивает вывод указанным количеством строк. Если значение лимита равно NULL, или LIMIT не указан, то вывод не ограничен.

OFFSET указывает отступ от начала (в строках). Если значение отступа равно NULL, или OFFSET не указан, то используется значение ноль.

Примеры

SELECT key FROM my_table
LIMIT 7;

SELECT key FROM my_table
LIMIT 7 OFFSET 3;

SELECT key FROM my_table
LIMIT 3, 7; -- эквивалентно предыдущему примеру

SELECT key FROM my_table
LIMIT NULL OFFSET NULL; -- эквивалентно SELECT key FROM my_table

ASSUME ORDER BY

Проверка сортированности результата SELECT по значению в указанном столбце или нескольких столбцах. Результат такого SELECT-а будет считаться сортированным, но без выполнения фактической сортировки. Проверка сортированности осуществляется на этапе исполнения запроса.

Как и для ORDER BY, поддерживается задание порядка сортировки с помощью ключевых слов ASC (по возрастанию) и DESC (по убыванию). Выражения в ASSUME ORDER BY не поддерживается.

Примеры:

SELECT key || "suffix" as key, -CAST(subkey as Int32) as subkey
FROM my_table
ASSUME ORDER BY key, subkey DESC;

TABLESAMPLE и SAMPLE

Построение случайной выборки из указанного во FROM источника данных.

TABLESAMPLE является частью SQL стандарта и работает следующим образом:

  • Указывается режим работы:
    • BERNOULLI означает «медленно, честно просмотрев все данные, но по-настоящему случайно»;
    • SYSTEM должен использовать знание о физическом хранении данных, чтобы избежать полного их чтения частично жертвуя случайностью выборки.
      Данные разбиваются на достаточно большие блоки, и происходит сэмплирование данных блоков целиком. Для прикладных расчётов на достаточно больших таблицах результат вполне может оказаться состоятельным.
  • Размер случайной выборки указывается в процентах следом за режимом в круглых скобках.
  • Управлять размером блоков для режима SYSTEM можно с помощью прагмы yt.SamplingIoBlockSize. Если при семплировании размер результата получается меньше одного блока, то выдается пустой результат.
  • Опционально далее указывается ключевое слово REPEATABLE и целое число в скобках, которое будет использовано как seed для генератора псевдослучайных чисел.

SAMPLE является более коротким алиасом, где нет сложных настроек, а размер выборки указывается в долях. На данный момент он соответствует режиму BERNOULLI.

Примечание

В режиме BERNOULLI при наличии ключевого слова REPEATABLE в seed подмешивается chunk id для каждого чанка таблицы. Поэтому выборка для разных таблиц с одинаковым содержимым может давать разные результаты.

Примеры:

SELECT *
FROM my_table
TABLESAMPLE BERNOULLI(1.0) REPEATABLE(123); -- один процент таблицы

SELECT *
FROM my_table
TABLESAMPLE SYSTEM(1.0); -- примерно один процент таблицы

SELECT *
FROM my_table
SAMPLE 1.0 / 3; -- треть таблицы

DISTINCT

Выбор уникальных строк.

Примечание

Применение DISTINCT к вычислимым значениям на данный момент не реализовано. С этой целью можно использовать подзапрос или выражение GROUP BY ... AS ....

Пример

SELECT DISTINCT value -- только уникальные значения из таблицы
FROM my_table;

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

Процедура выполнения SELECT

Результат запроса SELECT вычисляется следующим образом:

  • определяется набор входных таблиц – вычисляются выражения после FROM;
  • к входным таблицам применяется SAMPLE / TABLESAMPLE
  • выполняется FLATTEN COLUMNS или FLATTEN BY; алиасы, заданные во FLATTEN BY, становятся видны после этой точки;
  • выполняются все JOIN;
  • к полученным данным добавляются (или заменяются) колонки, заданные в GROUP BY ... AS ...;
  • выполняется WHERE — все данные не удовлетворяющие предикату отфильтровываются;
  • выполняется GROUP BY, вычисляются значения агрегатных функций;
  • выполняется фильтрация HAVING;
  • вычисляются значения оконных функций;
  • вычисляются выражения в SELECT;
  • выражениям в SELECT назначаются имена заданные алиасами;
  • к полученным таким образом колонкам применяется top-level DISTINCT;
  • таким же образом вычисляются все подзапросы в UNION ALL, выполняется их объединение (см. PRAGMA AnsiOrderByLimitInUnionAll);
  • выполняется сортировка согласно ORDER BY;
  • к полученному результату применяются OFFSET и LIMIT.

Порядок колонок в YQL

В стандартном SQL порядок колонок указанных в проекции (в SELECT) имеет значение. Помимо того, что порядок колонок должен сохраняться при отображении результатов запроса или при записи в новую таблицу, некоторые конструкции SQL этот порядок используют.
Это относится в том числе к UNION ALL и к позиционному ORDER BY (ORDER BY ordinal).

По умолчанию в YQL порядок колонок игнорируется:

  • порядок колонок в выходных таблицах и в результатах запроса не определен
  • схема данных результата UNION ALL выводится по именам колонок, а не по позициям

При включении PRAGMA OrderedColumns; порядок колонок сохраняется в результатах запроса и выводится из порядка колонок во входных таблицах по следующим правилам:

  • SELECT с явным перечислением колонок задает соответствующий порядок;
  • SELECT со звездочкой (SELECT * FROM ...) наследует порядок из своего входа;
  • порядок колонок после JOIN: сначала колонки левой стороны, потом правой. Если порядок какой-либо из сторон присутствующей в выходе JOIN не определен, порядок колонок результата также не определен;
  • порядок UNION ALL зависит от режима выполнения UNION ALL;
  • порядок колонок для AS_TABLE не определен;

UNION ALL

Конкатенация результатов нескольких SELECT (или подзапросов).

Поддерживаются два режима выполнения UNION ALL – по именам колонок (режим по умолчанию) и по позициям колонок (соответствует стандарту ANSI SQL и включается через соответствующую PRAGMA).

В режиме "по именам" результирующая схема данных выводится по следующим правилам:

  • в результирующую таблицу включаются все колонки, которые встречались хоть в одной из входных таблиц;
  • если колонка присутствовала не во всех входных таблицах, то ей автоматически присваивается опциональный тип данных (допускающий значение NULL);
  • если колонка в разных входных таблицах имела разные типы, то выводится общий тип (наиболее широкий);
  • если колонка в разных входных таблицах имела разнородный тип, например строку и число, то это считается ошибкой.

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

В режиме "по позициям" результирующая схема данных выводится по следующим правилам:

  • число колонок во всех входах должно быть одинаковым
  • порядок колонок во всех входах должен быть определен
  • имена результирующих колонок совпадают с именами колонок первой таблицы
  • тип результирующих колонок выводится как общий (наиболее широкий) тип из типов входных колонок стоящих на одинаковых позициях

Порядок выходных колонок в этом режиме совпадает с порядком колонок первого входа.

При наличии ORDER BY/LIMIT/DISCARD/INTO RESULT в объединяемых подзапросах применяются следующие правила:

  • ORDER BY/LIMIT/INTO RESULT допускается только после последнего подзапроса;
  • DISCARD допускается только перед первым подзапросом;
  • указанные операторы действуют на результат UNION ALL а на не подзапрос;
  • чтобы применить оператор к подзапросу, подзапрос необходимо взять в скобки.

Примеры

SELECT 1 AS x
UNION ALL
SELECT 2 AS y
UNION ALL
SELECT 3 AS z;

В результате выполнения данного запроса в режиме по-умолчанию будет сформирована выборка с тремя колонками x, y, и z. При включенной PRAGMA PositionalUnionAll; в выборке будет одна колонка x.

PRAGMA PositionalUnionAll;

SELECT 1 AS x, 2 as y
UNION ALL
SELECT * FROM AS_TABLE([<|x:3, y:4|>]); -- ошибка: порядок колонок в AS_TABLE не определен

SELECT * FROM T1
UNION ALL
(SELECT * FROM T2 ORDER BY key LIMIT 100); -- при отсутствии скобок ORDER BY/LIMIT применится к результату всего UNION ALL

COMMIT

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

Чтобы коммит выполнялся аналогичным образом автоматически после каждого выражения в запросе, можно использовать PRAGMA autocommit;.

Примеры:

INSERT INTO result1 SELECT * FROM my_table;
INSERT INTO result2 SELECT * FROM my_table;
COMMIT;
-- В result2 уже будет содержимое SELECT со второй строки:
INSERT INTO result3 SELECT * FROM result2;

WITHOUT

Исключение столбцов из результата SELECT *.

Примеры

SELECT * WITHOUT foo, bar FROM my_table;

PRAGMA simplecolumns;
SELECT * WITHOUT t.foo FROM my_table AS t
CROSS JOIN (SELECT 1 AS foo) AS v;

FROM ... SELECT ...

Перевернутая форма записи, в которой сначала указывается источник данных, а затем — операция.

Примеры

FROM my_table SELECT key, value;

FROM a_table AS a
JOIN b_table AS b
USING (key)
SELECT *;

FROM AS_TABLE

Обращение к именованным выражениям как к таблицам с помощью функции AS_TABLE.

AS_TABLE($variable) позволяет использовать значение $variable в качестве источника данных для запроса. При этом переменная $variable должна иметь тип List<Struct<...>>.

Пример

$data = AsList(
    AsStruct(1u AS Key, "v1" AS Value),
    AsStruct(2u AS Key, "v2" AS Value),
    AsStruct(3u AS Key, "v3" AS Value));

SELECT Key, Value FROM AS_TABLE($data);
В этой статье: