如何在BigQuery同查询中结合GroupBy与Window函数

报错原因 #

你遇到的错误是因为GROUP BY执行后，原表的purchases字段已不存在于结果集。分组操作会将同purchase_date+product的行合并，最终结果只保留分组键（purchase_date、product）和聚合生成的字段，此时直接在窗口函数中引用未聚合的purchases自然会报错。

解决方案一：先分组聚合，再基于结果计算当日总量 #

先通过GROUP BY得到每日各产品的采购总和，再在这个结果集上用窗口函数计算当日所有产品的总采购量：

WITH Purchase AS
 (
  SELECT '2023/06/14' as purchase_date, "kale" as product, 10 as purchases, 'John' as purchaser
  UNION ALL SELECT '2023/06/14', "kale", 15, 'Tom'
  UNION ALL SELECT '2023/06/14', "banana", 2, 'John'
  UNION ALL SELECT '2023/06/14', "banana", 4, 'Tom'
  UNION ALL SELECT '2023/06/15', "kale", 9, 'John'
  UNION ALL SELECT '2023/06/15', "kale",15, 'Tom'
  UNION ALL SELECT '2023/06/15', "banana", 6, 'John'
  UNION ALL SELECT '2023/06/15', "banana", 6, 'Tom'
),
DailyProductSum AS (
  SELECT
    purchase_date,
    product,
    SUM(purchases) as sum_of_product
  FROM Purchase
  GROUP BY purchase_date, product
)
SELECT
  purchase_date,
  product,
  sum_of_product,
  SUM(sum_of_product) OVER(PARTITION BY purchase_date) as total_daily_purchases
FROM DailyProductSum

解决方案二：全程用窗口函数，跳过GROUP BY #

直接通过两层窗口函数分别计算产品日总和与当日总采购量，最后用DISTINCT去重得到唯一的日期-产品组合：

WITH Purchase AS
 (
  SELECT '2023/06/14' as purchase_date, "kale" as product, 10 as purchases, 'John' as purchaser
  UNION ALL SELECT '2023/06/14', "kale", 15, 'Tom'
  UNION ALL SELECT '2023/06/14', "banana", 2, 'John'
  UNION ALL SELECT '2023/06/14', "banana", 4, 'Tom'
  UNION ALL SELECT '2023/06/15', "kale", 9, 'John'
  UNION ALL SELECT '2023/06/15', "kale",15, 'Tom'
  UNION ALL SELECT '2023/06/15', "banana", 6, 'John'
  UNION ALL SELECT '2023/06/15', "banana", 6, 'Tom'
)
SELECT DISTINCT
  purchase_date,
  product,
  SUM(purchases) OVER(PARTITION BY purchase_date, product) as sum_of_product,
  SUM(purchases) OVER(PARTITION BY purchase_date) as total_daily_purchases
FROM Purchase

两种方案都能得到你需要的结果：每日各产品采购量总和，以及当日所有产品的总采购量，且无需自连接操作。

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