BigQuery中基于id与pageview_date分区获取不晚于pageview_date的最新edited_date的SQL实现问题

问题分析 #

你的现有SQL窗口函数用了PARTITION BY pageview_date, id分区，这意味着它只会在同一id且同一pageview_date的小范围内计算符合条件的edited_date最大值。但你的需求是要检索该id下所有pageview_date之前的可用edited_date（也就是所有edited_date <= 当前行pageview_date的记录，不管这些edited_date属于哪个pageview_date），所以这个分区范围太窄，导致无法拿到跨pageview_date的有效日期。

修正后的SQL代码 #

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,
    -- 按id分区，筛选出所有不晚于当前pageview_date的edited_date并取最大值
    MAX(CASE WHEN edited_date <= pageview_date THEN edited_date END) OVER (PARTITION BY id) AS new_edited_date
FROM sample
ORDER BY pageview_date;

代码说明 #

1. 调整窗口分区范围：把分区从PARTITION BY pageview_date, id改为PARTITION BY id，这样窗口会包含该id下的所有记录，确保我们能看到所有可用的edited_date。
2. 过滤+取最大值：用CASE WHEN过滤掉比当前pageview_date晚的edited_date，再通过MAX()函数取出这些有效日期中的最大值。
3. 排序输出：最后按pageview_date排序，结果完全匹配你期望的输出：

大数据量优化版本 #

如果你的数据量很大，担心重复计算相同的edited_date，可以先提取每个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 
),
id_unique_edits AS (
    SELECT DISTINCT id, edited_date
    FROM sample
)
SELECT 
    s.id,
    s.pageview_date,
    MAX(ied.edited_date) AS new_edited_date
FROM sample s
LEFT JOIN id_unique_edits ied
    ON s.id = ied.id
    AND ied.edited_date <= s.pageview_date
GROUP BY s.id, s.pageview_date, s.edited_date
ORDER BY s.pageview_date;

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