PostgreSQL窗口函数：如何添加用户最后项目日期列？

表结构 #

User表

Project表

解决方案SQL #

用LEFT JOIN关联两表，结合MAX()窗口函数按用户分组提取最新项目日期：

SELECT
    p.id AS project_id,
    p.date_project,
    p.user_id,
    u.name AS user_name,
    MAX(p.date_project) OVER (PARTITION BY u.id) AS last_project
FROM
    User u
LEFT JOIN
    Project p ON u.id = p.user_id
ORDER BY
    u.id, p.date_project DESC;

关键说明 #

• LEFT JOIN：保证所有用户都能出现在结果里，哪怕该用户没有任何项目（此时项目相关字段为NULL，last_project也会是NULL）
• MAX(p.date_project) OVER (PARTITION BY u.id)：窗口函数按用户ID分组，计算每个用户的最大项目日期（即最后项目日期），并把这个值附加到该用户的每一条项目记录上
• 若只需要查询有项目的用户，把LEFT JOIN换成INNER JOIN即可

预期结果 #

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