緩やかに変化する値を遡って分析したい

15912 ワード
データマネジメント SQL データ分析 BigQuery Qiitadelika SQL テキストリンク

TL;DL

  • データマネジメントにおいて、緩やかに変化する値は"Slowly Changing Dimension"と呼ばれる
  • アプリケーション側のDBが値を上書きする仕様になっていた場合、過去の値を遡って分析することが出来ない
  • "Type-2 Slowly Changing Dimension" と呼ばれる形式でログテーブルを作成すればOK

「緩やかに変化する値」の具体例

広告配信を例に考えます。

クリック課金の広告配信において、広告主(user_id)は「1Clickに最大いくらまで払えるか」という上限額(max_cpc)を設定します。
DBには以下のような値が格納されています。

user_id max_cpc created_at updated_at
1 30 2022-03-01 09:00:00 2022-03-01 09:00:00

広告配信の効果感が良いので、広告主は3月3日昼12時に「もっと高いお金を払ってもいいのでより広告を出したい」と思い、max_cpcを30円から60円に変更します。
DBが値を上書きする仕様になっていた場合、DBは以下のようになります。

user_id max_cpc created_at updated_at
1 60 2022-03-01 09:00:00 2022-03-03 12:00:00

max_cpcが30円から60円になり、updated_atの値が3月3日12時になりました。

このように緩やかに設定値が変わっていくカラムを、データマネジメントの世界では"Slowly Changing Dimension"と呼びます。
詳細: https://zenn.dev/pei0804/articles/slowly-changing-dimensions

課題

アプリケーションの機能を提供する上では上記実装に問題はありませんが、過去の値が上書きされているため、テーブルの過去の状態を復元出来ません。
ただ、当該のカラムが「いつ」「どんな値だったのか」が分析上重要になることがあります。
そこで、過去の値を遡って分析可能なログテーブルを設計します。

過去の値のログが無いのであれば、まずログを貯めるところから始めましょう。

解決策&実装例

今回は、過去の値を分析しやすい形で格納する、スナップショットテーブルを設計します

以下の2点に分けて解説します。

  • スナップショットテーブルの作成
  • 分析に使ってみる

データベースはBigQueryを想定しています。

スナップショットテーブルの作成

やりたいこと

アプリケーション側のテーブル(Before)をもとに、Type-2 Slowly Changing Dimensions と呼ばれる形式のスナップショットテーブル(After)を作ります。

Before

user_id max_cpc created_at updated_at
1 30 2022-03-01 09:00:00 2022-03-01 09:00:00

↓ 上書き

user_id max_cpc created_at updated_at
1 60 2022-03-01 09:00:00 2022-03-03 12:00:00

After

user_id max_cpc valid_from valid_to
1 30 2022-03-01 09:00:00 2022-03-03 12:00:00
1 60 2022-03-03 12:00:00 2999-12-31 23:59:59

(余談) 最新のログのvalid_toはNULLのままでもいいですが、JOINが複雑になるので絶対に取り得ない大きな値を入れておきます。
分析用途のテーブルにおけるNULLの扱いについては以下を参考にしてください。私もNULLを除きたい派です。
https://zenn.dev/pei0804/articles/donot-use-nullable-in-star-schema

実装例

まずは、アプリケーション側のテーブルを用いてスナップショットテーブルの初期状態を作成します。

CREATE TEMP FUNCTION maxDatetime() AS (DATETIME("2999-12-31T23:59:59"));

CREATE TABLE IF NOT EXISTS `snapshots` AS (
SELECT
  user_id
  , max_cpc
  # snapshot作り方参考: https://docs.getdbt.com/docs/building-a-dbt-project/snapshots
  , updated_at AS valid_from
  , maxDatetime() AS valid_to
FROM
  `log`
)

次に、差分更新を行うバッチクエリを定期的に実行します。

BEGIN TRANSACTION;

CREATE TEMP FUNCTION maxDatetime() AS (DATETIME("2999-12-31T23:59:59"));

