可視化 SQL | 中央値を求める

# 初めに
年齢の違う７人がいます
このグループ内での真ん中の年齢を
SQLで求めてみます

真ん中の年齢・・(oﾟДﾟo)？

:::note
【取出し方】

ざっくりですが・・・

①元になるデータを作る
②左側のデータを作る
③右側のデータを作る
④重複する場所を探す
　⇨ <font color="red">重なる部分</font>が中央値となる
:::

ソートしたデータを見ると
イメージしやすいかと思います


▼ 中央値と取出しイメージ
![pic_1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/9f44c2c9-6119-46ce-b8ce-3fe386a294d0.png)



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

# Table & Data
```SQL:SQL
CREATE TABLE ages (
  id  CHAR(3) PRIMARY KEY -- ID
  ,age INTEGER -- 年齢
);

INSERT INTO ages VALUES('A01',	20);
INSERT INTO ages VALUES('A02',	28);
INSERT INTO ages VALUES('B04',	35);
INSERT INTO ages VALUES('B07',	48);
INSERT INTO ages VALUES('C03',	50);
INSERT INTO ages VALUES('C06',	55);
INSERT INTO ages VALUES('C08',	68);
```
▼ 出力
|id|age|
|---|---|
|A01|20|
|A02|28|
|B04|35|
|B07|48|
|C03|50|
|C06|55|
|C08|68|


# 元データを作る
```SQL:SQL
-- ages テーブルの自己結合
SELECT A1.*, A2.*
FROM ages A1, ages A2
```
ages テーブル同士をCROSS JOINしてます
　⇒ **全部の組合せを作る**
　　⇒ ages テーブルは７行
　　⇒ 7 x 7 = 49(行)のデータができる
  
> どの様に結合されるのかは
> 下記図を確認ください

▼ 出力(画像左)
![pic_2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/923b527c-6b31-41fc-9ce0-aebc1a1e1b6f.png)

# 元データを加工する
```SQL:SQL
SELECT A1.age, ARRAY_AGG(A2.age)
FROM ages A1, ages A2
GROUP BY A1.age
```
▼ 出力
|A1.age|ARRAY_AGG(A2.age)|
|---|---|
|20|{ 20,28,35,48,50,55,68 }|
|28|{ 20,28,35,48,50,55,68 }|
|35|{ 20,28,35,48,50,55,68 }|
|48|{ 20,28,35,48,50,55,68 }|
|50|{ 20,28,35,48,50,55,68 }|
|55|{ 20,28,35,48,50,55,68 }|
|68|{ 20,28,35,48,50,55,68 }|

:::note
**GROUP BY A1.age** で何をしてる？

id = A01 を見る
　⓵ A1.age(20)が6行あるので1行にする
　　　⇒ ユニークになる
　⓶ A2.ageが<font color="deeppink">6行あるが数字が違う</font>
　　　⇒ 20,28,35,48,50,55,68
　　　⇒ これを1行にまとめる
　　　⇒ ARRAY_AGG()で配列にしてる
:::

> <font color="red">重要なのは⓶のデータ</font>です
> 実際にはこの記述は必要ないのですが
> SQLの処理を可視化する為
> 確認用に追加してます
> 

▼ ARRAY_AGG()のイメージ図
![pic_3.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/79fe2106-501d-4eb3-b66e-224743204dea.png)

# 『左側データ』を作る準備
```SQL:SQL
SELECT 
  A1.age
 ,ARRAY_AGG(A2.age) AS ary
 -- 『左側データ』は不等号が逆になる
 ,ARRAY_AGG(CASE WHEN A2.age >= A1.age THEN 1 ELSE 0 END) AS flg_1
 ,SUM(CASE WHEN A2.age >= A1.age THEN 1 ELSE 0 END) AS cnt_1
FROM ages A1, ages A2
GROUP BY A1.age
```

GROUP BYで１行に集約してるので
A1.ageとA2.age(20,28,35,48,50,55,68)
を比較することができます
　⇒ 比較結果を flg_1
　⇒ 比較結果のSUMを cnt_1
とします


