探索DuckDB:数组字段转多列就这么简单
数组字段转多列就这么简单
前两天DuckDB群里有人提出这样一个需求， 数组如何转多列，
我有个表某一列是list结构，怎样快速将这个list结构列展开为多列(每个元素一列)？
就像下面这个例子，
CREATE TABLE tbl (
id INTEGER PRIMARY KEY,
y INTEGER[]
);
INSERT INTO tbl (id, y)
VALUES (1, ARRAY[1, 2, 3, 3, 5]::integer[])
, (2, ARRAY[3, 4, 5, 6]::integer[])
, (3, ARRAY[30, 400, 56, 16]::integer[]);| d | y |
| 1 | [1, 2, 3, 3, 5] |
| 2 | [3,4,5,6] |
| 3 | [30, 400, 56, 16] |
如何得到,
| id | y1 | y2 | y3 | y4 | y5 |
| 1 | 1 | 2 | 3 | 3 | 5 |
| 2 | 3 | 4 | 5 | 6 | None |
| 3 | 30 | 400 | 56 | 16 | None |
如果数组长度确定，我们可以尝试这样做
SELECT id, y[1] y1, y[2] y2,
y[3] y3, y[4] y4 ,y[5] y5
FROM tbl这样做的缺点是需要判断数组的最大长度，还有针对每个需求再写一次SQL
如果这样做，看看是不是很优雅？
WITH a AS (SELECT id, UNNEST(y) AS yy FROM tbl)
,b AS (SELECT *,
'y' || ROW_NUMBER() OVER (PARTITION BY id) AS rn
FROM a)
PIVOT b
ON rn
USING MAX(yy);这里用到了，
• UNNEST，把数组转行
• ROW_NUMBER，开窗函数
• PIVOT， 长表转宽表函数
再附上一个CTE递归+PIVOT实现的版本，
WITH RECURSIVE list_items AS (
SELECT id, 0 AS index, y[1] AS val,y
FROM tbl
UNION ALL
SELECT id, index + 1 AS index, y[index+2] AS val, y
FROM list_items
WHERE index < array_length(y) - 1
)
, a AS (SELECT id, 'y' ||index AS index, val FROM list_items)
PIVOT a
ON index
USING max(val);这一切都依赖DuckDB下的强大函数PIVOT[1]。
PIVOT
PIVOT 语句允许在一列中的独特值被分别放到自己的列中。这些新列中的值是使用对应每个独特值的子集行进行聚合函数计算得出的。
DuckDB 实现了 SQL 标准 PIVOT 语法和一个简化的 PIVOT 语法，该语法自动检测在旋转时创建列。如果将 PIVOT 关键字替换为 PIVOT_WIDER，也可以使用它。
数据分析，机器学习，在数据处理上这个功能是非常常用的，会Excel的基本都要求会这个操作
Postgres里没有PIVOT函数，要实现长转宽就比较麻烦，需要用到动态SQL，详细可以看我之前的文章 PostgreSQL实现动态行转列的方法汇总。
PIVOT 简化语法
PIVOT ⟨dataset⟩
ON ⟨columns⟩
USING ⟨values⟩
GROUP BY ⟨rows⟩
ORDER BY ⟨columns_with_order_directions⟩
LIMIT ⟨number_of_rows⟩;完整版本的语法，可以看下图，
这个语法充分体现了 DuckDB 践行的 DuckDB 让 SQL 更友好 以及 将 SQL 弯曲成灵活的新形状。
这里就不对PIVOT的使用详细展开了，官方的文档[2]写得非常详细了，网上看了几篇介绍PIVOT的文章，都不如官方文档写得详细。
引用链接
[1] PIVOT: https://duckdb.org/docs/sql/statements/pivot[2] 文档: https://duckdb.org/docs/sql/statements/pivot