Google BigQuery嵌套窗口函数实现高效唯一ID统计需求

需求 #

在Google BigQuery SQL中，统计每个时间点及之前所有时间戳里，最后一次value大于0的唯一ID数量；要求不使用GROUP BY，保留全表输出，且适配超10亿行的大规模数据表，保证查询效率。

示例数据表 #

预期结果 #

结果说明 #

每个时间点统计截至该时间点，最后一次value>0的唯一ID集合大小，如2021-01-03对应{B,C}，数量为2。

解决方案 #

此前尝试嵌套窗口函数方案，添加next_timestamp字段后编写查询，但BigQuery不支持VALUE OF语法，结果不符合预期。基于@Mikhail Berlyant的建议，采用以下高效查询，无需GROUP BY，适配超大规模数据表：

select * except(new_value), 
  sum(new_value) over win as count_val_gt_0
from (
  select *, 
    if(not lag(value) over by_id is null,
      if(lag(value) over by_id > 0,
        if(value > 0, 0, -1),
        if(value > 0, 1, 0)), 
      if(value > 0,1,0)
    ) new_value
  from final_table
  window by_id as (partition by id order by timestamp)
)
window win as (order by timestamp range between unbounded preceding and current row) 

代码逻辑说明 #

• 内层查询通过lag()函数按ID分组、时间戳排序，对比当前行与上一行的value值生成new_value标记：
  • 首次出现的ID，若value>0则标记为1，否则为0
  • 上一行value>0时：当前行value>0标记为0（ID仍在有效集合中，无变化）；当前行value≤0标记为-1（ID移出有效集合）
  • 上一行value≤0时：当前行value>0标记为1（ID加入有效集合）；当前行value≤0标记为0（ID仍不在有效集合中）
• 外层查询通过全局窗口（按时间戳排序）累加new_value，得到截至当前时间点的有效唯一ID数量。

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