mysql按天和属性计算上个月内的记录

I'm sure someone can answer this one easy, it is making my already poor brain hurt.

我敢肯定有人可以轻松回答这一点,这让我已经很穷的大脑受伤了。

I have a table of users with:

我有一个用户表:

created_date
id
user_type

I am trying to write a query in MYSQL that would count back from today for 30 days and spit back the count of new users added per day (expecting some days to probably be 0) by user_type

我试图在MYSQL中编写一个查询,从今天起计算30天,然后用user_type回吐每天新增用户数(预计有些天可能为0)

Would like a result similar to:

想得到类似的结果:

date:      | count(id[user_type = 1]) | count (id[user_type = 2])
2015-02-09           10                      9
2015-02-08            0                      10
2015-02-07            8                      0

and so on....

等等....

Thanks!

2 个解决方案

#1

use GROUP BY, and look into the time functions http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_day

使用GROUP BY,并查看时间函数http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_day

... GROUP BY MONTH(created_date), DAY(created_date)

... GROUP BY MONTH(created_date),DAY(created_date)

#2

Here's an approach that will work. [http://sqlfiddle.com/#!2/090e19/24]

这是一种有效的方法。 [http://sqlfiddle.com/#!2/090e19/24]

select
    date_sub(date(now()), interval (d0.n0 + d1.n1 * 5) day),
    count(case when user_type = 1 then 1 else null end),
    count(case when user_type = 2 then 1 else null end)
from
    (select 0 as n0 union all select 1 union all select 2 union all select 3 union all select 4) as d0
    cross join
    (select 0 as n1 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5) as d1
    left outer join users as u on u.created_date = date_sub(date(now()), interval (d0.n0 + d1.n1 * 5) day)
        and u.created_date >= date_sub(date(now()), interval 29 day)
group by date_sub(date(now()), interval (d0.n0 + d1.n1 * 5) day)
order by date_sub(date(now()), interval (d0.n0 + d1.n1 * 5) day) desc;

#1