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

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

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

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

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

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

こんなパズルを思いついた方も、ある意味すごい方ですが
これに対して色んな回答を考えた人達もすごいです
クレイジーな問いに対して、暑くなる猛者達っていますよね

このパズルはそんな問題なので、実用的ではありません・・・が
個人的には面白い問題だと思います

そんな回答のいくつかをご紹介させていただきます
最初は1行2列表示について解説してみます

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

▼ 出力する一覧のイメージ

![re_1.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/0d18da30-c314-185c-88b1-b62bf95780fa.png)

# テーブル と データ
```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/9a82257c-06cd-0c70-cfe7-72dff698cf55.png)

# LEFT JOINで元データを作る
```SQL:SQL
SELECT N1.name, N2.name
FROM Names AS N1
LEFT 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/99bfd519-9f9c-c071-c052-4197d3378660.png)


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

![re_4.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/63d8cd84-acaa-4599-61cb-6853400ac16f.png)


LEFT JOIN の結合条件に注目します
　⇒　**N1.name < N2.name**



組合せを作る時に、**自分の名前より後ろ名前を全て結合させています**
　⇒　N1.name を自分とすると、自分より後
　⇒　自分が A の場合、B,C,D,E,F,G ・・・
　⇒　自分が B の場合、C,D,E,F,G,H ・・・
　⇒　自分が C の場合、D,E,F,G,H,I ・・・


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



このLEFT JOIN して作った元データですが、Names テーブルを自己結合しています
Namesテーブルにはカラムが1つ。これをJOINしているので、出力は2列
つまり、**1行2列のデータが出来上がっています**
このデータの何処かの行が取り出したいデータとなります


![re_5.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/0aa9ab9c-14bb-f5bb-7cac-74e00d5e92e8.png)



# INNER JOINで別の組合せを作る
```SQL:SQL
SELECT A.name,B.name
FROM Names AS A 
INNER JOIN Names AS B ON A.name <= B.name
```

▼ 出力

結合条件に『=』 がついているので、自分と同じ名前の組合せが増える
　⇒　`太文字`の部分が増えた

『=』つけて組合せを作る理由は
Aから始まる名前の組合せは、全部でいくつあるか？
Bから始まる名前の組合せは、全部でいくつあるか？
Cから始まる名前の組合せは、全部でいくつあるか？
　・　　・　　・　　・　　・　　・　　・　　・
　・　　・　　・　　・　　・　　・　　・　　・
Nから始まる名前の組合せは、全部でいくつあるか？

を確認する為です。後で必要になります。


![re_6.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/ea6c8613-1e72-8949-4d09-a1fb1c43b020.png)




# 別の組合せを加工する①
```SQL:SQL
SELECT
  A.name,
  COUNT(B.name),
  (SELECT COUNT(*) FROM Names)
FROM Names AS A 
INNER JOIN Names AS B ON A.name <= B.name
GROUP BY A.name
```

▼ 出力

データの数をカウントします

COUNT(B.name) 
　⇒　A.name で GROUP BY していますが、A.name と B.name の数は同じです
　⇒　上の画像をご確認ください


SELECT COUNT(＊) FROM Names
　⇒　Names テーブルに有る名前の数です
　⇒　AI,Ben,Charlie,David,Ed,・・・・Mike,Neal
　⇒　14名います


![re_7.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/ee21b494-2cf8-1181-f9c0-36d06f426a5f.png)


# 別の組合せを加工する②
```SQL:SQL
SELECT
  A.name,
  COUNT(B.name),
  (SELECT COUNT(*) FROM Names),
  MOD(COUNT(B.name), 2),
  (SELECT MOD(COUNT(*), 2) FROM Names)
FROM Names AS A 
INNER JOIN Names AS B ON A.name <= B.name
GROUP BY A.name
```

▼ 出力


SELECT文の中に
MOD(COUNT(B.name), 2)と (SELECT MOD(COUNT(＊), 2) FROM Names) を追加
　⇒　MOD(●●●,2)
　⇒　●●● ÷ 2 の余りを出力します

SELECT MOD(COUNT(＊), 2) FROM Names
　⇒　COUNT(＊) は 14です
　⇒　14 ÷ 2 の余り = 0
　⇒　全てに 0 が入っています

