カンマ（，）区切りのレコードからデータを抽出して云々するSQL

こちら、Postglesqlを使ったSQLアンチパターンの記事です。

本記事を読むとこんなことができるようになります（SQLアンチパターン：信号無視、ジェイウォーク）

次の方が想定読者となります。

• 既存のDBにアンチパターンが盛り込まれてるけどスキーマを変更するだけのリソースがない人

普段はpgadminというGUIからSQLを書いています。CLIからガシガシ書ける方々が眩しい……。
SQLそのものが分からなさ過ぎてこれを想定通りの結果になるよう実行するまでにまる4日くらいかかりました😇

この記事を書くために1カ月くらいかかりました（片手間に書いたため）
Σ(っ °Д °;)っｶﾞｸｶﾞｸﾌﾞﾙﾌﾞﾙ。

まずはテストデータを作成するためにスキーマを定義します。
SQLの実行にはこちらを利用しました。

ここを使うとSQLがお試しで実行できるそうです。
まずはテーブルの作成をしていきましょう！！！

create_schema.sql

-- テーブルを作成する
CREATE TEMPORARY TABLE IF NOT EXISTS human (
human_no integer PRIMARY KEY, -- 管理番号
school varchar(10), -- 学校
name varchar(10) -- 名前
);
CREATE TEMPORARY TABLE IF NOT EXISTS team (
id integer PRIMARY KEY, -- ナンバリング
school varchar(10), -- 学校
humans varchar(100), -- 人の列挙
human_ids varchar(100) -- 人の管理番号の列挙
); 
/* 
 * テーブルに値を追加。
 * human_idsに値を入れるSQLを今回の記事で作成します
 */
INSERT INTO human VALUES
(1,'Qiita','佐藤'), -- Qiita学校の佐藤くん
(2,'Qiita','田中'),
(3,'Qiita','鈴木');
INSERT INTO team VALUES
(1,'Qiita','',''),
(2,'Qiita','佐藤',''),
(3,'Qiita','佐藤,鈴木',''),
(4,'Qiita','佐藤,田中,鈴木',''),
(5,'Qiita','高橋,田中','');

ちゃんとDB・スキーマ・テーブルが実在しているかどうか確認してみましょう。

select_all.sql

-- 存在チェック
SELECT current_database(); -- 接続DB
SELECT * FROM human; -- humanテーブル
SELECT * FROM team; -- teamテーブル

出力結果

※DBについては省略（実行SQL）
※オンライン実行環境では複数のテーブルを同時に出力することができません。オンラインで試したい方はSELECT文をひとつずつ実行してください。

myschema.humanテーブル（実行SQL）

human_no	school	name
1	Qiita	佐藤
2	Qiita	田中
3	Qiita	鈴木

myschema.teamテーブル（実行SQL）

id	school	humans	human_id
1	Qiita
2	Qiita	佐藤
3	Qiita	佐藤,鈴木
4	Qiita	佐藤,田中,鈴木
5	Qiita	高橋,田中

空欄や存在しないデータ（ニセモノ）も混ぜてみました。
稀によくありますよね、こういう現象。

このテーブルのhuman_idカラムにhumansカラムに紐づいたidをカンマ区切りで入れていくSQLをこれから作成します。

いきなり結論を書いてもいいのですが大分長いSQLとなってしまったため読みづらいかと思われます。
そのため段階を追って完成させていくことで理解が早まるように記事を書いていきます。

段階0：何故この処理が必要なのかを理解

すごくそもそも論ではありますが何故この処理を書かなければならないのか、そこを自分なりに理解しておく必要があります。
誰の役にも立たないプログラムを書くと萎えますから…。
今回は以下のようなストーリーがあると仮定してください（あなたは神様です）。

