BigQuery中SQL计算相邻日期number_clients字段差值

核心用BigQuery原生支持的LAG()窗口函数即可实现，不需要复杂表关联，查询性能更好。

实现逻辑 #

• 按日期升序排序，取每一行上一条记录的客户数值
• 当前行客户数减去上一行客户数得到相邻日期差值
• 第一条记录没有上一行数据，差值固定记为0

基础版SQL代码 #

替换代码里的表地址为你自己的实际项目、数据集、表名即可直接运行：

SELECT
  date,
  number_clients,
  COALESCE(
    number_clients - LAG(number_clients) OVER (ORDER BY date),
    0
  ) AS difference
FROM `你的项目名.你的数据集名.你的业务表名`
ORDER BY date

代码说明：

• LAG(number_clients) OVER (ORDER BY date)：按日期升序排序后，提取当前行上一条记录的客户数
• COALESCE(..., 0)：首行记录没有前置数据，LAG函数会返回NULL，用该函数将NULL替换为0，符合首条差值记0的要求

特殊场景适配 #

• 如果你的date字段是dd-mm-yyyy格式的字符串而非标准DATE类型，直接按字符串排序会出现顺序错乱，需要先转成日期类型再排序，代码调整为：

SELECT
  date,
  number_clients,
  COALESCE(
    number_clients - LAG(number_clients) OVER (ORDER BY PARSE_DATE('%d-%m-%Y', date)),
    0
  ) AS difference
FROM `你的项目名.你的数据集名.你的业务表名`
ORDER BY PARSE_DATE('%d-%m-%Y', date)

• 如果你需要按维度拆分计算（比如不同门店、不同区域分别统计各自的相邻日期差值），只需要在窗口函数中加PARTITION BY 维度字段即可，例如按region字段分区统计：

LAG(number_clients) OVER (PARTITION BY region ORDER BY date)

运行后输出的字段顺序、计算逻辑和你给出的示例格式完全一致。

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