unnest
UNNEST — это табличная функция, которая принимает массив и преобразует элементы в нем в несколько строк таблицы. Такое преобразование также известно как «развертывание» (flattening).
Вы можете использовать Lateral Join с UNNEST для реализации распространенных преобразований, например, из STRING, ARRAY или BITMAP в несколько строк. Для получения дополнительной информации см. Lateral join.
Начиная с версии v1.5.2, UNNEST может принимать переменное количество параметров массивов. Массивы могут различаться по типу и длине (количеству элементов). Если массивы имеют разную длину, применяется наибольшая длина, что означает, что в массивы меньшей длины будут добавлены значения null. Для получения дополнительной информации см. Пример 2.
Начиная с версии v1.5.2, 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.