Oracle查询从发行年份到END_DATE对应年度每年有效文件数量

Oracle SQL 实现方案 #

推荐写法（适用于大表，性能更稳定）

WITH all_years AS (
    -- 生成覆盖所有业务涉及年度的序列
    SELECT min_year + LEVEL - 1 AS stat_year
    FROM (
        SELECT 
            EXTRACT(YEAR FROM MIN(ISSUE_DATE)) AS min_year,
            EXTRACT(YEAR FROM MAX(END_DATE)) AS max_year
        FROM your_file_table -- 替换为实际业务表名
    )
    CONNECT BY LEVEL <= max_year - min_year + 1
)
SELECT 
    stat_year AS "Year（年度）",
    COUNT(t.唯一文件编号字段) AS "COUNT_OF_FILES（文件数量）"
FROM all_years y
LEFT JOIN your_file_table t
    ON EXTRACT(YEAR FROM t.ISSUE_DATE) <= y.stat_year
    AND EXTRACT(YEAR FROM t.END_DATE) >= y.stat_year
GROUP BY stat_year
ORDER BY stat_year;

备选写法（适用于小数据量场景，更简洁）

SELECT 
    stat_year AS "Year（年度）",
    COUNT(*) AS "COUNT_OF_FILES（文件数量）"
FROM (
    SELECT 
        EXTRACT(YEAR FROM ISSUE_DATE) + LEVEL - 1 AS stat_year
    FROM your_file_table -- 替换为实际业务表名
    CONNECT BY LEVEL <= EXTRACT(YEAR FROM END_DATE) - EXTRACT(YEAR FROM ISSUE_DATE) + 1
        AND PRIOR 唯一文件编号字段 = 唯一文件编号字段 -- 替换为实际唯一文件编号字段名
        AND PRIOR SYS_GUID() IS NOT NULL -- 避免循环报错
)
GROUP BY stat_year
ORDER BY stat_year;

注意事项

• 请将代码中的your_file_table替换为实际存储文件数据的表名，唯一文件编号字段替换为表中对应的唯一文件编号字段名
• 如果存在END_DATE为空（表示文件永久有效）的情况，可将关联条件中EXTRACT(YEAR FROM t.END_DATE) >= y.stat_year调整为NVL(EXTRACT(YEAR FROM t.END_DATE), 9999) >= y.stat_year，即可将永久有效文件计入所有后续年度的统计中

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