显示日期范围内的所有结果，将空记录替换为零

Question

我正在查询特定日期出现的日志计数。然而，在某些日子里，我正在搜索的日志记录不会被记录下来。如何将这些天的计数设置为0，并返回日期范围内的完整日期集的结果？

SELECT r.LogCreateDate, r.Docs
FROM(
SELECT SUM(TO_NUMBER(REPLACE(ld.log_detail, 'Total Documents:' , ''))) AS Docs, to_char(l.log_create_date,'YYYY-MM-DD') AS LogCreateDate
FROM iwbe_log l
LEFT JOIN iwbe_log_detail ld ON ld.log_id = l.log_id
HAVING to_char(l.log_create_date , 'YYYY-MM-DD') BETWEEN '2020-01-01' AND '2020-01-07'
GROUP BY to_char(l.log_create_date,'YYYY-MM-DD')
ORDER BY to_char(l.log_create_date,'YYYY-MM-DD') DESC
) r
ORDER BY r.logcreatedate

当前结果-我想包括0个文档的01、04、05。

LOGCREATE>	文档
2020-01-02	7
22020-01-03	3
2020-01-06	6
2020-01-07	1

您首先需要一个完整的日期列表，然后将日志数据外部连接到该列表。有几种方法可以生成日期列表，但现在通用表表达式(cte(是ANSI标准的方法，如下所示：

with cte (dt) as (
select to_date('2020-01-01','yyyy-mm-dd') as dt from dual -- start date here
union all
select dt + 1 from cte
where dt + 1 < to_date('2020-02-01','yyyy-mm-dd') -- finish (the day before) date here
)
select to_char(cte.dt,'yyyy-mm-dd') as LogCreateDate
, r.Docs 
from cte
left join (
SELECT SUM(TO_NUMBER(REPLACE(ld.log_detail, 'Total Documents:' , ''))) AS Docs
, trunc(l.log_create_date) AS LogCreateDate
FROM iwbe_log l
LEFT JOIN iwbe_log_detail ld ON ld.log_id = l.log_id
HAVING trunc(l.log_create_date) BETWEEN to_date('2020-01-01','yyyy-mm-dd' AND to_date('2020-01-07','yyyy-mm-dd')
GROUP BY trunc(l.log_create_date)
) r on cte.dt = r.log_create_date
order by cte.dt

此外，在处理日期时，我更喜欢而不是在最终输出之前将它们转换为字符串，这样您仍然可以获得正确的日期顺序和最大的查询效率。