先ごろWordPressのプラグイン関連の問い合わせで、データベースのとあるテーブルについてデータの持ち方の縦横を変換したいという要望をもらい、テーブル構造の縦横変換をやってみた。
ちなみに縦型にデータを持っているテーブル構造を横型の持ち方へ変換することを「ピボット（Pivot）」、その逆を「Unpivot（アンピボット）」と云う。MS OfficeのExcelでクロス集計を行う時などに使う「ピボットテーブル」を利用した事がある人には理解しやすいと思う。
テーブルのピボット検索（縦→横への変換）
実例として、下記のような縦型にデータを持っているテーブル（テーブル名：vtable）がある。
| ID | form_id | entry_id | field_id | value |
|---|---|---|---|---|
| 1 | 21 | 1 | a101 | Jane |
| 2 | 21 | 1 | a102 | Doe |
| 3 | 21 | 1 | a103 | Female |
| 4 | 21 | 2 | a101 | Jack |
| 5 | 21 | 2 | a102 | Jons |
| 6 | 21 | 2 | a103 | Male |
WEBフロントエンドでは、フォームのID:21に属する入力フォームが3つあり、ファーストネーム用のフィールド（a101）とファミリーネーム用のフィールド（a102）、性別用のフィールド（a103）からの値がこのテーブルに格納されるという建付けだ。入力者のユーザID的な値はentry_idである。
これを、下記の横型のデータの持ち方のテーブルのように変換して検索結果を得たいというのが要望だった。
| entry_id | form_id | first_name | family_name | gender |
|---|---|---|---|---|
| 1 | 21 | Jane | Doe | Female |
| 2 | 21 | Jack | Jons | Male |
早速、思いつくまま愚直にSELECT文を作ってみた。
SELECT
entry_id,
form_id,
(SELECT value FROM vtable WHERE field_id = 'a101' AND entry_id = v.entry_id) AS first_name,
(SELECT value FROM vtable WHERE field_id = 'a102' AND entry_id = v.entry_id) AS family_name,
(SELECT value FROM vtable WHERE field_id = 'a103' AND entry_id = v.entry_id) AS gender
FROM
vtable v
GROUP BY
entry_id
;
実行結果は次の通り。
+----------+---------+------------+-------------+--------+
| entry_id | form_id | first_name | family_name | gender |
+----------+---------+------------+-------------+--------+
| 1 | 21 | Jane | Doe | Female |
| 2 | 21 | Jack | Jons | Male |
+----------+---------+------------+-------------+--------+
要望は満たされているのだが、（個人的に）サブクエリが美しくないので、CASE文でもうちょっとスマートに書いてみる。
SELECT
entry_id,
form_id,
MAX(CASE WHEN field_id = 'a101' THEN value ELSE null END) AS first_name,
MAX(CASE WHEN field_id = 'a102' THEN value ELSE null END) AS family_name,
MAX(CASE WHEN field_id = 'a103' THEN value ELSE null END) AS gender
FROM
vtable
GROUP BY
entry_id
;
一見すると、検索結果はどちらも同じになるのだが……。
ピボット検索の性能検証
初期の要望については前述の検索クエリで満たされたわけだが、ピボット検索についてはパフォーマンスが気になったので、もうちょっと突っ込んで色々とやってみた。
とりあえず、縦型データ構造のテーブルに性能検証用のデータを大量に登録して、クエリの性能を見てみる。Fakerを使って1万ユーザ分のダミーデータを生成して、ピボット検索を試してみた。
SELECT count(ID) AS Records,MAX(entry_id) AS Users FROM vtable;
+---------+-------+
| Records | Users |
+---------+-------+
| 30000 | 10000 |
+---------+-------+
このvtableに対して、前項のサブクエリ型のピボット検索を実施してみたところ、
10000 rows in set (1 hour 1 min 26.61 sec)
なんと、1時間超もかかった……！（待ってる間にランチ食べて来たよw）
次にエレガントなCASE文でのピボット検索は、
10000 rows in set (1.07 sec)
こちらは1秒ちょっと。圧倒的な差がついた。まぁクエリの内部処理順を考えるとやる前から予想できていた結果なんだが……巷で云われる「MySQLの（洗練されていない）サブクエリは遅い」という定説が改めて証明された次第だw
まぁ、こういうひどいクエリを作ってしまった時は、EXPLAINでどんだけひどいか見てみると愉（たの）しいし、おまけに勉強にもなる。
+----+--------------------+--------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+--------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+
| 1 | PRIMARY | v | NULL | ALL | NULL | NULL | NULL | NULL | 30075 | 100.00 | Using temporary; Using filesort |
| 4 | DEPENDENT SUBQUERY | vtable | NULL | ALL | NULL | NULL | NULL | NULL | 30075 | 1.00 | Using where |
| 3 | DEPENDENT SUBQUERY | vtable | NULL | ALL | NULL | NULL | NULL | NULL | 30075 | 1.00 | Using where |
| 2 | DEPENDENT SUBQUERY | vtable | NULL | ALL | NULL | NULL | NULL | NULL | 30075 | 1.00 | Using where |
+----+--------------------+--------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+
4 rows in set, 4 warnings (0.08 sec)
ほら、ありました「DEPENDENT SUBQUERY」。基本、サブクエリ使っていて遅い時は十中八九この相関サブクエリが問題である。詳しくは、
──をご一読あれ。
そんなわけで、ピボット検索のクエリを考えるときは、できる限りサブクエリは使わないように注意しよう。
ピボットテーブルのVIEWを作成して効率化
さて、ここまででピボット検索についてはほぼOKなのだが、毎回ピボット検索用のSELECTクエリを発行するのは面倒だ。クエリの記述量も多いので、ストアドプロシージャにピボット検索を登録しておくというのも一案だが、ここはお手軽にVIEWを作ってしまうのが良いかと思った。
CREATE VIEW
vtable_view
AS SELECT
entry_id,
form_id,
MAX(CASE WHEN field_id = 'a101' THEN value ELSE null END) AS first_name,
MAX(CASE WHEN field_id = 'a102' THEN value ELSE null END) AS family_name,
MAX(CASE WHEN field_id = 'a103' THEN value ELSE null END) AS gender
FROM
vtable
GROUP BY
entry_id
;
やり方は簡単で、前出のエレガントなピボット検索のクエリを元にCREATE VIEWするだけだ。こうしてVIEWを作っておけば、ピボット検索する時は、
SELECT * FROM vtable_view;
(結果省略)
10000 rows in set (0.97 sec)
──と云うように、SELECTクエリをさらに簡略化できる。
また、下記のように条件を付与して特定データを抽出する時なども、わかりやすいクエリで処理ができるようになる。
SELECT
entry_id,first_name,family_name
FROM
vtable_view
WHERE
form_id = 23
AND
gender = 'Female'
LIMIT 10
;
+----------+------------+-------------+
| entry_id | first_name | family_name |
+----------+------------+-------------+
| 5 | Christa | Ledner |
| 14 | Otha | Moore |
| 16 | Constance | Bins |
| 18 | Kira | Johnston |
| 29 | Ilene | Greenfelder |
| 32 | Maurine | Heller |
| 33 | Nova | Dach |
| 34 | Edythe | Wolf |
| 35 | Salma | Hermiston |
| 44 | Kellie | Dare |
+----------+------------+-------------+
10 rows in set (1.00 sec)
ただし、VIEW経由でのピボット検索は、検索のたびに元テーブルの全データをVIEWへ置換するというオーバーヘッドが発生するので、素のSELECTよりその分だけ時間がかかってしまうというデメリットがある。データ数によってはVIEWを使わないピボット検索じゃないと高パフォーマンスが担保できなくなるリスクがあるので注意が必要だ。
アンピボット検索（横→縦への変換）
もう一つ気になるのが、縦から横への変換であるピボット検索の逆、横から縦へのアンピボット検索についてだ。ピボットについてはNoSQL等のキー・バリュー型のデータを論理構造を持つテーブルへ変換するというケーススタディなどが思い浮かぶのだが、逆のアンピボットの変換はどんなケースで必要になるのだろうか。考えられるとすれば、論理テーブルのデータを高速検索するためのシーケンス系テーブルを作成したり……とかか？ まぁ必要性があまり思いつかないが、アンピボット検索もやってみよう。
まず、実例を示そう。アンピボット検索の対象となる、データを横持ちで持っているリレーショナルデータベースでは一般的な論理型テーブル（テーブル名: htable）は下記の通りだ。ダミーデータはまたFakerで作成した。
| ID | first_name | family_name | gender | |
|---|---|---|---|---|
| 11 | Maxime | Mitchell | Male | zboncak.mason@price.com |
| 12 | Dessie | Graham | Female | virgie19@metz.com |
| 13 | Christopher | Stark | Male | simonis.natalie@ankunding.com |
これをアンピボット検索して、キー・バリュー方式の縦持ちデータ型で取得してみる。
SELECT ID AS entry_id, 'first_name' AS `key`, first_name AS value FROM htable
UNION ALL
SELECT ID AS entry_id, 'family_name' AS `key`, family_name AS value FROM htable
UNION ALL
SELECT ID AS entry_id, 'gender' AS `key`, gender AS value FROM htable
UNION ALL
SELECT ID AS entry_id, 'email' AS `key`, email AS value FROM htable
ORDER BY
entry_id,
FIELD(`key`, 'first_name', 'family_name', 'gender', 'email')
;
上記のクエリの注意点は二つある。まず結果出力時に疑似的に生成するフィールド名に「key」を使う場合、MySQLの予約語と重複するのでバッククオートで括ってやる必要があるのだ。そして、ユニオンした結果に対してのORDER BYによるソートはインデックスが使えないため、パフォーマンスに悪影響を及ぼすということだ。今回のように1万件程度のデータ数であればさほど気にする必要もないが、データ数によってはクエリを見直す必要が出てくるかもしれない。
ちなみに、ORDER BY句で使えるFIELD関数はカラム値の並び順を任意に指定できるものだ。上記の例のように文字列候補に対しても表示順を指定できるので、覚えておくと何気に重宝する。
──で、肝心なクエリの結果は下記のようになる。
+----------+-------------+-------------------------------+
| entry_id | key | value |
+----------+-------------+-------------------------------+
| 11 | first_name | Maxime |
| 11 | family_name | Mitchell |
| 11 | gender | Male |
| 11 | email | zboncak.mason@price.com |
| 12 | first_name | Dessie |
| 12 | family_name | Graham |
| 12 | gender | Female |
| 12 | email | virgie19@metz.com |
| 13 | first_name | Christopher |
| 13 | family_name | Stark |
| 13 | gender | Male |
| 13 | email | simonis.natalie@ankunding.com |
+----------+-------------+-------------------------------+
12 rows in set (0.01 sec)
アンピボットのやり方としては、前述のユニオンでやる方式ともう一つCROSS JOIN方式があるので、そちらも紹介しておこう。
SELECT
h.ID AS entry_id,
c.`key` AS `key`,
(
CASE c.`key`
WHEN 'first_name' THEN first_name
WHEN 'family_name' THEN family_name
WHEN 'gender' THEN gender
WHEN 'email' THEN email
END
) AS value
FROM
htable h
CROSS JOIN
(
SELECT 'first_name' AS `key`
UNION ALL SELECT 'family_name' AS `key`
UNION ALL SELECT 'gender' AS `key`
UNION ALL SELECT 'email' AS `key`
) c
;
結局CROSS JOIN時にユニオンで結合してるじゃん……という突っ込みはあるのだが、まぁ、こんな方式もあるよ……と云う一例だ。ちなみに、MySQLでのCROSS JOIN（交差結合）はJOINやINNER JOINと同等で、SQLite等他のSQLのCROSS JOINとは厳密な挙動が異なる。なので、CROSS JOINの部分は単にJOINとしても構わない。ただ、CROSS JOIN句を使うと暗黙的にON句が不要であることを明文化できるので、アンピボットの時はCROSS JOINが使われるようだ。
最終的にアンピボットの時はどちらを使うべきなのか？ それは次項の性能検証後に判断してみよう。
アンピボット検索の性能検証
さて、アンピボット検索についてもどの程度の性能差があるのか、前項での二つのクエリを比較してみよう。早速、Fakerで検証対象のテーブルに約1万件のデータを投入して比較してみる。
40000 rows in set (0.23 sec)
40000 rows in set (0.09 sec)
私の予想を裏切って、CROSS JOIN方式の方がパフォーマンスが良かったが、誤差の範囲と云えなくもない。もう少し詳しく、 EXPLAIN FORMAT=json でそれぞれのクエリコストを比較してみた。
{
"query_block": {
"union_result": {
"using_temporary_table": true,
"table_name": "<union1,2,3,4>",
"access_type": "ALL",
"query_specifications": [
{
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "2096.20"
},
"table": {
"table_name": "htable",
"access_type": "ALL",
"rows_examined_per_scan": 9996,
"rows_produced_per_join": 9996,
"filtered": "100.00",
"cost_info": {
"read_cost": "97.00",
"eval_cost": "1999.20",
"prefix_cost": "2096.20",
"data_read_per_join": "6M"
},
"used_columns": [
"ID",
"first_name"
]
}
}
},
{
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 2,
"cost_info": {
"query_cost": "2096.20"
},
"table": {
"table_name": "htable",
"access_type": "ALL",
"rows_examined_per_scan": 9996,
"rows_produced_per_join": 9996,
"filtered": "100.00",
"cost_info": {
"read_cost": "97.00",
"eval_cost": "1999.20",
"prefix_cost": "2096.20",
"data_read_per_join": "6M"
},
"used_columns": [
"ID",
"family_name"
]
}
}
},
{
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 3,
"cost_info": {
"query_cost": "2096.20"
},
"table": {
"table_name": "htable",
"access_type": "ALL",
"rows_examined_per_scan": 9996,
"rows_produced_per_join": 9996,
"filtered": "100.00",
"cost_info": {
"read_cost": "97.00",
"eval_cost": "1999.20",
"prefix_cost": "2096.20",
"data_read_per_join": "6M"
},
"used_columns": [
"ID",
"gender"
]
}
}
},
{
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 4,
"cost_info": {
"query_cost": "2096.20"
},
"table": {
"table_name": "htable",
"access_type": "ALL",
"rows_examined_per_scan": 9996,
"rows_produced_per_join": 9996,
"filtered": "100.00",
"cost_info": {
"read_cost": "97.00",
"eval_cost": "1999.20",
"prefix_cost": "2096.20",
"data_read_per_join": "6M"
},
"used_columns": [
"ID",
"email"
]
}
}
}
]
}
}
}
ユニオンで結合するテーブル毎にクエリコストが2096.20が発生していて、合計で8384.8が総クエリコストのようだ。結合前の同じテーブルを4回もフルスキャンしているので、パッと見で効率が悪そうな感じを受ける。
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "8104.83"
},
"nested_loop": [
{
"table": {
"table_name": "c",
"access_type": "ALL",
"rows_examined_per_scan": 4,
"rows_produced_per_join": 4,
"filtered": "100.00",
"cost_info": {
"read_cost": "10.20",
"eval_cost": "0.80",
"prefix_cost": "11.00",
"data_read_per_join": "128"
},
"used_columns": [
"key"
],
"materialized_from_subquery": {
"using_temporary_table": true,
"dependent": false,
"cacheable": true,
"query_block": {
"union_result": {
"using_temporary_table": false,
"query_specifications": [
{
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 2,
"message": "No tables used"
}
},
{
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 3,
"message": "No tables used"
}
},
{
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 4,
"message": "No tables used"
}
},
{
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 5,
"message": "No tables used"
}
}
]
}
}
}
}
},
{
"table": {
"table_name": "h",
"access_type": "ALL",
"rows_examined_per_scan": 9996,
"rows_produced_per_join": 39984,
"filtered": "100.00",
"using_join_buffer": "Block Nested Loop",
"cost_info": {
"read_cost": "97.03",
"eval_cost": "7996.80",
"prefix_cost": "8104.83",
"data_read_per_join": "26M"
},
"used_columns": [
"ID",
"first_name",
"family_name",
"gender",
"email"
]
}
}
]
}
}
一方、CROSS JOIN型はテーブルを結合した後に一回だけ検索するので、総クエリコストが8104.83とユニオン型より若干少なくなっている。
クエリコストで比較してもCROSS JOIN型の方がパフォーマンスが良いようだ。適切なインデックスを貼ってやれば、さらなるパフォーマンスの向上が見込めるだろう。
結論として、アンピボット検索を行うならばクエリはCROSS JOIN型の方が良いということがわかった。
まとめ
縦持ちから横持ちへのピボット検索については、問い合わせをもらったこともあり、結構利用するシーンがありそうだと思った。もしバックオフィス用の集計やデータ管理の用途として限定的に使うのであれば、VIEWを使ったピボット検索も非常に有効だと思われる。
一方で、アンピボット検索については使いどころがいまいちピンと来ないが、ワンクエリのSQLだけでやれることだけはわかった。
いやはや、SQLは奥が深い。そして、愉（たの）しい。