mwafi mwafi - 3 months ago 7
MySQL Question

MySQL: total users in DB aggregated over date

I need to calculate total number of users "aggregated", day by day, ex:

table users:

id create_date
0 2016-09-01 00:00:00
1 2016-09-01 00:00:00
2 2016-09-01 00:00:00
3 2016-09-02 00:00:00
4 2016-09-02 00:00:00
5 2016-09-02 00:00:00
6 2016-09-03 00:00:00
7 2016-09-03 00:00:00
8 2016-09-04 00:00:00
9 2016-09-04 00:00:00


using the following query:

select date(u.create_date),count(u.id)
from user u
group by date(u.create_date)


returns:

date(u.create_date) count(u.id)
2016-09-01 3
2016-09-02 3
2016-09-03 2
2016-09-04 2


but I need to return data aggregated like this:

date(u.create_date) count(u.id)
2016-09-01 3
2016-09-02 6
2016-09-03 8
2016-09-04 10


thx,

Note: table key "id" has holes, (Non-sequentially).

Answer

You want a cumulative sum. In MySQL, this is probably easiest using variables:

select dte, cnt, (@c := @c + cnt) as running_cnt
from (select date(u.create_date) as dte, count(u.id) as cnt
      from user u
      group by date(u.create_date)
     ) d cross join
     (select @c := 0) params
order by dte;

Note: When using aggregation with variables, I find that the subquery is necessary.

You could also do:

select d.dte,
       (select count(*)
        from users u
        where u.create_date < date_add(u.dte, interval 1 day)
       ) as running_cnt
from (select distinct date(u.create_date) as dte from user u) d;

For small amounts of data, this is fine performance-wise.