在BigQuery中用窗口函数计算移动窗口内分组最新条目最小收费日期

需求说明 #

需要在事件表中计算任意时间点的下一次收费事件发生时间：当某个分组新增收费日期时，回溯之前的行时忽略该分组此前的所有收费日期，仅考虑每个分组截至当前时间的最新条目，再从中获取整体最小的收费日期。

数据样本与预期输出 #

解决方案（BigQuery窗口函数实现） #

通过嵌套窗口函数结合IGNORE NULLS参数，可以简洁实现需求，SQL代码如下：

WITH events AS (
  -- 模拟输入数据
  SELECT * FROM UNNEST([
    STRUCT('1/1/2024' AS created_at, 'a' AS `group`, '2/21/2024' AS next_charge_date),
    ('1/2/2024', 'b', '2/22/2024'),
    ('1/3/2024', 'a', '2/23/2024'),
    ('1/4/2024', 'b', '2/22/2024'),
    ('1/5/2024', 'c', '2/20/2024'),
    ('1/6/2024', 'a', '2/24/2024'),
    ('1/7/2024', 'b', '2/23/2024')
  ])
),
formatted_events AS (
  -- 转换为标准日期类型，确保时间比较准确
  SELECT
    PARSE_DATE('%m/%d/%Y', created_at) AS created_at,
    `group`,
    PARSE_DATE('%m/%d/%Y', next_charge_date) AS next_charge_date
  FROM events
),
latest_per_group AS (
  -- 标记每个分组截至当前行的最新收费日期，非最新记录置空
  SELECT
    *,
    CASE
      WHEN created_at = MAX(created_at) OVER (
        PARTITION BY `group` 
        ORDER BY created_at 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
      ) THEN next_charge_date
      ELSE NULL
    END AS latest_charge_date
  FROM formatted_events
)
-- 计算移动窗口内所有分组最新收费日期的最小值
SELECT
  FORMAT_DATE('%m/%d/%Y', created_at) AS created_at,
  `group`,
  FORMAT_DATE('%m/%d/%Y', next_charge_date) AS next_charge_date,
  FORMAT_DATE('%m/%d/%Y', 
    MIN(latest_charge_date) OVER (
      ORDER BY created_at 
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 
      IGNORE NULLS
    )
  ) AS `goal - next_charge_date across all prior groups`
FROM latest_per_group
ORDER BY created_at;

逻辑说明 #

1. 日期格式化：将字符串日期转换为BigQuery标准日期类型，避免字符串排序的误差。
2. 标记分组最新记录：通过窗口函数MAX(created_at)获取每个分组截至当前行的最新创建时间，仅保留该记录的收费日期，其余记录置空。
3. 计算移动窗口最小值：使用带IGNORE NULLS的MIN()窗口函数，在从起始行到当前行的移动窗口内，忽略空值（即非最新的分组记录），直接取所有分组最新收费日期的最小值，得到目标结果。

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