はじめに
ビジネスやプロダクトKPIをモニタリングしたい時、リリース〜現在までの顧客数や月次売上を積み上げで見たいことありますよね？
特定の地点のデータを見るのは簡単なのですが、月ごとの推移を見るのが意外と大変なので、自分がやった方法をまとめてみます
前提
出したいデータのイメージ
横軸→リリースしたときから現在
縦軸→その月時点での数値
CRMツールだったり、プロダクトのDBのデータを色々こねくり回してやろうとするのですが、多くのケースで
| 売上日 | 顧客ID | 金額 |
|---|---|---|
| 2018/01/01 | 1 | 100,000 |
| 2018/02/20 | 2 | 200,000 |
| 2018/03/23 | 3 | 300,000 |
| 2018/04/13 | 1 | 150,000 |
のような形になっているのではないでしょうか。
これを
| 月 | 顧客ID | 金額 |
|---|---|---|
| 2018/01 | 1 | 100,000 |
| 2018/02 | 1 | 100,000 |
| 2018/02 | 2 | 200,000 |
| 2018/03 | 1 | 100,000 |
| 2018/03 | 2 | 200,000 |
| 2018/03 | 3 | 300,000 |
| 2018/04 | 1 | 250,000 |
| 2018/04 | 1 | 200,000 |
| 2018/04 | 1 | 300,000 |
のようにその月時点のデータ（月ごとに複数のレコードがある場合は加算していく）を表示したいものとします
積み上げ集計の方法
1.特定期間の月ごとのレコードを生成する
SELECT
EXTRACT(YEAR FROM term) AS year
,EXTRACT(MONTH FROM term) AS month
FROM UNNEST(GENERATE_DATE_ARRAY('xxx', CURRENT_DATE)) AS term
GROUP BY 1,2
まずはこのように特定期間の月ごとのレコードを生成します
| 月 |
|---|
| 2018/01 |
| 2018/02 |
| 2018/03 |
| 2018/04 |
2.顧客のID一覧を取得する
その後に全顧客のIDを取得します
例）
SELECT
id
FROM customer
GROUP BY 1
| 顧客ID |
|---|
| 1 |
| 2 |
| 3 |
3.CROSS JOINする
これを CROSS JOINします。そうすると
| 月 | 顧客ID |
|---|---|
| 2018/01 | 1 |
| 2018/01 | 2 |
| 2018/01 | 3 |
| 2018/02 | 1 |
| 2018/02 | 2 |
| 2018/02 | 3 |
| 2018/03 | 1 |
| 2018/03 | 2 |
| 2018/03 | 3 |
| 2018/04 | 1 |
| 2018/04 | 2 |
| 2018/04 | 3 |
のような、月*顧客IDのレコードが生成されます。
4.トランザクションデータを紐付ける
これに対して、売上データなどのトランザクションデータを紐付けていきます
| 月 | 顧客ID | 金額 |
|---|---|---|
| 2018/01 | 1 | 100,000 |
| 2018/01 | 2 | 0 |
| 2018/01 | 3 | 0 |
| 2018/02 | 1 | 100,000 |
| 2018/02 | 2 | 200,000 |
| 2018/02 | 3 | 0 |
| 2018/03 | 1 | 100,000 |
| 2018/03 | 2 | 200,000 |
| 2018/03 | 3 | 300,000 |
| 2018/04 | 1 | 250,000 |
| 2018/04 | 2 | 200,000 |
| 2018/04 | 3 | 300,000 |
あとは月ごとにGROUP BYしてグラフ化すれば完成です。
顧客IDもあるので、特定の顧客のみにフィルタすることもできます
おわりに
こういったSQLで同じテーブル同士を結合する累積も試してみたのですが、テーブル内にない月は表現することができず
色々試してこのやり方にたどり着きました。
おそらくもっといい方法があるはず、、、とは思うのですが、同じように困っている方もきっといると思うので、参考になれば幸いです