可視化SQL | レポートの整形(その２) 

# 初めに
この問題は、SQLパズル #62 『レポートの整形』 を参考にしています
手元に『SQL パズル』があれば問題の詳細が記載されているのでよりわかりやすいです

このパズルの目的はとてもシンプルです。
名前一覧のテーブルが有り、その表示を2行1列 や 3行1列 の様に表示させるです

・・と、言うのは簡単ですが、こんな事わざわざSQLではやらないでしょう
著作者のジョーセルコさんも言っています

～～～～～～～～～～～～～～～～～～～～～～～～～～～
本文より引用

SQLはデータ検索言語であって
レポートを作成するための道具ではない
残念なことに、人々はこの点に無感覚で
本来SQLのやるべき仕事ではないことまで
SQLでやろうとするケースが散見される。
・・・・
～～～～～～～～～～～～～～～～～～～～～～～～～～～

『その２』では、『その１』と少しアプローチがちがいます・・・が
全体となる元データを作って、取出したい行を MOD関数で求める考え方は同じです

今回は1行5列で表示させます
▼ 出力する一覧のイメージ

![re_1.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/663ee7c8-b4b9-32c3-cb14-f14b6efa0755.png)


**PostgreSQL で動作確認しています**

# テーブル と データ
```SQL:SQL
CREATE TABLE Names(
	name VARCHAR(15) NOT NULL PRIMARY KEY
);

INSERT INTO Names
VALUES ('Al'), 
	   ('Ben'),
	   ('Charlie'),
       ('David'),
	   ('Ed'),
	   ('Frank'),
       ('Greg'),
	   ('Howard'),
	   ('Ida'),
       ('Joe'),
	   ('Ken'),
	   ('Larry'),
       ('Mike'),
	   ('Neal');
```

▼ 出力

![re_2.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/4cdfa31c-c9f0-9f13-ef21-0ff07c37fecd.png)

# INNER JOINで元データを作る
```SQL:SQL
SELECT N1.name,N2.name
FROM Names AS N1 
INNER JOIN Names AS N2 ON N1.name >= N2.name
```

▼ 出力(画像左側)

名前が2列に表示されますが、下記画像の様に並び方を変形しています
これから解説するのに、こちらの方が都合がいいためです

![re_3.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/90fc966a-2519-7340-c888-1f52c01cf60d.png)

▼ 出力(形を変えて表示させています)

![re_4.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/c91b01a0-ce8b-1d44-2469-22a1ed3b97f3.png)


INNER JOIN の結合条件に注目します
　⇒　**N1.name >= N2.name**


組合せを作る時に、**自分の名前と同じ、または前の名前を全て結合させています**
　⇒　N1.name を自分とすると、自分と同じまたは自分より前の N2.name
　⇒　自分が A の場合、A
　⇒　自分が B の場合、A,B
　⇒　自分が C の場合、A,B,C
　　　・　　・　　・　　・
　　　・　　・　　・　　・
　⇒　自分が N の場合、A,B,C,D,E,D,G,H,I,J,K,L,M,N

N1.name >= N2.name は何をしているか？
　⇒　**同じ組合せを除外する** 
　⇒　**『=』 がついているので、自分と同じ名前の組合せも含みます**
　⇒　Greg  Greg の様な組合せは存在します


![re_5.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/5fca3deb-83e4-8364-8d7c-fa927ac67f2d.png)




このINNER JOIN して作った元データですが、その用途が『その１』と大きく異なります
『その１』では1行2列表示の為、JOINした後のデータが既に1行2列となっていました

しかし、今回は1行5列で表示させるので同じ様にはいきません。
`この元データで必要なのは N1.name だけです`
　⇒　一番左側に表示される名前(太文字)
　⇒　この名前を Names AS N1 から取り出します

目指すとことは
**まず一番に表示される名前を元データから取り出して**
**その横に1行5列になるように名前を結合させます**

下記が1行5列を作るイメージです


![re_6.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/4b3d4776-5b89-8f83-71e4-7e8857d7f26b.png)



# 元データを加工する①
```SQL:SQL
SELECT N1.name,COUNT(*),MOD(COUNT(*), 5)
FROM Names AS N1 
INNER JOIN Names AS N2 ON N1.name >= N2.name
GROUP BY N1.name
```

