(会社で書いたやつの焼き直しだったりする)
ワタクシ、とてもジョジョの奇妙な冒険が好きで、今日もジョジョリオン20巻買って会社でご飯食べながら読んでたんですが、 そのジョジョの主人公達を使って、MySQLでは8から入ったCTEを試してみました。
そいつの名はCTE
Common Table Expressions のこと。
通称WITH句。
あるクエリ結果を一時的にテーブルのように保持しておき、そのステートメント内で後で(場合によっては複数回)結果を参照できる名前付き一時結果セットです。
https://dev.mysql.com/doc/refman/8.0/en/with.html
CTEはSQL標準
なので、Oracle、PostgreSQL、SQLServerなどでも使えます。
MySQL系だとMariaDBでは使えるんですが、MySQLでは先ほども言った通り8.0で入った新機能となっています。
もし知らなかった人で再帰クエリ書きたくなった場合に現場のRDBMSがOracle、PostgreSQL、SQLServerであったり、またはMySQL8を使える環境がある人はぜひ使ってみてください。
黄金体験
じゃあ、使えると何がうれしいのか?
ずばり再帰クエリが書けることです。
CTEは静かに奏でられる
ここに、僕の大好きな【ジョジョの奇妙な冒険】のジョースター家と関連する人の一覧を用意します。
ID | last_name | first_name | father_id | mother_id | pucci_flg | jap_flg |
---|---|---|---|---|---|---|
1 | ジョージ(1世) | ジョースター | 0 | 0 | ||
2 | メアリー | ジョースター | 0 | 0 | ||
3 | ジョナサン | ジョースター | 1 | 2 | 0 | 0 |
4 | エリナ | ジョースター | 0 | 0 | ||
5 | ダリオ | ブランドー | 0 | 0 | ||
6 | ディオ | ブランドー | 5 | 0 | 0 | |
7 | ジョージ(2世) | ジョースター | 3 | 4 | 0 | 0 |
8 | エリザベス | ジョースター | 0 | 0 | ||
9 | ジョセフ | ジョースター | 7 | 8 | 0 | 0 |
10 | スージーQ | ジョースター | 0 | 0 | ||
11 | 貞夫 | 空条 | 0 | 1 | ||
12 | ホリィ | 空条 | 9 | 10 | 0 | 0 |
13 | 承太郎 | 空条 | 11 | 12 | 0 | 1 |
14 | 良平 | 東方 | 0 | |||
15 | 朋子 | 東方 | 14 | 0 | ||
16 | 仗助 | 東方 | 9 | 15 | 0 | |
17 | 静 | ジョースター | 9 | 0 | ||
18 | ジョルノ | ジョバァーナ | 6 | 0 | ||
19 | 徐倫 | 空条 | 13 | 0 | ||
20 | グレゴリオ | ツェペリ | 1 | |||
21 | ジャイロ | ツェペリ | 20 | 1 | ||
22 | ジョージ | ジョースター | 1 | |||
23 | ジョニィ | ジョースター | 22 | 1 | ||
24 | 憲助(初代) | 東方 | 1 | |||
25 | 理那 | 東方 | 24 | 1 | ||
26 | ディエゴ | ブランドー | 1 | |||
27 | ジョージ(3世) | ジョースター | 23 | 25 | 1 | |
28 | エリザベス | ジョースター | 1 | |||
29 | ジョセフ | ジョースター | 27 | 28 | 1 | |
30 | スージーQ | ジョースター | 1 | |||
31 | 吉輝 | 吉良 | 1 | |||
32 | ホリー | 吉良 | 29 | 30 | 1 | |
33 | 吉影 | 吉良 | 31 | 32 | 1 | |
34 | 京 | 虹村 | 31 | 32 | 1 | |
35 | 憲助(2代目) | 東方 | 24 | 1 | ||
36 | 憲助(3代目) | 東方 | 35 | 1 | ||
37 | 憲助(4代目) | 東方 | 36 | 1 | ||
38 | 花都 | 東方 | 1 | |||
39 | 定助 | 東方 | 37 | 1 | ||
40 | 常敏 | 東方 | 37 | 38 | 1 | |
41 | 密葉 | 東方 | 1 | |||
42 | つるぎ | 東方 | 40 | 41 | 1 | |
43 | 鳩 | 東方 | 37 | 38 | 1 | |
44 | 常秀 | 東方 | 37 | 38 | 1 | |
45 | 大弥 | 東方 | 37 | 38 | 1 |
テーブルを作り、
CREATE TABLE j_pedigree ( id INT PRIMARY KEY, last_name VARCHAR(10), first_name VARCHAR(10), father_id INT, mother_id INT, pucci_flg INT, jap_flg INT, unique key (last_name,first_name,pucci_flg), key i_j_pedigree_1(father_id) ) ENGINE=InnoDB DEFAULT CHARSET=UTF8mb4 COLLATE=utf8mb4_bin;
データをオラオラ入れます。
insert into j_pedigree values(1,'ジョージ(1世)','ジョースター',null,null,0,0); insert into j_pedigree values(2,'メアリー','ジョースター',null,null,0,0); insert into j_pedigree values(3,'ジョナサン','ジョースター',1,2,0,0); insert into j_pedigree values(4,'エリナ','ジョースター',null,null,0,0); insert into j_pedigree values(5,'ダリオ','ブランドー',null,null,0,0); insert into j_pedigree values(6,'ディオ','ブランドー',5,null,0,0); insert into j_pedigree values(7,'ジョージ(2世)','ジョースター',3,4,0,0); insert into j_pedigree values(8,'エリザベス','ジョースター',null,null,0,0); insert into j_pedigree values(9,'ジョセフ','ジョースター',7,8,0,0); insert into j_pedigree values(10,'スージーQ','ジョースター',null,null,0,0); insert into j_pedigree values(11,'貞夫','空条',null,null,0,1); insert into j_pedigree values(12,'ホリィ','空条',9,10,0,1); insert into j_pedigree values(13,'承太郎','空条',11,12,0,1); insert into j_pedigree values(14,'良平','東方',null,null,0,1); insert into j_pedigree values(15,'朋子','東方',14,null,0,1); insert into j_pedigree values(16,'仗助','東方',9,15,0,1); insert into j_pedigree values(17,'静','ジョースター',9,null,0,0); insert into j_pedigree values(18,'ジョルノ','ジョバァーナ',6,null,0,0); insert into j_pedigree values(19,'徐倫','空条',13,null,0,1); insert into j_pedigree values(20,'グレゴリオ','ツェペリ',null,null,1,0); insert into j_pedigree values(21,'ジャイロ','ツェペリ',20,null,1,0); insert into j_pedigree values(22,'ジョージ','ジョースター',null,null,1,0); insert into j_pedigree values(23,'ジョニィ','ジョースター',22,null,1,0); insert into j_pedigree values(24,'憲助(初代)','東方',null,null,1,1); insert into j_pedigree values(25,'理那','東方',24,null,1,0); insert into j_pedigree values(26,'ディエゴ','ブランドー',null,null,1,0); insert into j_pedigree values(27,'ジョージ(3世)','ジョースター',23,25,1,0); insert into j_pedigree values(28,'エリザベス','ジョースター',null,null,1,0); insert into j_pedigree values(29,'ジョセフ','ジョースター',27,28,1,0); insert into j_pedigree values(30,'スージーQ','ジョースター',null,null,1,0); insert into j_pedigree values(31,'吉輝','吉良',null,null,1,1); insert into j_pedigree values(32,'ホリー','吉良',29,30,1,1); insert into j_pedigree values(33,'吉影','吉良',31,32,1,1); insert into j_pedigree values(34,'京','虹村',31,32,1,1); insert into j_pedigree values(35,'憲助(2代目)','東方',24,null,1,1); insert into j_pedigree values(36,'憲助(3代目)','東方',35,null,1,1); insert into j_pedigree values(37,'憲助(4代目)','東方',36,null,1,1); insert into j_pedigree values(38,'花都','東方',null,null,1,1); insert into j_pedigree values(39,'定助','東方',37,null,1,1); insert into j_pedigree values(40,'常敏','東方',37,38,1,1); insert into j_pedigree values(41,'密葉','東方',null,null,1,1); insert into j_pedigree values(42,'つるぎ','東方',40,41,1,1); insert into j_pedigree values(43,'鳩','東方',37,38,1,1); insert into j_pedigree values(44,'常秀','東方',37,38,1,1); insert into j_pedigree values(45,'大弥','東方',37,38,1,1);
CTEの世界
ジョージ(1世)=ジョースターから脈々とつながるジョースター家の血をプッチ神父による世界一巡前の世界でこんな感じで表現してみます。
ID | last_name | first_name | 親の名前 | プッチ神父 |
---|---|---|---|---|
1 | ジョージ(1世) | ジョースター | ジョージ(1世)(本人) | 世界一巡前 |
3 | ジョナサン | ジョースター | ジョージ(1世) | 世界一巡前 |
7 | ジョージ(2世) | ジョースター | ジョナサン | 世界一巡前 |
9 | ジョセフ | ジョースター | ジョージ(2世) | 世界一巡前 |
12 | ホリィ | 空条 | ジョセフ | 世界一巡前 |
13 | 承太郎 | 空条 | ホリィ | 世界一巡前 |
16 | 仗助 | 東方 | ジョセフ | 世界一巡前 |
17 | 静 | ジョースター | ジョセフ | 世界一巡前 |
19 | 徐倫 | 空条 | 承太郎 | 世界一巡前 |
プッチ神父、世界一巡前とか後とかなんのことかわからない人は、1部から6部までの話だと思ってくれていいです。
これを普通のクエリでやろうとすると、重たいサブクエリまたはアプリケーションに一回外だしが必要になりますが、WITH句でやるとこんな感じになります。
WITH RECURSIVE Jojo AS ( SELECT id, last_name, first_name,pucci_flg,concat(last_name,'(本人)') as parent_last_name FROM j_pedigree WHERE last_name= "ジョージ(1世)" AND first_name = "ジョースター" and pucci_flg = 0 UNION ALL SELECT child.id, child.last_name, child.first_name ,child.pucci_flg,Jojo.last_name as parent_last_name FROM j_pedigree as child, Jojo WHERE Jojo.id= child.father_id or Jojo.id= child.mother_id ) SELECT id, last_name, first_name,parent_last_name,case when pucci_flg=0 then '世界一巡前' else '世界一巡後' end as 'メイドインヘブン' FROM Jojo order by pucci_flg,id;
見栄えを気にしたので、caseとか入ってるんですが、説明すると
WITH RECURSIVE Jojo AS (
SELECT id, last_name, first_name,pucci_flg,concat(last_name,'(本人)') as parent_last_name FROM j_pedigree WHERE last_name= "ジョージ(1世)" AND first_name = "ジョースター" and pucci_flg = 0
上記クエリではID=1のジョージ(1世)=ジョースターのレコードが抽出されます。
Jojo一時テーブルにはその結果が入ります。
UNION ALL SELECT child.id, child.last_name, child.first_name ,child.pucci_flg,Jojo.last_name as parent_last_name FROM j_pedigree as child, Jojo WHERE Jojo.id= child.father_id or Jojo.id= child.mother_id )
先ほどのID=1を父親もしくは母親のIDとして持つレコードを取得します。
その結果がJojo一時テーブルに格納され、今度はその取得ID(3=ジョナサン ジョースター)を使って次の子供のレコード(7=ジョージ(2世) )を取得するというように紐づく限り再帰的にレコードを取得してくれます。
SELECT id, last_name, first_name,parent_last_name,case when pucci_flg=0 then '世界一巡前' else '世界一巡後' end as 'メイドインヘブン' FROM Jojo order by pucci_flg,id;
最後にJojo 一時テーブルから出力形式を整えて完了です。
何回でも繰りかえす!!
まるでゴールド・エクスペリエンス・レクイエムのように!(いや、終わるんですけどね)