今まで、人の名前はメモ書き程度の需要しかありませんでした。学校ごとに校内で自由にチームを結成することができますが結成して以降は、チーム内メンバーの名前が合っていようと間違っていようと問題はない、後から生徒名簿の名前が書き換えられても、チーム内メンバーの名前は書き変わらなくていい……その程度の存在です。
しかし時は進み、人の数は増え学校の数も増え、学校内では様々な事情で名前が変わる人も増えてきました。チーム内の人は誰もその人の名前が変わったことに気付けません。そんな中ある日、学校を束ねる委員会の人（？）はこう言いました
「人の名前が変わってもチームの人がそれに気付けないのは不便！神様これ何とかして！」

残り工数的にDBスキーマを大きく変更することはできません。神様はチーム内の生徒をIDで管理することで、チーム名簿を参照するときに生徒の名前が変わっていても生徒名簿の情報を参照できるようにしよう、と考えたのでした。

ここでひとつ問題になるのはチーム名簿内の生徒名がカンマ区切りのテキストデータで書かれていることです。これでは生徒のIDを判別することが困難……うまいことこのデータを加工し、名前から生徒名簿のIDを検索しなければなりません。
（あとからチーム内名簿に、生徒名簿には存在しない生徒…ニセモノがいることがわかりましたが、それは無視されます）

段階1：カンマ区切りの文字列を作成

まずはhumansカラムと同じフォーマットの「カンマ区切りの文字列」をどうやったらPostglesqlで表現できるのか、そこからやっていきましょう。

SQL言語の種類によって存在しない予約語や、異なる挙動があったりします。今回の主題をもしMySQLやBigQueryで書いたなら案外簡単に実装ができてしまうのかも……。
（ご存じの方は後学のためにコメントで教えてください）

ここからはスキーマ定義が完了している前提でSQLを書いていくのでよろしくお願いします。

phase1.sql

-- Qiita学校の生徒をカンマ区切りでテキストに出力する
SELECT array_to_string(array_agg(human_no),',')
FROM human
WHERE school = 'Qiita';

こうなれば成功です。
Postglesqlにはarray_to_stringとarray_aggという関数が存在します。

出力結果（実行SQL）

array_to_string
1,2,3

この値をすべてのhuman_idsカラムの中に適切に入れることができれば今回の作業は完了です。

段階2：テキストを分割してテーブル出力

次はカンマ区切りのテキストを分割してテーブル形式で出力する作業を練習していきます。これができればteamテーブル内で1行にまとめられてしまった生徒たちをひとりひとり調べることも可能になる……かもしれません。

複雑なソースコードを一発書きするためには多くの経験を求められるので、初めのうちは段階を踏んで要件をひとつだけ満たす最低限のソースコード（スクリプト）を組み立てていきましょう。

phase2.sql

-- Qiita学校のチームをひとつ抽出してテーブル出力
SELECT regexp_split_to_table(humans,',') 
FROM team
WHERE id = 4
AND school = 'Qiita';

出力結果（実行SQL）

regexp_split_to_table
佐藤
田中
鈴木

テーブルを生成することができれば、後に複雑なクエリを作成するときにサブクエリとして利用することができるようになりますね！

段階3：生成したテーブルを基にIDを取得

ここから少しSQLが長くなり始めます。
いきなりこのステップからやり始めると行き詰るかもしれません。

phase3.sql

-- teamのhumansを基にしてhuman_noを抽出したい（1個だけ）
SELECT 
team.id,
team.school,
human.name,
human.human_no
FROM team
INNER JOIN human ON (team.school = human.school)
WHERE team.id = 4 
AND human.name IN ( -- カンマ区切りのタグ名をひとつずつ分けてテーブル出力するサブクエリ 
	SELECT regexp_split_to_table(team2.humans,',') as human_ids_sub 
				   FROM team team2
				  WHERE team2.id = 4)
order by human.human_no asc;

出力結果（実行SQL）

id	school	name	human_no
4	Qiita	佐藤	1
4	Qiita	田中	2
4	Qiita	鈴木	3

INNER JOINによってhumanテーブルとteamテーブルを結合しているのですが、この際に無駄な結合が発生しています（どのように無駄かは、SELECTで全出力して試してみてください）。
humansカラムの名前をカンマごとに分割してテーブルにし、そのテーブルを用いたサブクエリによってhuman_noの特定を行なうことができております。

