使用ROW_NUMBER()按ID分组实现别名变更历史追踪咨询

你当前使用的ROW_NUMBER()取分区倒序第一条的逻辑，本质是取每个ID的最新快照，天然无法保留历史变更链路，要实现全历史追踪+主键匹配的需求，可以按以下方式实现：

核心实现逻辑 #

给每个ID下的每一条别名变更记录标记生效时间区间，不用破坏ID作为主键的匹配能力，同时可追溯任意时间点的别名映射关系，核心SQL如下：

WITH alias_version AS (
  SELECT
    ID,
    Email AS alias_name,
    EventDate AS effective_start,
    -- 同ID下下一次变更的时间即为当前别名的失效时间，无后续变更则标记为永久有效
    LEAD(EventDate, 1, '9999-12-31') OVER (
      PARTITION BY ID 
      ORDER BY EventDate ASC
    ) AS effective_end,
    ROW_NUMBER() OVER (
      PARTITION BY ID 
      ORDER BY EventDate ASC
    ) AS version
  FROM your_source_table
  -- 提前过滤空值脏数据，减少计算量
  WHERE ID IS NOT NULL AND Email IS NOT NULL AND EventDate IS NOT NULL
)
SELECT * FROM alias_version

查询返回的每一行对应该ID的一次别名生效记录，可直接看到每个别名的生效起止时间、版本序号。

不同场景的取数方式 #

• 日常ID匹配需要取最新别名时，直接加过滤条件WHERE effective_end = '9999-12-31'即可，返回结果和你原有SQL完全一致，执行效率更高
• 需要回溯某一特定时间点的ID-别名映射关系时，加过滤条件WHERE {target_query_time} >= effective_start AND {target_query_time} < effective_end
• 需要查看单个ID的全量变更历史时，按ID过滤后按version字段升序排列，即可得到完整的变更时间线

大型数据集优化建议 #

• 给源表创建(ID, EventDate)联合索引，窗口函数计算时可直接走索引完成排序，避免全表排序带来的性能损耗
• 若数据是定期增量写入，不要每次全量重算全量历史，仅计算上次更新节点后的新增变更记录，合并到已有的别名历史表即可
• 可单独维护一张仅存最新ID-别名映射的快照表，日常数据匹配直接查询快照表，需要追溯历史时再关联历史表，兼顾查询效率和追溯能力

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