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

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

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

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


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

『その４』がこのパズルの最後の解説となります

『その４』でも同じくMOD関数を使用していますが１度だけです
１番左に来る名前の取り出しに使っています

2人目、3人目・・・の取り出し方がとても技術的・・・といいますか
『その３』と同様に
よくこんなSQLを考えたなぁ～～と、ただただ感服です

それでは解説を始めます！！

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

![re_1.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/310ffc78-4d08-dbd8-d84a-4c1b22ff2bb1.jpeg)

**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.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/11fab2b2-dae4-c55b-8f7b-857d91b0240f.jpeg)

# 元データを作る①
```SQL:SQL
SELECT N1.name, N2.name
FROM Names AS N1, Names AS N2
WHERE N1.name >= N2.name
```

『その２』『その３』では INNER JOIN してON に条件を書く
> SELECT N1.name, N2.name
> FROM Names AS N1
> INNER JOIN Names AS N2 ON **N1.name >= N2.name**

『その４』では Names テーブルを CROSS JOIN してから
WHERE句に条件を書いています

出力結果は全く同じです。ただ記述方法が違うだけです

下記画像は出力される N1.name, N2.name を
横並びに表示させる解説画像です

元データの詳細については『その２』『その３』をご確認ください
『その４』では割愛させていただきます

![re_3.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/13e29f5f-b1c3-d9e3-c216-937753d269f6.jpeg)

-------

▼出力

![re_4.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/a0ad23be-3ad6-4c1c-62fb-b9b7088c8874.jpeg)


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

COUNT(＊)を追加
　⇒　N1.name で GROUP BY されているので、N1.name が持っている行数
　⇒　N1.name に結合されているN2.nameの数です

MOD(COUNT(＊), 4) を追加
　⇒　COUNT(＊)の数字を 4 で割った余りです

![re_5.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/066963fb-e055-d117-cb66-8024998fea61.jpeg)

-----

▼出力

ここまでは特に『その２』『その３』と変わりません。
何か見たことのある様な下準備です

『その４』はココから変わってきます。２人目、３人目・・・を取出す為の
元データをもう1つ作成します


![re_6.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/87d291f5-0ff5-e1a5-8929-521f4f9808f9.jpeg)


# 元データ2 を作る
```SQL:SQL
SELECT N3.name, N4.name, N5.name
FROM Names AS N3, Names AS N4, Names AS N5
WHERE N3.name < N5.name 
```

Names テーブルを3つ CROSS JOIN しています
　⇒　14 x 14 x 14 = 2744 行のデータが結合されます
　⇒　ここから N3.name < N5.name の条件で 1274 行になります

行が多いので一部抜粋して解説します

『N3.name < N5.name』の要件により下記は除外されます(画像右側)
　⇒　N5.name が Al の組合せは除外されます(緑色)
　⇒　N3.name が Neal の組合せは除外されます(青色)
　⇒　N3.name > N5.name となる組合せ(黄色)

▼出力(画像左側)

この状態では、まだこの処理の意図が分かりません・・・


![re_7.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/d29ec059-a77f-9e78-c12f-fe1428f0df1b.jpeg)

# 元データ2 を加工する①
```SQL:SQL
SELECT N3.name, N4.name, N5.name
FROM Names AS N3, Names AS N4, Names AS N5
WHERE N3.name < N5.name 
  AND N4.name BETWEEN N3.name AND N5.name

order by N3.name, N5.name, N4.name
```

WHERE句に条件を追加
　⇒　`N4.name BETWEEN N3.name AND N5.name`
　⇒　N4.name が N3.name と N5.name の間にある名前が残ります
　⇒　N4.name が N.3.name または N5.name と同じでも条件を満たします

▼出力(画像右側)

何か規則性の様な物が見えてきました・・・
N3.name, N4.name, N5.name がアルファベット順になるような感じです

この条件を追加する前(画像左側)
この条件を追加すると残った行数に規則性が見えてきます(画像右側)
Al の場合、上から 2行、3行、4行・・・とデータが残ります

> **N4.nameの役割**
> N3.name を1番左に来る名前とすると、2番目以降の名前は N5.name となります
> N3.name に結合されている N5.name の数が、上から連番になるようにします
> この規則性を作る為に N4.name が利用されています
>
> 連番については次で解説します


![re_8.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/00c88702-0f5c-1f3b-b0ef-7b8cfccb8b5e.jpeg)


# 元データ2 を加工する②
```SQL:SQL
SELECT N3.name, N5.name, COUNT(*)
FROM Names AS N3, Names AS N4, Names AS N5
WHERE N3.name < N5.name 
  AND N4.name BETWEEN N3.name AND N5.name
GROUP BY N3.name, N5.name

order by N3.name,N5.name
```

N3.name と N5.name で GROUP BY して、行数をCOUNTしています
N4.name は必要ないので表示していません

全体を見ると、規則性が有ります。**COUNT(*)は連番になっています**
2つ目の元データで作りたかったのは・・・
　⇒　`N5.name を使って2人目,3人目,4人目の名前を取出す`準備と
　⇒　その時に必要となる`連番 COUNT(*) の作成`です

