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

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

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

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


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



『その３』ですが、MOD関数を使って、取出したい位置を特定する・・・のは似ていますが
『その１』,『その２』と全くアプローチが違います

元データを作る時に1度だけ JOIN していますが、この1回だけです。
5列になるように、SELECT文を5つ記述しています
一見シンプルに見えますが、巧妙に考えられたSQLです。


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

![re_1.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/5c145301-e000-e837-f670-ef963d3936c5.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/795f8e51-f276-4884-6e2d-b77bb8377fc2.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/3c3839e0-24c6-4f54-611f-97fdd0a40fac.png)

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

![re_4.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/67436426-2d5f-d570-fbe0-655e9af59fee.png)


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


組合せを作る時に、**自分の名前と同じ、または前の名前を全て結合させています**
　⇒　N1.name を自分とすると、自分と同じまたは自分より前の N2.name
　⇒　自分が A の場合、A (AI)
　⇒　自分が B の場合、A,B (AI,Ben)
　⇒　自分が C の場合、A,B,C (AI,Ben,Charlie)
　　　・　　・　　・　　・
　　　・　　・　　・　　・
　⇒　自分が 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/5dca37aa-1694-cc13-9960-0011ac819655.png)

ここまでは『その2』と同じですが・・・ここからアプローチが変わってきます

# 元データを加工する(名前1人目)①
```SQL:SQL
SELECT 
  N1.name, 
 (SELECT COUNT(*) FROM Names AS N3 WHERE N1.name <= N3.name),
 (SELECT array_agg(N3.name) FROM Names AS N3 WHERE N1.name <= N3.name)

FROM Names AS N1
INNER JOIN Names AS N2 ON N1.name >= N2.name
GROUP BY N1.name
```


名前(N1.name) でGROUP BY して14行のユニークの名前のデータを作ります

SELECT文の中に COUNT(＊)を追加
　⇒　N1.name <= N3.name の条件に合うデータを Namesテーブルから取り出してカウント
　⇒　上から順に 14 -> 13 -> 12 -> ・・・ 3 -> 2 -> 1 と連番になっています
　⇒　`この番号を加工して縦に並んでいる名前を横並びに表示させます`

SELECT文の中に array_agg(N3.name)を追加
　⇒　COUNT(＊)の中身を確認しています
　⇒　この名前の集合の数をカウントしています


▼出力(画像上)

![re_6.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/54576804-897f-b59c-38e1-46364f74e1c5.png)



# 元データを加工する(名前１人目)②
```SQL:SQL
SELECT 
  N1.name, 
 (SELECT COUNT(*) FROM Names AS N3 WHERE N1.name <= N3.name),
 (SELECT MOD(COUNT(*), 5) FROM Names AS N3 WHERE N1.name <= N3.name), -- 追加
 (SELECT array_agg(N3.name) FROM Names AS N3 WHERE N1.name <= N3.name)

FROM Names AS N1
INNER JOIN Names AS N2 ON N1.name >= N2.name
GROUP BY N1.name
```

SELECT文の中に、MOD(COUNT(＊), 5) を追加
　⇒　名前の集合をカウントして、5で割った余りです
　⇒　4 -> 3 -> 2 -> 1 -> 0 -> 4 -> 3 ・・・・
　⇒　`0～4 の5つの数字になります`
　⇒　言い換えると、**5つのグループに分類ができます**
　⇒　1行5列に加工する際にこの数字を利用します


▼出力

![re_7.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/42247eaa-107d-e408-98d0-07b16005a45a.png)



# 元データを加工する(名前１人目)③
```SQL:SQL
SELECT 
  N1.name, 
 (SELECT COUNT(*) FROM Names),
 (SELECT MOD(COUNT(*), 5) FROM Names)

FROM Names AS N1
INNER JOIN Names AS N2 ON N1.name >= N2.name
GROUP BY N1.name
```

▼出力

`SELECT COUNT(*) FROM Names`
　⇒　Names テーブルには14名の名前が有り、カウントしてます

`SELECT MOD(COUNT(*), 5) FROM Names`
　⇒　Names テーブルに入っている名前の数をカウントして5で割った余りです
　⇒　14 ÷ 5 の余りは 4
　⇒　MOD(COUNT(＊), 5) = 4
　⇒　1行5列に加工する際にこの数字を利用します



![re_8.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/558bc234-9f4a-32d2-c565-906e9be3a779.png)

