如何基于ID、DATE、LOCATION三列获取唯一行并保留重复组最后一行？

问题场景 #

现有一张包含id、date、location、value字段的数据表，需基于ID、DATE、LOCATION三列分组，每组仅保留重复记录的最后一行；非重复行直接保留。其中id=3的三条记录在这三列上值完全重复，需保留该组的最后一行（value=6的记录）。

原数据表 #

预期结果表 #

原SQL问题分析 #

你尝试的SQL存在两个关键问题：

SELECT *
FROM my table
QUALIFY ROW_NUMBER() OVER (PARTITION BY ID, DATE,LOCATION ORDER BY ID, DATE) = 1
order by ID, DATE;

1. 排序逻辑无效：ORDER BY ID, DATE在分组内没有意义——同一分组的ID、DATE值完全相同，无法生成有区分度的行号，也就无法定位到最后一行。
2. 筛选逻辑与需求相反：需求是保留分组的最后一行，但你取的是ROW_NUMBER() = 1，也就是分组内的第一行。

正确SQL写法 #

基于你的需求，我们需要在分组内按能区分行顺序的字段降序排序（示例中value的递增顺序对应记录的先后，所以按value降序），再筛选行号为1的记录：

SELECT *
FROM my_table  -- 注意表名空格改为下划线，避免语法错误
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY ID, DATE, LOCATION 
    ORDER BY value DESC  -- 按value降序，让最后一行（value最大）排在第一位
) = 1
ORDER BY ID, DATE;

逻辑说明 #

• PARTITION BY ID, DATE, LOCATION：将数据按这三列分组，相同组合的记录归为一组
• ORDER BY value DESC：分组内按value从大到小排序，原数据的最后一行（value=6）会成为分组内的第1行
• QUALIFY ROW_NUMBER() = 1：筛选出每组的第1行，也就是原数据的最后一行

如果你的表有记录插入时间（比如create_time）或者自增主键（比如row_id），建议用这些字段排序，能更精准地定位到实际插入的最后一行：

SELECT *
FROM my_table
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY ID, DATE, LOCATION 
    ORDER BY create_time DESC  -- 用插入时间排序更准确
) = 1
ORDER BY ID, DATE;

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