Visualize SQL | How to Find the Median Value

# Beginning
There are 7 people of different ages.
let's find the age that is in the middle with SQL

find the age in the middle・・(oﾟДﾟo)？

:::note
【how to find】

Simply say・・・

① Create source data
② Create the left side data
③ Create the right side data
④ Find overlapping age
　⇒ <font color="red">the overlapping age</font> is median
:::

If you look the sorted data
it's easy to imagine


▼ Image of median & taking out
![pic_1.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/87b67d7d-2e1d-4f5b-af0d-50a08b4b0f81.jpeg)


**check working with PostgreSQL**

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

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);
```
▼ Output
|id|age|
|---|---|
|A01|20|
|A02|28|
|B04|35|
|B07|48|
|C03|50|
|C06|55|
|C08|68|


# Create Source Data
```SQL:SQL
-- Cross Join the ages table
SELECT A1.*, A2.*
FROM ages A1, ages A2
```
CROSS JOIN the ages table
　⇒ **Make all combinations**
　　⇒ `ages table` has７ rows
　　⇒ 7 x 7 = 49(rows)
  
> Check the below image to see
> how to join the tables

▼ Output(left side of image)
![pic_2.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/1364aafc-5ea0-4fd7-99a4-0a406c6fba82.jpeg)

# Edit The Source Data
```SQL:SQL
SELECT A1.age, ARRAY_AGG(A2.age)
FROM ages A1, ages A2
GROUP BY A1.age
```
▼ Output
|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
What is **GROUP BY A1.age** doing？

Look at (id = A01)
　⓵ There are 6 rows that are all 20
　　　⇒ make it unique (one row)
　⓶ There are 6 rows that are <font color="deeppink">different A1.age</font>
　　　⇒ 20,28,35,48,50,55,68
　　　⇒ make it "one row"
　　　⇒ create an array with ARRAY_AGG()
:::

> <font color="red">⓶ is important data</font>
> Actually this isn't necessary
> but I've added it just to check SQL's behavior
> 

▼ a image of aggregation
![pic_3.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/6bb30f6f-aa38-4060-beaf-b8f5a1872ae3.jpeg)

# Create 『left side data』 | Pre-process
```SQL:SQL
SELECT 
  A1.age
 ,ARRAY_AGG(A2.age) AS ary
 -- 『right side data』has the opposite inequality sign
 ,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
```

Since `GROUP BY` aggregates the data into one row,
it can be possible to compare
A1.age and A2.age(20,28,35,48,50,55,68)
　⇒ Summarize the comparison results in **flg_1**
　⇒ The SUM of the comparison results is **cnt_1**

▼ Output
|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.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/ffa463c9-8dcd-46a5-b1c3-07b4ccc41a4b.jpeg)

 > let's see more detail of 
 > the comparison behavior (´◉ω◉`)


# Compare A1.age and A2.age
【check A1.age is 35】

◾️CASE statement
　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>
　　⇒ the A2.age that is greater than or equal to <font color="green">35</font>
◾️ARRAY_AGG(CASE WHEN ・・・)
　⇒ Return value is <font color="deeppink">TRUE</font> -> 1
　⇒ Return value is <font color="deepskyblue">FALSE</font> -> 0
　　⇒ `Return values` ​​into one row
　　⇒ { 0, 0, 1, 1, 1, 1, 1 }　(use array)
　　⇒ flg_1

◾️SUM(CASE WHEN ・・・)
　⇒ ( 0 + 0 + 1 + 1 + 1 + 1 + 1 )
　⇒ sum all 『0 & 1』
　　⇒ 5
　　⇒ cnt_1


▼ **Compare A1.age(<font color="green">35</font>) and 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 represents positions
`cnt_1` is the sum of all the comparison results(0 or 1) 
that come from the CASE statement

This number will be the key 
to find the median (<font color="red">overlapping number</font>)
〜〜 (・_・?) 

