MySQL сгруппировать по дате и количеству, включая пропущенные даты
Ранее я делал следующее, чтобы получить количество дней в таблице отчетов.
SELECT COUNT(*) AS count_all, tracked_on
FROM `reports`
WHERE (domain_id = 939 AND tracked_on >= '2014-01-01' AND tracked_on <= '2014-12-31')
GROUP BY tracked_on
ORDER BY tracked_on ASC;
Очевидно, это не даст мне 0 отсчетов за пропущенные даты.
Тогда я наконец нашелоптимальное решение создать серию дат между заданным диапазоном дат. Но следующая проблема, с которой я столкнулся, - это присоединиться к ней с моей таблицей отчетов и сгруппировать данные по дате.
select count(*), all_dates.Date as the_date, domain_id
from (
select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) all_dates
inner JOIN reports r
on all_dates.Date >= '2014-01-01'
and all_dates.Date <= '2014-12-31'
where all_dates.Date between '2014-01-01' and '2014-12-31' AND domain_id = 939 GROUP BY the_date order by the_date ASC ;
В результате я получаю
count(*) the_date domain_id
46 2014-01-01 939
46 2014-01-02 939
46 2014-01-03 939
46 2014-01-04 939
46 2014-01-05 939
46 2014-01-06 939
46 2014-01-07 939
46 2014-01-08 939
46 2014-01-09 939
46 2014-01-10 939
46 2014-01-11 939
46 2014-01-12 939
46 2014-01-13 939
46 2014-01-14 939
...
Принимая во внимание, что я ищу заполнить недостающие даты с 0что-то вроде
count(*) the_date domain_id
12 2014-01-01 939
23 2014-01-02 939
46 2014-01-03 939
0 2014-01-04 939
0 2014-01-05 939
99 2014-01-06 939
1 2014-01-07 939
5 2014-01-08 939
...
Еще одна попытка, которую я дал, была:select count(*), all_dates.Date as the_date, domain_id
from (
select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) all_dates
inner JOIN reports r
on all_dates.Date = r.tracked_on
where all_dates.Date between '2014-01-01' and '2014-12-31' AND domain_id = 939 GROUP BY the_date order by the_date ASC ;
Результаты:
count(*) the_date domain_id
38 2014-09-03 939
8 2014-09-04 939
Минимальные данные с вышеуказанными запросами:http://sqlfiddle.com/#!2/dee3e/6