FLATTEN

Статья создана

FLATTEN BY
- Уточнение типа контейнера
- Аналоги FLATTEN BY для других СУБД
FLATTEN COLUMNS

FLATTEN BY

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

Например:

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

[a, b, c] 1

[d] 2

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

a 1

b 1

c 1

d 2

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

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 присутствует в запросе.
Тип столбца-результата зависит от типа исходного столбца:

Тип контейнера	Тип результата	Комментарий
`List<X>`	`X`	Тип ячейки списка
`Dict<X,Y>`	`Tuple<X,Y>`	Кортеж из двух элементов с парами «ключ—значение»
`Optional<X>`	`X`	Результат практически эквивалентен конструкции `WHERE foo IS NOT NULL`, но тип колонки `foo` будет изменен на `X`

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

Примечание

FLATTEN BY интерпретирует опциональные типы данных как списки длины 0 или 1. Строки таблицы с NULL пропускаются, и тип столбца меняется на аналогичный неопциональный.

FLATTEN BY делает только одно преобразование за раз, поэтому на опциональных контейнерах, например, Optional<List<String>> следует использовать FLATTEN LIST BY или FLATTEN OPTIONAL BY.

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

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

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.

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

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

Примеры

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

FLATTEN COLUMNS

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

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