如何实现无硬编码年份的年度有效物品关联事件统计？

现有SQL语句可查询指定年度（如2022年）存在的物品（以驾照为例，即该年度处于驾照有效期内：issue_date≤当年且termination_date≥当年）：

SELECT 
    t.item_type_code,
    t.item_id
FROM   
    ITEM_TABLE t
WHERE
    extract(year from t.issue_date) <= 2022 AND
    extract(year from t.termination_date) >= 2022

通过CTE关联事件表后可统计该年度这些物品的当年事件数量：

WITH items AS
(      
    SELECT 
        t.item_type,
        t.item_id
FROM   
    ITEM_TABLE t
WHERE
    extract(year from t.issue_date) <= 2022 AND
    extract(year from t.termination_date) >= 2022
)
SELECT
    t.item_type,
    COUNT(event_id)
FROM
    items t 
INNER JOIN
    events_xref x ON t.item_id = x.item_id
INNER JOIN
    events e ON x.event_id = e.event_id
WHERE
    extract(year from e.effective_date) = 2022
GROUP BY
    t.item_type

当前需手动修改语句中3处年份值才能获取近75年各年度的统计数据，需求是去除硬编码年份，让SQL自动遍历年份，输出每年度有效物品的当年事件统计结果。

核心思路是先生成近75年的年份序列，再将这个序列与有效物品、事件表关联，一次性计算所有年份的统计数据。以下是不同主流数据库的实现方式：

1. PostgreSQL 实现 #

利用generate_series生成年份序列，再关联表进行统计：

WITH year_series AS (
    -- 生成近75年的年份，以当前年份往前推74年为例，比如2024年则生成1949-2024
    SELECT generate_series(
        EXTRACT(YEAR FROM CURRENT_DATE)::INT - 74,
        EXTRACT(YEAR FROM CURRENT_DATE)::INT
    ) AS stat_year
),
valid_items_per_year AS (
    SELECT
        ys.stat_year,
        t.item_type,
        t.item_id
    FROM year_series ys
    JOIN ITEM_TABLE t
        ON EXTRACT(YEAR FROM t.issue_date) <= ys.stat_year
        AND EXTRACT(YEAR FROM t.termination_date) >= ys.stat_year
)
SELECT
    vi.stat_year,
    vi.item_type,
    COUNT(e.event_id) AS event_count
FROM valid_items_per_year vi
LEFT JOIN events_xref x ON vi.item_id = x.item_id
LEFT JOIN events e 
    ON x.event_id = e.event_id
    AND EXTRACT(YEAR FROM e.effective_date) = vi.stat_year
GROUP BY vi.stat_year, vi.item_type
ORDER BY vi.stat_year DESC, vi.item_type;

2. MySQL 实现 #

MySQL 8.0+ 可借助递归CTE生成年份序列：

WITH RECURSIVE year_series AS (
    SELECT YEAR(CURDATE()) - 74 AS stat_year
    UNION ALL
    SELECT stat_year + 1 FROM year_series WHERE stat_year < YEAR(CURDATE())
),
valid_items_per_year AS (
    SELECT
        ys.stat_year,
        t.item_type,
        t.item_id
    FROM year_series ys
    JOIN ITEM_TABLE t
        ON YEAR(t.issue_date) <= ys.stat_year
        AND YEAR(t.termination_date) >= ys.stat_year
)
SELECT
    vi.stat_year,
    vi.item_type,
    COUNT(e.event_id) AS event_count
FROM valid_items_per_year vi
LEFT JOIN events_xref x ON vi.item_id = x.item_id
LEFT JOIN events e 
    ON x.event_id = e.event_id
    AND YEAR(e.effective_date) = vi.stat_year
GROUP BY vi.stat_year, vi.item_type
ORDER BY vi.stat_year DESC, vi.item_type;

3. SQL Server 实现 #

使用递归CTE生成年份序列：

WITH year_series AS (
    SELECT DATEPART(YEAR, GETDATE()) - 74 AS stat_year
    UNION ALL
    SELECT stat_year + 1 FROM year_series WHERE stat_year < DATEPART(YEAR, GETDATE())
),
valid_items_per_year AS (
    SELECT
        ys.stat_year,
        t.item_type,
        t.item_id
    FROM year_series ys
    JOIN ITEM_TABLE t
        ON DATEPART(YEAR, t.issue_date) <= ys.stat_year
        AND DATEPART(YEAR, t.termination_date) >= ys.stat_year
)
SELECT
    vi.stat_year,
    vi.item_type,
    COUNT(e.event_id) AS event_count
FROM valid_items_per_year vi
LEFT JOIN events_xref x ON vi.item_id = x.item_id
LEFT JOIN events e 
    ON x.event_id = e.event_id
    AND DATEPART(YEAR, e.effective_date) = vi.stat_year
GROUP BY vi.stat_year, vi.item_type
ORDER BY vi.stat_year DESC, vi.item_type;

关键说明 #

• 用LEFT JOIN替代INNER JOIN可以保留那些当年有有效物品但无事件的年份和类型，统计数为0，若不需要可改回INNER JOIN
• 年份序列的起始值可根据需求调整，比如固定起始年份（如1950）而非基于当前年份计算
• 若数据库支持，可将年份序列生成逻辑封装成函数或视图，方便复用

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