▼ 出力

N1.name で GROUP BY してから数を数える

MOD(COUNT(＊), 5)
　⇒　COUNT(＊) を 5 で割り算した余りを取出す


![re_7.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/1a70200b-dad7-189d-3a90-4215b7b1177c.png)

一番左側に来る名前が Al, Frank, Ken なので、この名前を取出すには
MOD(COUNT(＊), 5) が 1 となる行を指定すると取り出せます

# 元データを加工する②
```SQL:SQL
SELECT N1.name,COUNT(*),MOD(COUNT(*), 5)
FROM Names AS N1 
INNER JOIN Names AS N2 ON N1.name >= N2.name
GROUP BY N1.name
HAVING MOD(COUNT(*), 5) = 1  -- 追加
```

▼ 出力

HAVING MOD(COUNT(＊), 5) = 1
　⇒　MOD(COUNT(＊), 5) が 1 となる行を取出します
　⇒　一番左側に来る名前が取り出せました


![re_8.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/d80c5f5d-4d61-80a5-743f-bd549d33cc7e.png)

# 2つ目の名前を取出す①
```SQL:SQL
SELECT N3.name,N4.name
FROM
(
	SELECT N1.name
	FROM Names AS N1 
	INNER JOIN Names AS N2 ON N1.name >= N2.name
	GROUP BY N1.name
	HAVING MOD(COUNT(*), 5) = 1
) AS N3(name) 
LEFT JOIN Names AS N4 ON N3.name < N4.name -- 2つ目の名前
```

▼ 出力

一番左側の名前を取得するSQLに対して、Names テーブルを LEFT JOIN します
　⇒　ここから、2つ目に表示される名前を取出します

LEFT JOIN 結合条件
　⇒　N3.name < N4.name 
　⇒　N4.name が2つ目に表示される名前なので、左側の名前より後の名前になります
　⇒　同じ名前は並ばないので、『=』はついていません
　⇒　**このSQLの出力結果から、太文字の行を取出します**


![re_9.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/121cf873-4b73-281e-156b-f3759308cc01.png)




# 2つ目の名前を取出す②
```SQL:SQL
SELECT N3.name,MIN(N4.name)
FROM
(
	SELECT N1.name
	FROM Names AS N1 
	INNER JOIN Names AS N2 ON N1.name >= N2.name
	GROUP BY N1.name
	HAVING MOD(COUNT(*), 5) = 1
) AS N3(name) 
LEFT JOIN Names AS N4 ON N3.name < N4.name -- 2つ目の名前
GROUP BY N3.name
```

▼ 出力

一番左に来る名前(N3.name)でGROUP BY することで、2つ目に表示する名前を取出します
　⇒　AI, Frank, Ken は1つだけ残ります
　⇒　N4.name で欲しいのは先頭の名前になります
　⇒　MIN(N4.name) で取り出せます


![re_10.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/0a14daa6-e5f2-954b-1da8-eb91623ec3fc.png)

# 3つ目の名前を取出す①
```SQL:SQL
SELECT N3.name,N4.name,N5.name
FROM
(
	SELECT N1.name
	FROM Names AS N1 
	INNER JOIN Names AS N2 ON N1.name >= N2.name
	GROUP BY N1.name
	HAVING MOD(COUNT(*), 5) = 1
) AS N3(name) 
LEFT JOIN Names AS N4 ON N3.name < N4.name -- 2つ目の名前
LEFT JOIN Names AS N5 ON N4.name < N5.name -- 3つ目の名前
```


3つ目の名前を取出してみます
一番左側に来る名前に、2つの Names テーブルを LEFT JOIN します
　⇒　N4から、2つ目の名前を取出します(上記で対応済)
　⇒　N5から、3つ目の名前を取出します

2つ目の LEFT JOIN を追加すると AI だけで、79行のデータが結合されます
LEFT JOIN している様子をご確認ください

3つ目の名前を取出す LEFT JOIN 結合条件
　⇒　N4.name < N5.name
　⇒　2人目がBenの場合、3人目の組合せはCharlie以下12人います
　⇒　2人目がChalieの場合、3人目の組合せはDavid以下11人います
　⇒　2人目がDavidの場合、3人目の組合せはEd以下10人います
　　　・　　・　　・　　・　　・　　・
　　　・　　・　　・　　・　　・　　・
　⇒　2人目がMikeの場合、3人目の組合せはNeal以下1人います
　⇒　2人目がNealの場合、3人目の組合せは存在しません


