Перейти к основному содержимому

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.