BigQuery窗口函数查询不晚于pageview_date的最大edited_date

问题背景 #

需求为遍历edited_date列的所有取值，与pageview_date列的对应行值做比对，提取不晚于当前行pageview_date值的edited_date最大值。
原实现代码如下：

with sample as (
  select 'a' as id, DATE('2022-02-27') as pageview_date, DATE('2022-01-28') as edited_date
  UNION ALL
  select 'a' as id, DATE('2022-02-27') as pageview_date, DATE('2022-03-01') as edited_date
  UNION ALL
  select 'a' as id, DATE('2022-03-01') as pageview_date, DATE('2022-03-28') as edited_date
  UNION ALL
  select 'a' as id, DATE('2022-03-01') as pageview_date, DATE('2022-01-28') as edited_date
  UNION ALL
  select 'a' as id, DATE('2022-03-05') as pageview_date, DATE('2017-02-28') as edited_date
)
SELECT
  id,
  pageview_date,
  MAX(IF(edited_date <= pageview_date, edited_date, null)) OVER (ORDER BY pageview_date RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as new_edited_date
FROM sample

原写法不生效的核心原因有两点：

• 窗口范围设置为全表首尾（UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING），所有行都会返回同一个全局计算结果，无法按当前行的pageview_date做边界限制
• 条件判断仅对比当前行自身的edited_date和pageview_date，没有跨所有行统计符合日期大小要求的edited_date值

预期输出结果为：

id          pageview_date         new_edited_date
a           2022-02-27             2022-01-28
a           2022-02-27             2022-01-28
a           2022-03-01             2022-03-01
a           2022-03-01             2022-03-01
a           2022-03-05             2022-03-01

正确实现（BigQuery 原生写法） #

利用BigQuery的数组能力，先按id聚合所有出现过的编辑日期，再逐行筛选小于等于当前浏览日期的最大值即可，写法简洁且执行效率高，天然支持多id分区计算：

WITH sample AS (
  select 'a' as id, DATE('2022-02-27') as pageview_date, DATE('2022-01-28') as edited_date
  UNION ALL
  select 'a' as id, DATE('2022-02-27') as pageview_date, DATE('2022-03-01') as edited_date
  UNION ALL
  select 'a' as id, DATE('2022-03-01') as pageview_date, DATE('2022-03-28') as edited_date
  UNION ALL
  select 'a' as id, DATE('2022-03-01') as pageview_date, DATE('2022-01-28') as edited_date
  UNION ALL
  select 'a' as id, DATE('2022-03-05') as pageview_date, DATE('2017-02-28') as edited_date
),
agg_edited_dates AS (
  SELECT
    id,
    ARRAY_AGG(DISTINCT edited_date IGNORE NULLS) AS all_edited_dates
  FROM sample
  GROUP BY id
)
SELECT
  s.id,
  s.pageview_date,
  (
    SELECT MAX(ed)
    FROM UNNEST(a.all_edited_dates) AS ed
    WHERE ed <= s.pageview_date
  ) AS new_edited_date
FROM sample s
JOIN agg_edited_dates a USING(id)

执行后可完全匹配预期输出。

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