> we call A2.age(20,28,35,48,50,55,68) 
> as <font color="orange">set A</font>, here



◾️ A1.age is 20

　Compare 20 and <font color="orange">set A</font>
　All return values from case statement are <font color="deeppink">TRUE</font>
　　⇒ there are **seven『1』**
　other words・・
　there are 7 numbers that are grater than or equal to 20
　20 is the smallest number, so it comes to **the far left**



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

　Compare 48 and <font color="orange">set A</font>
　Case statement return <font color="deeppink">TRUE</font>
　　when A2.age are 48,50,55 and 68
　　⇒ there are **four『1』**
　other words・・
　there are 4 numbers that are grater than or equal to 48
　the position of『48』is **in the middle**

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

　Compare 68 and <font color="orange">set A</font>
　Case statement return <font color="deeppink">TRUE</font>
　　when A2.age is only 68
　　⇒ there is **one『1』**
　other words・・
　there is only 1 number that are grater than or equal to 68
　68 is the largest number, so it comes to **the far right**

▼ **cnt_1(number) represents positions**
![pic_6.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/d72b0766-8b0d-45d1-9534-230a0a306389.jpeg)


# Choose 『left side data』
```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
```
▼ Output
|A1.age|cnt_1|middle|
|---|---|---|
|20|7|3.5|
|28|6|3.5|
|35|5|3.5|
|48|4|3.5|

:::note
Choose『left side data』with HAVING
　⇒ numbers that are greater than the midpoint
 
What is the midpoint?

The location(1~7) is determined by cnt_1
　⇒ The middle number of 7 is 3.5
　⇒ COUNT(A1.age)/2.0
　　⇒ we call 3.5 is <font color="chocolate">the midpoint</font> here

Choose the numbers where ・・
cnt_1 is greater than or equal to <font color="chocolate">the midpoint</font>(3.5)
　　⇒ 『left side data』are left
　　　20 28 35 48 ~~50 55 68~~
:::
   
▼ **Choose『left side data』**
![pic_7.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/8d64a592-72d0-4b51-a61f-a46a3a4e9bbf.jpeg)


> choose『right side data』in the same way ・・
> next


# Create 『right side data』 | Pre-process
```SQL:SQL
SELECT 
  A1.age
 ,ARRAY_AGG(A2.age) AS ary
 -- 『left side data』has the opposite inequality sign
 ,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
```

▼ Output(top image)
![pic_8.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/6c6a3116-d793-4d27-a6a4-53628f86297c.jpeg)


The way of choosing『right side data』is almost 
the same as the『left side data』
except <font color="red">the inequality sign is opposite</font>
so skip details

The results of comparison A1.age and A2.age are <font color="red">symmetric</font>
　⇒ check the order of <font color="blue">『１』</font>
　　 it's kind of understandable (｡-_-｡)

# Compare A1.age and A2.age, again
【check A1.age is 35】

We did the same comparison before ・・
because of the opposite inequality sign
the results(TRUE or FALSE) are different


▼ Output
![pic_9.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/2a2643db-8489-4b82-97c1-173278ca378f.jpeg)

# cnt_2 represents positions
Like『left side data』・・
determine the position of the seven numbers using cnt_2
　⇒ cnt_2 is the SUM of `comparison results`

we use cnt_2 to choose the numbers
that are greater than or equal to <font color="chocolate">the midpoint</font>(3.5)
 

▼ **cnt_2 represents positions**
![pic_10.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/6f604525-e15a-499e-b221-8aeac4497221.jpeg)


# Choose 『right side data』
```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
```
▼ Output
|A1.age|cnt_2|middle|
|---|---|---|
|48|4|3.5|
|50|5|3.5|
|55|6|3.5|
|68|7|3.5|

:::note
Choose 『right side data』with HAVING

