如何在Snowflake中按customer_key透视前3订单的part_前缀列？

需求：按CUSTOMER_KEY对排名前3的订单中前缀为PART_的三列（PART_KEY、PART_QUANTITY、PART_AMOUNT）进行透视。

原始数据 #

预期结果 #

示例数据（CTE） #

WITH t1 AS (
SELECT '10003' AS CUSTOMER_KEY, '98909' AS PART_KEY, 39 AS PART_QUANTITY, 74408.10 AS PART_AMOUNT, 1 AS TOP_ORDER_RANK UNION ALL
SELECT '10003' AS CUSTOMER_KEY, '157096' AS PART_KEY, 49 AS PART_QUANTITY, 56501.41 AS PART_AMOUNT, 2 AS TOP_ORDER_RANK UNION ALL
SELECT '10003' AS CUSTOMER_KEY, '179085' AS PART_KEY, 42 AS PART_QUANTITY, 48891.36 AS PART_AMOUNT, 3 AS TOP_ORDER_RANK UNION ALL
SELECT '10003' AS CUSTOMER_KEY, '179075' AS PART_KEY, 10 AS PART_QUANTITY, 28891.36 AS PART_AMOUNT, 4 AS TOP_ORDER_RANK
)

实现方案 #

使用条件聚合实现透视，兼容大多数SQL引擎：

WITH t1 AS (
SELECT '10003' AS CUSTOMER_KEY, '98909' AS PART_KEY, 39 AS PART_QUANTITY, 74408.10 AS PART_AMOUNT, 1 AS TOP_ORDER_RANK UNION ALL
SELECT '10003' AS CUSTOMER_KEY, '157096' AS PART_KEY, 49 AS PART_QUANTITY, 56501.41 AS PART_AMOUNT, 2 AS TOP_ORDER_RANK UNION ALL
SELECT '10003' AS CUSTOMER_KEY, '179085' AS PART_KEY, 42 AS PART_QUANTITY, 48891.36 AS PART_AMOUNT, 3 AS TOP_ORDER_RANK UNION ALL
SELECT '10003' AS CUSTOMER_KEY, '179075' AS PART_KEY, 10 AS PART_QUANTITY, 28891.36 AS PART_AMOUNT, 4 AS TOP_ORDER_RANK
)
SELECT
  CUSTOMER_KEY,
  MAX(CASE WHEN TOP_ORDER_RANK = 1 THEN PART_KEY END) AS PART_1_KEY,
  MAX(CASE WHEN TOP_ORDER_RANK = 1 THEN PART_QUANTITY END) AS PART_1_QUANTITY,
  MAX(CASE WHEN TOP_ORDER_RANK = 1 THEN PART_AMOUNT END) AS PART_1_AMOUNT,
  MAX(CASE WHEN TOP_ORDER_RANK = 2 THEN PART_KEY END) AS PART_2_KEY,
  MAX(CASE WHEN TOP_ORDER_RANK = 2 THEN PART_QUANTITY END) AS PART_2_QUANTITY,
  MAX(CASE WHEN TOP_ORDER_RANK = 2 THEN PART_AMOUNT END) AS PART_2_AMOUNT,
  MAX(CASE WHEN TOP_ORDER_RANK = 3 THEN PART_KEY END) AS PART_3_KEY,
  MAX(CASE WHEN TOP_ORDER_RANK = 3 THEN PART_QUANTITY END) AS PART_3_QUANTITY,
  MAX(CASE WHEN TOP_ORDER_RANK = 3 THEN PART_AMOUNT END) AS PART_3_AMOUNT
FROM t1
WHERE TOP_ORDER_RANK <=3
GROUP BY CUSTOMER_KEY;

逻辑说明 #

• 先通过WHERE TOP_ORDER_RANK <=3过滤出前3名订单
• 针对每个排名（1-3），用CASE语句提取对应的PART_KEY、PART_QUANTITY、PART_AMOUNT字段
• 最后按CUSTOMER_KEY分组，用MAX聚合得到每个分组下各排名对应的字段值

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