BigQuery SQL窗口分区取前置最新last_modified_date结果异常排查

问题说明 #

需要逐行计算custom_last_modified_date字段，取值规则为：从last_modified_date列中选取不晚于当前行date字段值的最新日期。
原有实现仅在同id、同date的分区内查找符合条件的日期，未覆盖同id下所有早于当前date的last_modified_date记录（包括存在于更大date行中的符合日期大小规则的last_modified_date），导致结果不符合预期。

样例源数据 #

id           date           last_modified_date
A           02/28/22          2017-02-28 22:44
A           03/05/22          2017-02-28 05:14
A           03/05/22          2017-02-28 07:49
A           03/22/22          2017-02-28 06:09
A           03/22/22          2022-03-01 06:49
B           03/25/22          2022-03-20 07:49
B           03/25/22          2022-04-01 09:24 

原有问题代码 #

SELECT 
id, 
date,
MAX(
        IF(
            date(
                string(
                    TIMESTAMP(
                        DATETIME(
                            parse_datetime('%Y-%m-%d %H:%M', last_modified_date)
                        )
                    )
                )
            ) <= date,
            date(
                string(
                    TIMESTAMP(
                        DATETIME(
                            parse_datetime('%Y-%m-%d %H:%M', last_modified_date)
                        )
                    )
                )
            ),
            null
        )
    ) OVER (PARTITION BY date, id) as custom_last_modified_date
FROM `my_table`

当前错误输出 #

id           date        custom_last_modified_date
A           02/28/22          02/28/17
A           03/05/22          02/28/17
A           03/05/22          02/28/17
A           03/22/22          03/01/22
A           03/22/22          03/01/22
B           03/25/22          03/20/22
B           03/25/22          03/20/22

期望输出结果 #

id           date        custom_last_modified_date
A           02/28/22          02/28/22
A           03/05/22          03/01/22
A           03/05/22          03/01/22
A           03/22/22          03/01/22
A           03/22/22          03/01/22
B           03/25/22          03/20/22
B           03/25/22          03/20/22

问题根因 #

• 窗口分区逻辑错误：PARTITION BY date, id将相同date的行划分为独立窗口，无法跨date取值
• 日期处理存在风险：原date字段为MM/DD/YY格式字符串，直接与日期类型比较存在隐式转换错误风险；last_modified_date的解析逻辑存在多层冗余转换，执行效率低
• 常规窗口排序逻辑无法覆盖全量数据：如果使用PARTITION BY id ORDER BY date的窗口，默认仅能读取排序在当前行之前的记录，无法获取存在于更大date行中、但日期值本身小于当前行date的last_modified_date，会漏算符合规则的值

修正后代码 #

实现逻辑：先解析格式化所有日期字段，按id聚合收集全量去重的修改日期，再逐行匹配小于等于当前行日期的最大值。

WITH parsed_data AS (
  SELECT
    id,
    date,
    -- 解析MM/DD/YY格式的行日期为标准DATE类型，避免隐式转换错误
    PARSE_DATE('%m/%d/%y', date) AS row_date,
    -- 直接解析last_modified_date取日期部分，去掉冗余转换
    DATE(PARSE_DATETIME('%Y-%m-%d %H:%M', last_modified_date)) AS lm_date
  FROM `my_table`
),
id_all_lm_dates AS (
  SELECT
    id,
    -- 聚合同id下所有去重的修改日期，忽略空值
    ARRAY_AGG(DISTINCT lm_date IGNORE NULLS) AS lm_date_list
  FROM parsed_data
  GROUP BY id
)
SELECT
  p.id,
  p.date,
  -- 从当前id的所有修改日期中，筛选不晚于当前行日期的最大值
  (
    SELECT MAX(lm_date)
    FROM UNNEST(i.lm_date_list) AS lm_date
    WHERE lm_date <= p.row_date
  ) AS custom_last_modified_date
FROM parsed_data p
JOIN id_all_lm_dates i USING(id)

注：样例中id=A、date=02/28/22的期望输出为02/28/22，属于源数据笔误，按现有源数据该值应为2017-02-28，若源数据中该行last_modified_date年份为2022则会输出期望结果，逻辑本身符合取值规则。

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