▼ 出力(LEFT JOIN の様子)


![re_11.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/e4bcdb61-93fb-3939-3b33-bd8332cf569d.png)



----

▼ 出力(Frank から始まる結合データ)

同じ様に、AI,Ken の結合データが存在します

![re_12.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/eafcd5b8-99ac-e889-b439-c46d68044ef7.png)

# 3つ目の名前を取出す②
```SQL:SQL
SELECT N3.name,MIN(N4.name),MIN(N5.name)
FROM
(
	SELECT N1.name
	FROM Names AS N1 
	INNER JOIN Names AS N2 ON N1.name >= N2.name
	GROUP BY N1.name
	HAVING MOD(COUNT(*), 5) = 1
) AS N3(name) 
LEFT JOIN Names AS N4 ON N3.name < N4.name -- 2つ目の名前
LEFT JOIN Names AS N5 ON N4.name < N5.name -- 3つ目の名前
GROUP BY N3.name
```

▼ 出力(画像左側)

3つ目の名前の取出しも、2つ目と同じです。
一番左の名前 N3.name で GROUP BY してから N5.name の先頭を取出す
　⇒　2つ目の名前(N4.Name)からGregを1つ取出したいので、MIN(N4.name)とします
　⇒　3つ目の名前は(N5.Name)の先頭なので、MIN(N5.name)で取り出します


![re_13.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/e8bef33b-a153-fa13-4a61-3d0b35fe9fe8.png)




------

Frankの場合の `4つ目の名前` を取出す為に3つ目の LEFT JOIN を行います
取出したい名前は **Ida** ですが
もう一つ LEFT JOIN を追加しただけで Frankには64行のデータが結合されすが
必要なのは最初の1行だけ。その他は無駄な結合データとなります。

5つ目の名前を取出すにはもう一つ LEFT JOIN が必要になります
もっと無駄なデータが出てきます

Frankより名前が前にある AI の場合は、これより行数が多くなります

つまり、この手法で1行5列の名前を取出そうとすると
**Names テーブルを4回自己結合しているので、かなり無駄な行数を作成しています**

しかし、このパズルは元々SQLで対応するには実用的ではない・・・と分かっているので
こんな方法でデータが取り出せる・・・と見ていただければと思います

今回の様な自己結合を複数回使わない方法で
同じ結果を取出すSQLを考えた強者がいらっしゃいます

別記事で解説したいと思います

▼出力(Frankで始まる名前で4つ目の名前を取出す元データ)

![re_14.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/c2102f56-d0b7-2c18-5768-97de7f9c425e.png)


# 回答SQL
```SQL:SQL
SELECT N3.name,MIN(N4.name),MIN(N5.name),MIN(N6.name),MIN(N7.name)
FROM
(
	SELECT N1.name
	FROM Names AS N1 
	INNER JOIN Names AS N2 ON N1.name >= N2.name
	GROUP BY N1.name
	HAVING MOD(COUNT(*), 5) = 1
) AS N3(name) 
LEFT JOIN Names AS N4 ON N3.name < N4.name -- 2つ目の名前
LEFT JOIN Names AS N5 ON N4.name < N5.name -- 3つ目の名前
LEFT JOIN Names AS N6 ON N5.name < N6.name -- 4つ目の名前
LEFT JOIN Names AS N7 ON N6.name < N7.name -- 5つ目の名前
GROUP BY N3.name
```

▼ 出力

4つ目、5つ目の名前の取出しは同じ様にLEFT JOIN を追加します
名前の取出しはMIN(N6.name),MIN(N7.name)となります

最初のINNER JOIN で作った元データから、一番左に表示される名前を取出します
この名前に対して、LEFT JOIN を使って名前を結合しているのがこのSQLです


![re_15.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/4492c72c-8e18-d781-5da7-f940dd94b5ba.png)



# 参考文献
[SQLパズル 第2版~プログラミングが変わる書き方/考え方 | Joe Celko, ミック](https://www.shoeisha.co.jp/book/detail/9784798114132)