BigQuery SQL：按ID取不晚于pageview_date的最新edited_date

需求规则 #

• 源表包含id、pageview_date、edited_date三个字段，后两个为日期类型
• 逐行保留所有原始数据，对每一行的edited_date按如下逻辑重算：按id分组，取同组内所有edited_date中**不晚于该行pageview_date**的最大值作为最终输出值
• 若存在edited_date与pageview_date相等的情况，会自动被最大值逻辑命中，无需额外单独判断

样例参考 #

源数据 #

预期输出 #

实现SQL #

核心思路 #

• 先按id聚合，收集同ID下所有去重的edited_date生成数组，避免重复计算
• 逐行遍历原表数据，从对应ID的日期数组中筛选出不大于当前行pageview_date的最大值，作为最终的edited_date，该写法不会改变原表行数，同ID下存在不同pageview_date时也能正确逐行匹配

-- 将代码中your_table替换为实际业务表名即可
WITH id_edited_collect AS (
  SELECT
    id,
    ARRAY_AGG(DISTINCT edited_date IGNORE NULLS) AS all_edited_dates
  FROM your_table
  GROUP BY id
)
SELECT
  t.id,
  t.pageview_date,
  (
    SELECT MAX(ed_date)
    FROM UNNEST(c.all_edited_dates) AS ed_date
    WHERE ed_date <= t.pageview_date
  ) AS edited_date
FROM your_table t
LEFT JOIN id_edited_collect c
USING(id)

匹配逻辑验证 #

• ID=A：聚合后的日期数组为[2022-02-28, 2022-02-02]，所有行pageview_date为2022-03-01，筛选后最大值为2022-02-28，符合预期
• ID=B：聚合后的日期数组为[2022-01-01, 2022-01-31]，筛选后最大值为2022-01-31，符合预期
• ID=C：聚合后的日期数组为[2022-04-01, 2022-03-25, 2022-03-01]，仅2022-03-01满足不晚于pageview_date的条件，直接返回该值，符合预期

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