SQLのみで、月毎のユーザ数の推移を算出する方法

Question

実現したいこと

SQL文のみで、月毎のユーザ数の推移を出力したいです。
前月の新規ユーザ数を次月に累積させる必要があります。

月毎の新規ユーザ数の推移は、算出できるのですが、月毎のユーザ数の推移はどうやってSQL書けばいいかわかりませんでした。。

経緯

BIツールを利用し始めたのですが、SQLだけでデータ作る必要がありそうで、相談させていただきました。

前提

問題提起となるテーブル(user)とデータを作ります。

※MySQL5,7もしくは8での動作を期待しております。

create table user (
   id int(10) unsigned,
   name varchar(255),
   created_at date
);

insert into user values
('1', 'a', '2022/01/01'),
('2', 'b', '2022/01/15'),
('3', 'c', '2022/02/01'),
('4', 'd', '2022/03/01'),
('5', 'e', '2022/03/15'),
('6', 'f', '2022/05/01'),
('7', 'g', '2022/06/01'),
('8', 'h', '2022/06/15'),
('9', 'i', '2022/07/01'),
('10', 'j', '2022/08/01'),
('11', 'k', '2022/09/01'),
('12', 'l', '2022/10/01'),
('13', 'm', '2022/11/01'),
('14', 'n', '2022/11/15'),
('15', 'o', '2022/12/01');

以下のSQLですと、新規ユーザ数の推移となります。

SELECT 
  DATE_FORMAT(created_at, '%Y/%m') AS month, 
  COUNT(id) AS user_num
FROM 
 user
GROUP BY 
  month 
ORDER BY 
  month

month	user_num
2022/01	2
2022/02	1
2022/03	2
2022/05	1
2022/06	2
2022/07	1
2022/08	1
2022/09	1
2022/10	1
2022/11	2
2022/12	1

Comments

[yambejp]

上記の例を利用して具体的に結果としてどういう出力を期待しているのでしょうか？

Answer 1

select m,cnt,sum(cnt) over (order by m) as rui
from(
select last_day(created_at) + interval 1 day - interval 1 month as m,
count(*) as cnt
from user
group by m) as sub

もしくはこんな感じでも

select @a:=last_day(created_at) + interval 1 day - interval 1 month as m
,count(*) as cnt
,sum(count(*)) over (order by @a)  as rui
from user
group by m

Answer 2

以下のSQLですと、新規ユーザ数の推移となります。

へのヒントで
日付を切り捨てる方法

MySQLでは「DATE_FORMAT関数」で擬似的に切り捨て可能

をSELECTの並び と GROUP BY句 に設定する。

Comments

[usop]

すみません。。ヒントを教えて頂いてるのはわかるのですが、ヒント聞いてもわからなくて、教えていただきたいです。。

Answer 3

ChatGPTに相談したら、答えを教えてくれました。。

SELECT
  DATE_FORMAT(created_at, '%Y-%m') AS month,
  COUNT(DISTINCT id) AS user_count,
  @cumulative_user := @cumulative_user + COUNT(DISTINCT id) AS cumulative_user_count
FROM
  user,
  (SELECT @cumulative_user := 0) AS init
GROUP BY
  DATE_FORMAT(created_at, '%Y-%m')
ORDER BY
  month ASC;

Comments

[yambejp]

ChatGPTは平気で嘘をつくのですぐに信じるのはやめましょう

[usop]

回答ありがとうございます！

でもこの回答に誤りはないですよね？

[Orlofsky]

> id int(10) unsigned,
id 列はint型だから、

>insert into user values
>('1', 'a', '2022/01/01'),
insert文でidの値をシングルクォーティングしない。

> COUNT(DISTINCT id) AS user_count,
DISTINCT も不要では？

[yambejp]

> でもこの回答に誤りはないですよね？

データ量次第ですかね、小規模のデータなら力技でやればいいし
データが増えてきたときに効率的な方法を理解していないなら破綻が目に見えています
本当であれば集計する列をあらかじ生成列という仕組みで作成しておくのが妥当ですが
自己解決（？）の方法で満足ならとくにこれ以上は助言しません。
（別に困るのは私ではないので）