# 元データを加工する(名前１人目)④
```SQL:SQL
SELECT 
  N1.name,
  (SELECT MOD(COUNT(*), 5) FROM Names AS N3 WHERE N1.name <= N3.name),
  (SELECT MOD(COUNT(*), 5) FROM Names),

  (SELECT MAX(N1.name) FROM Names AS N3 WHERE N1.name <= N3.name
     HAVING MOD(COUNT(*), 5) = (SELECT MOD(COUNT(*), 5) FROM Names)
  ),
  (SELECT array_agg(N1.name) FROM Names AS N3 WHERE N1.name <= N3.name
     HAVING MOD(COUNT(*), 5) = (SELECT MOD(COUNT(*), 5) FROM Names)
  )
FROM Names AS N1
INNER JOIN Names AS N2 ON N1.name >= N2.name
GROUP BY N1.name
```


1人目の名前を取出しています。取出し方を詳しく解説します


`SELECT MOD(COUNT(*), 5) FROM Names AS N3 WHERE N1.name <= N3.name`
　⇒　**この MOD関数の出力は 0番～4番の5つのグループに分類する為の数値です(赤線)**
　⇒　5列1行の 列 を特定する番号(**分類番号**と呼びます)
　⇒　**分類番号**と呼びます
　⇒　このMOD関数をSELECT文のHAVING句で使っています
　⇒　2人目、3人目・・・の取出しもこの数値を使います

`SELECT MOD(COUNT(*), 5) FROM Names`
　⇒　**この MOD関数の出力は 取出したい名前の行を決める為の数値です(青線)**
　⇒　このMOD関数をSELECT文のHAVING句で使っています
　⇒　**分類番号(4) を取出す為の定数です**


Having句の中にこの2つのMOD関数を条件として記述します
　⇒　2つのMOD関数の戻り値が同じ場所を特定している
　⇒　4 = 4 となる行
　⇒　AI, Frank, Ken がこの条件に一致します

名前(N1.name) を取出したいのですが、そのままでは取り出せません
　⇒　array_agg(N1.name) を見ると、名前の集合になっています
　⇒　MAX(N1.name) を使って 1つだけ名前を取出しています
　⇒　集約できればいいので MIN(N1.name) でも大丈夫です


![re_10.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/9b3c716e-4601-5676-a895-67cb68093ea5.png)


# 元データを加工する(名前１人目)⑤
```SQL:SQL
SELECT 
  N1.name,
  (SELECT MAX(N1.name) FROM Names AS N3 WHERE N1.name <= N3.name
     HAVING MOD(COUNT(*), 5) = (SELECT MOD(COUNT(*), 5) FROM Names)
  )
FROM Names AS N1
INNER JOIN Names AS N2 ON N1.name >= N2.name
GROUP BY N1.name
```

▼出力(画像左)

最初の N1.name と 5列1行の1人目の名前だけを残しました
2人目、3人目、4人目、5人目の名前の取出しイメージを右側に置きます

次に、同じ手法で2人目を取出してみます


![re_10.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/a9481cc7-7805-4512-5407-d4328850d06f.png)

# 元データを加工する(名前２人目)①
```SQL:SQL
SELECT 
	N1.name,
	(SELECT COUNT(*) FROM Names),
	(SELECT MOD(COUNT(*)-1, 5) FROM Names)

FROM Names AS N1
INNER JOIN Names AS N2 ON N1.name >= N2.name
GROUP BY N1.name
```

▼出力

『 元データを加工する(名前１人目)③ 』 のSQLとほとんど同じです
違いは COUNT(＊) -1 としている所です

上でも言いましたが・・・
`SELECT MOD(COUNT(＊)-1, 5) FROM Names`　は名前を取出したい場所を指定する定数です

元データの N1.name は14行でアルファベット順に並んでいます
　⇒　1人目の取出しの時に名前(N1.name)を  0～4 の番号に分類しました(**分類番号**) 
　⇒　`SELECT MOD(COUNT(＊), 5) FROM Names AS N3 WHERE N1.name <= N3.name`
　⇒　取出したい場所を `SELECT MOD(COUNT(＊)-1, 5) FROM Names` で指定します
　⇒　**分類番号が 3** となる行を元データから取り出します

![re_11.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/60c43fae-0c6a-4ebd-6c77-eeddda470df4.png)

# 元データを加工する(名前２人目)②
```SQL:SQL
SELECT 
  N1.name,
  (SELECT MOD(COUNT(*), 5) FROM Names AS N3 WHERE N1.name <= N3.name),
  (SELECT MOD(COUNT(*)-1, 5) FROM Names),

  (SELECT MAX(N1.name) FROM Names AS N3 WHERE N1.name <= N3.name
     HAVING MOD(COUNT(*), 5) = (SELECT MOD(COUNT(*)-1, 5) FROM Names)
  ),
  (SELECT array_agg(N1.name) FROM Names AS N3 WHERE N1.name <= N3.name
     HAVING MOD(COUNT(*), 5) = (SELECT MOD(COUNT(*)-1, 5) FROM Names)
  )
FROM Names AS N1
INNER JOIN Names AS N2 ON N1.name >= N2.name
GROUP BY N1.name
```