▼ 出力
|A1.age|ARRAY_AGG(A2.age)|flg_1|cnt_1|
|---|---|---|---|
|20|{ 20,28,35,48,50,55,68 }|{ 1, 1, 1, 1, 1, 1, 1 }|7|
|28|{ 20,28,35,48,50,55,68 }|{ 0, 1, 1, 1, 1, 1, 1 }|6|
|35|{ 20,28,35,48,50,55,68 }|{ 0, 0, 1, 1, 1, 1, 1 }|5|
|48|{ 20,28,35,48,50,55,68 }|{ 0, 0, 0, 1, 1, 1, 1 }|4|
|50|{ 20,28,35,48,50,55,68 }|{ 0, 0, 0, 0, 1, 1, 1 }|3|
|55|{ 20,28,35,48,50,55,68 }|{ 0, 0, 0, 0, 0, 1, 1 }|2|
|68|{ 20,28,35,48,50,55,68 }|{ 0, 0, 0, 0, 0, 0, 1 }|1|

![pic_4.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/d9ec50eb-4d17-4f53-b392-bbceef380c7b.png)


> 比較の挙動については・・
> もう少し詳しくみてみます (´◉ω◉`)


# A1.ageとA2.ageを比較する
【A1.age(35)で確認】

◾️CASE文
　CASE WHEN **A2.age** >= <font color="green">A1.age</font> THEN 1 ELSE 0 END
　　⇒ {**20,28,35,48,50,55,68**} >= <font color="green">35</font>
　　⇒ A2.ageが<font color="green">35</font>より大きい or 同じ

◾️ARRAY_AGG(CASE WHEN ・・・)
　⇒ CASE文が <font color="deeppink">TRUE</font> の時 1 
　⇒ CASE文が <font color="deepskyblue">FALSE</font> の時 0
　　⇒ 戻り値を１行にまとめる
　　⇒ { 0, 0, 1, 1, 1, 1, 1 } (配列)
　　⇒ flg_1

◾️SUM(CASE WHEN ・・・)
　⇒ ( 0 + 0 + 1 + 1 + 1 + 1 + 1 )
　⇒ 合計する
　　⇒ 5
　　⇒ cnt_1

▼ A1.age(<font color="green">35</font>)とA2.ageを比較
![pic_5.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/59a17d8f-5c77-4292-827e-aeada64af25f.jpeg)


# cnt_1はデータの位置
cnt_1 は CASE文の比較結果(1 or 0)を
全て足し算(SUM)した数字です

この数字が
中央値(<font color="red">重なる部分</font>)を探すKeyになります
〜〜 (・_・?) 

> ここで
> A2.age(20,28,35,48,50,55,68)
> を <font color="orange">集合A</font>と呼びます


◾️ A1.age = 20 の時

　20と<font color="orange">集合A</font>を比較
　CASE文が全て<font color="deeppink">TRUE</font>になる
　　⇒ **1の数が7個**
　言い換えると
　20より大きい or 同じ数字が7個ある
　20は一番小さな数字なので・・
　位置は**一番左**

<font color="darkgray">〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜</font>
◾️ A1.age = 48 の時

　48と<font color="orange">集合A</font>を比較
　CASE文で48,50,55,68が<font color="deeppink">TRUE</font>になる
　　⇒ **1の数が4個**
　言い換えると
　48より大きい or 同じ数字が4個ある
　全7個の内４つなので・・
　48の位置は**真ん中**

<font color="darkgray">〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜</font>
◾️ A1.age = 68 の時

　68と<font color="orange">集合A</font>を比較
　CASE文で68が<font color="deeppink">TRUE</font>になる
　　⇒ **1の数が1個**
　言い換えると
　68より大きい or 同じ数字が1個しかない
　68は一番大きな数字なので・・
　位置は**一番右**


▼ cnt_1(数字)はA1.ageの位置を表してる
![pic_7.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/01a6a018-9b9d-451c-880a-c2b774842502.jpeg)


# 『左側データ』を取出す
```SQL:SQL
SELECT 
  A1.age
-- ,ARRAY_AGG(A2.age) AS ary
-- ,ARRAY_AGG(CASE WHEN A2.age >= A1.age THEN 1 ELSE 0 END) AS flg_1
 ,SUM(CASE WHEN A2.age >= A1.age THEN 1 ELSE 0 END) AS cnt_1
 ,COUNT(A1.age)/2.0 AS middle
FROM ages A1, ages A2
GROUP BY 
  A1.age
HAVING 
  SUM(CASE WHEN A2.age >= A1.age THEN 1 ELSE 0 END) >= COUNT(A1.age)/2.0
```
▼ 出力
|A1.age|cnt_1|middle|
|---|---|---|
|20|7|3.5|
|28|6|3.5|
|35|5|3.5|
|48|4|3.5|
   
:::note
HAVINGで『左側データ』を取る
　⇒ 中間点より大きい数字
 
中間点・・とは？

数字が7個並んでます
cnt_1 を使って、場所(1~7)が決まってます
　⇒ 7の中間は3.5
　⇒ COUNT(A1.age)/2.0
　　⇒ ここでは、3.5を<font color="chocolate">中間点</font>と呼びます
  
cnt_1が<font color="chocolate">中間点</font>(3.5)より大きな数字を取出す
　　⇒ 『左側データ』が残ります
　　　20 28 35 48 ~~50 55 68~~
:::
   
▼ 『左側データ』を取出すイメージ
![pic_8.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/9c25907a-cf91-42b1-b77a-e226292a9d6a.jpeg)


> 次に、同じ様にして
> 『右側データ』を取出します


# 『右側データ』を作る準備
```SQL:SQL
SELECT 
  A1.age
 ,ARRAY_AGG(A2.age) AS ary
 -- 『右側データ』と不等号の向きが逆
 ,ARRAY_AGG(CASE WHEN A2.age <= A1.age THEN 1 ELSE 0 END) AS flg_2
 ,SUM(CASE WHEN A2.age <= A1.age THEN 1 ELSE 0 END) AS cnt_2
FROM ages A1, ages A2
GROUP BY A1.age
```

▼ 出力(画像上側)
![pic_9.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/0a9904ad-8b07-4f6c-ba18-ae856b06503a.jpeg)



『右側データ』の取出し方は
左側とほぼ同じですが・・・
<font color="red">不等号の向きが逆</font>になります
他は同じなので、詳細は割愛します

A1.ageとA2.ageを比較した結果(flg_2)の
<font color="red">1となる場所が対照</font>になってます
　⇒ 不等号の向きを変えたので
 　　何となく理解はできます(｡-_-｡)

# 再度、A1.ageとA2.ageを比較する
【A1.age(35)で確認】

CASE文の比較ですが・・
『左側データ』の時と同じです

不等号の向きが違うので
TURE or FALSEの結果が違ってきます



▼ 出力
![pic_10.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/5e887caf-7825-4a64-8fe0-8edc70eee636.jpeg)

# cnt_2はデータの位置
『左側データ』と同じように
cnt_2 を使って7個の数字の位置を決めます
　⇒ cnt_2 は`比較結果の合計`です

cnt_2は、<font color="chocolate">中間点</font>(3.5)より大きな数字を
取得する為に使用します
 

▼ 『右側データ』を取出す準備
![pic_12.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/cdd0dc62-abd8-4c56-8f02-dd42344d5a9f.jpeg)

# 『右側データ』を取出す
```SQL:SQL
SELECT 
  A1.age
-- ,ARRAY_AGG(A2.age) AS ary
-- ,ARRAY_AGG(CASE WHEN A2.age <= A1.age THEN 1 ELSE 0 END) AS flg_2
 ,SUM(CASE WHEN A2.age <= A1.age THEN 1 ELSE 0 END) AS cnt_2
 ,COUNT(A1.age)/2.0 AS middle
FROM ages A1, ages A2
GROUP BY 
  A1.age
HAVING 
  SUM(CASE WHEN A2.age <= A1.age THEN 1 ELSE 0 END) >= COUNT(A1.age)/2.0
```
▼ 出力
|A1.age|cnt_2|middle|
|---|---|---|
|48|4|3.5|
|50|5|3.5|
|55|6|3.5|
|68|7|3.5|

:::note
HAVINGで『右側データ』を取る

条件を追記してます
　⇒ HAVING cnt_2 >= 3.5 
　　 <font color="chocolate">中間点</font>(3.5)より大きな数字を取出します
　　　⇒ 『右側データ』が残ります
　　　　 ~~20 28 35~~ 48 50 55 68
:::

▼『右側データ』を取出すイメージ
![pic_13.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/60ca00c5-94cc-4c32-9bff-d097a52576a9.jpeg)

> ようやく終わりが見えました　(´д｀;)
> 最後に・・
> 『左側データ』と『右側データ』の共通部分を
> 取出します

# 回答SQL
```SQL:SQL
SELECT A1.age
FROM ages A1, ages A2
GROUP BY A1.age
HAVING
  -- 『左側データ』取出し
  SUM(CASE WHEN A2.age >= A1.age THEN 1 ELSE 0 END) >= COUNT(A1.age)/2.0
  AND
  -- 『右側データ』取出し
  SUM(CASE WHEN A2.age <= A1.age THEN 1 ELSE 0 END) >= COUNT(A1.age)/2.0
```
▼ 出力
|A1.age|
|---|
|48|

> ここで重要なのは
> 『左側データ』『右側データ』の取得条件に
> `=` がついている事です
> 　　　↓
> cnt_1 >= <font color="chocolate">中間点</font> (3.5)
> cnt_2 <= <font color="chocolate">中間点</font> (3.5)
> 
> `=` がついているので、<font color="red">重複</font>が発生します

▼ 重なる場所
![pic_14.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/68d1996d-4746-42e0-baf5-646b92f3bc16.jpeg)


:::note alert
【注意点】

このクエリはデータの数が奇数の場合に
動作しますが、偶数の場合は少し違ってきます

:::

# Table & Data | 偶数データ
```SQL:SQL
DELETE FROM ages;

INSERT INTO ages VALUES('A01',	20);
INSERT INTO ages VALUES('A02',	28);
INSERT INTO ages VALUES('B04',	35);
INSERT INTO ages VALUES('B07',	48);
INSERT INTO ages VALUES('C03',	50);
INSERT INTO ages VALUES('C06',	55);
-- INSERT INTO ages VALUES('C08',	68);
```
データを1行削除して全部で6行に変更

# 回答SQLを実行
```SQL:SQL
SELECT 
  A1.age
  ,COUNT(A1.age)/2.0 AS middle -- 中間点
FROM ages A1, ages A2
GROUP BY A1.age
HAVING
  -- 『左側データ』取出し
  SUM(CASE WHEN A2.age >= A1.age THEN 1 ELSE 0 END) >= COUNT(A1.age)/2.0
  AND
  -- 『右側データ』取出し
  SUM(CASE WHEN A2.age <= A1.age THEN 1 ELSE 0 END) >= COUNT(A1.age)/2.0
```
▼ 出力
|A1.age|middle|
|---|---|
|35|3.0|
|48|3.0|

<font color="chocolate">中間点</font>を追記して実行しました
出力が２行になりました
　⇒ 下記イメージ図をご確認ください
 
 <font color="chocolate">中間点</font>(3.0)より大きい『左側データ』『右側データ』を
 HAVINGで取出すと、<font color="red">重複が２箇所</font>となります

 その為、中央値は(35+48)/2 ですね
 　⇒ 平均値

 ▼ 重複が２箇所
 ![pic_17.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/1b7341f7-e025-4160-9bb0-5b79e050cf49.jpeg)

# 回答SQL 修正
```SQL:SQL
SELECT AVG(b1.age) AS age
FROM (
  SELECT A1.age
  FROM ages A1, ages A2
  GROUP BY A1.age
  HAVING
    -- 『左側データ』取出し
    SUM(CASE WHEN A2.age >= A1.age THEN 1 ELSE 0 END) >= COUNT(A1.age)/2.0
    AND
    -- 『右側データ』取出し
    SUM(CASE WHEN A2.age <= A1.age THEN 1 ELSE 0 END) >= COUNT(A1.age)/2.0
) AS b1
```
▼ 出力
|A1.age|
|---|
|41.5|

回答SQLをサブクエリにして
ここから平均値を取出すように変更しました

これなら、偶数、奇数の両方で
中央値が取れます

以上となります　ヾ( ˆoˆ )/

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