sql 数组三 展平数组

展平数组
如需将 ARRAY 转换为一组行（即执行展平操作），请使用 UNNEST 运算符。UNNEST 获取一个 ARRAY，然后返回一个表，ARRAY 中的每个元素均占该表的一行。

由于 UNNEST 破坏了 ARRAY 元素的顺序，您可能需要恢复表中的顺序。为此，请使用可选的 WITH OFFSET 子句返回另一个包含各数组元素偏移量的列，然后使用 ORDER BY 子句按偏移量对行进行排序。

示例

SELECT *
FROM UNNEST(['foo', 'bar', 'baz', 'qux', 'corge', 'garply', 'waldo', 'fred'])
  AS element
WITH OFFSET AS offset
ORDER BY offset;

+----------+--------+
| element  | offset |
+----------+--------+
| foo      | 0      |
| bar      | 1      |
| baz      | 2      |
| qux      | 3      |
| corge    | 4      |
| garply   | 5      |
| waldo    | 6      |
| fred     | 7      |
+----------+--------+

请注意，对于相关交叉联接，UNNEST 运算符为可选运算符，CROSS JOIN 可表示为逗号联接。上述示例使用此简写表示法后变为：

WITH sequences AS
  (SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers
   UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers
   UNION ALL SELECT 3 AS id, [5, 10] AS some_numbers)
SELECT id, flattened_numbers
FROM sequences, sequences.some_numbers AS flattened_numbers;

+------+-------------------+
| id   | flattened_numbers |
+------+-------------------+
|    1 |                 0 |
|    1 |                 1 |
|    1 |                 1 |
|    1 |                 2 |
|    1 |                 3 |
|    1 |                 5 |
|    2 |                 2 |
|    2 |                 4 |
|    2 |                 8 |
|    2 |                16 |
|    2 |                32 |
|    3 |                 5 |
|    3 |                10 |
+------+-------------------+