如何让PostgreSQL窗口函数在子分组内返回相同结果？

数据表 #

当前查询语句 #

WITH ranked_messages AS (
    select file, created, path,
           row_number() OVER (PARTITION BY file ORDER BY created DESC)  AS rating_in_section
    from files
    order by file
)
SELECT path FROM ranked_messages WHERE rating_in_section > 1 group by path order by path desc;

当前结果 #

期望结果 #

中间状态对比 #

当前中间状态 #

需要的中间状态 #

解决方案 #

要实现需求，需调整窗口函数的分区逻辑，改用dense_rank()替代row_number()，让同一file下相同path的记录共享同一排名：

调整后的查询语句 #

WITH ranked_messages AS (
    SELECT 
        file, 
        created, 
        path,
        dense_rank() OVER (PARTITION BY file ORDER BY MAX(created) OVER (PARTITION BY file, path) DESC) AS rating_in_section
    FROM files
)
SELECT DISTINCT path 
FROM ranked_messages 
WHERE rating_in_section > 1 
ORDER BY path DESC;

逻辑说明 #

1. 内层分区计算：MAX(created) OVER (PARTITION BY file, path) 先获取每个file+path组合的最新创建时间，确保同一path的记录拥有相同的时间标识。
2. 外层排名：用dense_rank()按file分区，以内层得到的最新时间降序排序，这样同一path的记录会被分配相同的排名。
3. 筛选去重：最后筛选排名大于1的记录，通过DISTINCT去重后得到期望结果。

内容的提问来源于stack exchange，提问作者Юрий Кот