RDBで昨年対比や昨月対比が出来るテーブルを考えてみる
はじめに
分析のために昨年対比や昨月対比を求めたい事は結構あります。「去年に比べて〇×がどのくらい削減されています」とか「毎月△□ずつ伸びています」とかですね。ありふれた用途ですがトランザクションテーブルのように追記しかされないとか、そもそも分析しやすいテーブルならなんら問題ないのですが、それが更新主体のマスタテーブルだったら？ 例えば、サーバ管理台帳からサーバ台数の増減を調べようとしたり、顧客マスタから有料会員の増減を調べようとしたり... DWHに既存のテーブルを突っ込んで 「さあ分析するぞ！」となって困るやつですね。元々の業務テーブルは分析の為では無く業務自体のために作られてるので仕方がない。
ここ最近、たまたま同じような質問を受けたので、ぱっと思いつた内容を整理を兼ねて書いて置きたいと思います。「もっと、こういうやり方が良いよ！」 というコメントなり指摘は大歓迎です。
TL;DR
- モダンなDWHでスナップショットを取るのがベストだと思う
- テーブルを分解して差分のみを持つのもバージョニングも出来て便利
- 懐事情とユースケースにあった方法を選ぼう！
ユースケース
とりあえず、ユースケースとして以下のような顧客マスタを考えます。is_premiumがtrueなら有料会員とします。
| id | Integer |
|---|---|
| name | Text |
| is_premium | Boolean |
| updated_at | Timestamp |
このテーブルから月ごとの有料会員数の増減を求めたいのですが、DWHにこのテーブルのミラーを常に連携するだけでは過去の履歴を持ってないので不可能ですよね？ 以下のように月毎に状態が変化したときにはどのように分析をするための仕組みを作れば良いかを考えていきます。
1月
| id | name | is_premium | updated_at |
|---|---|---|---|
| 1 | User A | TRUE | 2022/1/1 |
| 2 | User B | FALSE | 2022/1/1 |
| 3 | User C | FALSE | 2022/1/1 |
2月
| id | name | is_premium | updated_at |
|---|---|---|---|
| 1 | User A | TRUE | 2022/1/1 |
| 2 | User B | TRUE | 2022/2/1 |
| 3 | User C | FALSE | 2022/1/1 |
| 4 | User D | FALSE | 2022/2/1 |
3月
| id | name | is_premium | updated_at |
|---|---|---|---|
| 1 | User A | TRUE | 2022/1/1 |
| 2 | User B | FALSE | 2022/3/1 |
| 3 | User C | TRUE | 2022/3/1 |
| 4 | User D | TRUE | 2022/3/1 |
案1：テーブルコピー/バックアップ/スナップショットを取る
もっとも簡単なのは毎月テーブルのコピーを作る事です。例えば毎月の差分で十分なのであれば、業務DBからDWHに連携する月次バッチでUser_202201, User_202202, User_202203など必要な数だけ単純コピーしていきます。
比較のSQLも非常に簡単に作る事が出来、データ量が十分に少ないなら悪くない方法です。
一方で、変わらなかった値も持つ事になるので顧客マスタのような大きなテーブルには一般的に不向きです。ストレージ効率が非常に悪い。。。
ただし自前でテーブルのコピーを作らなくてもDWHの機能でよりスマートにこのアイデアを実現出来る事があります。
私自身は試したことが無いのですが、例えばBigQueryだとのテーブル スナップショットを作り、そこも検索対象とする事が出来るので効率的に上記を実現できます。こうしたモダンな基盤を使うのが最適解な気はしますが、それ以外の方法も検討してみましょう。
案2：バージョン管理出来る構造にする
先ほどの案は更新してないデータも持つ事でストレージ効率が悪かったという問題点がありました。また、月次とか決められたタイミングの分析しかできないので 「先週との差分も見たい」 とか 新たな要件 が出てきても対応できません。この二つの問題を解決するアイデアとして 「バージョン管理が出来るようなテーブル構造に変換する」 という方法もあります。
バージョン管理のためにはメタモデルっぽいテーブル構造にするのが簡単です。つまりテーブル構造を記述するテーブル構造にするのです。今回で言えば具体的には1つのテーブルに対して、カラム毎に別テーブルを作るというアプローチをとります。
具体的には以下のようなテーブルに分解します。
user
| id | created_at |
|---|---|
| 1 | 2022/1/1 |
| 2 | 2022/1/1 |
| 3 | 2022/1/1 |
| 4 | 2022/2/1 |
name_history
| id | user_id | value | created_at |
|---|---|---|---|
| 1 | 1 | User A | 2022/1/1 |
| 2 | 2 | User B | 2022/1/1 |
| 3 | 3 | User C | 2022/1/1 |
| 4 | 4 | User D | 2022/2/1 |
ispremium
| id | user_id | value | created_at |
|---|---|---|---|
| 0 | 1 | TRUE | 2022/1/1 |
| 1 | 2 | FALSE | 2022/1/1 |
| 2 | 3 | FALSE | 2022/1/1 |
| 3 | 2 | TRUE | 2022/2/1 |
| 4 | 4 | FALSE | 2022/2/1 |
| 5 | 2 | FALSE | 2022/3/1 |
| 6 | 3 | TRUE | 2022/3/1 |
| 7 | 4 | TRUE | 2022/3/1 |
これに以下のようなクエリを投げます。
select u.id, n.value name, p.value is_premium, p.created_at updated_at from user u
join (select id, user_id, value, created_at from name_history x where x.created_at <= '2022-02-01' group by user_id having created_at = MAX(created_at)) n on n.user_id = u.id
join (select id, user_id, value, created_at from ispremium_history x where x.created_at <= '2022-02-01' group by user_id having created_at = MAX(created_at)) p on p.user_id = u.id;
これで以下のように2月分の状態が検索できます。。
| id | name | is_premium | updated_at |
|---|---|---|---|
| 1 | User A | TRUE | 2022/1/1 |
| 2 | User B | TRUE | 2022/2/1 |
| 3 | User C | FALSE | 2022/1/1 |
| 4 | User D | FALSE | 2022/2/1 |
副問合せの中のwhere句の値を変える事で1月でも3月でも何だったら日付単位の差分でも出すことが可能です。実際はこのクエリに対して差分を出す集計クエリを直接書くのは面倒なので、これをViewにして使う事になるかと思います。
私が普段関わる範囲では業務システム自体で概念スキーマと外部スキーマはほぼ１：１なのでViewを作る事は無いのですが、データ分析だと今回のように分けたほうが良いケースも多いですよね。この柔軟性はRDBらしさかな、と。
ただし、この方法ももちろん万能ではなく、問題は計算コストが掛かり過ぎる事です。毎回計算することになりますからね。Viewの代わりにMaterialized Viewを使うことが考えられますが、その場合はストレージコストが相応に掛かってしまうのでベストとは言えません。この辺は実際のデータ量とDWHのアーキテクチャ、マシンリソース、ユースケースに強く依存するので、実際に仮組みして試してみるしか無い気がします。
もっと効率的なデータ構造/クエリはある気がしますが、まあ、今回は考え方の方向性、ということで。より良い方法があればぜひコメント等にお願いします！
案3：Event Soucing (ES)を使う
これは元も子もない話ですが、そもそも業務システム側を弄るケースです。
元も弄るのでDWH側で考慮することはなくなりシンプルですが、当然業務システムとしてそれが最善かを検討する必要あります。
CURDによる更新主体のテーブルにすることはメリットになる部分も多いので可能であれば採用を検討するのも良いと思います。このあたりの記事が参考になりますね。
まとめ
とりあえずマスタテーブルを連携したDWHでどのように昨月対比のような過去との比較や分析を行うのか？ という点で整理してみました。
個人的には使えるならモダンなアーキテクチャのDWHやDatalakeを採用するのがベターだと思いますが、必要に応じて案１や２でもやりたい事は既存のインフラでも実現出来ると思うので、コスパの良い現実的な方法を検討していきたいですね。
「こういう方法もいいよ！」ってのがあれば、ぜひ教えて下さい。
それではHappy Hacking!
Discussion