BigQuery中按stay_active重置的累计求和逻辑异常求助

我在使用BigQuery时，需要按purchaseguestid维度计算session_count的累计值，要求每当stay_active状态发生变更时，累计值重新从0开始计算。但当前代码未达到预期效果——从截图可见，null值之后的累计值显示为3，而预期应该是1。

原代码如下：

SELECT 
  purchaseguestid,
  stay_active,
  mo,
  session_count,
  sum(session_count) over (partition by purchaseguestid, stay_active order by mo, stay_active) as RT_session_count_status,
from stay_active_status
where purchaseguestid = "00493848-e6e1-40ea-ac38-08a39e52d654"
order by purchaseguestid,mo

原代码问题 #

你当前的partition by purchaseguestid, stay_active逻辑存在缺陷：它会把所有相同stay_active值的行归到同一个分区，哪怕这些行在时间序列上并不连续（比如中间插入了其他状态或null的行）。举个例子，如果某个purchaseguestid先有两行stay_active=true，之后出现null，再出现一行stay_active=true，原代码会把这三行stay_active=true的行放在同一个分区累计，导致null之后的累计值直接叠加之前的数值，出现你看到的3而非预期的1。

修正思路 #

需要先给连续的相同stay_active状态段分配唯一的分组ID，再基于purchaseguestid和这个分组ID做分区累计。可以用LAG()函数对比当前行和上一行的stay_active值，状态变化时生成新的分组标识，再通过累计求和得到分组ID。

修正后的代码 #

WITH status_groups AS (
  SELECT 
    purchaseguestid,
    stay_active,
    mo,
    session_count,
    -- 生成连续状态分组ID：当当前stay_active与上一行不同时，加1，否则继承之前的分组ID
    SUM(CASE WHEN stay_active != LAG(stay_active) OVER (PARTITION BY purchaseguestid ORDER BY mo) THEN 1 ELSE 0 END) 
      OVER (PARTITION BY purchaseguestid ORDER BY mo) AS status_group_id
  FROM stay_active_status
  WHERE purchaseguestid = "00493848-e6e1-40ea-ac38-08a39e52d654"
)
SELECT 
  purchaseguestid,
  stay_active,
  mo,
  session_count,
  SUM(session_count) OVER (PARTITION BY purchaseguestid, status_group_id ORDER BY mo) AS RT_session_count_status
FROM status_groups
ORDER BY purchaseguestid, mo;

代码说明 #

1. 首先在CTEstatus_groups中，用LAG(stay_active)获取上一行的状态，对比当前行状态：如果不同，标记为1，否则0。对这个标记值做累计求和，得到每个连续状态段的唯一status_group_id。
2. 主查询中，基于purchaseguestid和status_group_id分区，按mo排序累计session_count，这样每次状态变更时，status_group_id会变化，累计就会重新开始计数。

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