Блог

Главная - Вопросы по программированию - Показать все результаты в диапазоне дат, заменив нулевые записи нулем

Показать все результаты в диапазоне дат, заменив нулевые записи нулем

#sql #oracle #group-by #nvl

Вопрос:

Я запрашиваю количество журналов, которые появляются в определенные дни. Однако в некоторые дни записи в журнале, которые я ищу, не записываются. Как я могу установить значение 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

Текущий результат — идентификатор хотел бы включить 01, 04, 05 с 0 документами.

ДАТА СОЗДАНИЯ ЖУРНАЛА Документы
2020-01-02 7
2020-01-03 3
2020-01-06 6
2020-01-07 1

Комментарии:

1. Слева присоедините свои данные (справа) к списку всех дат (слева), которые вы хотите видеть в диапазоне. В oracle вы можете сгенерировать список дат, например, с помощью select TO_DATE('2020-01-01', 'YYYY-MM-DD') rownum - 1 from dual connect by rownum <= 30

2. Более короткая версия: select date '2020-01-01' rownum - 1 from xmltable('1 to 30');

Ответ №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

кроме того, при работе с датами я предпочитаю не преобразовывать их в строки до окончательного вывода, что позволяет вам по-прежнему получать правильный порядок дат и максимальную эффективность запросов.

Метки: ,