PostgreSQL中如何计算Racings表每赛道每车辆最新3条记录的圈时均值

假设表中id为自增主键，id值越大代表记录越新，可直接使用以下SQL实现需求：

SELECT 
    id,
    track,
    car,
    circle_time,
    CASE 
        WHEN COUNT(*) OVER w = 3 
        THEN ROUND(AVG(circle_time) OVER w, 1)
        ELSE NULL 
    END AS avg3_circle_time
FROM Racings
WINDOW w AS (
    PARTITION BY track, car 
    ORDER BY id DESC 
    ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
)
ORDER BY id DESC;

逻辑说明 #

• 窗口定义PARTITION BY track, car：将数据按赛道、车辆分组，每个分组独立计算
• ORDER BY id DESC：每个分组内按id降序排列，保证最新生成的记录排在最前
• ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING：限定窗口范围为当前行 + 当前行之后的2行，刚好覆盖包含当前行在内的最近3条记录
• 外层CASE判断：仅当窗口内存在3条完整记录时返回平均值，否则返回NULL，和给出的预期结果完全匹配

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