✨

スリーズブーケで学ぶ、Data Vault 2.0のEffectivity Satellite

に公開

経緯

仕事でData Vault 2.0について調べることがあり、その中でEffectivity Satelliteについて存在意義について悩んでいました。Linkに有効期限付けるんじゃだめなのかと。

ちょうどその時旧Twitter見たところ今月初めに行われました蓮ノ空のライブの感想とか流れてまして、各キャラ(キャスト)のユニット加入期間なんかに使えるんじゃないかと思い試してみました。ぴったりでした。なのでそのユニットの一つの、スリーズブーケで例えてみます。

別に蓮ノ空でなくても、バンドマンの加入期間なんかでも例えられると思います。

前提

蓮ノ空女学院には、スリーズブーケ、ドルケストラ、みらくらぱーく!、Edel note(105期のみ)の4つのユニットがあります。それぞれのユニットにスクールアイドルが数人所属しています。 時期によって卒業による離脱や入学による加入があります。

スリーズブーケの場合、以下の期間でキャラ・キャストが在籍しています(した)

  • 2021年度: 沙知
  • 2022年度: 沙知(秋ごろまで)、梢
  • 2023年度: 梢、花帆
  • 2024年度: 梢、花帆、吟子
  • 2025年度: 花帆、吟子
  • 2026年度: 花帆、吟子、梢(6月1日から再加入と仮定)

梢が102期、花帆が103期・・と呼ばれているのですが、学年などが現実の時間と連動している作品なので、ここでは1900を足した年度にしています。2025年は花帆のやらかしにより、新入生・編入生はEdel noteの二人だけです。2026年4月には花帆も卒業するのですが、キャストのライブが5月にあること、更に7月には明らかに102期生の梢役の人も出てくるんじゃ・・という空気が漂ってるため、ここでは2026年も3人と仮定しています。梢センパイ2026年に出なくても石投げないでね。

閑話休題して、これをData Vault 2.0で表現しますと、割といい感じで表現できます。

Data Vault 2.0とEffectivity Satelliteとは

一言でいいますと、ビッグデータ向けの、データ変更履歴の取れるデータの持ち方です。
詳細は https://data.wingarc.com/data-vault-52996 辺りを見てください。特徴としてデータをHub, Link, Satelliteなどに分けて保管することで、変更に強い構造を取ります。

  • Hub: オンラインショップの在庫管理であれば商品IDやオーダーIDなど、要素のIDぐらいのものをまとめたものです。IDは重複なく保持し、商品が100種類ならば、Hubテーブルも100行だけ持ちます
  • Satellite: Hubの要素に対する補足情報が入っているのがこちらになります。各IDに対し複数の行を持つことができ、履歴を保持することができます。各要素の属性を変更する場合、こちらのテーブルに行を追加していきます。既存の行を上書きしたり消したりはしません
  • Link: 各Hubテーブルを結ぶのに特化したテーブルです。従来のテーブルですと注文のテーブルにある商品IDや顧客IDのキーを使ってデータを結合するのですが、Data VaultではHubテーブル同志は直接結合せずに、Linkを介して結合するようになっています。これにより、Hub同志の関係が変わっても、HubをいじらずLinkだけをいじることで済むメリットがあります

その以外ににEffectivity Satelliteというのがありまして、それも一言で言うと「Linkテーブルの構造に加え、データの有効期間の列も加えたテーブル」になります。

スリーズブーケの在籍状況をData Vault 2.0で表すとこうなる

これはあくまでモデリングの一例ですが、こう表すことができます。

  • HUB_MEMBER: 花帆とか吟子といった、各キャラを入れるテーブルです。本当は更にキャラの詳細を入れたSATELLITE_MEMBERもあるかと思いますが、今回は無くても説明できるため省略します
  • HUB_UNIT: スリーズブーケ、ドルケストラなどのユニット名を入れたテーブルになります
  • LINK_MEMBER_UNIT: キャラとユニットを関連付けるテーブルになります
  • EFF_SAT_MEMBER_UNIT: これが今回の説明の大事な部分です。キャラがいつそのユニットに居たのかを管理するテーブルになります。なお今回はLINKキーを持たせていますが、Effectivity Satellite自体が直接Hubのハッシュキーを持たせても良いようです

