BigQuery中按<=各唯一日期分组的可扩展实现方案咨询

需求说明 #

我们有示例数据表t1，需要生成每个唯一日期对应一行的统计结果：对每个日期，统计该日期及之前所有记录的行数、stat1总和、stat2总和。原方案通过硬编码日期实现，无法适配数据中新增的日期，缺乏扩展性，需要自动提取t1中的唯一gameDate完成统计。

示例数据表t1 #

WITH t1 AS (
  SELECT '2022-12-01' AS gameDate, 3 AS stat1, 7 AS stat2 UNION ALL
  SELECT '2022-12-01' AS gameDate, 2 AS stat1, 5 AS stat2 UNION ALL
  SELECT '2022-12-01' AS gameDate, 5 AS stat1, 6 AS stat2 UNION ALL
  SELECT '2022-12-02' AS gameDate, 3 AS stat1, 8 AS stat2 UNION ALL
  SELECT '2022-12-02' AS gameDate, 4 AS stat1, 7 AS stat2 UNION ALL
  SELECT '2022-12-02' AS gameDate, 2 AS stat1, 8 AS stat2 UNION ALL
  SELECT '2022-12-02' AS gameDate, 3 AS stat1, 8 AS stat2 UNION ALL
  SELECT '2022-12-03' AS gameDate, 1 AS stat1, 6 AS stat2 UNION ALL
  SELECT '2022-12-03' AS gameDate, 2 AS stat1, 6 AS stat2 UNION ALL
  SELECT '2022-12-03' AS gameDate, 3 AS stat1, 8 AS stat2 UNION ALL
  SELECT '2022-12-03' AS gameDate, 4 AS stat1, 9 AS stat2 UNION ALL
  SELECT '2022-12-03' AS gameDate, 4 AS stat1, 5 AS stat2 UNION ALL
  SELECT '2022-12-04' AS gameDate, 2 AS stat1, 9 AS stat2 UNION ALL
  SELECT '2022-12-04' AS gameDate, 1 AS stat1, 7 AS stat2 UNION ALL
  SELECT '2022-12-04' AS gameDate, 2 AS stat1, 7 AS stat2 UNION ALL
  SELECT '2022-12-04' AS gameDate, 1 AS stat1, 5 AS stat2 UNION ALL
  SELECT '2022-12-04' AS gameDate, 4 AS stat1, 9 AS stat2 UNION ALL
  SELECT '2022-12-05' AS gameDate, 3 AS stat1, 8 AS stat2 UNION ALL
  SELECT '2022-12-05' AS gameDate, 3 AS stat1, 6 AS stat2 UNION ALL
  SELECT '2022-12-05' AS gameDate, 4 AS stat1, 6 AS stat2 UNION ALL
  SELECT '2022-12-06' AS gameDate, 1 AS stat1, 5 AS stat2 UNION ALL
  SELECT '2022-12-06' AS gameDate, 3 AS stat1, 7 AS stat2
)

解决方案 #

推荐两种可扩展的BigQuery实现方式：

方法1：交叉连接生成日期维度 #

先提取t1中所有唯一的gameDate作为日期维度表，再和原表关联，过滤出gameDate <= rowDate的记录后分组统计，逻辑直观易懂：

WITH t1 AS (
  SELECT '2022-12-01' AS gameDate, 3 AS stat1, 7 AS stat2 UNION ALL
  SELECT '2022-12-01' AS gameDate, 2 AS stat1, 5 AS stat2 UNION ALL
  SELECT '2022-12-01' AS gameDate, 5 AS stat1, 6 AS stat2 UNION ALL
  SELECT '2022-12-02' AS gameDate, 3 AS stat1, 8 AS stat2 UNION ALL
  SELECT '2022-12-02' AS gameDate, 4 AS stat1, 7 AS stat2 UNION ALL
  SELECT '2022-12-02' AS gameDate, 2 AS stat1, 8 AS stat2 UNION ALL
  SELECT '2022-12-02' AS gameDate, 3 AS stat1, 8 AS stat2 UNION ALL
  SELECT '2022-12-03' AS gameDate, 1 AS stat1, 6 AS stat2 UNION ALL
  SELECT '2022-12-03' AS gameDate, 2 AS stat1, 6 AS stat2 UNION ALL
  SELECT '2022-12-03' AS gameDate, 3 AS stat1, 8 AS stat2 UNION ALL
  SELECT '2022-12-03' AS gameDate, 4 AS stat1, 9 AS stat2 UNION ALL
  SELECT '2022-12-03' AS gameDate, 4 AS stat1, 5 AS stat2 UNION ALL
  SELECT '2022-12-04' AS gameDate, 2 AS stat1, 9 AS stat2 UNION ALL
  SELECT '2022-12-04' AS gameDate, 1 AS stat1, 7 AS stat2 UNION ALL
  SELECT '2022-12-04' AS gameDate, 2 AS stat1, 7 AS stat2 UNION ALL
  SELECT '2022-12-04' AS gameDate, 1 AS stat1, 5 AS stat2 UNION ALL
  SELECT '2022-12-04' AS gameDate, 4 AS stat1, 9 AS stat2 UNION ALL
  SELECT '2022-12-05' AS gameDate, 3 AS stat1, 8 AS stat2 UNION ALL
  SELECT '2022-12-05' AS gameDate, 3 AS stat1, 6 AS stat2 UNION ALL
  SELECT '2022-12-05' AS gameDate, 4 AS stat1, 6 AS stat2 UNION ALL
  SELECT '2022-12-06' AS gameDate, 1 AS stat1, 5 AS stat2 UNION ALL
  SELECT '2022-12-06' AS gameDate, 3 AS stat1, 7 AS stat2
),
unique_dates AS (
  SELECT DISTINCT gameDate AS rowDate FROM t1
)
SELECT
  ud.rowDate,
  COUNT(t1.gameDate) AS ct,
  SUM(t1.stat1) AS sumStat1,
  SUM(t1.stat2) AS sumStat2
