質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.44%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Q&A

解決済

3回答

553閲覧

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

usop

総合スコア64

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

0グッド

1クリップ

投稿2023/02/21 07:53

編集2023/02/21 08:00

実現したいこと

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

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

経緯

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

前提

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

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

SQL

1create table user ( 2 id int(10) unsigned, 3 name varchar(255), 4 created_at date 5); 6 7insert into user values 8('1', 'a', '2022/01/01'), 9('2', 'b', '2022/01/15'), 10('3', 'c', '2022/02/01'), 11('4', 'd', '2022/03/01'), 12('5', 'e', '2022/03/15'), 13('6', 'f', '2022/05/01'), 14('7', 'g', '2022/06/01'), 15('8', 'h', '2022/06/15'), 16('9', 'i', '2022/07/01'), 17('10', 'j', '2022/08/01'), 18('11', 'k', '2022/09/01'), 19('12', 'l', '2022/10/01'), 20('13', 'm', '2022/11/01'), 21('14', 'n', '2022/11/15'), 22('15', 'o', '2022/12/01');

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

SQL

1SELECT 2 DATE_FORMAT(created_at, '%Y/%m') AS month, 3 COUNT(id) AS user_num 4FROM 5 user 6GROUP BY 7 month 8ORDER BY 9 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

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

yambejp

2023/02/21 08:01

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

回答3

0

SQL

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

もしくはこんな感じでも

SQL

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

投稿2023/02/21 10:58

編集2023/02/21 11:23
yambejp

総合スコア115275

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

0

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

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

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

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

投稿2023/02/21 08:19

Orlofsky

総合スコア16415

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

usop

2023/02/21 08:28

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

0

自己解決

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

SQL

1SELECT 2 DATE_FORMAT(created_at, '%Y-%m') AS month, 3 COUNT(DISTINCT id) AS user_count, 4 @cumulative_user := @cumulative_user + COUNT(DISTINCT id) AS cumulative_user_count 5FROM 6 user, 7 (SELECT @cumulative_user := 0) AS init 8GROUP BY 9 DATE_FORMAT(created_at, '%Y-%m') 10ORDER BY 11 month ASC;

投稿2023/02/21 08:08

編集2023/02/21 08:10
usop

総合スコア64

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

yambejp

2023/02/21 10:59

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

2023/02/21 11:30

回答ありがとうございます! でもこの回答に誤りはないですよね?
Orlofsky

2023/02/21 14:34

> 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

2023/02/22 03:44 編集

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

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.44%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問