どうも、hosigakiです！
サービスの数字を見ていく中で、昨年と今年のデータを対比したい時ってありますよね。
例えば、直近のユーザー登録数が増えている時、それが施策による影響なのか、毎年見られる時期的影響なのか、そういったことを判断するためには、昨年と今年のユーザー登録数の推移を見比べる必要があります。
そこで今日は、昨年対比をSQLで算出するための、テクニックをご紹介します！
今回使用するテーブル
| id | name | created_at |
|---|---|---|
| 1 | 太郎 | 2019-01-01 21:00:59 |
| 2 | よしお | 2019-01-01 22:01:03 |
| 3 | SAKI | 2019-01-02 05:10:47 |
| ... | ... | ... |
| 10031 | みか | 2023-03-19 13:04:50 |
今回は、上記のusersテーブルを元に解説します。
最終的に算出したいアウトプット
例えば現在が2023年3月中旬だとした場合、今回出したいアウトプットは、以下のようになります。
| month | y2022 | y2023 |
|---|---|---|
| 01 | ××× | ××× |
| 02 | ××× | ××× |
| 03 | ××× | ××× |
| 04 | ××× | 0 |
| 05 | ××× | 0 |
| 06 | ××× | 0 |
| 07 | ××× | 0 |
| 08 | ××× | 0 |
| 09 | ××× | 0 |
| 10 | ××× | 0 |
| 11 | ××× | 0 |
| 12 | ××× | 0 |
「xxx」となっているところは、その月の登録者数が入ります。
なお2023年4月以降は未来の話なので、まだ登録者数が0人です。
クエリの書き方
クエリ
SELECT
FORMAT_DATETIME("%m", created_at) AS target_month,
SUM(CASE WHEN FORMAT_DATE("%Y", created_at) = "2022" THEN 1 ELSE 0 END) AS y2022,
SUM(CASE WHEN FORMAT_DATE("%Y", created_at) = "2023" THEN 1 ELSE 0 END) AS y2023
FROM
users
WHERE
DATE(created_at) BETWEEN "2022-01-01" AND "2023-03-31"
GROUP BY
target_month
ORDER BY
target_monthクエリの解説
今回の抽出の流れは、以下になります。
①WHERE句で、対象期間のユーザーレコードのみに絞る
...
WHERE
DATE(created_at) BETWEEN "2022-01-01" AND "2023-03-31"
...今回は、昨年の1月から今年の3月までのデータを抽出しています。
②SELECT句とGROUP BY句で、各ユーザーレコードの登録月のみを抽出し、その値によりグルーピング
SELECT
FORMAT_DATETIME("%m", created_at) AS target_month,
...
GROUP BY
target_month
...FORMAT_DATETIME関数は、特定の値の情報を用いて、別の値を算出する関数です。
今回のケースでいうと、created_atカラムの日時データから、月の値のみ抽出しています。
（例えばcerated_atカラムの値が「2023-01-31 23:30:59」の場合、「01」という値が抽出されます。）
③各月別のグループ内で、2022年のレコードと2023年のレコードを、それぞれカウント
...
SUM(CASE WHEN FORMAT_DATE("%Y", created_at) = "2022" THEN 1 ELSE 0 END) AS y2022,
SUM(CASE WHEN FORMAT_DATE("%Y", created_at) = "2023" THEN 1 ELSE 0 END) AS y2023
...各グループ内で、2022年と2023年それぞれをカウントしています。
注意点
FORMAT関数で抽出した値は、数字（INT型）ではなく文字列（STRING型）となります。そのため、例えば2022年のレコードを数える際に「= 2022」と書いてしまうと、FORMAT関数で抽出した文字列が数字の「2022」に一致しているか否かという意味になってしまい、型違いによりエラーが起きてしまいます（「=」の前後は、同じ型に揃えないといけないルールがあるのです。）。
そのため、「= "2022"」とダブルクオテーションをつけ、文字列の「2022」だと明示する必要があります。
以上が、SQLで昨年対比を算出するテクニックでした！
最後まで読んでいただき、誠にありがとうございます！
また次の記事でお会いしましょう(^^)/