1人目を取出したのと同じ考え方です

[1] SELECT文の2つ目
　⇒　(SELECT MOD(COUNT(*), 5) FROM Names AS N3 WHERE N1.name <= N3.name)
　⇒　N1.name が縦に14名の名前で並んでます
　⇒　この名前を 0～4 の番号で分類しています(分類番号) 

[2] SELECT文の3つ目
　⇒　SELECT MOD(COUNT(＊)-1, 5) FROM Names
　⇒　取出したい2人目の名前は、**分類番号が 3** です

[1]と[2] を 1つの SELECT文でHAVING句の条件で記述します
　⇒　3 = 3 となる行
　⇒　Ben, Greg, Larry が条件に一致します

N1.name は 名前の集合になっているので、MAX()関数で集約して1つを取出します
　⇒ MAX(N1.name)

![re_13.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/7b770428-2b33-9664-f93b-4620a54585c0.png)


# 元データを加工する(名前２人目)③
```SQL:SQL
SELECT 
  N1.name,
  (SELECT MAX(N1.name) FROM Names AS N3 WHERE N1.name <= N3.name
     HAVING MOD(COUNT(*), 5) = (SELECT MOD(COUNT(*)-1, 5) FROM Names)
  )
FROM Names AS N1
INNER JOIN Names AS N2 ON N1.name >= N2.name
GROUP BY N1.name
```

▼出力

最初の N1.name と 5列1行の2人目の名前だけを残しました
次に、1人目と2人目の名前を同時に取出してみます

![re_14.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/a443b1da-6804-b5a1-0e16-e7f4c337e931.png)

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

▼出力

1人目、2人目の名前を取出す SELECT文を並べてみました
これを見ると、3人目、4人目、5人目・・・は想像がつきます

取出したい場所を指定する `SELECT MOD(COUNT(*), 5) FROM Names` の
COUNT(＊) を調整すると取出せそうです

![re_15.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/02bd9d49-6513-ce0e-2d86-9d9f8c290eb4.png)

# 1人目 ～ 5人目の名前を取出す
```SQL:SQL
SELECT
  N1.name,
  (COUNT(*) - 1) / 5,

  (SELECT MOD(COUNT(*), 5) FROM Names AS N3 WHERE N1.name <= N3.name),

  (SELECT MAX(N1.name) FROM Names AS N3 WHERE N1.name <= N3.name
     HAVING MOD(COUNT(*), 5) = (SELECT MOD(COUNT(*), 5) FROM Names)
  ),
  (SELECT MAX(N1.name) FROM Names AS N3 WHERE N1.name <= N3.name
     HAVING MOD(COUNT(*), 5) = (SELECT MOD(COUNT(*)-1, 5) FROM Names)
  ),
  (SELECT MAX(N1.name) FROM Names AS N3 WHERE N1.name <= N3.name
     HAVING MOD(COUNT(*), 5) = (SELECT MOD(COUNT(*)-2, 5) FROM Names)
  ),
  (SELECT MAX(N1.name) FROM Names AS N3 WHERE N1.name <= N3.name
     HAVING MOD(COUNT(*), 5) = (SELECT MOD(COUNT(*)-3, 5) FROM Names)
  ),
  (SELECT MAX(N1.name) FROM Names AS N3 WHERE N1.name <= N3.name
     HAVING MOD(COUNT(*), 5) = (SELECT MOD(COUNT(*)-4, 5) FROM Names)
  )
FROM Names AS N1
INNER JOIN Names AS N2 ON N1.name >= N2.name
GROUP BY N1.name
```

SELECT文の3つ目
　⇒　(SELECT MOD(COUNT(*), 5) FROM Names AS N3 WHERE N1.name <= N3.name)
　⇒　この MOD関数を HAVING句で使用しています
　⇒　分類番号


取出したい名前を指定する(SELECT MOD(COUNT(＊), 5) FROM Names)を調整します
　⇒　取出したい行(分類番号)を指定します

～～～～～～～～～～～～～～～～～～～～～～～～～～
1人目の取出し(分類番号:4)
　⇒　(SELECT MOD(COUNT(＊), 5) FROM Names)
　⇒　14 ÷ 5 の余り 4
～～～～～～～～～～～～～～～～～～～～～～～～～～
2人目の取出し(分類番号:3)
　⇒　(SELECT MOD(COUNT(＊)**-1**, 5) FROM Names)
　⇒　13 ÷ 5 の余り 3
～～～～～～～～～～～～～～～～～～～～～～～～～～
3人目の取出し(分類番号:2)
　⇒　(SELECT MOD(COUNT(＊)**-2**, 5) FROM Names)
　⇒　12 ÷ 5 の余り 2
～～～～～～～～～～～～～～～～～～～～～～～～～～
4人目の取出し(分類番号:1)
　⇒　(SELECT MOD(COUNT(＊)**-3**, 5) FROM Names)
　⇒　11 ÷ 5 の余り 1
～～～～～～～～～～～～～～～～～～～～～～～～～～
5人目の取出し(分類番号:0)
　⇒　(SELECT MOD(COUNT(＊)**-4**, 5) FROM Names)
　⇒　10 ÷ 5 の余り 0
～～～～～～～～～～～～～～～～～～～～～～～～～～

