如何在Presto中将订单商品数组拆分为item与价格对应多列

场景1：已知订单最大商品数，可接受固定列数 #

这种情况直接用数组下标取值即可，Presto中数组下标从1开始，超出数组长度的取值会返回NULL，刚好符合你要的效果。
示例查询代码：

SELECT
    `order`,
    item[1].item_id AS item1,
    item[2].item_id AS item2,
    item[3].item_id AS item3,
    -- 最大商品数有多少就追加写到对应itemN即可
    item[1].item_price AS price1,
    item[2].item_price AS price2,
    item[3].item_price AS price3
    -- 同理追加写到对应priceN即可
FROM 你的表名

你提供的示例数据执行上述查询，就能得到你贴的示例输出结果。

场景2：商品数量无上限，需要动态列 #

Presto原生SQL不支持动态列输出，因为SQL执行前必须确定返回的列数和列类型，无法随数据内容动态调整列数，这种情况可以用以下两种替代方案：

方案A：输出为Map/JSON格式 #

把所有item和price的键值对存在一个Map列里，不管多少个商品都可以完整存储，查询时可以直接按键取值。
示例查询代码：

WITH order_items AS (
    SELECT
        `order`,
        pos,
        item_info.item_id,
        item_info.item_price
    FROM 你的表名
    CROSS JOIN UNNEST(item) WITH ORDINALITY AS t(item_info, pos)
)
SELECT
    `order`,
    map_agg(
        CASE WHEN type = 'item' THEN 'item' || pos ELSE 'price' || pos END,
        value
    ) AS item_price_map
FROM order_items
CROSS JOIN (VALUES ('item', item_id), ('price', item_price)) AS t(type, value)
GROUP BY `order`

输出的item_price_map列结构为{"item1":11,"price1":1000,"item2":22,"price2":3000...}，可以直接按key读取对应值。

方案B：上层工具处理 #

如果必须要多列展示，可以先查询出每个订单的原始item数组，在业务代码、BI工具中做动态列展开，市面上多数BI工具（比如Metabase、Superset）都支持数组类型自动展开为多列的功能。

内容的提问来源于stack exchange，提问作者Ánh Lâm