イミュータブルデータモデルには PostgreSQL の View を使う
イミュータブルデータモデルだと、クエリが複雑になる?
以下の記事で、イミュータブルデータモデリングにおける削除フラグの考察をしていきました。
ユーザーの情報を管理したいだけなのに、テーブルが増えてしまい、
データの取得が複雑になるのでは?と考える方も多いでしょう...
それは確かに複雑になると思います。
ただ、その 複雑さを View の中に閉じ込めること ができます。
PostgreSQL の View を活用する
View とは
データベース内のテーブルから抽出された結果セットを表現する仮想的なテーブルです。
View は既存のテーブルから必要なデータを選択し、組み合わせ、計算、または変換して、
より "使いやすい" 形式でデータにアクセスできるようにするのに役立ちます。
View 作成クエリ
CREATE VIEW VIEWの名前 AS
SELECT文
;
最新の作成日時のレコードだけが欲しい
以下のように、イミュータブルに設計したテーブルがあるとして、
現在のユーザー情報の一覧を取得できるようにしていきましょう。
users
id |
---|
1 |
2 |
3 |
user_detail_histories
id | user_id | 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 | 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 | 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 | 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 | 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 | 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 を活用して難しい取得条件を隠してしまう方法を紹介しました。
履歴管理の代償として増えるレコードをいかに上手に扱うかは難しい問題だなと感じます...
イミュータブルな設計を使いこなせるように精進していきたいと思います🥵🥵
Discussion