Snowflake SQL实现行级订单表转单订单多商品列格式方案咨询

实现逻辑 #

要将每行对应单个商品的订单表转换为每行对应一个订单、最多包含20组商品及价格的结构，核心步骤是：

• 为每个订单内的商品分配唯一序号，限制最多保留前20个
• 通过条件聚合将同订单的多行数据转为一行多列

完整SQL代码 #

WITH ordered_products AS (
    -- 为每个订单内的商品排序并编号，仅保留前20个
    SELECT 
        Orders,
        Product,
        Price,
        ROW_NUMBER() OVER (PARTITION BY Orders ORDER BY Product) AS item_seq
    FROM productlisting
    QUALIFY item_seq <= 20
)
SELECT 
    Orders,
    -- 生成第1组商品和价格列
    MAX(CASE WHEN item_seq = 1 THEN Product END) AS "Product 1",
    MAX(CASE WHEN item_seq = 1 THEN Price END) AS "Price 1",
    -- 生成第2组商品和价格列
    MAX(CASE WHEN item_seq = 2 THEN Product END) AS "Product 2",
    MAX(CASE WHEN item_seq = 2 THEN Price END) AS "Price 2",
    -- 生成第3组商品和价格列
    MAX(CASE WHEN item_seq = 3 THEN Product END) AS "Product 3",
    MAX(CASE WHEN item_seq = 3 THEN Price END) AS "Price 3",
    -- 依次复制到第20组
    MAX(CASE WHEN item_seq = 4 THEN Product END) AS "Product 4",
    MAX(CASE WHEN item_seq = 4 THEN Price END) AS "Price 4",
    MAX(CASE WHEN item_seq = 5 THEN Product END) AS "Product 5",
    MAX(CASE WHEN item_seq = 5 THEN Price END) AS "Price 5",
    MAX(CASE WHEN item_seq = 6 THEN Product END) AS "Product 6",
    MAX(CASE WHEN item_seq = 6 THEN Price END) AS "Price 6",
    MAX(CASE WHEN item_seq = 7 THEN Product END) AS "Product 7",
    MAX(CASE WHEN item_seq = 7 THEN Price END) AS "Price 7",
    MAX(CASE WHEN item_seq = 8 THEN Product END) AS "Product 8",
    MAX(CASE WHEN item_seq = 8 THEN Price END) AS "Price 8",
    MAX(CASE WHEN item_seq = 9 THEN Product END) AS "Product 9",
    MAX(CASE WHEN item_seq = 9 THEN Price END) AS "Price 9",
    MAX(CASE WHEN item_seq = 10 THEN Product END) AS "Product 10",
    MAX(CASE WHEN item_seq = 10 THEN Price END) AS "Price 10",
    MAX(CASE WHEN item_seq = 11 THEN Product END) AS "Product 11",
    MAX(CASE WHEN item_seq = 11 THEN Price END) AS "Price 11",
    MAX(CASE WHEN item_seq = 12 THEN Product END) AS "Product 12",
    MAX(CASE WHEN item_seq = 12 THEN Price END) AS "Price 12",
    MAX(CASE WHEN item_seq = 13 THEN Product END) AS "Product 13",
    MAX(CASE WHEN item_seq = 13 THEN Price END) AS "Price 13",
    MAX(CASE WHEN item_seq = 14 THEN Product END) AS "Product 14",
    MAX(CASE WHEN item_seq = 14 THEN Price END) AS "Price 14",
    MAX(CASE WHEN item_seq = 15 THEN Product END) AS "Product 15",
    MAX(CASE WHEN item_seq = 15 THEN Price END) AS "Price 15",
    MAX(CASE WHEN item_seq = 16 THEN Product END) AS "Product 16",
    MAX(CASE WHEN item_seq = 16 THEN Price END) AS "Price 16",
    MAX(CASE WHEN item_seq = 17 THEN Product END) AS "Product 17",
    MAX(CASE WHEN item_seq = 17 THEN Price END) AS "Price 17",
    MAX(CASE WHEN item_seq = 18 THEN Product END) AS "Product 18",
    MAX(CASE WHEN item_seq = 18 THEN Price END) AS "Price 18",
    MAX(CASE WHEN item_seq = 19 THEN Product END) AS "Product 19",
    MAX(CASE WHEN item_seq = 19 THEN Price END) AS "Price 19",
    MAX(CASE WHEN item_seq = 20 THEN Product END) AS "Product 20",
    MAX(CASE WHEN item_seq = 20 THEN Price END) AS "Price 20"
FROM ordered_products
GROUP BY Orders
ORDER BY Orders;

关键细节说明 #

• 序号控制：使用ROW_NUMBER()窗口函数按订单分组排序商品，QUALIFY子句直接过滤掉每个订单第20个之后的商品，减少后续计算量。
• 条件聚合：通过MAX(CASE...)提取对应序号的商品和价格，没有对应商品的列会返回NULL，符合目标表的结构要求。
• 性能适配：针对8万级SKU基数，Snowflake的分布式计算能高效处理窗口函数和分组聚合，若数据量极大，可考虑对Orders列建立聚簇键提升查询效率。

内容的提问来源于stack exchange，提问作者2Teachis2LearnTwice