sql - SQL(在Oracle上)按天数划分的老化报告

我需要有关在oracle上编写老化报告的帮助。
该报告应类似于:

 aging file to submit total       17
 aging file to submit 0-2 days    3
 aging file to submit 2-4 days    4
 aging file to submit 4-6 days    4
 aging file to submit 6-8 days    2
 aging file to submit 8-10 days   4

我可以为每个部分创建一个查询，然后合并所有结果，例如:

select 'aging file to submit total  ' || count(*) from FILES_TO_SUBMIT where trunc(DUE_DATE) > trunc(sysdate) -10
union all
select 'aging file to submit 0-2 days ' || count(*) from FILES_TO_SUBMIT where trunc(DUE_DATE) <= trunc(sysdate)  and trunc(DUE_DATE) >= trunc(sysdate-2)
union all
select 'aging file to submit 2-4 days ' || count(*) from FILES_TO_SUBMIT where trunc(DUE_DATE) <= trunc(sysdate-2) and trunc(DUE_DATE) >= trunc(sysdate-4) ;

我想知道是否有更好的方法使用oracle解析函数或任何其他可以获得更好性能的查询？

样本数据:

CREATE TABLE files_to_submit(file_id int,   file_name varchar(255),due_date date);

INSERT INTO FILES_TO_SUBMIT(FILE_ID,FILE_NAME,DUE_DATE) VALUES  ( 1, 'file_' || 1, sysdate);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,FILE_NAME,DUE_DATE) VALUES  ( 2, 'file_' || 2, sysdate -5);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,FILE_NAME,DUE_DATE) VALUES  ( 3, 'file_' || 3, sysdate -4);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,FILE_NAME,DUE_DATE) VALUES  ( 4, 'file_' || 4, sysdate);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,FILE_NAME,DUE_DATE) VALUES  ( 5, 'file_' || 5, sysdate-3);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,FILE_NAME,DUE_DATE) VALUES  ( 6, 'file_' || 6, sysdate-7);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,FILE_NAME,DUE_DATE) VALUES  ( 7, 'file_' || 7, sysdate-10);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,FILE_NAME,DUE_DATE) VALUES  ( 8, 'file_' || 8, sysdate-12);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,FILE_NAME,DUE_DATE) VALUES  ( 9, 'file_' || 9, sysdate-3);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,FILE_NAME,DUE_DATE) VALUES  ( 10, 'file_' || 10, sysdate-5);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,FILE_NAME,DUE_DATE) VALUES  ( 11, 'file_' || 11, sysdate-6);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,FILE_NAME,DUE_DATE) VALUES  ( 12, 'file_' || 12, sysdate-7);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,FILE_NAME,DUE_DATE) VALUES  ( 13, 'file_' || 13, sysdate-5);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,FILE_NAME,DUE_DATE) VALUES  ( 14, 'file_' || 14, sysdate-4);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,FILE_NAME,DUE_DATE) VALUES  ( 15, 'file_' || 15, sysdate-2);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,FILE_NAME,DUE_DATE) VALUES  ( 16, 'file_' || 16, sysdate-6);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,FILE_NAME,DUE_DATE) VALUES  ( 17, 'file_' || 17, sysdate-6);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,FILE_NAME,DUE_DATE) VALUES  ( 18, 'file_' || 18, sysdate-5);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,FILE_NAME,DUE_DATE) VALUES  ( 19, 'file_' || 19, sysdate-10);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,FILE_NAME,DUE_DATE) VALUES  ( 20, 'file_' || 20, sysdate-9);


DROP TABLE files_to_submit;

最佳答案

请允许我建议WIDTH_BUCKET。
这会将日期范围划分为相等的大小。由于您希望将10天范围划分为2天，因此存储桶大小将为10/2 = 5。

询问:

SELECT
    CASE GROUPING(bucket)
        WHEN 1
            THEN 'aging file to submit Total'
            ELSE 'aging file to submit ' || (bucket-1)*2 || '-' || (bucket)*2 || ' days'
    END             AS bucket_number,
    COUNT(1)        AS files
FROM (
    SELECT
        WIDTH_BUCKET(due_date, sysdate, sysdate-10, 5) bucket
    FROM
        files_to_submit
    WHERE
        due_date >= sysdate-10
    )
GROUP BY
    ROLLUP(bucket)
ORDER BY
    bucket NULLS FIRST;

结果:

BUCKET_NUMBER                             FILES
------------------------------------ ----------
aging file to submit Total                   17
aging file to submit 0-2 days                 2
aging file to submit 2-4 days                 3
aging file to submit 4-6 days                 6
aging file to submit 6-8 days                 5
aging file to submit 8-10 days                1