MOD(COUNT(B.name), 2)
　⇒　14 ÷ 2 の余り = 0 | AI
　⇒　13 ÷ 2 の余り = 1 | Ben
　⇒　12 ÷ 2 の余り = 0 | Charlie
　⇒　11 ÷ 2 の余り = 1 | David
　　　・　　・　　・　　・
　　　・　　・　　・　　・
　　　・　　・　　・　　・
　⇒　2 ÷ 2 の余り = 0 | Mike
　⇒　1 ÷ 2 の余り = 1 | Neal


![re_8.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/d2f78dc6-2c34-8834-2ea1-33d3f2dd3942.png)

# 別の組合せを加工する③
```SQL:SQL
SELECT
  A.name,
  COUNT(B.name),
  (SELECT COUNT(*) FROM Names),
  MOD(COUNT(B.name), 2),
  (SELECT MOD(COUNT(*), 2) FROM Names)
FROM Names AS A 
INNER JOIN Names AS B ON A.name <= B.name
GROUP BY A.name

HAVING MOD(COUNT(B.name), 2) = (SELECT MOD(COUNT(*), 2) FROM Names)
```

▼ 出力

HAVING ・・・を追加しました
これは、割算した時の余りが同じ0となる行を取出しています

先程も言いましたが・・・

今回のゴールは名前一覧を『1行2列』で表示させる・・・です
元データは同じ Names テーブルを使って LEFT JOIN しているので
既に、`『1行2列』となっている組合せが縦に複数並んでいる状態です`
　⇒　この中から **特定の行を取出す必要が有ります**
　⇒　今回の場合、MOD(COUNT(B.name), 2) の余りが 0 となる名前
　⇒　名前の組合せが偶数個有る A.name


これを行っているのが
`HAVING MOD(COUNT(B.name), 2) = (SELECT MOD(COUNT(*), 2) FROM Names)`
です


![re_9.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/e8529550-48d8-a2d2-8aba-f85b98a8a8c4.png)



これで大方の準備が整いました
何故この様な場所を見つける必要があるのか？はこれから確認できます

～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
**注意点**

今回のテーブルデータは14件なので
SELECT MOD(COUNT(*), 2) FROM Names は0を返しますが
11件や23件などの奇数の場合は何らかの手直しが必要となります
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～

# 元データを加工する
```SQL:SQL
SELECT N1.name, N2.name
FROM Names AS N1 
LEFT JOIN Names AS N2 ON N1.name < N2.name
WHERE N1.name IN (
  SELECT A.name FROM Names AS A 
  INNER JOIN Names AS B ON A.name <= B.name
  GROUP BY A.name
  HAVING MOD(COUNT(B.name), 2) = (SELECT MOD(COUNT(*), 2) FROM Names)
)
```

最初にLEFT JOINして元データを作ったSQLにWHERE句を追加して
先程名前を取出したSQLをサブクエリとして使用します

最終的には下記画像の赤文字のデータを取出すのですが、その前処理として
`サブクエリから返ってくる名前と同じ名前(N1.name)のデータを取出します`



![re_10.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/1660da09-583a-7a18-3140-2531be10c98f.png)

-------

▼ 出力

元データから名前が AI,Charlie,Ed,Greg,Ida,Ken,Mike と一致する名前の組合せを取出しました


![re_11.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/2a06f946-ce3c-7d04-e0d6-57d55906fb5b.png)


最後に、赤文字の名前の組合せを取出します


# 回答SQL
```SQL:SQL
SELECT N1.name, MIN(N2.name) AS name2
FROM Names AS N1 
LEFT JOIN Names AS N2 ON N1.name < N2.name
WHERE N1.name IN (
	SELECT A.name FROM Names AS A 
	INNER JOIN Names AS B ON A.name <= B.name
	GROUP BY A.name
	HAVING MOD(COUNT(B.name), 2) = (SELECT MOD(COUNT(*), 2) FROM Names)
)
GROUP BY N1.name
```

▼ 出力

1行2列の表示が出来ました


![re_12.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/21e2d132-aed8-b416-0d55-122923bbac12.png)



この問題のポイントは、WHERE句の中に有るサブクエリで
LEFT JOIN して作った元データから必要な行を取出している所です

最後に N1.name でGROUP BYして1人目の名前を取出す。
2人目の名前は(N2.name)はGROUP BYされていないので MIN(N2.name)で取り出す
　⇒　MIN関数を使うので、先頭に有る名前(赤文字)が取り出せる



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