正直手を動かしてみないと理解が難しい場所なので、やってみてくださいｼﾁｼﾁ（←汗汗って意味らしいです）。

段階4：取得したhuman_noをhuman_idsに格納する練習（単体対象）

ここから本記事の核心に迫っていきます。この辺りから私の理解が追い付かなくなっていったため、WITH句を用いてSQLの整理をしています。
大規模なスクリプト・ソースコードにはセンスの良いネーミングとコメントが欠かせませんね。

phase4.sql

/*
 * -----------------------------------
 * 取得したtag_idをtag_idsのフォーマットで更新する練習（単体の名刺）
 * -----------------------------------
 */
WITH human_ids_sub AS ( -- nameからhuman_noを抽出するSQL
SELECT team.id,
       human.school,
       human.name,
       human.human_no
FROM team
INNER JOIN human ON (team.school = human.school)
WHERE team.id = 4
AND human.name in ( -- 「,」区切りのnameをひとつずつ分けてテーブル出力する
    SELECT regexp_split_to_table(team2.humans,',')
    FROM team team2
    WHERE team2.id = 4)
ORDER BY human.human_no ASC)
-- 更新処理
UPDATE ONLY team SET human_ids = (
    SELECT array_to_string(array_agg(human_ids_sub.human_no),',') AS human_ids_sub
    FROM human_ids_sub)
WHERE id = 4;
-- 結果表示
SELECT * FROM team ORDER BY id ASC;

出力結果（実行SQL）

id	school	humans	human_ids
1	Qiita
2	Qiita	佐藤
3	Qiita	佐藤,鈴木
4	Qiita	佐藤,田中,鈴木	1,2,3
5	Qiita	田中,高橋

ここまで来ると「もう何をやっているんだろう……？」という気持ちになります。これだけ長いSQLを記述しても所詮アンチパターンですからね。いずれはスキーマ変更に飲み込まれて消えゆく運命にあります。
しかし目先の問題を解決するためにも一歩ずつ、確実に目的に迫っていきましょう。

次の段階ではteamテーブルの全てのhuman_idsにhuman_noを格納していきます。

ただしUPDATEは実行しません。SQL Fiddleの存在を知る前にこのSQLを書いていたのですが、DBにUPDATEを何度もかけているうちにDBが汚染されてしまったことに、後から気付いたのです。反省………。

段階5：取得したhuman_noを基にhuman_idsを生成する練習（複数対象）

単体のターゲットを対象に処理が上手く動いたからといって複数のターゲットに対して同様の処理を走らせられるとは限らないんですよね。
実際にやろうとした際に行き詰って気付きました。

じゃあなぜわざわざ段階を踏む必要があるのかというと、そうしないと理解が進まないからです。
難儀な性分に育ってしまったなぁ……。いきなり答えを書き出そうとして、それができてしまう人は、段階を踏まない方が効率がいいことでしょう。

phase5.sql

/*
 * -----------------------------------
 * 取得したtag_idをtag_idsのフォーマットで表示する練習（複数の名刺）
 * -----------------------------------
 */
WITH find_human_no AS ( -- タグ名からtag_idを抽出するSQL
	SELECT 
		team.id,
		team.humans,
		human.school,
		human.name,
		human.human_no
	FROM team
	INNER JOIN human ON (team.school = human.school)
	WHERE team.id IN (3,4) 
	AND human.name IN ( -- ,（カンマ）区切りのタグ名をひとつずつ分けてテーブル出力するサブクエリ
		SELECT regexp_split_to_table(team2.humans,',') 
			FROM team team2
			WHERE team2.id IN (3,4))
	GROUP BY
		team.id,
		team.humans,
		human.school,
		human.name,
		human.human_no
	ORDER BY team.id, human.human_no ASC
),
format_human_ids AS ( -- tag_idからtag_idsを作成するSQL
	SELECT
		id,
		array_to_string(array_agg(human_no),',') AS human_ids
	FROM find_human_no
	WHERE name IN (SELECT regexp_split_to_table(humans,','))
	GROUP BY id
)
-- 結果表示
SELECT * FROM format_human_ids;

