Google Cloud Platform中SQL行转列问题求助

问题场景 #

有一张包含数十万行的表，核心列如下（另有25列左右）：

期望将Order_Event的不同取值转为列，每个Order_ID对应一行，结果如下：

尝试过的方法及问题 #

1. 无Order_ID的查询代码：

SELECT
  max(case when Order_Event = "Order_created" then FORMAT_TIMESTAMP ('%R',Timestamp) end)Order_Created,
  max(case when Order_Event = "Order_delivered" then FORMAT_TIMESTAMP ('%R',Timestamp) end) Order_Delivered,
  max(case when Order_Event = "Order_planned_delivery" then FORMAT_TIMESTAMP ('%R',Timestamp) end) Order_Planned_Delivery,
FROM my_sweet_table
limit 100

问题：仅返回一行结果，是所有订单对应事件时间的最大值，无法按单个Order_ID拆分数据。

2. 添加Order_ID后的查询代码：

SELECT
Order_ID, 
  max(case when Order_Event = "Order_created" then FORMAT_TIMESTAMP ('%R',Timestamp) end)Order_Created,
  max(case when Order_Event = "Order_delivered" then FORMAT_TIMESTAMP ('%R',Timestamp) end) Order_Delivered,
  max(case when Order_Event = "Order_planned_delivery" then FORMAT_TIMESTAMP ('%R',Timestamp) end) Order_Planned_Delivery,
FROM my_sweet_table
limit 100

问题：报错SELECT list expression references column Order_ID which is neither grouped nor aggregated at [2:1]

解决方案 #

方法1：添加GROUP BY子句 #

要按Order_ID拆分结果，必须将Order_ID加入GROUP BY子句，让聚合函数按每个Order_ID单独计算：

SELECT
  Order_ID,
  MAX(CASE WHEN Order_Event = "Order_Created" THEN Timestamp END) AS Order_Created,
  MAX(CASE WHEN Order_Event = "Order_delivered" THEN Timestamp END) AS Order_delivered,
  MAX(CASE WHEN Order_Event = "Order_planned_delivery" THEN Timestamp END) AS Order_planned_delivery
FROM my_sweet_table
GROUP BY Order_ID
LIMIT 100

说明：

• 无需提前格式化时间，直接保留原始时间类型即可；若需要特定格式，可在MAX内部添加FORMAT_TIMESTAMP转换
• MAX函数用于提取每个Order_ID对应事件的唯一时间（假设每个订单的每个事件仅一条记录），若存在重复记录，会自动取最晚的时间

方法2：使用BigQuery PIVOT函数 #

BigQuery的PIVOT函数支持时间类型值，无需局限于数值类型，直接使用即可：

SELECT *
FROM my_sweet_table
PIVOT(
  MAX(Timestamp) FOR Order_Event IN (
    "Order_Created" AS Order_Created,
    "Order_delivered" AS Order_delivered,
    "Order_planned_delivery" AS Order_planned_delivery
  )
)
LIMIT 100

说明：

• PIVOT内部必须使用聚合函数，这里用MAX是因为每个Order_ID+Order_Event组合仅对应一条记录，MAX结果就是该记录的时间值
• 结果会自动按Order_ID分组，符合需求

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