BigQuery中按ID分区计算指定日期范围内最大编辑日期并生成自定义键的SQL实现问题

问题分析 #

你的原SQL逻辑存在一个关键缺陷：它仅对当前行满足last_edited_date <= date的记录进行分组聚合，而没有考虑同一id下所有行中last_edited_date不大于当前custom_key对应date的情况。

比如对于2022-06-30_74c这个custom_key，原SQL只看了原始表中date=2022-06-30且last_edited_date<=2022-06-30的记录（即last_edited_date=2018-05-06的那行），但忽略了同一id=74c下date=2022-06-18且last_edited_date=2021-05-06的记录——这个值同样满足<=2022-06-30，且是更大的有效值。

解决方案 #

我们需要先提取所有唯一的date+id组合（因为原始表存在重复组合），然后对每个组合，跨同一id的所有记录筛选出符合条件的last_edited_date并取最大值。可以通过关联子查询实现：

SELECT 
  CONCAT(t.date, '_', t.id) AS custom_key,
  (
    SELECT MAX(last_edited_date) 
    FROM `my_table` t2 
    WHERE t2.id = t.id 
      AND t2.last_edited_date <= t.date
  ) AS custom_date
FROM (
  -- 先获取所有唯一的date和id组合，避免重复计算
  SELECT DISTINCT date, id 
  FROM `my_table`
) t
ORDER BY custom_key;

验证结果 #

执行上述SQL后，你将得到期望的输出：

另一种高效实现（窗口函数版本） #

如果你的SQL引擎支持窗口函数，也可以用以下写法，性能可能更优：

WITH unique_combinations AS (
  SELECT DISTINCT date, id FROM `my_table`
),
id_last_edited_dates AS (
  SELECT 
    id,
    last_edited_date
  FROM `my_table`
  GROUP BY id, last_edited_date -- 去重同一id的重复last_edited_date
)
SELECT 
  CONCAT(uc.date, '_', uc.id) AS custom_key,
  MAX(ild.last_edited_date) AS custom_date
FROM unique_combinations uc
JOIN id_last_edited_dates ild 
  ON uc.id = ild.id 
  AND ild.last_edited_date <= uc.date
GROUP BY uc.date, uc.id
ORDER BY custom_key;

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