FLATTEN

FLATTEN BY

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

Например:

• Исходная таблица:

  [a, b, c]	1
  [d]	2
  []	3

• Таблица после вызова FLATTEN BY к левому столбцу:

  a	1
  b	1
  c	1
  d	2

Пример

$sample = AsList(
    AsStruct(AsList('a','b','c') AS value, CAST(1 AS Uint32) AS id),
    AsStruct(AsList('d') AS value, CAST(2 AS Uint32) AS id),
    AsStruct(AsList() AS value, CAST(3 AS Uint32) AS id)
);

SELECT value, id FROM as_table($sample) FLATTEN BY (value);

Такое преобразование может быть удобным в следующих случаях:

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

Синтаксис

• FLATTEN BY указывается после FROM, но перед GROUP BY, если GROUP BY присутствует в запросе.
• Тип столбца-результата зависит от типа исходного столбца:

• По умолчанию столбец с результатом заменяет исходный. Используйте FLATTEN BY foo AS bar для сохранения исходного контейнера. В результате исходный контейнер останется доступным в foo, а построенный — в bar.
• Чтобы построить декартово произведение нескольких столбцов-контейнеров, используйте конструкцию FLATTEN BY (a, b, c). Скобки обязательны, чтобы избежать конфликтов в грамматике.
• В FLATTEN BY можно использовать только имена столбцов из входной таблицы. Чтобы применить FLATTEN BY к результату вычисления, используйте подзапрос.
• В FLATTEN BY можно использовать не только столбцы, но и произвольные именованные выражения (в отличие от столбцов AS обязательно). Из-за грамматических неоднозначностей выражения после FLATTEN BY должны быть заключены в скобки: ... FLATTEN BY (ListSkip(col, 1) AS col) ...
• Если в исходном столбце были вложенные контейнеры, например List<Dict<X,Y>>, FLATTEN BY развернет только внешний уровень. Чтобы полностью развернуть вложенные контейнеры, используйте подзапрос.

Чтобы применить FLATTEN BY к Yson, нужно сначала преобразовать его в один из обсуждавшихся выше типов контейнеров. Например, это можно сделать с помощью одноименного UDF-модуля из списка предустановленных.

Уточнение типа контейнера

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

• FLATTEN LIST BY

  Для Optional<List<T>> операция FLATTEN LIST BY будет разворачивать список, интерпретируя NULL-значение как пустой список.

• FLATTEN DICT BY

  Для Optional<Dict<T>> операция FLATTEN DICT BY будет разворачивать словарь, интерпретируя NULL-значение как пустой словарь.

• FLATTEN OPTIONAL BY

  Чтобы фильтровать NULL-значения без размножения, необходимо уточнить операцию до FLATTEN OPTIONAL BY.

Примеры

SELECT
  t.item.0 AS key,
  t.item.1 AS value,
  t.dict_column AS original_dict,
  t.other_column AS other
FROM my_table AS t
FLATTEN DICT BY dict_column AS item;

SELECT * FROM (
    SELECT
        AsList(1, 2, 3) AS a,
        AsList("x", "y", "z") AS b
) FLATTEN LIST BY (a, b);

SELECT * FROM (
    SELECT
        "1;2;3" AS a,
        AsList("x", "y", "z") AS b
) FLATTEN LIST BY (String::SplitToList(a, ";") as a, b);

Аналоги FLATTEN BY для других СУБД

• PostgreSQL: unnest;
• Hive: LATERAL VIEW;
• MongoDB: unwind;
• Google BigQuery: FLATTEN;
• ClickHouse: ARRAY JOIN / arrayJoin;

FLATTEN COLUMNS

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

Имена исходных столбцов-структур не используются и не возвращаются в результате. Имена элементов структур не должны повторяться в исходных столбцах.

Пример

SELECT x, y, z
FROM (
  SELECT
    AsStruct(
        1 AS x,
        "foo" AS y),
    AsStruct(
        false AS z)
) FLATTEN COLUMNS;