Как вернуть нули по месяцам, которых нет в базе данных

Question

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

Следующий запрос возвращает количество людей, зарегистрированных в месяцах в базе данных:

select 
    extract(year from "DATECREATED") as YEAR, 
    extract(month from "DATECREATED") as mon,
    count("USERID") as Total
from 
    TBLG2O_USEROFO 
where 
    extract(year from "DATECREATED") = '2014'
group by 
    extract(year from "DATECREATED"), 
    extract(month from "DATECREATED")
order by 
    1, 2

Answer 1

К сожалению, для этого вам нужна вторая таблица, которая просто содержит номер месяца в строке.

Предполагая, что вы создаете таблицу с именем MONTHS с одним столбцом, MONTH_NUMBERВы можете использовать этот запрос для создания искомого результата - он будет подсчитывать количество строк в месяц для указанного года и показывать нули для любого месяца без строк.

Обратите внимание, что этот запрос также имеет недостаток - он будет работать только в течение календарного года, поэтому вы не сможете разбить его по годам (скажем, июль 2013 - июнь 2014).

SELECT COALESCE(YEAR, 2014) AS YEAR,
       MONTHS.MONTH_NUMBER  AS MONTH,
       COALESCE(TOTAL, 0)   AS "MONTH COUNT"
  FROM (SELECT EXTRACT(YEAR FROM DATECREATED) AS YEAR,
               EXTRACT(MONTH FROM DATECREATED) AS MONTH,
               COUNT(DATECREATED) AS TOTAL
          FROM TBLG2O_USEROFO            
         WHERE EXTRACT(YEAR FROM DATECREATED) = '2014'
         GROUP BY EXTRACT(YEAR FROM DATECREATED), 
                  EXTRACT(MONTH FROM DATECREATED)) LFT
      RIGHT OUTER JOIN MONTHS
               ON MONTH_NUMBER = LFT.MONTH
 ORDER BY YEAR, MONTH

Смотрите этот запрос в действии на SQL Fiddle.

По сути, это Oracle-версия ответа @Eran's MySQL.

Answer 2

Я проверил это на mysql, поэтому вам может потребоваться немного поработать над оракулом.

Сначала создайте таблицу с месяцами года...

create table months(month int);
insert into months values(1);
insert into months values(2);
insert into months values(3);
insert into months values(4);
insert into months values(5);
insert into months values(6);
insert into months values(7);
insert into months values(8);
insert into months values(9);
insert into months values(10);
insert into months values(11);
insert into months values(12);

Используйте правильное соединение с таблицей месяцев, чтобы достичь желаемого эффекта:

select coalesce(YEAR, 2014) year, m.month, coalesce(Total, 0) as total 
from 
  (select year(DATECREATED) as YEAR, month(c. DATECREATED) month, count("DATECREATED") as Total 
  from comments c
  where year(DATECREATED) ='2014'
  group by year, month(c.date_sent)) as c
right join months m
on c.month = m.month
order by year, month

Если вы не можете добавить временную таблицу, вот способ обойти это:

select coalesce(YEAR, 2014) year, m.month, coalesce(Total, 0) as total 
from 
  (select year(DATECREATED) as YEAR, month(c. DATECREATED) month, count("DATECREATED") as Total 
  from comments c
  where year(DATECREATED) ='2014'
  group by year, month(c.date_sent)) as c
right join
  (select 1 as month union select 2 as month union select 3 as month 
   union select 4  as month union select 5 as month union select 6 as month 
   union select 7 as month union select 8 as month union select 9 as month 
   union select 10 as month union select 11 as month union select 12 as month) m
on c.month = m.month
order by year, month

Answer 3

В Oracle, когда вам нужно "синтезировать" строки, которые непосредственно не присутствуют в базе данных, вы можете использовать предложение моделирования. ключевое слово модели

Я использовал SQL Fiddle Джареда для создания еще более простого примера, без какой-либо второй таблицы:

select * from
(
  select count(1) cnt, trunc(datecreated, 'MONTH') dt
  from TBLG2O_USEROFO
  group by trunc(datecreated, 'MONTH')
)
model
--partition by (department)
dimension by (dt)
measures (cnt, cast (null as number) tech_cnt)
rules sequential order(
    tech_cnt[ for dt from to_date('2013-01-01','RRRR-MM-DD') 
                       to to_date('2014-12-31','RRRR-MM-DD') 
                increment NUMTOYMINTERVAL(1,'MONTH')] 
    = nvl(cnt[cv(dt)], 0)
)
order by dt;

Представьте, что результатом запроса является лист Excel. Тогда назначение tech_cnt[dt] = nvl(cnt[cv(dt), 0) является "макросом Excel".