在BigQuery中按周、型号聚合订单数据的需求实现

针对你需要将百万行订单表降粒度，按周和型号聚合的需求，以下是符合要求的BigQuery SQL实现：

WITH order_level_agg AS (
  SELECT
    DATE_TRUNC(DATE_REQUEST, WEEK(MONDAY)) AS PURCHASE_WEEK_MONDAY_START,
    订单编号,
    型号, -- 每个订单编号对应唯一型号，直接保留即可
    MAX(数量) AS order_max_qty, -- 取每个订单的数量最大值
    SUM(价格) AS order_total_price -- 计算每个订单的总价格
  FROM
    `your-project.your-dataset.your-order-table` -- 替换为你的表路径
  GROUP BY
    DATE_TRUNC(DATE_REQUEST, WEEK(MONDAY)),
    订单编号,
    型号
)
SELECT
  EXTRACT(WEEK FROM PURCHASE_WEEK_MONDAY_START) AS 周,
  型号,
  SUM(order_max_qty) AS 数量, -- 按周+型号汇总订单最大数量
  SUM(order_total_price) AS 价格 -- 按周+型号汇总所有订单的总价格
FROM
  order_level_agg
GROUP BY
  PURCHASE_WEEK_MONDAY_START,
  型号
ORDER BY
  周,
  型号;

逻辑说明 #

1. 子查询order_level_agg：先对每个订单进行聚合处理，解决同一订单多行的问题：

   • 用DATE_TRUNC将订单日期转换为周一为起始的周日期
   • 按订单编号分组，因每个订单对应唯一型号，直接保留型号字段
   • 取每个订单的最大数量，避免同一订单多行数量重复累加
   • 计算每个订单所有行的价格总和

2. 外层查询：基于订单级聚合结果，按周和型号做最终汇总：

   • 用EXTRACT(WEEK ...)将周日期转换为数字周数，匹配示例格式
   • 对每个周+型号组合，累加所有订单的最大数量得到最终数量
   • 累加所有订单的总价格得到最终价格

示例验证 #

针对你提供的原始数据，执行上述SQL后会得到和示例完全一致的结果：

• 第10周AA型号：数量=2（A23）+3（B45）=5，价格=40（A23总价）+50（B45总价）=90
• 第10周BB型号：数量=2（E78），价格=40（E78总价）
• 第11周CC型号：数量=1（C56）+3（D89）=4，价格=35（C56总价）+15（D89总价）=50

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