Sql(在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,DUE_DATE) VALUES  ( 2,'file_' || 2,sysdate -5);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,DUE_DATE) VALUES  ( 3,'file_' || 3,sysdate -4);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,DUE_DATE) VALUES  ( 4,'file_' || 4,DUE_DATE) VALUES  ( 5,'file_' || 5,sysdate-3);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,DUE_DATE) VALUES  ( 6,'file_' || 6,sysdate-7);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,DUE_DATE) VALUES  ( 7,'file_' || 7,sysdate-10);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,DUE_DATE) VALUES  ( 8,'file_' || 8,sysdate-12);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,DUE_DATE) VALUES  ( 9,'file_' || 9,DUE_DATE) VALUES  ( 10,'file_' || 10,sysdate-5);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,DUE_DATE) VALUES  ( 11,'file_' || 11,sysdate-6);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,DUE_DATE) VALUES  ( 12,'file_' || 12,DUE_DATE) VALUES  ( 13,'file_' || 13,DUE_DATE) VALUES  ( 14,'file_' || 14,sysdate-4);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,DUE_DATE) VALUES  ( 15,'file_' || 15,sysdate-2);
INSERT INTO FILES_TO_SUBMIT(FILE_ID,DUE_DATE) VALUES  ( 16,'file_' || 16,DUE_DATE) VALUES  ( 17,'file_' || 17,DUE_DATE) VALUES  ( 18,'file_' || 18,DUE_DATE) VALUES  ( 19,'file_' || 19,DUE_DATE) VALUES  ( 20,'file_' || 20,sysdate-9);


DROP TABLE files_to_submit;

解决方法

查询：

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!