こんにちは！
株式会社バトンズでエンジニアをしているyamafです。
エンジニアはマーケや営業からデータ集計の依頼を受けることがよくあるのではないでしょうか。
以下のようなデータ集計の依頼があった場合、皆さんどのように取得しますか？
企業別の新規ユーザー登録数を月毎に出してほしい。
期間は2022年10月から2022年12月まで。
その後のデータ分析の都合上、月毎に抜けもれなくカウントを出してほしい。 例えば、その月対象企業が登録がなかった場合でもカウントは0と出してほしい。
以下のイメージ。
| 企業名 | 月 | ユーザー登録者数 |
|---|---|---|
| 株式会社AAA | 2022-10 | 3 |
| 株式会社AAA | 2022-11 | 0 |
| 株式会社AAA | 2022-12 | 1 |
| 株式会社BBB | 2022-10 | 5 |
| 株式会社BBB | 2022-11 | 0 |
| 株式会社BBB | 2022-12 | 0 |
| 株式会社CCC | 2022-10 | 0 |
| 株式会社CCC | 2022-11 | 0 |
| 株式会社CCC | 2022-12 | 3 |
私も実際に業務で似たような依頼を受けたことがあります。 一件何の論点もない簡単なデータ取得に見えますが、 少々苦戦したので途中の取得法も含めてご紹介していきます。
なお、DBはPostgreSQLを利用しており、ポイントはタイトルのとおり generate_series関数とCROSS JOIN構文になります。
※記事下部に、動作確認用のSQLを用意しておりますので、実際に動きを見たい方はそちらもご参照ください。
対応①：GROUP BY句で企業別・月別にカウントを取る
まずは、真っ先にこれが思いつく方も多いのではないでしょうか。
SELECT c.company_name, -- 企業名 to_char(cu.created_at, 'YYYY-MM') AS target_month, -- 対象月 COUNT(cu.id) AS user_count -- 対象月におけるユーザー登録数 FROM company_users cu INNER JOIN companies c ON c.id = cu.company_id GROUP BY c.company_name, target_month ORDER BY c.company_name, target_month
こちらを実行すると以下のような結果が得られます。
| 企業名 | 月 | ユーザー登録者数 |
|---|---|---|
| 株式会社AAA | 2022-10 | 3 |
| 株式会社AAA | 2022-12 | 1 |
| 株式会社BBB | 2022-10 | 5 |
| 株式会社CCC | 2022-12 | 3 |
これで、企業毎の月別ユーザー登録数が取得できますね。 ただ、これだと以下の条件をみたすことができません。
その後のデータ分析の都合上、月毎に抜けもれなくカウントを出してほしい。 例えば、その月対象企業が登録がなかった場合でもカウントは0と出してほしい。
以下のイメージ。
| 企業名 | 月 | ユーザー登録者数 |
|---|---|---|
| 株式会社AAA | 2022-10 | 3 |
| 株式会社AAA（★このレコードがない） | 2022-11 | 0 |
| 株式会社AAA | 2022-12 | 1 |
| 株式会社BBB | 2022-10 | 5 |
| 株式会社BBB（★このレコードがない） | 2022-11 | 0 |
| 株式会社BBB（★このレコードがない） | 2022-12 | 0 |
| 株式会社CCC（★このレコードがない） | 2022-10 | 0 |
| 株式会社CCC（★このレコードがない） | 2022-11 | 0 |
| 株式会社CCC | 2022-12 | 3 |
その月に登録がない場合はレコードとして抽出されないのは当然の動きですね。
さて、困りました。
ここから色々と調査・試行錯誤し、以下に記載する対応②の方法で欲しいデータを得ることができましたのでご紹介させていただきます。
対応②：generate_series関数とcross joinを活用する
結果として、こちらの方法で期待するデータを得ることができました。
SQLとしては以下のようなイメージです。
WITH month_series AS ( SELECT to_char(generate_series( '2022-10-01'::TIMESTAMP, '2022-12-01'::TIMESTAMP, '1 month'), 'YYYY-MM') AS month ), every_month_companies AS ( SELECT ms.month, c.id, c.company_name FROM month_series ms CROSS JOIN companies c ) SELECT emc.company_name, -- 企業名 emc.month AS target_month, -- 対象月 COUNT(cu.id) AS user_count -- 対象月におけるユーザー登録数 FROM every_month_companies emc LEFT OUTER JOIN company_users cu ON cu.company_id = emc.id AND to_char(cu.created_at , 'YYYY-MM') = emc.month GROUP BY emc.company_name, emc.month ORDER BY emc.company_name, emc.month
こちらを実行すると、以下のような結果が得られます。
| 企業名 | 月 | ユーザー登録者数 |
|---|---|---|
| 株式会社AAA | 2022-10 | 3 |
| 株式会社AAA | 2022-11 | 0 |
| 株式会社AAA | 2022-12 | 1 |
| 株式会社BBB | 2022-10 | 5 |
| 株式会社BBB | 2022-11 | 0 |
| 株式会社BBB | 2022-12 | 0 |
| 株式会社CCC | 2022-10 | 0 |
| 株式会社CCC | 2022-11 | 0 |
| 株式会社CCC | 2022-12 | 3 |
期待通りですね！
見慣れない関数や構文があると思いますので、このSQLについて順を追って解説していきます。
1.generate_series関数を用いて、連続した月を表す一時テーブルを生成する
WITH month_series AS ( SELECT to_char(generate_series( '2022-10-01'::TIMESTAMP, '2022-12-01'::TIMESTAMP, '1 month'), 'YYYY-MM') AS month )
上記クエリでは、WITH句を使ってmonth_seriesという名前で一時テーブルを生成しています。
ポイントはgenerate_seriesという関数で、以下の文法で連続した集合を返してくれます。
generate_series(start, stop, step)
generate_series関数は数値の連番も作れるので、大量のテストデータを用意したい場合などにも活用できますね。
今回はstartに10/1、stopに12/1を指定し、stepに1ヶ月を指定してるので、以下のようなデータを返してくれます。
| month |
|---|
| 2022-10 |
| 2022-11 |
| 2022-12 |
2.CROSS JOINを用いて、連続した月を表す一時テーブルを生成する
every_month_companies AS ( SELECT c.id, c.company_name, ms.month FROM month_series ms CROSS JOIN companies c )
上記クエリでは、WITH句を使ってevery_month_companiesという名前で一時テーブルを生成しています。
ポイントは先程作成したmonth_seriesとcompaniesテーブルを「CROSS JOIN」を使って直積結合している点です。
LEFT JOINやINNER JOINはよく使うと思いますが、CROSS JOINは聞いたことがない、または使ったことがない方も多いと思います（実際私もこの問いに対応するまで知りませんでした）
CROSS JOINについての詳細は、こちら などをご確認頂ければと思いますが、今回の例でいうと以下図のようなイメージで、２つの集合の直積（集合1の各要素と集合2の各要素を組み合わせて作られる新しい集合）を返してくれます。
このevery_month_companiesは以下のようなデータを返してくれます。 ここまでくれば、ゴールが見えてきますね！
| 企業ID | 企業名 | 月 |
|---|---|---|
| 1 | 株式会社AAA | 2022-10 |
| 2 | 株式会社BBB | 2022-10 |
| 3 | 株式会社CCC | 2022-10 |
| 1 | 株式会社AAA | 2022-11 |
| 2 | 株式会社BBB | 2022-11 |
| 3 | 株式会社CCC | 2022-11 |
| 1 | 株式会社AAA | 2022-12 |
| 2 | 株式会社BBB | 2022-12 |
| 3 | 株式会社CCC | 2022-12 |
3.LEFT JOINとGROUP BYを用いて、企業の月別レコードをベースに登録数をカウントする
SELECT emc.company_name, -- 企業名 emc.month AS target_month, -- 対象月 COUNT(cu.id) AS user_count -- 対象月におけるユーザー登録数 FROM every_month_companies emc LEFT OUTER JOIN company_users cu ON cu.company_id = emc.id AND to_char(cu.created_at , 'YYYY-MM') = emc.month GROUP BY emc.company_name, emc.month ORDER BY emc.company_name, emc.month
先程のevery_month_companiesに対して、company_users（企業内ユーザー）を左外部結合します。月別のカウントをとりたいので、結合条件は企業IDだけでなく、月も条件に加える必要があります。後は、GROUP BYで企業名と月を指定し、ユーザー数をカウントしてあげればOKです。
少々長くなりましたが、こちらで以上となります。
本記事がみなさまのデータ抽出や分析の一助となることができれば幸いです。
参考：動作を確認したい方向けのSQL
DDL(Data Definition Language)
以下のSQLでテーブルを作成します。
-- 企業テーブル CREATE TABLE companies ( id SERIAL PRIMARY KEY, company_name VARCHAR(255) NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW() ); -- ユーザーテーブル CREATE TABLE company_users ( id SERIAL PRIMARY KEY, user_name VARCHAR(255) NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW(), company_id INTEGER REFERENCES companies(id) );
DML(Data Manipulation Language)
以下のSQLで冒頭でサンプルとして記載した状態になるようなデータを投入します。
-- 企業テーブルへのデータ挿入 INSERT INTO companies (company_name, created_at) VALUES ('株式会社AAA', '2022-10-01'::TIMESTAMPTZ), ('株式会社BBB', '2022-10-01'::TIMESTAMPTZ), ('株式会社CCC', '2022-10-01'::TIMESTAMPTZ); -- ユーザーテーブルにデータを挿入 INSERT INTO company_users (user_name, created_at, company_id) VALUES ('ユーザーA1', '2022-10-01'::TIMESTAMPTZ, 1), ('ユーザーA2', '2022-10-01'::TIMESTAMPTZ, 1), ('ユーザーA3', '2022-10-01'::TIMESTAMPTZ, 1), ('ユーザーA4', '2022-12-01'::TIMESTAMPTZ, 1), ('ユーザーB1', '2022-10-01'::TIMESTAMPTZ, 2), ('ユーザーB2', '2022-10-01'::TIMESTAMPTZ, 2), ('ユーザーB3', '2022-10-01'::TIMESTAMPTZ, 2), ('ユーザーB4', '2022-10-01'::TIMESTAMPTZ, 2), ('ユーザーB5', '2022-10-01'::TIMESTAMPTZ, 2), ('ユーザーC1', '2022-12-01'::TIMESTAMPTZ, 3), ('ユーザーC2', '2022-12-01'::TIMESTAMPTZ, 3), ('ユーザーC3', '2022-12-01'::TIMESTAMPTZ, 3);
