BigQuery中使用LAG函数计算游戏Sum差值的缺失日期问题

在BigQuery Superset中，通过SQL计算各游戏一段时间内Sum值的差值时，原SQL因语法问题导致日期缺失时计算结果错误，以下是问题分析与修正方案：

原SQL语句 #

select Date , SUM(Sum) ,
           SUM(Sum) - 
           lag(SUM(Sum)) over(partition by Game order by min(Date) ASC)
   from my_table

错误查询结果 #

Game    Date        Sum     Prev_sum
First   2022-08-15  178.68  
        2022-08-16  345.09  166.41
        2022-08-18  570.53  991.89   --wrong input ,missing day
        2022-08-19  173.69  -396.84
        2022-08-20  532.35  358.66
        2022-08-21  248.35  -284
Second  2022-08-17  518.59  600.53   --wrong input ,missing day
        2022-08-20  182.91  148.3
Third   2022-08-15  527.35  
        2022-08-17  359.93  426.53
        2022-08-20  539.05  564.57
        2022-08-21  543.74  4.69

期望正确结果 #

Game    Date        Sum     Prev_sum
First   2022-08-15  178.68  
        2022-08-16  345.09  166.41
        2022-08-18  570.53  225   
        2022-08-19  173.69  -396.84
        2022-08-20  532.35  358.66
        2022-08-21  248.35  -284
Second  2022-08-17  518.59  
        2022-08-20  182.91  -336

修正后的SQL语句 #

SELECT 
  Game,
  Date,
  SUM(Sum) AS Sum,
  SUM(Sum) - LAG(SUM(Sum)) OVER(PARTITION BY Game ORDER BY Date ASC) AS Prev_sum
FROM my_table
GROUP BY Game, Date
ORDER BY Game, Date

修正说明 #

1. 补充GROUP BY Game, Date：按游戏和日期维度聚合数据，确保每行对应单个游戏的单日Sum总和
2. 调整窗口函数排序字段：将order by min(Date)改为order by Date，直接按日期排序，确保在每个游戏分组内，LAG函数能正确获取上一行的Sum值，无论日期是否连续
3. 显式指定Game字段：原SQL未查询Game字段，导致结果中Game仅显示第一行，修正后每行都携带对应游戏标识

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