Pergunta simples, resposta complicada.
Quero retornar o número de cadastros nos últimos 10 dias via group by.
Problema: Não houve cadastros em alguns dias, logo aparecem buracos no meu result set.
mysql> select count(1), date(regdate) reg from users where regdate > date(now()) - 10 group by reg order by reg desc;
±---------±-----------+
| count(1) | reg |
±---------±-----------+
| 12 | 2008-02-26 |
| 16 | 2008-02-25 |
| 25 | 2008-02-24 |
| 46 | 2008-02-23 |
| 87 | 2008-02-22 |
| 195 | 2008-02-21 |
| 1 | 2008-02-20 |
±---------±-----------+
Solução via bacalhau: (tenho que subtrair 1 de todas as datas)
mysql> select sum(t), m from (
-> select count(1) t, date(regdate) m from users where regdate > date(now()) - 10 group by m
-> UNION ALL
-> select 1,t.d d from (
-> select date(now()) d from dual union
-> select date(date(now()) - 1) d from dual union
-> select date(date(now()) - 2) d from dual union
-> select date(date(now()) - 3) d from dual union
-> select date(date(now()) - 4) d from dual union
-> select date(date(now()) - 5) d from dual union
-> select date(date(now()) - 6) d from dual union
-> select date(date(now()) - 7) d from dual union
-> select date(date(now()) - 8) d from dual union
-> select date(date(now()) - 9) d from dual
-> ) t) tt group by m order by m desc;
±-------±-----------+
| sum(t) | m |
±-------±-----------+
| 13 | 2008-02-26 |
| 17 | 2008-02-25 |
| 26 | 2008-02-24 |
| 47 | 2008-02-23 |
| 88 | 2008-02-22 |
| 196 | 2008-02-21 |
| 2 | 2008-02-20 |
| 1 | 2008-02-19 |
| 1 | 2008-02-18 |
| 1 | 2008-02-17 |
±-------±-----------+
10 rows in set (0.01 sec)
Alguém saberia uma solução mais bela para isso em SQL ???
