select
date_format(a.created_at, '%Y-%m-%d') as created_on,
count(*) as count
from
users a
group by
created_on
order by
created_on;
結果
created_on
count
2014-10-20
2
2014-10-21
1
2014-10-22
2
日毎に累計する
select
date_format(a.created_at, '%Y-%m-%d') as created_on,
(
select
count(*)
from
users
where
date_format(created_at, '%Y-%m-%d') <= date_format(a.created_at, '%Y-%m-%d')
) as count
from
users a
group by
created_on
order by
created_on;
create database if not exists sample_database;
use sample_database;
drop table users;
create table users (
id integer auto_increment not null,
name varchar(256) not null,
created_at datetime not null,
primary key (id)
);
insert into users (name, created_at) values
('user1', '2014-10-20 18:17:53'),
('user2', '2014-10-20 18:17:57'),
('user3', '2014-10-21 18:18:03'),
('user4', '2014-10-22 18:18:14'),
('user5', '2014-10-22 18:18:18');
select
date_format(a.created_at, '%Y-%m-%d') as created_on,
count(*) as count
from
users a
group by
created_on
order by
created_on;
select
date_format(a.created_at, '%Y-%m-%d') as created_on,
ANY_VALUE(
(
select
count(*)
from
users
where
date_format(created_at, '%Y-%m-%d') <= date_format(a.created_at, '%Y-%m-%d')
)
) as count
from
users a
group by
created_on
order by
created_on;