このE-R図を見ても理解できないと思うので、具体的なデータも置いておきます。

-- Hub: メンバー
CREATE TABLE hub_member (
    member_hk BINARY(16) PRIMARY KEY,
    member_id VARCHAR(50),
    load_date TIMESTAMP,
    record_source VARCHAR(50)
);

-- Hub: ユニット
CREATE TABLE hub_unit (
    unit_hk BINARY(16) PRIMARY KEY,
    unit_id VARCHAR(50),
    load_date TIMESTAMP,
    record_source VARCHAR(50)
);

-- Link: メンバーとユニットの関係
CREATE TABLE link_member_unit (
    member_unit_hk BINARY(16) PRIMARY KEY,
    member_hk BINARY(16),
    unit_hk BINARY(16),
    load_date TIMESTAMP,
    record_source VARCHAR(50),
    FOREIGN KEY (member_hk) REFERENCES hub_member(member_hk),
    FOREIGN KEY (unit_hk) REFERENCES hub_unit(unit_hk)
);

-- Effectivity Satellite: 所属期間
CREATE TABLE eff_sat_member_unit (
    member_unit_hk BINARY(16),
    load_date TIMESTAMP,
    load_end_date TIMESTAMP,  -- NULL = 現在も有効
    record_date TIMESTAMP,
    record_source VARCHAR(50),
    PRIMARY KEY (member_unit_hk, load_date),
    FOREIGN KEY (member_unit_hk) REFERENCES link_member_unit(member_unit_hk)
);
-- Hub: メンバーデータ
INSERT INTO hub_member VALUES
(UNHEX(MD5('沙知')), '沙知', '2021-04-01 00:00:00', 'SYSTEM'),
(UNHEX(MD5('梢')), '梢', '2022-04-01 00:00:00', 'SYSTEM'),
(UNHEX(MD5('花帆')), '花帆', '2023-04-01 00:00:00', 'SYSTEM'),
(UNHEX(MD5('吟子')), '吟子', '2024-04-01 00:00:00', 'SYSTEM');

-- Hub: ユニットデータ
INSERT INTO hub_unit VALUES
(UNHEX(MD5('スリーズブーケ')), 'スリーズブーケ', '2000-04-01 00:00:00', 'SYSTEM');

-- Link: メンバーとユニットの関係
INSERT INTO link_member_unit VALUES
(UNHEX(MD5(CONCAT('沙知', 'スリーズブーケ'))), UNHEX(MD5('沙知')), UNHEX(MD5('スリーズブーケ')), '2021-04-01 00:00:00', 'SYSTEM'),
(UNHEX(MD5(CONCAT('梢', 'スリーズブーケ'))), UNHEX(MD5('梢')), UNHEX(MD5('スリーズブーケ')), '2022-04-01 00:00:00', 'SYSTEM'),
(UNHEX(MD5(CONCAT('花帆', 'スリーズブーケ'))), UNHEX(MD5('花帆')), UNHEX(MD5('スリーズブーケ')), '2023-04-01 00:00:00', 'SYSTEM'),
(UNHEX(MD5(CONCAT('吟子', 'スリーズブーケ'))), UNHEX(MD5('吟子')), UNHEX(MD5('スリーズブーケ')), '2024-04-20 00:00:00', 'SYSTEM');

