SQL实现按客户端分组标记表中最新更新记录的方法

• 业务表存储客户端多版本数值，固定字段为StartDate、ClientID、Value、UpdatedDate
• 单ClientID对应多条不同UpdatedDate的版本记录
• 需新增outdated字段做版本标识：按ClientID分组，组内UpdatedDate最新的记录赋值1，其余历史记录赋值0

支持窗口函数的引擎（MySQL8.0+、PostgreSQL、Hive、Spark SQL等） #

直接用ROW_NUMBER()窗口函数分组排序打标，性能最优：

SELECT
    StartDate,
    ClientID,
    Value,
    UpdatedDate,
    CASE
        WHEN ROW_NUMBER() OVER (PARTITION BY ClientID ORDER BY UpdatedDate DESC) = 1 THEN 1
        ELSE 0
    END AS outdated
FROM Original;

注意：如果同一ClientID下存在多条UpdatedDate完全相同的最新记录，需要把这些记录都标记为1时，将ROW_NUMBER()替换为RANK()即可，避免同时间最新记录被错标为0。

不支持窗口函数的老版本环境（如MySQL 5.x） #

用关联子查询匹配分组内最大更新时间实现：

SELECT
    o1.StartDate,
    o1.ClientID,
    o1.Value,
    o1.UpdatedDate,
    CASE
        WHEN o1.UpdatedDate = (SELECT MAX(o2.UpdatedDate) FROM Original o2 WHERE o2.ClientID = o1.ClientID) THEN 1
        ELSE 0
    END AS outdated
FROM Original o1;

以测试数据为例：

• ClientID为101的3条记录，更新时间分别为2024-01-05、2024-02-10、2024-03-15，最新时间2024-03-15对应的记录outdated为1，其余两条为0
• ClientID为102的2条记录，更新时间分别为2024-02-20、2024-04-02，最新时间2024-04-02对应的记录outdated为1，其余一条为0
  输出结果完全符合预期。

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