PostgreSQL如何查询分组内重复pos值的首次出现记录

需求说明 #

按msr_id、user_id、pos分组，筛选pos = 'AL'的记录，取每个分组内时间最早的第一条记录（对应示例中id为210的记录）。 #

实现方案 #

方案1：使用PostgreSQL专属DISTINCT ON语法（性能最优） #

SELECT DISTINCT ON (msr_id, user_id, pos)
    id, msr_id, user_id, date, pos
FROM your_table_name
WHERE pos = 'AL'
ORDER BY msr_id, user_id, pos, date ASC;

说明：DISTINCT ON后接分组字段，ORDER BY末尾添加排序规则，按date升序排序时会自动保留每个分组的第一条记录。如果确认自增id的顺序和时间顺序完全一致，也可以将排序字段替换为id ASC。使用前请将your_table_name替换为实际表名。

方案2：窗口函数通用写法（兼容所有支持标准SQL的数据库） #

WITH ranked_records AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY msr_id, user_id, pos ORDER BY date ASC) AS rn
    FROM your_table_name
    WHERE pos = 'AL'
)
SELECT id, msr_id, user_id, date, pos
FROM ranked_records
WHERE rn = 1;

说明：通过ROW_NUMBER()窗口函数给每个分组内的记录按时间排序编号，筛选编号为1的记录即为分组内首次出现的符合条件的记录。 #

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