Zenn
コラボスタイル DevelopersPublicationへの投稿
📦

イミュータブルデータモデルには PostgreSQL の View を使う

山とコード
2023/09/08に公開
PostgreSQL
SQL
#
イミュータブルデータモデル
#
イミュータブルデータモデリング
tech

イミュータブルデータモデルだと、クエリが複雑になる？

以下の記事で、イミュータブルデータモデリングにおける削除フラグの考察をしていきました。

https://zenn.dev/collabostyle/articles/15a6f1f8562281

ユーザーの情報を管理したいだけなのに、テーブルが増えてしまい、
データの取得が複雑になるのでは？と考える方も多いでしょう...

それは確かに複雑になると思います。

ただ、その 複雑さを View の中に閉じ込めること ができます。

PostgreSQL の View を活用する

View とは

データベース内のテーブルから抽出された結果セットを表現する仮想的なテーブルです。

View は既存のテーブルから必要なデータを選択し、組み合わせ、計算、または変換して、
より "使いやすい" 形式でデータにアクセスできるようにするのに役立ちます。

View 作成クエリ

CREATE VIEW VIEWの名前 AS
    SELECT文
;

最新の作成日時のレコードだけが欲しい

以下のように、イミュータブルに設計したテーブルがあるとして、
現在のユーザー情報の一覧を取得できるようにしていきましょう。

users

id
1
2
3

user_detail_histories

id user_id email created_at created_by name
1 1 taro.t@example.com 2023-09-05 22:44:15.548 1 田中 太郎
2 2 sato_jiro@example.com 2023-09-07 23:18:21.246 2 佐藤 次郎
3 3 hanako.yamada@example.com 2023-09-05 22:47:03.947 1 山田 花子
4 3 hanako@example.com 2023-09-05 22:48:26.897 1 山田 花子
5 3 hanako@example.com 2023-09-05 22:48:28.854 1 山本 花子

user_status_histories

id user_id status created_at created_by
1 1 active 2023-09-04 22:51:47.042 1
2 2 active 2023-09-05 20:52:23.720 1
3 2 archived 2023-09-05 21:53:00.727 3
4 3 active 2023-09-05 22:52:26.699 1
5 3 archived 2023-09-05 22:52:39.295 1
6 3 active 2023-09-05 22:52:59.921 1
7 3 deleted 2023-09-07 23:18:49.705 1
  • ユーザーIDが「1」の田中さんは、詳細履歴もステータス履歴も 1 件のみ存在しています。
  • ユーザーIDが「2」の佐藤さんは、詳細履歴は 1 件ですが、ステータス履歴を 2 件持っています。
    • archived が最後に行われているので、ステータスは「archived」になる
  • ユーザーIDが「3」の山本さん（旧姓：山田）は、詳細履歴は 3 件、ステータス履歴を 4 件持っています。
    • メールアドレス、名前が変更されている
    • ステータスも複数回変更があった、最後には削除されている

つまり、取得したい情報は以下のようになります。

id name email status updated_at
1 田中 太郎 taro.t@example.com active 2023-09-05 22:44:15.548
2 佐藤 次郎 sato_jiro@example.com archived 2023-09-07 23:18:21.246
3 山本 花子 hanako@example.com deleted 2023-09-07 23:18:49.705

各ユーザーIDごとの最新作成日時のレコードを紐づけて、ユーザー詳細やステータスを取得する必要があります。

クエリを考えてみる

やみくもにテーブルを JOIN しても、取得したい形にはなりません。

select 
    u.*,
    udh.*,
    ush.*
from
    users u 
inner join
    user_detail_histories udh 
    on udh.user_id = u.id 
inner join
    user_status_histories ush 
    on ush.user_id = u.id
order by
    u.id asc
;

結果

id id user_id email created_at created_by name id user_id status created_at created_by
1 1 1 taro.t@example.com 2023-09-05 22:44:15.548 1 田中 太郎 5 1 active 2023-09-07 22:51:47.042 1
2 2 2 sato_jiro@example.com 2023-09-07 23:18:21.246 2 佐藤 次郎 7 2 archived 2023-09-07 22:53:00.727 3
2 2 2 sato_jiro@example.com 2023-09-07 23:18:21.246 2 佐藤 次郎 6 2 active 2023-09-07 22:52:23.720 1
3 3 3 hanako.yamada@example.com 2023-09-05 22:47:03.947 1 山田 花子 1 3 active 2023-09-05 22:52:26.699 1
3 4 3 hanako@example.com 2023-09-05 22:48:26.897 1 山田 花子 4 3 deleted 2023-09-07 23:18:49.705 1
3 4 3 hanako@example.com 2023-09-05 22:48:26.897 1 山田 花子 3 3 active 2023-09-05 22:52:59.921 1
3 4 3 hanako@example.com 2023-09-05 22:48:26.897 1 山田 花子 2 3 archived 2023-09-05 22:52:39.295 1
3 4 3 hanako@example.com 2023-09-05 22:48:26.897 1 山田 花子 1 3 active 2023-09-05 22:52:26.699 1
3 5 3 hanako@example.com 2023-09-05 22:48:28.854 1 山本 花子 4 3 deleted 2023-09-07 23:18:49.705 1
3 5 3 hanako@example.com 2023-09-05 22:48:28.854 1 山本 花子 3 3 active 2023-09-05 22:52:59.921 1
3 5 3 hanako@example.com 2023-09-05 22:48:28.854 1 山本 花子 2 3 archived 2023-09-05 22:52:39.295 1
3 3 3 hanako.yamada@example.com 2023-09-05 22:47:03.947 1 山田 花子 4 3 deleted 2023-09-07 23:18:49.705 1
3 5 3 hanako@example.com 2023-09-05 22:48:28.854 1 山本 花子 1 3 active 2023-09-05 22:52:26.699 1
3 3 3 hanako.yamada@example.com 2023-09-05 22:47:03.947 1 山田 花子 3 3 active 2023-09-05 22:52:59.921 1
3 3 3 hanako.yamada@example.com 2023-09-05 22:47:03.947 1 山田 花子 2 3 archived 2023-09-05 22:52:39.295 1