-- Effectivity Satellite: 所属期間
INSERT INTO eff_sat_member_unit VALUES
-- 沙知: 2021年4月~2022年10月31日
(UNHEX(MD5(CONCAT('沙知', 'スリーズブーケ'))), '2021-04-01 00:00:00', '2022-10-31 23:59:59', '2025-12-01 00:00:00', 'SYSTEM'),
-- 梢: 2022年4月~2024年3月
(UNHEX(MD5(CONCAT('梢', 'スリーズブーケ'))), '2022-04-01 00:00:00', '2024-03-31 23:59:59', '2025-12-01 00:00:00', 'SYSTEM'),
-- 花帆: 2023年4月から現在まで
(UNHEX(MD5(CONCAT('花帆', 'スリーズブーケ'))), '2023-04-01 00:00:00', NULL, '2025-12-01 00:00:00', 'SYSTEM'),
-- 吟子: 2024年4月から現在まで
(UNHEX(MD5(CONCAT('吟子', 'スリーズブーケ'))), '2024-04-20 00:00:00', NULL, '2025-12-01 00:00:00', 'SYSTEM');

各種日にちは公開されてないので、適当に置いています。重要なのはある期間スリーズブーケに属していて、それ以外は属していない(卒業している)です。

(更に厳密にいうと沙知先輩は102期には3ユニット同時に属してましたし、101期には3年生(100期)の先輩方と活動してたかもしれませんが、説明がややこしくなるので省きます)

このデータ構造を使うことで、仮に梢センパイが再加入する場合も、既存のHub, Linkを変更することなくEFF_SAT_MEMBER_UNITに加えることで表現できます。(12月1日にはまだ知りませんでしたが、5thライブの最後に劇場版で年度末に帰ってくることが判明しました。多分劇中では2026/3/31なんでしょうけど、キャストさんが7月ぐらいに復帰しそうなのでその設定で書いておきます)

-- Effectivity Satellite: 梢の再加入レコードを追加
INSERT INTO eff_sat_member_unit VALUES
(UNHEX(MD5(CONCAT('梢', 'スリーズブーケ'))), '2026-06-01 00:00:00', NULL, '2025-12-10 01:00:00','SYSTEM');

逆に沙知先輩の加入期間を間違えた、となった場合は以下のようにレコードを追加することで状態を更新できます。

-- 訂正レコード追加
INSERT INTO eff_sat_member_unit VALUES
(UNHEX(MD5(CONCAT('沙知', 'スリーズブーケ'))), 
 '2021-04-01 00:00:00',        -- 加入開始日(同じ)
 '2022-09-30 23:59:59',        -- 脱退日(訂正後)
 '2025-12-10 10:30:00',        -- レコード登録日時(訂正時)
 'SYSTEM_CORRECTION');

もしくは加入期間の概念自体消したいんであれば、脱退日まで一緒にして、その後に打ち消しのフラグなどを持たせて同時に新しい属性を追加する方法もありそうです。

この状態で、例えば2023年10月時点のメンバーを調べたければ、以下のように書くことで取り出せます。

-- 2023年10月のメンバーを取得
WITH latest_records AS (
    SELECT 
        es.*,
        ROW_NUMBER() OVER (
            PARTITION BY es.member_unit_hk, es.load_date 
            ORDER BY es.record_date DESC
        ) as rn
    FROM eff_sat_member_unit es
)
SELECT DISTINCT hm.member_id
FROM latest_records es
JOIN link_member_unit lmu ON es.member_unit_hk = lmu.member_unit_hk
JOIN hub_member hm ON lmu.member_hk = hm.member_hk
JOIN hub_unit hu ON lmu.unit_hk = hu.unit_hk
WHERE hu.unit_id = 'スリーズブーケ'
  AND es.rn = 1  -- 最新レコードのみ
  AND '2023-10-01' BETWEEN es.load_date 
      AND COALESCE(es.load_end_date, '9999-12-31')
ORDER BY hm.member_id;

結果:

member_id
---------
梢
花帆

duckdbで軽く試せました。duckdbはblob型にサイズとか指定しないようなので、BINARY(16)をBINARYに直してください。

現実の時間で、ユーザからみた加入期間の変化

題名を少し難しく書きましたが、要はいつ時点での登録データで出力するかということも、この構造で出すことができます。

これまでは単純に特定日付の所属者だけ出しましたが、load_dateを2025/12/09以前とかに制限することで、その日時点の情報(=梢センパイは復帰するかわからない)のデータで出すことができます。

