BigQuery查询：计算优惠券状态与费率变更间隔天数

先直接上可运行的SQL，再拆解逻辑：

WITH ranked_records AS (
  -- 按账户分区，时间戳降序排序，获取上一条记录的状态和费率
  SELECT
    account_ID,
    timestamp,
    active,
    rate,
    LAG(active) OVER (PARTITION BY account_ID ORDER BY timestamp DESC) AS prev_active,
    LAG(rate) OVER (PARTITION BY account_ID ORDER BY timestamp DESC) AS prev_rate
  FROM
    `你的项目.你的数据集.你的表名`
),
change_markers AS (
  -- 标记出所有发生状态或费率变更的记录
  SELECT
    account_ID,
    timestamp,
    -- 标记是否为状态变更事件
    active != prev_active AS is_status_change,
    -- 标记是否为费率变更事件
    rate != prev_rate AS is_rate_change
  FROM
    ranked_records
  -- 排除最旧的记录（没有上一条数据可对比）
  WHERE prev_active IS NOT NULL
),
latest_change_times AS (
  -- 提取每个账户最近一次状态/费率变更的时间戳
  SELECT
    account_ID,
    MAX(IF(is_status_change, timestamp, NULL)) AS last_status_change_ts,
    MAX(IF(is_rate_change, timestamp, NULL)) AS last_rate_change_ts
  FROM
    change_markers
  GROUP BY
    account_ID
)
-- 计算变更至今的天数（这里用当前时间，若要对比最新记录时间可替换为MAX(timestamp)）
SELECT
  account_ID,
  DATE_DIFF(CURRENT_TIMESTAMP(), TIMESTAMP_MILLIS(last_status_change_ts), DAY) AS days_since_status_change,
  DATE_DIFF(CURRENT_TIMESTAMP(), TIMESTAMP_MILLIS(last_rate_change_ts), DAY) AS days_since_rate_change
FROM
  latest_change_times;

逻辑拆解 #

• ranked_records CTE：按账户分组，时间从新到旧排序，用LAG()函数拉取上一条记录的active和rate，实现当前记录与上一条的变更对比。
• change_markers CTE：通过对比当前值与上一条值，标记出真正发生状态或费率变更的记录，同时排除最旧的无前置记录的条目。
• latest_change_times CTE：按账户分组，提取最近一次状态变更和费率变更的时间戳。
• 最终查询：用DATE_DIFF()计算从变更时间到当前时间的天数，注意用TIMESTAMP_MILLIS()把毫秒时间戳转成BigQuery支持的TIMESTAMP类型。

适配示例结果的说明 #

如果你的“至今”是指表中最新记录的时间（而非系统当前时间），可以修改逻辑：

1. 在ranked_records中添加MAX(timestamp) OVER (PARTITION BY account_ID) AS latest_ts
2. 在最终查询中把CURRENT_TIMESTAMP()替换为TIMESTAMP_MILLIS(latest_ts)

这样就能计算从最近一次变更到表中最新记录的天数。

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