PostgreSQL按ID分组统计记录并展示对应最大年份

原始数据表 #

期望输出 #

注：你给出的期望输出中id=10的max year写为2017，这与原始数据不符，原始数据里id=10的最大年份是2019，以下是修正后的正确期望输出：

错误SQL分析 #

你尝试的SQL存在以下问题：

• 子查询中将MAX(year)命名为year，与原表字段重名，导致字段混淆
• 错误使用FULL JOIN且引用了不存在的table2，实际只需操作单张表
• 未采用正确的统计方式，无法在保留原始记录的同时计算分组统计值

修正后的SQL #

SELECT 
    id,
    year,
    MAX(year) OVER (PARTITION BY id) AS "max year",
    COUNT(*) OVER (PARTITION BY id) AS count
FROM your_table_name;

将your_table_name替换为你的实际表名即可。

原理说明 #

使用PostgreSQL的窗口函数OVER (PARTITION BY id)，按id分组后：

• MAX(year) OVER (...)计算每个id分组的最大年份，填充到该分组的每条记录中
• COUNT(*) OVER (...)统计每个id分组的记录总数，同样填充到每条记录中
• 该方式无需额外关联表，直接在原表上完成计算，保留所有原始记录的同时得到分组统计值

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