填写缺少的日期DB2 SQL [英] Filling in missing dates DB2 SQL

问题描述

我的初始查询如下：

select process_date, count(*) batchCount
from T1.log_comments 
order by process_date asc;

我需要能够对缺少的周末进行一些快速分析，但想知道是否有一种快速的方法来填充 process_date 中不存在的缺少的日期。

I need to be able to do some quick analysis for weekends that are missing, but wanted to know if there was a quick way to fill in the missing dates not present in process_date.

我已经看到了解决方案此处，但对是否存在db2中隐藏的任何魔术都可以对我的原始查询进行较小的修改就可以做到这一点。

I've seen the solution here but am curious if there's any magic hidden in db2 that could do this with only a minor modification to my original query.

推荐答案

注意：未经测试，根据我对SQL Server / Oracle的了解对其进行构图。我想这给了您一个主意：
*现在已在DB2上进行了修改和测试*

Note: Not tested, framed it based on my exposure to SQL Server/Oracle. I guess this gives you the idea though: *now amended and tested on DB2*

WITH MaxDateQry(MaxDate) AS 
(
    SELECT MAX(process_date) FROM T1.log_comments 
),
MinDateQry(MinDate) AS 
(
    SELECT MIN(process_date) FROM T1.log_comments 
),
DatesData(ProcessDate) AS 
(
    SELECT MinDate from MinDateQry
    UNION ALL
    SELECT (ProcessDate  + 1 DAY) FROM DatesData WHERE  ProcessDate < (SELECT MaxDate FROM MaxDateQry)
)
SELECT  a.ProcessDate, b.batchCount
  FROM  DatesData a LEFT JOIN 
    (
        SELECT process_date, COUNT(*) batchCount
        FROM T1.log_comments 
    ) b
    ON a.ProcessDate = b.process_date
ORDER BY a.ProcessDate ASC;

这篇关于填写缺少的日期DB2 SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！