Entity Attribute Valueとは
書籍SQLアンチパターンで紹介されているパターンの一例です。
Attribute - Valueなペアを子レコードとして大量に作成し、
あるエンティティが可変的に属性を持てるようにするテーブル設計のことです。
以下の例で説明します。
t_userテーブルに属性をカラムとして定義してしまうと、ユーザーが保持するデータの柔軟性は失われています。
ところが、t_personal_dataというt_userに対する外部キー参照とキー&値のみのシンプルなテーブルを持たせることで
あとからいくらでも属性を柔軟に追加することが出来ます。
例えば新たに「既婚フラグ」などを持たせたくなった場合は、ALTER文を実行する必要がありますが、EAV形式を採用した場合は、insert文のみでOKです。
各方面で使われているようですが、
問い合わせクエリを書くのが恐ろしいほど面倒です。
with t_personal_data(user_id,k,v) as(
select '21','年齢','29才' from dual union all
select '21','身長','160m' from dual union all
select '21','体重','60kg' from dual union all
select '21','趣味','サッカー' from dual
),
t_user(user_id,name) as(
select '21','山田' from dual
)
| USER_ID | K | V |
|---|---|---|
| 21 | 年齢 | 29才 |
| 21 | 身長 | 160m |
| 21 | 体重 | 60kg |
| 21 | 趣味 | サッカー |
上記のデータを以下のように一行で表現したいケースは多いと思われます。
| USER_ID | 年齢 | 身長 | 体重 | 趣味 |
|---|---|---|---|---|
| 21 | 29才 | 160m | 60kg | サッカー |
今回はそんなEAVを扱うクエリを紹介したいと思います。
ちなみに今回のテーブルはEAV的には
- Entity:USER
- Attribute:K
- Value:V
という対応を持ちます。
LEFT JOINを多用する
書籍SQLアンチパターンで紹介されている例と同等のものです。
一番発想が自然で、読む人にもわかりやすいと思います。
select
t1.user_id,t2.v,t3.v,t4.v,t5.v
from t_user t1
left outer join t_personal_data t2 on t1.user_id = t2.user_id and t2.k='年齢'
left outer join t_personal_data t3 on t1.user_id = t3.user_id and t3.k='身長'
left outer join t_personal_data t4 on t1.user_id = t4.user_id and t4.k='体重'
left outer join t_personal_data t5 on t1.user_id = t5.user_id and t5.k='趣味'
where t1.user_id = 21
CASE式 とMAX関数を組み合わせる
maxの中にcase式を入れるテクニックは他にも色々使えるので好きなのですが、初見の人は何をやっているのか意味不明なので、共有される恐れのある部分にこれを使うのはあまりオススメではありません。
select
user_id,
max(case k when '年齢' then v else null end) 年齢,
max(case k when '身長' then v else null end) 身長,
max(case k when '体重' then v else null end) 体重,
max(case k when '趣味' then v else null end) 趣味
from t_personal_data
group by user_id
LISTAGG関数を利用する(Oracle)
出力は1カラムになってしまいますが、
クエリ自体はシンプルです。
select
user_id,
listagg(v,',') within group (order by null) values_csv
from t_personal_data group by user_id
| USER_ID | VALUES_CSV |
|---|---|
| 21 | 160m,29才,60kg,サッカー |
引数はmeasure_exprとdelimiterの2つです。
ILSTAGG(,)
delimiterは省略可。delimiterに指定した文字で区切り文字を変更することができます。
measure_exprには式を利用できます
こちらで紹介したcase式を利用することで任意のソート順を設定できます。
select
user_id,
listagg(v,',') within group (ORDER BY
CASE k WHEN '趣味' THEN 1 ELSE 2 END,
CASE k WHEN '体重' THEN 1 ELSE 2 END,
CASE k WHEN '身長' THEN 1 ELSE 2 END,
CASE k WHEN '年齢' THEN 1 ELSE 2 END,k) values_csv
from t_personal_data group by user_id
| USER_ID | VALUES_CSV |
|---|---|
| 21 | サッカー,60kg,160m,29才 |
LISTAGGは分析関数としても利用できます。
select tpd.*,
listagg(v,',') within group (order by null)
over(partition by user_id)
from t_personal_data tpd
| USER_ID | K | V | LISTAGG(V,',')WITHINGROUP(ORDERBYNULL)OVER(PARTITIONBYUSER_ID) |
|---|---|---|---|
| 21 | 年齢 | 29才 | 29才,サッカー,60kg,160m |
| 21 | 趣味 | サッカー | 29才,サッカー,60kg,160m |
| 21 | 体重 | 60kg | 29才,サッカー,60kg,160m |
| 21 | 身長 | 160m | 29才,サッカー,60kg,160m |
PIVOT関数を利用する(Oracle)
その名の通り、縦横変換のために作られた関数。
やはり他のものと違って、専用で作られたものなのでしっくりくる。
Oracle Database の PIVOT文で取得レコードの縦横を変換する。
select * from
(select user_id,k,v
from
t_personal_data )
pivot (max(v) for k in('年齢','身長','体重','趣味'))
in句の中にASを書いて出力列名を指定することも可能。
in('年齢' as 年齢,'身長' as 身長,'体重' as 体重,'趣味' as趣味)
in句の中にサブクエリを書いて
select * from
(select user_id,k,v
from
t_personal_data )
pivot (max(v) for k in(select k from t_personal_data))
のような感じで列の値を書かずに実行できると一番スマートな気がするのだが,
どうやらxmlモードでないと使用できない模様。
A subquery is used only in conjunction with the XML keyword.
検証:oracle11g