Add a condition
　⇒ HAVING cnt_2 >= 3.5
　　Choose the numbers that are grater than or equal to <font color="chocolate">the midpoint</font>(3.5)
　　　⇒ 『right side data』are left
　　　　 ~~20 28 35~~ 48 50 55 68
:::

▼ **Choose 『right side data』**
![pic_11.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/21ca5ece-c012-4d73-94eb-ac95f569ede3.jpeg)

> The goal is in sight　(´д｀;)
>
> Retrive the overlapping number of『left side data』
> and『right side data』in the end


# Answer SQL
```SQL:SQL
SELECT A1.age
FROM ages A1, ages A2
GROUP BY A1.age
HAVING
  -- choose『left side data』
  SUM(CASE WHEN A2.age >= A1.age THEN 1 ELSE 0 END) >= COUNT(A1.age)/2.0
  AND
  -- choose『right side data』
  SUM(CASE WHEN A2.age <= A1.age THEN 1 ELSE 0 END) >= COUNT(A1.age)/2.0
```
▼ Output
|A1.age|
|---|
|48|

> The important thing here is・・
>
> the conditions for retriving『left side data』
> and『left side data』have `=` 
>　　　↓
> cnt_1 >= <font color="chocolate">the midpoint</font> (3.5)
> cnt_2 <= <font color="chocolate">the midpoint</font> (3.5)
> 
> The `=` sign creates <font color="red">overlapping parts</font>



▼ **The overlapping part**
![pic_12.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/fbec60b8-c989-4e45-b6bb-98fcc4a55ff8.jpeg)


:::note alert
【cautionary points】

This query works 
if the number of data is odd

if the number is even
this does not work

:::

# Table & Data | Even 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);
```

Delete one row and change it to 6 rows in total

# Execute Answer SQL
```SQL:SQL
SELECT 
  A1.age
  ,COUNT(A1.age)/2.0 AS middle -- the midpoint
FROM ages A1, ages A2
GROUP BY A1.age
HAVING
  -- choose『left side data』
  SUM(CASE WHEN A2.age >= A1.age THEN 1 ELSE 0 END) >= COUNT(A1.age)/2.0
  AND
  -- choose『right side data』
  SUM(CASE WHEN A2.age <= A1.age THEN 1 ELSE 0 END) >= COUNT(A1.age)/2.0
```
▼ Output
|A1.age|middle|
|---|---|
|35|3.0|
|48|3.0|


Add <font color="chocolate">the midpoint</font> in SELECT clause
then execute the query
　⇒ The output is two rows
　⇒ check the below image

Getting out『left side data』and『right side data』
that are grater than <font color="chocolate">the midpoint</font>,
there are <font color="red">two overlap numbers</font>
 
so the median should be `(35+48)/2`
 　⇒ average

 ▼ **two overlap numbers**
![pic_13.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/d9b20fcb-2a22-4602-95df-cd3ee33f9fc2.jpeg)

# Modify the Andwer SQL
```SQL:SQL
SELECT AVG(b1.age) AS age
FROM (
  SELECT A1.age
  FROM ages A1, ages A2
  GROUP BY A1.age
  HAVING
    -- choose『left side data』
    SUM(CASE WHEN A2.age >= A1.age THEN 1 ELSE 0 END) >= COUNT(A1.age)/2.0
    AND
    -- choose『right side data』
    SUM(CASE WHEN A2.age <= A1.age THEN 1 ELSE 0 END) >= COUNT(A1.age)/2.0
) AS b1
```
▼ Output
|A1.age|
|---|
|41.5|

Changed `Answer SQL` to a subquery
to retrive the average value

This will allow you to find the median
for both even and odd numbers


That's all　ヾ( ˆoˆ )/



# Reference
[How to Find the Median Value](https://www.mathsisfun.com/median.html)

[Joe Celko's SQL Puzzles and Answers](https://www.amazon.com/Puzzles-Answers-Kaufmann-Management-Systems/dp/0123735963)