各ユーザーIDごとの最新作成日時のレコードを紐づける

ユーザーIDごとのグループ内で新しい/古いもの順の連番をつけることで、最新のみを取得できるようにしていきます。

ROW_NUMBER() というウィンドウ関数を使用します。

ROW_NUMBER() OVER(PARTITION BY グループ化する列 ORDER BY 並びかえる列 ASC/DESC)

この関数を使用することで、並びかえる列を昇順・降順に並び替え、グループごとに連番（順位）をふることが可能です。

ユーザー詳細履歴を ROW_NUMBER() と合わせて取得してみます。

select 
    udh.*,
    row_number() over (PARTITION BY user_id ORDER BY created_at DESC) AS priority
from
    user_detail_histories udh
;

結果

id user_id email created_at created_by name priority
1 1 taro.t@example.com 2023-09-05 22:44:15.548 1 田中 太郎 1
2 2 sato_jiro@example.com 2023-09-07 23:18:21.246 2 佐藤 次郎 1
5 3 hanako@example.com 2023-09-05 22:48:28.854 1 山本 花子 1
4 3 hanako@example.com 2023-09-05 22:48:26.897 1 山田 花子 2
3 3 hanako.yamada@example.com 2023-09-05 22:47:03.947 1 山田 花子 3

ユーザーIDが「3」の山本さん（旧姓：山田）の priority に連番がふられています。

priority = 1 が各ユーザーの取得すべき詳細情報となっているのがわかると思います。

ROW_NUMBER() と合わせた状態のテーブルを JOIN してクエリを作成します。

select 
    u.id as "id",
    udh_latest."name" as "name",
    udh_latest.email as "email",
    ush_latest.status as "status",
    case
        when udh_latest.created_at > ush_latest.created_at then udh_latest.created_at
        else ush_latest.created_at
    end as "updated_at"
from
    users u 
inner join
    (
        select 
            udh.*,
            row_number() over (PARTITION BY user_id ORDER BY created_at DESC) AS priority
        from
            user_detail_histories udh 
    ) as udh_latest
    on udh_latest.user_id = u.id and udh_latest.priority = 1
inner join
    (
        select 
            ush.*,
            row_number() over (PARTITION BY user_id ORDER BY created_at DESC) AS priority
        from
            user_status_histories ush 
    ) as ush_latest
    on ush_latest.user_id = u.id and ush_latest.priority = 1
order by
    u.id asc
;

結果

id name email status updated_at
1 田中 太郎 taro.t@example.com active 2023-09-05 22:44:15.548
2 佐藤 次郎 sato_jiro@example.com archived 2023-09-07 23:18:21.246
3 山本 花子 hanako@example.com deleted 2023-09-07 23:18:49.705

期待した結果が得られました！！

View を作成して、楽にデータを取得する

上記クエリを毎回記述するのは面倒ではないでしょうか？

また、毎回書くことになれば、記述ミスを誘発しバグを生んでしまうかもしれません。

そこで、View の出番というわけです。

create view user_view as
select 
    u.id as "id",
    udh_latest."name" as "name",
    udh_latest.email as "email",
    ush_latest.status as "status",
    case
        when udh_latest.created_at > ush_latest.created_at then udh_latest.created_at
        else ush_latest.created_at
    end as "updated_at"
from
    users u 
inner join
    (
        select 
            udh.*,
            row_number() over (PARTITION BY user_id ORDER BY created_at DESC) AS priority
        from
            user_detail_histories udh 
    ) as udh_latest
    on udh_latest.user_id = u.id and udh_latest.priority = 1
inner join
    (
        select 
            ush.*,
            row_number() over (PARTITION BY user_id ORDER BY created_at DESC) AS priority
        from
            user_status_histories ush 
    ) as ush_latest
    on ush_latest.user_id = u.id and ush_latest.priority = 1
;

これを一度実行しておけば、それ以降は以下のクエリで期待した結果を取得することができます。

select * from user_view;
id name email status updated_at
1 田中 太郎 taro.t@example.com active 2023-09-05 22:44:15.548
2 佐藤 次郎 sato_jiro@example.com archived 2023-09-07 23:18:21.246
3 山本 花子 hanako@example.com deleted 2023-09-07 23:18:49.705

もちろん、並び順を変更することや、
ID指定でユーザー情報を取得したい場合、名前でユーザーを検索したい場合などは
Where 句を指定することもできます。

select * from user_view order by id desc;

select * from user_view where id = 1;

select * from user_view where "name" like '%佐藤%';

View に最新の詳細やステータスを... という設計を閉じ込めているので、
ユーザーの情報が欲しいという時には user_view から取得するようにすれば難しいことをいちいち考えなくても済むというわけです！

まとめ

イミュータブルデータモデリングによる複雑化したテーブルからレコードを取得する際には、
ROW_NUMBER() と View を活用して難しい取得条件を隠してしまう方法を紹介しました。

履歴管理の代償として増えるレコードをいかに上手に扱うかは難しい問題だなと感じます...

イミュータブルな設計を使いこなせるように精進していきたいと思います🥵🥵

コラボスタイル Developers
コラボスタイル DevelopersPublication

私達は『ワークスタイルの未来を切り拓く』という理念のもとワークフローの本質を追求し、新しいワークスタイルを提案するとともに、コラボスタイルが率先して時代に適したワークスタイルを常にバージョンアップし発信していきます。

Discussion