プログラミング

SQLで日ごとに累計したい

2014年10月23日

累計はプログラム側でやるしかないと思ってたけど、SQLだけできるってことをいまさら知った。

会員数の推移を把握したい場合の例

usersテーブル

idnamecreated_at
1user12014-10-20 00:00:00
2user22014-10-20 00:00:00
3user32014-10-21 00:00:00
4user42014-10-22 00:00:00
5user52014-10-22 00:00:00

日毎に集計する

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_oncount
2014-10-202
2014-10-211
2014-10-222

日毎に累計する

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;

結果

created_oncount
2014-10-202
2014-10-213
2014-10-225

2021/10/03追記

MySQLのバージョンがあがってエラーになっていた、ANY_VALUE()が必要になっていたので修正したのがこちら。

MySQLはmacOSのHomebrewでインストールしたVersion 8.0.26。

queries.txt

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;

queries.txtを実行する(mysql -uroot < queries.txt)

created_oncount
2014-10-202
2014-10-211
2014-10-222
created_oncount
2014-10-202
2014-10-213
2014-10-225

-プログラミング
-