PostgreSQL 10：如何获取分组中最近过去记录的主键？

在PostgreSQL 10中开发数据库查询时，遇到了获取聚合值对应行主键的需求。现有instructor_schedule表，需要获取每个讲师/课程组合的当前记录（即过去的最近记录，未来记录不计入），同时得到对应行的instructor_schedule_id主键。

示例表数据 #

期望结果 #

如示例所示，需要筛选出每个讲师对应课程的最近已发生记录（Bob的2024年记录属于未来，不计入；2015年记录不是最近的，也排除）。

尝试的SQL语句无法运行：

select * 
from instructor_schedule 
where instructor_schedule_id in (select instructor_schedule_id 
                                 from 
                                     (select
                                          min(start_date),
                                          course_name,
                                          instructor_name,
                                          instructor_schedule_id
                                      from
                                          instructor_schedule
                                      group by
                                          course_name, instructor_name)  inner);

报错原因是instructor_schedule_id既不在GROUP BY子句中，也未用聚合函数包裹，不符合PostgreSQL分组查询规则。

核心需求：按course_name和instructor_name分组后，获取每个组中**过去最近的start_date**对应的instructor_schedule_id及整行数据。

解决方案 #

方法1：窗口函数ROW_NUMBER()（通用兼容） #

窗口函数是处理分组取Top N场景的标准方案，PostgreSQL 10完全支持：

WITH ranked_schedules AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY instructor_name, course_name 
            ORDER BY start_date DESC
        ) AS rn
    FROM instructor_schedule
    WHERE start_date <= NOW() -- 过滤未来记录
)
SELECT 
    instructor_schedule_id,
    instructor_name,
    course_name,
    start_date
FROM ranked_schedules
WHERE rn = 1; -- 取每个分组的第一条（最近的记录）

逻辑说明：

1. 先通过WHERE过滤所有未来的记录；
2. 用PARTITION BY按讲师和课程分组，ORDER BY start_date DESC让每组内最近的记录排在最前面；
3. ROW_NUMBER()为每组内的记录编号，最近的记录编号为1；
4. 最后筛选编号为1的记录，得到目标结果。

方法2：PostgreSQL特有的DISTINCT ON（更简洁） #

PostgreSQL提供的DISTINCT ON语法可直接按指定字段去重，保留每组的第一条记录：

SELECT DISTINCT ON (instructor_name, course_name)
    instructor_schedule_id,
    instructor_name,
    course_name,
    start_date
FROM instructor_schedule
WHERE start_date <= NOW()
ORDER BY instructor_name, course_name, start_date DESC;

逻辑说明：

1. DISTINCT ON (instructor_name, course_name)表示按讲师和课程组合去重；
2. ORDER BY必须先声明DISTINCT ON的字段，再按start_date DESC排序，确保每组内最近的记录排在最前面并被保留；
3. 同样通过WHERE过滤未来记录。

方法3：子查询关联（传统写法） #

若习惯传统关联写法，可先找出每个分组的最近已发生日期，再关联原表获取对应主键：

SELECT 
    s.instructor_schedule_id,
    s.instructor_name,
    s.course_name,
    s.start_date
FROM instructor_schedule s
JOIN (
    SELECT 
        instructor_name,
        course_name,
        MAX(start_date) AS latest_date
    FROM instructor_schedule
    WHERE start_date <= NOW()
    GROUP BY instructor_name, course_name
) latest
ON s.instructor_name = latest.instructor_name
AND s.course_name = latest.course_name
AND s.start_date = latest.latest_date;

逻辑说明：

1. 子查询按讲师和课程分组，计算每个组的最近已发生日期（MAX(start_date)）；
2. 将原表与子查询结果关联，匹配讲师、课程和日期，得到对应的主键及整行数据。

复现用DDL代码 #

drop table if exists instructor_schedule;

create table instructor_schedule
(
    instructor_schedule_id bigserial primary key,
    instructor_name varchar(255),
    course_name varchar(255),
    start_date timestamp with time zone
);

alter table instructor_schedule add constraint no_dupes unique(instructor_name, course_name, start_date);

insert into instructor_schedule (instructor_name, course_name, start_date) 
values
('bob', 'databases', '2015-01-01 00:00:00.000000 +00:00'),
('bob', 'databases', '2018-01-01 00:00:00.000000 +00:00'),
('bob', 'databases', '2024-01-01 00:00:00.000000 +00:00'),
('alice', 'databases', '2021-01-01 00:00:00.000000 +00:00'),
('alice', 'databases', '2022-01-01 00:00:00.000000 +00:00');

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