PostgreSQL按emp_id分组，获取最新调薪年对应rating的查询需求

原始表结构与数据 #

employee表结构及样本数据：
emp_id | salary | rating | increment_year
--------------------------------------
28      3000      1         2023
25      2000      2         2023
30      1500      1         2021
28      1000      3         2022
32      1200      1         2023
23      1550      1         2023
30      2500      3         2022        
30      3000      2         2023

需求说明 #

按emp_id分组，返回以下字段：

• emp_id：员工ID
• sum_salary：该员工的薪资总和
• selected_rating_on_latest_increment_year：最新调薪年份对应的rating值
• selected_increment_year_to_get_rating：获取上述rating的最新调薪年份
  要求必须使用GROUP BY实现

解决方案 #

利用PostgreSQL的聚合函数特性，结合array_agg按年份倒序聚合后取第一个元素，即可在GROUP BY中直接获取最新年份对应的rating：

SELECT
    emp_id,
    SUM(salary) AS sum_salary,
    ARRAY_AGG(rating ORDER BY increment_year DESC)[1] AS selected_rating_on_latest_increment_year,
    MAX(increment_year) AS selected_increment_year_to_get_rating
FROM employee
GROUP BY emp_id
ORDER BY emp_id;

代码解释 #

1. SUM(salary)：直接计算每个员工的薪资总和
2. MAX(increment_year)：获取每个员工的最新调薪年份
3. ARRAY_AGG(rating ORDER BY increment_year DESC)[1]：将每个员工的rating按调薪年份倒序存入数组，取数组第一个元素即为最新年份对应的rating值

查询结果 #

emp_id | sum_salary | selected_rating_on_latest_increment_year | selected_increment_year_to_get_rating
------------------------------------------------------------------------------------------------------
23       1550         1                                         2023
25       2000         2                                         2023
28       4000         1                                         2023
30       7000         2                                         2023
32       1200         1                                         2023

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