Zenn
🦁

MySQLで累計を取得するためのSQL

2022/06/23に公開
MySQL
SQL
#
RDB
tech

きっかけ

ある日PMにこんなSQLを書いてくれと言われました。

id name created_at
1 sato 2022-06-01
2 tanaka 2022-06-01
3 suzuki 2022-06-02
4 mori 2022-06-04

こんなUsersテーブルがあるから、6月のユーザー数の遷移（累計）を書いてほしい。
この依頼がきた時はなーんだ簡単じゃん！と思ったのですが、よく考えてみるとgroup by created_atとか書いたらその日に登録された数は出せるけど累計出せないし、created_atも登録されてない日付もあって日付が飛び飛びになっちゃうしでいろいろ悩んだので記事にしました。

SQL

SELECT date_table.date,	count(users.id) as count
FROM
  (SELECT
     DATE_FORMAT(DATE_ADD(DATE("2022-06-01"), interval @num:=@num+1 day), '%Y-%m-%d') AS date
   FROM
     (SELECT table_name FROM information_schema.COLUMNS) as a,
     (SELECT @num:=-1) as b
  ) AS date_table
left outer join users on users.created_at <= date_table.date
WHERE date_table.date <= DATE("2022-06-30")
group by date_table.date
date count
2022-06-01 2
2022-06-02 3
... ...
2022-06-30 4

解説

日付一覧を表示する

まず累計を出すためには、6月の日付一覧を表示する必要があります。
information_schema.COLUMNSテーブルからselectし、適当に行を表示。（この部分はこのサイト[1]がわかりやすいです！）
@numをインクリメントしていき、6/1に一日ずつ足していきます。

as a、as bはサブクエリに名前をつけろと怒られるので適当な名前つけてます(*´-`)

こちらが日付一覧を取得する部分を抽出したSQLです。

SELECT date_t.date
FROM
  (SELECT
     DATE_FORMAT(DATE_ADD(DATE("2022-06-01"), interval @num:=@num+1 day), '%Y-%m-%d') AS date
   FROM
     (SELECT table_name FROM information_schema.COLUMNS) as a,
     (SELECT @num:=-1) as b
  ) AS date_t
 WHERE  date_t.date <= DATE("2022-06-30")

このSQLでこんなデータが取得できます。

date
2022-06-01
2022-06-02
...
2022-06-30

ユーザー数を取得する

外部結合の部分で=ではなく、<=で結合し、 group byとcountで集計すれば完璧です！

感想

ユーザー数の累計データ見たい！みたいな依頼はどの会社でも発生してそうなのに、なかなか記事が見つからないのが意外でした（´-`）.｡oO
（累計SQLの記事はなくはなかったのですが、日付が飛び飛びじゃないのが前提な記事ばかりでした）
もしもっといい方法で書けるよ！ってあったらぜひ教えてください！

脚注

  1. https://tadtadya.com/mysql-mariadb-make-date-list-with-search-query-sql/ ↩︎

Discussion