出力結果（実行SQL）

id	human_ids
3	1,3
4	1,2,3

ここまでできれば、このスクリプトで生成することのできたhuman_idsをいまだ空欄となっている大本のテーブルにUPDATEしていく作業に取り掛かることができます。
human_idsの値がNull,Emptyになった場合の処理や、上記スクリプトで検索することのできないnameだった場合の処理などを考える必要がありますが、8割がた完成したと言ってもよいでしょう。

段階6：カンマ区切りのレコードからデータを抽出してIDを取得し、カンマ区切りのIDを作成するSQL

タイトル長っが。
この段階で本記事の主題を満たします。SQLアンチパターンから生まれた厄介な問題に立ち向かい続けて、ついにそれを解決するまでにいたることができそうです。

phase6.sql

/*
 * -----------------------------------
 * 人間（human）とチーム（team）のデータを連携するための補正処理
 * -----------------------------------
 */ 
 WITH find_human_no AS 
	( -- 名前からhuman_noを抽出するSQL
 	    SELECT team.id, 
	           team.humans, 
	           human.name, 
	           human.human_no
	    FROM team
	    INNER JOIN human ON (team.school = human.school) 
	    WHERE human.name IN 
				( --,（カンマ）区切りのタグ名をひとつずつ分けてテーブル出力するサブクエリ
 					SELECT REGEXP_SPLIT_TO_TABLE(team2.humans,',') 
					FROM team team2
				) 
		GROUP BY team.id, 
                 team.humans, 
		         human.name, 
		         human.human_no 
		ORDER BY team.id,
			     human.human_no ASC), 
 format_human_ids AS 
	( -- human_noからhuman_idsを作成するSQL
 	SELECT id, 
		   humans, 
		   ARRAY_TO_STRING(ARRAY_AGG(human_no),',') AS human_ids 
	FROM find_human_no 
	WHERE name IN 
				( -- nameがhumansの中に存在するか
 				SELECT REGEXP_SPLIT_TO_TABLE(humans,',')
				)
		GROUP BY id,
			     humans
	)

-- humanとteamの連携
UPDATE team
SET human_ids = format_human_ids.human_ids
FROM format_human_ids
WHERE team.id = format_human_ids.id;

-- 結果の表示
SELECT id,
humans, 
human_ids
FROM team
ORDER BY id ASC;

出力結果（実行SQL）

id	humans	human_ids
1
2	佐藤	1
3	佐藤,鈴木	1,3
4	佐藤,田中,鈴木	1,2,3
5	高橋,田中	2

遂に完成しました。出力結果を見ると、ゴミデータとしてhumansに存在していた「高橋」はhuman_idsに加えられることはありませんでした。（全国の高橋さん、なんとなくごめんなさい）。
あとはバックエンドの処理にてhuman_idsを参照した処理を記述すれば一見落着です。

……とはいきません。
これはSQLアンチパターンに基づいて作成されたDBスキーマを変更するリソースを会社から与えられなかったために実施した一時しのぎです。

段階7以降でDBスキーマの変更やデータ移行など、マイグレーション処理を行なっていく必要があります。
そのため今回の補正SQLで得られるものは束の間の平和、といったところでしょうか。
しかしこれで一応要件は満たせたので、タスク的には完了となり、開発者であるあなたは次のタスクに取り掛かることができるようになりました。

おめでとうございます。

段階7以降…

段階7以降はSQLアンチパターンとなっているDB構造を何とかしようとする内容……本記事においては蛇足な項目となります。
そのため本記事はまだ最後まで書きあがっていませんが一旦公開させていただきます。

更新予定
段階7：スキーマ構成を変えるために提案をする（SQLアンチパターンのため）
段階8：テーブル設計を行う
段階9：マイグレーションを行なう練習
段階10：マイグレーション本番

続きが気になるかたは是非LGTMをよろしくお願いします。