PostgreSQL分组查询获取员工最高业绩的最新周期记录

表数据与需求说明 #

现有employeeperformance表，插入数据的SQL语句如下：

INSERT INTO employeeperformance VALUES
(101,'Alice','Q2',2023,20000),
(101,'Alice','Q3',2023,20000),
(101,'Alice','Q4',2023,20000),
(101,'Alice','Q1',2024,20000),
(101,'Alice','Q2',2024,25000),
(102,'Bob','Q4',2023,15000),
(102,'Bob','Q1',2024,15000),
(102,'Bob','Q2',2024,30000),
(103,'Charlie','Q1',2024,28000),
(103,'Charlie','Q2',2024,22000),
(104,'Danny','Q2',2024,22000)

需求：查询每个员工的最高业绩周期，若业绩相同则优先选择最新周期，最终仅返回4条目标记录（Alice的第5行、Bob的第8行、Charlie的第9行、Danny的第11行）。

原尝试的问题 #

你之前用MAX(sales_amount)窗口函数的语句会返回所有行，只能拿到最高业绩值，但无法直接定位到对应的周期，尤其是业绩相同时的最新周期，且不想用关联原表再排序去重的方式。

最优实现方法 #

可以用ROW_NUMBER()窗口函数，按员工分组后，先按业绩降序排序，业绩相同则按年份降序、季度降序排序（确保最新周期优先），最后筛选出行号为1的记录即可，无需关联原表。SQL语句如下：

SELECT employee_id, employee_name, quarter, year, sales_amount
FROM (
    SELECT 
        employee_id, employee_name, quarter, year, sales_amount,
        ROW_NUMBER() OVER (
            PARTITION BY employee_id 
            ORDER BY sales_amount DESC, year DESC, 
                CASE quarter 
                    WHEN 'Q1' THEN 1 
                    WHEN 'Q2' THEN 2 
                    WHEN 'Q3' THEN 3 
                    WHEN 'Q4' THEN 4 
                END DESC
        ) AS rn
    FROM employeeperformance
) t
WHERE rn = 1;

说明： #

• PARTITION BY employee_id：按员工分组，确保每个员工单独排序
• ORDER BY sales_amount DESC：优先按业绩从高到低排序，锁定最高业绩的行
• year DESC：业绩相同时，年份更大的最新周期优先
• CASE quarter ... DESC：把季度转成数字后降序，确保同一年份里Q4比Q3、Q2、Q1更优先（也可以简化为ORDER BY sales_amount DESC, year DESC, quarter DESC，字符串排序下Q4会比Q1大，效果一致）
• 外层筛选rn=1，每个员工只保留排序后的第一条记录，正好匹配需求的目标行

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