BigQuery SQL查询需求：计算可变时间段内每日数值变化及当日最新值

没问题，我来帮你搞定这个需求！咱们一步步拆解思路：

你的核心需求是用当日最新值减去昨日最新值得到变化量，同时支持按天/周这类可变时间段筛选数据。那咱们的实现分两步走：

1. 先从原始表中提取每一天的最新value值（毕竟每天可能有多条数据）
2. 基于每日最新值，计算和前一天的差值

第一步：获取每日最新值 #

咱们用窗口函数ROW_NUMBER()给每天的数据按时间戳ts降序排序，取排名第一的记录就是当日最新值：

WITH daily_latest AS (
  SELECT
    date,
    value,
    ts,
    -- 按日期分组，给每条数据按时间戳倒序排名
    ROW_NUMBER() OVER (PARTITION BY date ORDER BY ts DESC) AS rn
  FROM
    `你的项目名.数据集名.表名`
  -- 这里可以加可变时间段过滤，比如按日期范围
  -- WHERE date BETWEEN '2024-01-01' AND '2024-01-31'
  -- 或者按周过滤：EXTRACT(WEEK FROM PARSE_DATE('%Y-%m-%d', date)) = 5
)
SELECT
  date,
  value AS daily_latest_value,
  ts AS latest_ts
FROM daily_latest
WHERE rn = 1 -- 只保留当日最新的那条记录
ORDER BY date ASC

第二步：计算每日变化量 #

基于上面的daily_latest结果，用LAG()函数获取前一天的最新值，再计算差值：

WITH daily_latest AS (
  SELECT
    date,
    value,
    ROW_NUMBER() OVER (PARTITION BY date ORDER BY ts DESC) AS rn
  FROM
    `你的项目名.数据集名.表名`
  -- 可根据需求添加时间段过滤
  -- WHERE date BETWEEN '2024-01-01' AND '2024-01-31'
),
daily_values AS (
  SELECT
    date,
    value AS daily_latest_value
  FROM daily_latest
  WHERE rn = 1
)
SELECT
  date,
  daily_latest_value,
  -- 用LAG取前一天的最新值，无前置数据时显示NULL
  daily_latest_value - LAG(daily_latest_value) OVER (ORDER BY date ASC) AS daily_change
FROM daily_values
ORDER BY date ASC

额外优化提示 #

• 按周筛选时，把WHERE条件改成周提取逻辑即可：

  WHERE EXTRACT(WEEK FROM PARSE_DATE('%Y-%m-%d', date)) = 202405 -- 2024年第5周
  

• 第一行的daily_change会是NULL，可以用IFNULL()替换成0或其他默认值：

  IFNULL(daily_latest_value - LAG(daily_latest_value) OVER (ORDER BY date ASC), 0) AS daily_change
  

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