在BigQuery中将季度至今累计数据转换为日度数据

原始表数据 #

期望输出 #

要实现从季度累计数据计算日度增量，可借助BigQuery的LAG()窗口函数，获取同一id下前一日的累计值后做差值计算，对于每个id的最早日期（无前置数据）直接保留原数值即可。

创建视图的SQL语句如下：

CREATE OR REPLACE VIEW `你的项目名.你的数据集名.daily_increment_view` AS
SELECT
  id,
  -- 当日累计值减前一日累计值，无前一日则取当前值
  value - COALESCE(LAG(value) OVER (PARTITION BY id ORDER BY date), 0) AS value,
  date
FROM
  `你的项目名.你的数据集名.原始表名`
ORDER BY
  date DESC, id;

代码说明 #

• PARTITION BY id：按id分组，确保仅计算同一id内的日度增量
• ORDER BY date：在每个id组内按日期排序，保证LAG()取到的是前一天的累计数据
• COALESCE(..., 0)：处理每个id的最早日期（无前一日数据），此时LAG(value)返回NULL，用0替换后差值等于原value，匹配期望结果
• CREATE OR REPLACE VIEW：创建或覆盖指定视图，确保逻辑更新后视图同步生效

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