Edited at

EAV(Entity Attribute Value)アンチパターンを有効に扱うクエリを書く

More than 1 year has passed since last update.


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は分析関数としても利用できます。

https://docs.oracle.com/cd/E16338_01/server.112/b56299/functions089.htm

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.


https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10002.htm#SQLRF01702

検証:oracle11g