▼出力

![re_16.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/4c6ae940-2824-29f9-88ae-ea60c50ea260.png)




もう一息です。横に5名並んでいますが、空白が有るので5列1行になっていません
ここから空白を取り除きます

その前に、 `(COUNT(*) - 1) / 5` について解説します
　⇒　COUNT(＊)は N1.name で GROUP BY されているので、N1.name が持っている行数
　⇒　N1.name に結合されているN2.nameの数です
　⇒　元データの画像をご確認ください
　⇒　**この商を使って 0, 1, 2 の3つのグループに分類します**

■ `(COUNT(*) - 1) / 5` の計算
(1-1)  ÷ 5 の商 0 ・・・ AI
(2-1)  ÷ 5 の商 0 ・・・ Ben
(3-1)  ÷ 5 の商 0 ・・・ Charlie
(4-1)  ÷ 5 の商 0 ・・・ David
(5-1)  ÷ 5 の商 0 ・・・ Ed
(6-1)  ÷ 5 の商 1 ・・・ Frank
(7-1)  ÷ 5 の商 1 ・・・ Greg
(8-1)  ÷ 5 の商 1 ・・・ Howard
(9-1)  ÷ 5 の商 1 ・・・ Ida
(10-1) ÷ 5 の商 1 ・・・ Joe
(11-1) ÷ 5 の商 2 ・・・ Ken
(12-1) ÷ 5 の商 2 ・・・ Larry
(13-1) ÷ 5 の商 2 ・・・ Mike
(14-1) ÷ 5 の商 2 ・・・ Neal

![re_17.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/48c3e8cf-11cd-6888-6b8e-d74bbce5e735.png)



# 回答SQL
```SQL:SQL
SELECT MAX(name1), MAX(name2), MAX(name3), MAX(name4), MAX(name5)
FROM
(
  SELECT
    (COUNT(*) - 1) / 5, 

    (SELECT MAX(N1.name) FROM Names AS N3
     WHERE N1.name <= N3.name
     HAVING MOD(COUNT(*), 5)= (SELECT MOD(COUNT(*), 5) FROM Names)),
			
    (SELECT MAX(N1.name) FROM Names AS N3
     WHERE N1.name <= N3.name
     HAVING MOD(COUNT(*), 5) = (SELECT MOD((COUNT(*) - 1), 5) FROM Names)),
			
    (SELECT MAX(N1.name) FROM Names AS N3
     WHERE N1.name <= N3.name
     HAVING MOD(COUNT(*), 5) = (SELECT MOD((COUNT(*) - 2), 5) FROM Names)),

    (SELECT MAX(N1.name) FROM Names AS N3
     WHERE N1.name <= N3.name
     HAVING MOD(COUNT(*), 5) = (SELECT MOD((COUNT(*) - 3), 5) FROM Names)),

    (SELECT MAX(N1.name) FROM Names AS N3
     WHERE N1.name <= N3.name
     HAVING MOD(COUNT(*), 5) = (SELECT MOD((COUNT(*) - 4), 5) FROM Names))
  FROM Names AS N1
  INNER JOIN Names AS N2 ON N1.name >= N2.name
  GROUP BY N1.name
) AS X0(cnt, name1, name2, name3, name4, name5)
GROUP BY cnt;
```

ポイントは先程追加した  `(COUNT(*) - 1) / 5` です
　⇒　0, 1, 2 の3つの数字になります(cnt にリネーム)
　⇒　14行のデータが上から 5,5,4 で三分割されています
　⇒　全体をこの数字で集約(GROUP BY) します
　⇒　0, 1, 2 の3行が残ります
　⇒　各N1.name は集約変数ではないので、このままでは表示できません
　⇒　MAX(name)を使って集約します
　⇒　N1.name は name1,name2,name3,name4,name5 にリネームしてます
　⇒　空白を含む N1.name から1つだけ名前を取出します

▼出力(画像上)


![re_18.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/cd7c42d8-6af6-d51b-15bc-613f2f79b625.png)


>個人的な感想ですが・・・
>
>SQLでデータを抽出する時、どうやってJOINするか・・？が
>頭に浮かびます
>
>しかしこのSQLでは分類(グループ化)を使う事で
>JOIN を使わずにデータを取出しています
>
>お見事です・・・
>
>



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