Alの場合ですが・・・
　⇒　1番左にAlが選択されているとします
　⇒　次に来るのは Ben なので COUNT(＊)は2
　⇒　その次は Charlie なので COUNT(＊)は3
　⇒　その次は David なので COUNT(＊)は4
　⇒　その次は Frank なので COUNT(＊)は5
　　　・　　・　　・　　・　　・
　　　・　　・　　・　　・　　・

Benの場合ですが・・・
　⇒　１番左にBenが選択されているとします
　⇒　次に来るのは Charlie なので COUNT(＊)は2
　⇒　その次は David なので COUNT(＊)は3
　⇒　その次は Frank なので COUNT(＊)は4
　⇒　その次は Greg なので COUNT(＊)は5
　　　・　　・　　・　　・　　・
　　　・　　・　　・　　・　　・

他の名前も同様です

**Namesテーブルを3つCROSS JOIN して WHERE句の条件で**
**必要な行だけ残すこのSQLは、この規則性のあるデータを作成する為です**


![re_9.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/8d75f7cd-8f04-e0a2-4232-d63cf269a9fb.jpeg)



# 元データ2 を加工する③
```SQL:SQL
SELECT FirstCol.name AS name1,
       OtherCols.final_name,
       OtherCols.cnt
FROM 
(
  SELECT N1.name
  FROM Names AS N1, Names AS N2
  WHERE N1.name >= N2.name
  GROUP BY N1.name
  HAVING MOD(COUNT(*), 4) = 1
) AS FirstCol(name)
LEFT JOIN
(
  SELECT N3.name, N5.name, COUNT(*)
  FROM Names AS N3, Names AS N4, Names AS N5
  WHERE N3.name < N5.name AND N4.name BETWEEN N3.name AND N5.name
  GROUP BY N3.name, N5.name
) AS OtherCols(name,final_name, cnt) 
     ON FirstCol.name = OtherCols.name

order by FirstCol.name,OtherCols.final_name
```

上記で作成した **2つの元データを結合させます**


『元データを加工する①』のSQLからHAVINGを使って1番左に来る名前を取出します
　⇒　サブクエリ名：`FirstCol`
　⇒　MOD(COUNT(＊), 4) が 1となる行
　⇒　Al, Ed, Ida, Mike を取出します


![re_10.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/8bc01ea4-96e1-78f1-63f1-48b311fcb5a3.jpeg)



`FirstCol` から取出した Al, Ed, Ida, Mike に
『元データ2 を加工する②』のデータ(`OtherCols`)をLEFT JOINします

![re_11.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/013eb770-4e71-c690-4b97-fb07f7d33387.jpeg)

----

▼出力(画像左側)

FirstCol から取り出す name(name1) が1人目の名前になります
2人目、3人目、4人目は OtherCols から取り出します・・・が
もう少しSQLの加工が必要です

画像右側に、2人目、3人目、4人目の名前の取出しイメージを置きます

あともう一息です


![re_12.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/ecf1b689-f578-f682-1d24-3a63751d558d.jpeg)



# 回答SQL
```SQL:SQL
SELECT FirstCol.name AS name1,
       MAX(CASE WHEN OtherCols.cnt = 2
                THEN OtherCols.final_name
                ELSE NULL END) AS name2,
       MAX(CASE WHEN OtherCols.cnt = 3
                THEN OtherCols.final_name
                ELSE NULL END) AS name3,
       MAX(CASE WHEN OtherCols.cnt = 4
                THEN OtherCols.final_name
                ELSE NULL END) AS name4
FROM 
(
	SELECT N1.name
	FROM Names AS N1, Names AS N2
	WHERE N1.name >= N2.name
	GROUP BY N1.name
	HAVING MOD(COUNT(*), 4) = 1
) AS FirstCol(name)
LEFT JOIN
(
	SELECT N3.name, N5.name, COUNT(*)
	FROM Names AS N3, Names AS N4, Names AS N5
	WHERE N3.name < N5.name AND N4.name BETWEEN N3.name AND N5.name
	GROUP BY N3.name, N5.name
) AS OtherCols(name,final_name, cnt) 
     ON FirstCol.name = OtherCols.name
GROUP BY FirstCol.name
```

▼出力

![re_14.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/58c014f4-86a8-5d60-0896-af7c95488454.jpeg)

--------


FirstCol.name で GROUP BY しているので
一番左に来る名前(name1)が集約されて1つになります

2人目、3人目、4人目の名前は OtherColsから取得するのですが
この中の N5.name は集約変数ではないので
MAX関数を使って集約して取り出します
**CASE文を使って 縦に並んでいる名前を 横並びに変換しています**

Alの場合
　⇒　1人目が Al
　⇒　2人目は(cnt が2) Ben を Al の横に表示する
　⇒　3人目は(cnt が3) Charlie を Ben の横に表示する
　⇒　4人目は(cnt が4) David を Charlieの表示する
　⇒　cnt が5以上は何も表示しない

名前を取出すイメージは下記の様になります



![re_15.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/7f748b4c-058a-0292-f7f1-91003a63f863.jpeg)


> 『その４』も作者の熱意が感じられるSQLです
> 2人目～4人目を取出すサブクエリ(OtherCols)は素晴らしい！！
> データ結合を熟知してるからこそ出来る技です
> 
> あっぱれ！！です


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