BIGQUERY连续数据处理：缺失行补0及按维度分组累计求和问题咨询

核心逻辑说明 #

• 补全缺失分类：先生成日期、code2、a/b/c/d分类的全量维度组合，左连原始数据后将缺失的value赋值为0
• 分组累计求和：在窗口函数中增加PARTITION BY code2即可实现按code2分区计算累计值，而非全局累计

完整可运行SQL代码 #

WITH all_dimensions AS (
  -- 生成全量维度组合：每个日期+每个code2 对应a/b/c/d全部4个分类
  SELECT DISTINCT tanggal, code2, c.code
  FROM `market-maker-328312.MarketMakerV2.MM16`,
  UNNEST(['a','b','c','d']) AS c
),
filled_data AS (
  -- 左连原始表，缺失的data字段补0
  SELECT 
    ad.tanggal, 
    ad.code, 
    ad.code2, 
    IFNULL(mm.data, 0) AS data
  FROM all_dimensions ad
  LEFT JOIN `market-maker-328312.MarketMakerV2.MM16` mm
  ON ad.tanggal = mm.tanggal 
  AND ad.code = mm.code 
  AND ad.code2 = mm.code2
)
-- 按code2分组计算累计求和
SELECT 
  tanggal,
  code,
  code2,
  data,
  SUM(data) OVER (
    PARTITION BY code2 
    ORDER BY tanggal, code
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS ContinousData
FROM filled_data

注意事项 #

• 如果你的分类字段不是code，可自行替换为实际存储分类的字段
• 如果累计求和的排序规则和示例不同，可自行调整ORDER BY后的字段

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