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(<measure_expr>,<delimiter>)
 delimiterは省略可。delimiterに指定した文字で区切り文字を変更することができます。
 measure_exprには式を利用できます
 
 [こちら](https://qiita.com/gooddoog/items/f37f3d3f0081b5b39e05)で紹介した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文で取得レコードの縦横を変換する。](http://qiita.com/ora_gonsuke777/items/558a6ae91b7490b7bea1)


```
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