# 何か変更が行われている(過去のログテーブルに存在しない)ログを取得
# これをログテーブルに追加する
CREATE TEMP TABLE `rows_to_append` AS (
  SELECT
    user_id
    , max_cpc
    , updated_at AS valid_from
    , maxDatetime() AS valid_to
  FROM
    `aplication_table` AS `at`
  WHERE NOT EXISTS (
    SELECT
      user_id
      , max_cpc
      , valid_from AS updated_at
    FROM
      `snapshots` AS `s`
    WHERE
      at.user_id = s.user_id
      AND at.max_cpc = s.max_cpc
      AND at.updated_at = s.valid_from
  )
);

# ログテーブルに追加されるキャンペーンの最新ログを取得
# これらのログは最新ではなくなるので"valid_to"を変更する
CREATE TEMP TABLE `rows_to_modified` AS (
  SELECT
    s.user_id
    , s.max_cpc
    , s.valid_from
    , rta.valid_from AS valid_to
  FROM
    `snapshots` AS `s`
  INNER JOIN `rows_to_append` AS `rta` USING(user_id)
  WHERE
    # 最新のログを持ってくる
    bcs.valid_to = maxDatetime()
);

# 最新ではなくなったログの"valid_to"をmaxDatetime()から追加予定のログの"updated_at"に変更
UPDATE `snapshots` AS `s`
SET valid_to = rtm.valid_to
FROM `rows_to_modified` AS `rtm`
WHERE 
  s.user_id = rtm.user_id
  AND s.valid_from = rtm.valid_from;

# 最新ログを追加
INSERT INTO `snapshots`
SELECT * FROM `rows_to_append`;

COMMIT TRANSACTION;

これでスナップショットテーブルの更新が出来ました。
更新バッチを実行する度に値の変更履歴が追記されます。

スナップショットテーブルの更新頻度は注意が必要です。
例えば日毎に更新をする場合、同じ日に値が複数回変わっても最後の変更値しか取得出来ません。
一方で、差分を検知する間隔を狭めるほどクエリ発行コストがかかります。

分析に使ってみる

スナップショットテーブルが出来たので、早速分析に使ってみましょう。
分析したいテーブルのtimestampをスナップショットテーブルのvalid_fromとvalid_toで挟み込んであげればOKです。

例えば、以下のような形式で広告クリックログがあるとします。
ログ1行 = クリック1回とします。

user_id timestamp
1 2022-03-01 09:00:00
1 2022-03-03 09:03:00
... ...

支払い上限価格を変更する毎の、1時間あたりのImpression数を集計してみます。

SELECT
  l.user_id
  , s.max_cpc
  , s.valid_from
  , s.valid_to
  , SAFE_DIVIDE(COUNT(*), TIMESTAMP_DIFF(s.valid_to, s.valid_from, HOUR)) AS count_click_per_hour
FROM
  `click_log` AS l
LEFT JOIN `snapshots` AS s
  ON l.user_id = s.user_id
  # click_logテーブルのtimestampをsnapthosテーブルで挟み込むのがキモ
  AND l.timestamp >= s.valid_from
  AND l.timestamp < s.valid_to
ORDER BY
  s.valid_from
user_id max_cpc valid_from valid_to count_click_per_hour
1 30 2022-03-01 09:00:00 2022-03-03 12:00:00 10000
1 60 2022-03-03 12:00:00 2022-03-07 18:00:00 30000
... ... ... ... ...

支払い上限額を30円→60円に上げることで、1時間あたりの広告クリック数が10000回から30000回に増えていることがわかります。

終わりに

ログテーブルを作成するのはそれなりにコストがかかるため、重要なカラムに絞ってピンポイントにログを作るのがいいと思います。
アプリケーション側のテーブルの持ち方を変える選択肢もありますが、分析用途でしか使わない場合はシュッとスナップショットテーブルを作ってしまいましょう。

過去履歴を考慮した分析を行うことで、データの力をより引き出しましょう。

参考

dbt の snapshots 機能
https://docs.getdbt.com/docs/building-a-dbt-project/snapshots

Slowly Changing Dimension
https://zenn.dev/pei0804/articles/slowly-changing-dimensions

Star Schema
https://www.amazon.co.jp/Schema-Complete-Reference-Christopher-Adamson/dp/0071744320