Sql (关于Oracle)按天计的老化报告

Question

我需要帮助写一份关于甲骨文的老化报告。报告应如下所示：

 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) ;

我想知道是否有更好的方法使用甲骨文分析函数或任何其他查询，以获得更好的性能？

样本数据：

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;

Answer 1

请允许我建议桶，桶。这将将日期范围划分为相同的大小。因为你想要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

Answer 2

您可以使用这种简单的方法来获取所有天的报告(没有总计)：

select 
'aging file to submit '|| trunc(dist/2)*2 ||'-'|| (trunc(dist/2)*2+2) || ' days: ' ||  count(*)
from (
      select trunc(sysdate) - trunc(DUE_DATE) as dist
      from FILES_TO_SUBMIT 
      --where trunc(DUE_DATE) > trunc(sysdate) -10
)
group by trunc(dist/2)
order by trunc(dist/2);

唯一重要的是天数(dist(ance)字段)。

如果您希望在相同的扫描中也使用“总计”：

select 
'aging file to submit '|| 
 case 
    when trunc(dist/2) is null 
    then 'Total ' 
    else trunc(dist/2)*2 ||'-'|| (trunc(dist/2)*2+2) || ' days: ' 
 end  ||  
 count(*)
from (
      select trunc(sysdate) - trunc(DUE_DATE) as dist
      from FILES_TO_SUBMIT 
      where trunc(DUE_DATE) > trunc(sysdate) -10
)
group by rollup(trunc(dist/2))
order by trunc(dist/2)
nulls first;

提示:如果您有数百天的历史，一个索引将是有用的。(注意:如果您的表很大，>100百万，创建索引将需要一些时间)

create index index_name on files_to_submit(due_date);

然后将条件更改为：

where DUE_DATE > trunc(sysdate) - 10

这样可以加快速度

Answer 3

我使用您的样本数据得到了不同的计数--我得到了19条总数，而不是17条(这似乎是合适的，因为样本数据中的20条记录中只有一条超出了范围)：

WITH d1 AS (
    SELECT 2 AS day_cnt FROM dual
     UNION ALL
    SELECT 4 FROM dual
     UNION ALL
    SELECT 6 FROM dual
     UNION ALL
    SELECT 8 FROM dual
     UNION ALL
    SELECT 10 FROM dual
)
SELECT NVL(title, 'aging file to submit total') AS title, COUNT(DISTINCT file_id)
  FROM (
    SELECT 'aging file to submit ' || prev_day || '-' || day_cnt || ' days' AS title, f1.file_id
      FROM (
        SELECT day_cnt, NVL(LAG(day_cnt) OVER ( ORDER BY day_cnt ), 0) AS prev_day
          FROM d1
    ) d2, files_to_submit f1
     WHERE TRUNC(f1.due_date) <= TRUNC(SYSDATE - d2.prev_day)
       AND TRUNC(f1.due_date) >= TRUNC(SYSDATE - d2.day_cnt)
) GROUP BY ROLLUP(title);

此外，日范围的计数不正确(它们加起来不等于19，也就是说)，因为由于使用TRUNC()并包括两个结束情况，这些文件可以被计算两次。但我相信你可以调整上面的内容来满足你的需求。