PostgreSQL如何创建按时间序统计运动员各距离PB的视图

我们可以通过两种方式实现需求，均能完全匹配你给出的伪代码逻辑和预期过滤规则： #

方案1：NOT EXISTS 直观实现 #

逻辑非常好理解：只要某条记录不存在「同用户、同距离、参赛时间更早、耗时更短/相等」的其他记录，就属于PB记录。

CREATE OR REPLACE VIEW athlete_personal_bests AS
SELECT
    id,
    "user",
    race_distance,
    elapsed_time,
    start_date
FROM race_records r1
WHERE NOT EXISTS (
    SELECT 1
    FROM race_records r2
    WHERE r2."user" = r1."user"
      AND r2.race_distance = r1.race_distance
      AND r2.start_date < r1.start_date
      AND r2.elapsed_time <= r1.elapsed_time
)
ORDER BY "user", race_distance, start_date ASC;

注意：user是PostgreSQL保留关键字，必须用双引号包裹避免语法报错，如果你实际表中的用户字段名不是user可以修改为对应的字段名。

方案2：窗口函数高性能实现 #

如果你的参赛记录量级很大，推荐用窗口函数实现，只需要一次表扫描即可完成计算，性能远高于关联子查询方案：

CREATE OR REPLACE VIEW athlete_personal_bests AS
WITH records_with_pb_flag AS (
    SELECT
        id,
        "user",
        race_distance,
        elapsed_time,
        start_date,
        -- 计算同用户同距离下按时间排序的滚动最小耗时
        MIN(elapsed_time) OVER w AS running_min,
        -- 取上一条记录的滚动最小耗时
        LAG(MIN(elapsed_time) OVER w) OVER w AS prev_running_min
    FROM race_records
    WINDOW w AS (PARTITION BY "user", race_distance ORDER BY start_date ASC)
)
SELECT id, "user", race_distance, elapsed_time, start_date
FROM records_with_pb_flag
WHERE prev_running_min IS NULL -- 同组第一条记录默认是PB
   OR running_min < prev_running_min -- 比之前所有记录都快的新PB
ORDER BY "user", race_distance, start_date ASC;

结果验证 #

以你给出的Bolt 100米参赛记录为例：

• 按时间排序的4条记录耗时分别为970、999、958、960
• 耗时999的记录存在更早的970耗时更短，会被过滤
• 耗时960的记录存在更早的958耗时更短，会被过滤
• 最终仅970、958两条PB记录保留，完全符合你的预期规则。

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