Oracle统计每年活跃文件数：按发布日期累计计数查询实现

实现思路 #

• 先生成从表中最早发布年份到当前系统年份的连续年份序列
• 将连续年份与文件表关联，关联规则为：年份 >= 文件发布年份
• 按年份分组统计关联后的文件数量即可得到每年的活跃文件总数

参考查询语句 #

WITH all_years AS (
    -- 生成从最早发布年份到当前年份的连续年份
    SELECT min_year + LEVEL - 1 AS year
    FROM (
        SELECT EXTRACT(YEAR FROM MIN(ISSUE_DATE)) min_year,
               EXTRACT(YEAR FROM SYSDATE) max_year
        FROM your_file_table
    )
    CONNECT BY LEVEL <= max_year - min_year + 1
)
SELECT 
    a.year AS "Year",
    COUNT(f."File #") AS "COUNT_OF_FILES"
FROM all_years a
LEFT JOIN your_file_table f
    ON a.year >= EXTRACT(YEAR FROM f.ISSUE_DATE)
-- 可选：过滤发布日期为空的无效数据
-- WHERE f.ISSUE_DATE IS NOT NULL
GROUP BY a.year
ORDER BY a.year;

使用说明 #

• 请将代码中的your_file_table替换为你实际的业务表名
• 若需要统计到指定截止年份而非当前系统年份，直接修改max_year的赋值逻辑即可
• 本写法兼容Oracle 11g及以上版本

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