如何在BigQuery中计算当前行与前一日期同字段的差值？

你可以根据自己使用的工具选择对应实现方式：

1 SQL实现（支持窗口函数的关系型数据库通用，如MySQL8.0+、PostgreSQL、Hive等） #

使用窗口函数LAG提取按日期排序后的上一行a字段值，再做差值计算即可：

SELECT 
  date,
  a,
  -- 第一行没有前序值，b默认返回NULL，如果需要第一行b为0可改为 a - COALESCE(LAG(a,1) OVER(ORDER BY date), 0) AS b
  a - LAG(a, 1) OVER(ORDER BY date) AS b 
FROM 你的表名;

注意：如果你的表存在多个分组维度（比如不同用户、不同品类分别计算差值），需要在OVER子句里加上PARTITION BY 分组字段名，比如按用户分组就写OVER(PARTITION BY user_id ORDER BY date)

2 Python Pandas实现 #

如果是处理本地DataFrame数据，使用shift方法偏移a字段后计算差值即可：

import pandas as pd

# 第一步：先将date字段转为日期格式，再按日期升序排序，避免差值计算错误
df['date'] = pd.to_datetime(df['date'])
df = df.sort_values('date', ascending=True).reset_index(drop=True)

# 第二步：计算b字段，第一行默认返回NaN
df['b'] = df['a'] - df['a'].shift(1)

# 可选：将第一行的NaN替换为0
# df['b'] = df['b'].fillna(0)

注意事项 #

如果你的日期序列存在断层，且需求要求必须取自然日的前一天而非数据中存在的最近前序日期的a值，需要先补全完整的日期序列，再执行上述差值计算逻辑。

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