PostgreSQL查询：获取包含从最新年份起连续3年数据的ID

要解决这个问题，核心是先确定每个ID的最新数据年份，再验证该ID是否同时拥有最新年份、最新年份-1、最新年份-2这三个年份的记录。下面是两种高效的实现方案，适配你的需求：

方案一：使用CTE分步处理 #

这个方法逻辑清晰，先提取每个ID的所有唯一年份，再计算最新年份，最后验证连续三年的存在性：

WITH id_year_data AS (
    -- 提取每个ID对应的所有唯一年份（避免同一年份多条记录干扰）
    SELECT 
        id,
        DISTINCT EXTRACT(YEAR FROM date) AS year
    FROM your_table_name  -- 替换成你的实际表名
),
id_latest_year AS (
    -- 计算每个ID的最新数据年份
    SELECT 
        id,
        MAX(year) AS latest_year
    FROM id_year_data
    GROUP BY id
)
-- 筛选出同时拥有最新三年数据的ID
SELECT ild.id
FROM id_latest_year ild
JOIN id_year_data iyd ON ild.id = iyd.id
WHERE iyd.year IN (ild.latest_year, ild.latest_year - 1, ild.latest_year - 2)
GROUP BY ild.id, ild.latest_year
HAVING COUNT(DISTINCT iyd.year) = 3;

逻辑解释 #

1. id_year_data：先去重每个ID的年份，确保每个年份只统计一次。
2. id_latest_year：通过分组聚合得到每个ID的最新年份。
3. 关联两个CTE后，筛选出属于最新三年的年份记录，最后通过HAVING COUNT(DISTINCT iyd.year) = 3验证这三个年份都存在。

方案二：窗口函数简化写法 #

如果喜欢更紧凑的代码，可以用窗口函数直接计算每个ID的最新年份，减少CTE的数量：

WITH id_year_info AS (
    SELECT 
        id,
        EXTRACT(YEAR FROM date) AS year,
        -- 窗口函数直接获取当前ID的最新年份
        MAX(EXTRACT(YEAR FROM date)) OVER (PARTITION BY id) AS latest_year
    FROM your_table_name
    GROUP BY id, EXTRACT(YEAR FROM date)  -- 同样先按ID和年份去重
)
SELECT DISTINCT id
FROM id_year_info
WHERE year >= latest_year - 2  -- 筛选最新三年的年份
GROUP BY id, latest_year
HAVING COUNT(year) = 3;  -- 确认三个年份都存在

验证你的测试数据 #

用你提供的测试数据跑上面的SQL，结果只会返回id = 1，完全符合预期：

• ID1的年份是2020、2019、2018，最新年份2020，三个年份齐全；
• ID2缺少2020年数据，ID3缺少2018年数据，其余ID均不满足连续三年的要求。

注意事项 #

• 如果你的date字段是字符串类型（而非DATE/TIMESTAMP），需要先转换为日期类型再提取年份，比如用EXTRACT(YEAR FROM TO_DATE(date, 'YYYY-MM-DD'))；
• 若某个ID的最新年份不足3年（比如最新是2018年），会自动被排除，因为无法满足连续三年的条件。

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