FROM unique_dates ud
LEFT JOIN t1 ON t1.gameDate <= ud.rowDate
GROUP BY ud.rowDate
ORDER BY ud.rowDate ASC;

方法2：窗口函数（高效实现） #

先按日期分组计算每日的统计值，再用累计窗口函数计算到当前日期的总和，避免了交叉连接的笛卡尔积，性能更优，适合大数据量场景：

WITH t1 AS (
  SELECT '2022-12-01' AS gameDate, 3 AS stat1, 7 AS stat2 UNION ALL
  SELECT '2022-12-01' AS gameDate, 2 AS stat1, 5 AS stat2 UNION ALL
  SELECT '2022-12-01' AS gameDate, 5 AS stat1, 6 AS stat2 UNION ALL
  SELECT '2022-12-02' AS gameDate, 3 AS stat1, 8 AS stat2 UNION ALL
  SELECT '2022-12-02' AS gameDate, 4 AS stat1, 7 AS stat2 UNION ALL
  SELECT '2022-12-02' AS gameDate, 2 AS stat1, 8 AS stat2 UNION ALL
  SELECT '2022-12-02' AS gameDate, 3 AS stat1, 8 AS stat2 UNION ALL
  SELECT '2022-12-03' AS gameDate, 1 AS stat1, 6 AS stat2 UNION ALL
  SELECT '2022-12-03' AS gameDate, 2 AS stat1, 6 AS stat2 UNION ALL
  SELECT '2022-12-03' AS gameDate, 3 AS stat1, 8 AS stat2 UNION ALL
  SELECT '2022-12-03' AS gameDate, 4 AS stat1, 9 AS stat2 UNION ALL
  SELECT '2022-12-03' AS gameDate, 4 AS stat1, 5 AS stat2 UNION ALL
  SELECT '2022-12-04' AS gameDate, 2 AS stat1, 9 AS stat2 UNION ALL
  SELECT '2022-12-04' AS gameDate, 1 AS stat1, 7 AS stat2 UNION ALL
  SELECT '2022-12-04' AS gameDate, 2 AS stat1, 7 AS stat2 UNION ALL
  SELECT '2022-12-04' AS gameDate, 1 AS stat1, 5 AS stat2 UNION ALL
  SELECT '2022-12-04' AS gameDate, 4 AS stat1, 9 AS stat2 UNION ALL
  SELECT '2022-12-05' AS gameDate, 3 AS stat1, 8 AS stat2 UNION ALL
  SELECT '2022-12-05' AS gameDate, 3 AS stat1, 6 AS stat2 UNION ALL
  SELECT '2022-12-05' AS gameDate, 4 AS stat1, 6 AS stat2 UNION ALL
  SELECT '2022-12-06' AS gameDate, 1 AS stat1, 5 AS stat2 UNION ALL
  SELECT '2022-12-06' AS gameDate, 3 AS stat1, 7 AS stat2
),
daily_stats AS (
  SELECT
    gameDate,
    COUNT(*) AS daily_ct,
    SUM(stat1) AS daily_sumStat1,
    SUM(stat2) AS daily_sumStat2
  FROM t1
  GROUP BY gameDate
)
SELECT
  gameDate AS rowDate,
  SUM(daily_ct) OVER (ORDER BY gameDate) AS ct,
  SUM(daily_sumStat1) OVER (ORDER BY gameDate) AS sumStat1,
  SUM(daily_sumStat2) OVER (ORDER BY gameDate) AS sumStat2
FROM daily_stats
ORDER BY gameDate ASC;

方案对比 #

• 方法1：逻辑简单直观，适合小数据集，新增日期会自动纳入统计，无需修改代码。
• 方法2：性能更高效，尤其当t1数据量较大时，避免了交叉连接带来的计算开销，是更推荐的生产环境方案。

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