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

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