unnest

Описание

UNNEST — это табличная функция, которая принимает массив и преобразует элементы этого массива в несколько строк таблицы. Это преобразование также известно как "выравнивание".

Вы можете использовать Lateral Join с UNNEST для реализации общих преобразований, например, из STRING, ARRAY или BITMAP в несколько строк. Для получения дополнительной информации см. Lateral join.

Начиная с версии 1.5.0, UNNEST может принимать переменное количество параметров массива. Массивы могут различаться по типу и длине (количеству элементов). Если массивы имеют разную длину, преобладает наибольшая длина, что означает, что к массивам меньшей длины будут добавлены значения null. См. Пример 2 для получения дополнительной информации.

Начиная с версии 1.5.0, UNNEST можно использовать с LEFT JOIN ON TRUE, что позволяет сохранить все строки в левой таблице, даже если соответствующие строки в правой таблице пусты или содержат значения null. Для таких пустых или NULL строк возвращаются значения NULL. См. Пример 3 для получения дополнительной информации.

Синтаксис

unnest(array0[, array1 ...])

Параметры

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

Возвращаемое значение

Возвращает несколько строк, преобразованных из массива. Тип возвращаемого значения зависит от типов элементов в массиве.

Для типов элементов, поддерживаемых в массиве, см. ARRAY.

Примечания по использованию

• UNNEST — это табличная функция. Она должна использоваться с Lateral Join, но ключевое слово Lateral Join не нужно указывать явно.
• Если выражение массива вычисляется как NULL или оно пустое, строки не будут возвращены (за исключением LEFT JOIN ON TRUE).
• Если элемент в массиве равен NULL, для этого элемента возвращается NULL.

Примеры

Пример 1: UNNEST принимает один параметр

-- Создание таблицы student_score, где scores является столбцом ARRAY.
CREATE TABLE student_score
(
    `id` bigint(20) NULL COMMENT "",
    `scores` ARRAY<int> NULL COMMENT ""
)
DUPLICATE KEY (id)
DISTRIBUTED BY HASH(`id`);

-- Вставка данных в эту таблицу.
INSERT INTO student_score VALUES
(1, [80,85,87]),
(2, [77, null, 89]),
(3, null),
(4, []),
(5, [90,92]);

-- Запрос данных из этой таблицы.
SELECT * FROM student_score ORDER BY id;
+------+--------------+
| id   | scores       |
+------+--------------+
|    1 | [80,85,87]   |
|    2 | [77,null,89] |
|    3 | NULL         |
|    4 | []           |
|    5 | [90,92]      |
+------+--------------+

-- Использование UNNEST для выравнивания столбца scores в несколько строк.
SELECT id, scores, unnest FROM student_score, unnest(scores) AS unnest;
+------+--------------+--------+
| id   | scores       | unnest |
+------+--------------+--------+
|    1 | [80,85,87]   |     80 |
|    1 | [80,85,87]   |     85 |
|    1 | [80,85,87]   |     87 |
|    2 | [77,null,89] |     77 |
|    2 | [77,null,89] |   NULL |
|    2 | [77,null,89] |     89 |
|    5 | [90,92]      |     90 |
|    5 | [90,92]      |     92 |
+------+--------------+--------+

[80,85,87], соответствующий id = 1, преобразуется в три строки.

[77,null,89], соответствующий id = 2, сохраняет значение null.

scores, соответствующие id = 3 и id = 4, равны NULL и пусты, поэтому пропускаются.

Пример 2: UNNEST принимает несколько параметров

-- Создание таблицы example_table, где столбцы type и scores различаются по типу.
CREATE TABLE example_table (
id varchar(65533) NULL COMMENT "",
type varchar(65533) NULL COMMENT "",
scores ARRAY<int> NULL COMMENT ""
) ENGINE=OLAP
DUPLICATE KEY(id)
COMMENT "OLAP"
DISTRIBUTED BY HASH(id)
PROPERTIES (
"replication_num" = "3");

-- Вставка данных в таблицу.
INSERT INTO example_table VALUES
("1", "typeA;typeB", [80,85,88]),
("2", "typeA;typeB;typeC", [87,90,95]);

-- Запрос данных из таблицы.
SELECT * FROM example_table;
+------+-------------------+------------+
| id   | type              | scores     |
+------+-------------------+------------+
| 1    | typeA;typeB       | [80,85,88] |
| 2    | typeA;typeB;typeC | [87,90,95] |
+------+-------------------+------------+

-- Использование UNNEST для преобразования type и scores в несколько строк.
SELECT id, unnest.type, unnest.scores
FROM example_table, unnest(split(type, ";"), scores) AS unnest(type,scores);
+------+-------+--------+
| id   | type  | scores |
+------+-------+--------+
| 1    | typeA |     80 |
| 1    | typeB |     85 |
| 1    | NULL  |     88 |
| 2    | typeA |     87 |
| 2    | typeB |     90 |
| 2    | typeC |     95 |
+------+-------+--------+

type и scores в UNNEST различаются по типу и длине.

type — это столбец VARCHAR, а scores — столбец ARRAY. Функция split() используется для преобразования type в ARRAY.

Для id = 1 type преобразуется в ["typeA","typeB"], который содержит два элемента.

Для id = 2 type преобразуется в ["typeA","typeB","typeC"], который содержит три элемента.

Чтобы обеспечить одинаковое количество строк для каждого id, к ["typeA","typeB"] добавляется элемент null.

Пример 3: UNNEST с LEFT JOIN ON TRUE

-- Создание таблицы student_score, где scores является столбцом ARRAY.
CREATE TABLE student_score
(
`id` bigint(20) NULL COMMENT "",
`scores` ARRAY<int> NULL COMMENT ""
)
DUPLICATE KEY (id)
DISTRIBUTED BY HASH(`id`)
PROPERTIES (
"replication_num" = "1"
);

-- Вставка данных в эту таблицу.
INSERT INTO student_score VALUES
(1, [80,85,87]),
(2, [77, null, 89]),
(3, null),
(4, []),
(5, [90,92]);

-- Запрос данных из этой таблицы.
SELECT * FROM student_score ORDER BY id;
+------+--------------+
| id   | scores       |
+------+--------------+
|    1 | [80,85,87]   |
|    2 | [77,null,89] |
|    3 | NULL         |
|    4 | []           |
|    5 | [90,92]      |
+------+--------------+

-- Использование LEFT JOIN ON TRUE.
SELECT id, scores, unnest FROM student_score LEFT JOIN unnest(scores) AS unnest ON TRUE ORDER BY 1, 3;
+------+--------------+--------+
| id   | scores       | unnest |
+------+--------------+--------+
|  1   | [80,85,87]   |     80 |
|  1   | [80,85,87]   |     85 |
|  1   | [80,85,87]   |     87 |
|  2   | [77,null,89] |   NULL |
|  2   | [77,null,89] |     77 |
|  2   | [77,null,89] |     89 |
|  3   | NULL         |   NULL |
|  4   | []           |   NULL |
|  5   | [90,92]      |     90 |
|  5   | [90,92]      |     92 |
+------+--------------+--------+

[80,85,87], соответствующий id = 1, преобразуется в три строки.

Значение null в [77,null,89], соответствующем id = 2, сохраняется.

scores, соответствующие id = 3 и id = 4, равны NULL и пусты. Left Join сохраняет эти две строки и возвращает для них значения NULL.