2025/12/1時点ではいつ復帰しそうかわからないので、その時点の加入期間の結果を出すとこうなります。

WITH latest_records AS (
      SELECT
          es.*,
          ROW_NUMBER() OVER (
              PARTITION BY es.member_unit_hk, es.load_date
              ORDER BY es.record_date DESC
          ) as rn
      FROM eff_sat_member_unit es
      WHERE es.record_date <= '2025-12-01 23:59:59'  -- この時点までに記録されたもの
  )
  SELECT
      hm.member_id,
      es.load_date AS 加入日,
      es.load_end_date AS 脱退日,
      es.record_date AS レコード登録日時
  FROM latest_records es
  JOIN link_member_unit lmu ON es.member_unit_hk = lmu.member_unit_hk
  JOIN hub_member hm ON lmu.member_hk = hm.member_hk
  WHERE hm.member_id = '梢'
    AND es.rn = 1
  ORDER BY es.load_date;
┌───────────┬─────────────────────┬─────────────────────┬─────────────────────┐
│ member_id │       加入日        │       脱退日        │  レコード登録日時   │
│  varchar  │      timestamp      │      timestamp      │      timestamp      │
├───────────┼─────────────────────┼─────────────────────┼─────────────────────┤
│ 梢        │ 2022-04-01 00:00:00 │ 2024-03-31 23:59:59 │ 2025-12-01 00:00:00 │
└───────────┴─────────────────────┴─────────────────────┴─────────────────────┘

一方で、5thライブツアーの終わり(2025/12/10)には梢センパイが戻ってくることが示唆されましたため、その時点での所属期間(願望)は以下の通り出てくるわけです。

WITH latest_records AS (
      SELECT
          es.*,
          ROW_NUMBER() OVER (
              PARTITION BY es.member_unit_hk, es.load_date
              ORDER BY es.record_date DESC
          ) as rn
      FROM eff_sat_member_unit es
      WHERE es.record_date <= '2025-12-10 23:59:59'  -- この時点までに記録されたもの
  )
  SELECT
      hm.member_id,
      es.load_date AS 加入日,
      es.load_end_date AS 脱退日,
      es.record_date AS レコード登録日時
  FROM latest_records es
  JOIN link_member_unit lmu ON es.member_unit_hk = lmu.member_unit_hk
  JOIN hub_member hm ON lmu.member_hk = hm.member_hk
  WHERE hm.member_id = '梢'
    AND es.rn = 1
  ORDER BY es.load_date;
┌───────────┬─────────────────────┬─────────────────────┬─────────────────────┐
│ member_id │       加入日        │       脱退日        │  レコード登録日時   │
│  varchar  │      timestamp      │      timestamp      │      timestamp      │
├───────────┼─────────────────────┼─────────────────────┼─────────────────────┤
│ 梢        │ 2022-04-01 00:00:00 │ 2024-03-31 23:59:59 │ 2025-12-01 00:00:00 │
│ 梢        │ 2026-06-01 00:00:00 │ NULL                │ 2025-12-10 01:00:00 │
└───────────┴─────────────────────┴─────────────────────┴─────────────────────┘

ここで重要なのは、梢センパイが帰ってくるかどうかもですが、それよりも 過去の特定の時点の情報が今のデータの持ち方だけでそのまま取れる というところにあります。単純にデータを持つだけですと、過去の全データのバックアップを取る必要がありましたが、この方法ですとその必要が無いわけです。

またEffectivity Satelliteが期間の日付を二週類持つ理由も、これで説明できそうです。花帆ちゃん達のスリーズブーケ加入期間と、それを私らが知った日は違うじゃ無いですか。つまりそういうことです。

終わりに

この記事ではData Vault 2.0のEffectivity Satelliteの使い方を、スリーズブーケの加入期間で例えて解説してみました。そもそもData Vault自体ビッグデータ扱うところでなければ有効性発揮しないですのでニッチな話かと思いますが、もし触れる機会がありましたらご参考になればと思います。
私自身まだ勉強中ですので、認識違うところが多々あるかもしれません。

Discussion