중첩 하위 쿼리

SQL에서의 데이터 조작

Mona Khalil

Data Scientist, Greenhouse Software

중첩 하위 쿼리란?

하위 쿼리 안에 또 다른 하위 쿼리
여러 단계의 변환 수행

하위 쿼리...

각 국가의 평균이 전체 평균과 얼마나 다른가요?

SELECT
    c.name AS country,
    AVG(m.home_goal + m.away_goal) AS avg_goals,
    AVG(m.home_goal + m.away_goal) - 
        (SELECT AVG(home_goal + away_goal) 
         FROM match) AS avg_diff
FROM country AS c
LEFT JOIN match AS m
ON c.id = m.country_id
GROUP BY country;

하위 쿼리...

| country     | avg_goals | avg_diff |
|-------------|-----------|----------|
| Belgium     | 2.8015    | 0.096    |
| England     | 2.7105    | 0.005    |
| France      | 2.4431    | -0.2624  |
| Germany     | 2.9016    | 0.196    |
| Italy       | 2.6168    | -0.0887  |
| Netherlands | 3.0809    | 0.3754   |
| Poland      | 2.425     | -0.2805  |
| Portugal    | 2.5346    | -0.1709  |
| Scotland    | 2.6338    | -0.0718  |
| Spain       | 2.7671    | 0.0616   |
| Switzerland | 2.9297    | 0.2241   |

...하위 쿼리 안에!

각 달의 총 득점이 목표의 평균 월간 총합과 어떻게 다른가요?

SELECT
  EXTRACT(MONTH FROM date) AS month,
  SUM(m.home_goal + m.away_goal) AS total_goals,
  SUM(m.home_goal + m.away_goal) - 
  (SELECT AVG(goals) 
   FROM (SELECT
           EXTRACT(MONTH FROM date) AS month,
           SUM(home_goal + away_goal) AS goals
         FROM match
         GROUP BY month)) AS avg_diff
FROM match AS m
GROUP BY month;

내부 하위 쿼리

SELECT
  EXTRACT(MONTH from date) AS month,
  SUM(home_goal + away_goal) AS goals
FROM match
GROUP BY month;

| month | goals |
|-------|-------|
| 01    | 2988  |
| 02    | 3768  |
| 03    | 3936  |
| 04    | 4055  |
| 05    | 2719  |
| 06    | 84    |
| 07    | 366   |

외부 하위 쿼리

SELECT AVG(goals) 
FROM (SELECT
        EXTRACT(MONTH from date) AS month,
        SUM(home_goal + away_goal) AS goals
FROM match
GROUP BY month)

2944.75

최종 쿼리

SELECT
  EXTRACT(MONTH FROM date) AS month,
  SUM(m.home_goal + m.away_goal) AS total_goals,
  SUM(m.home_goal + m.away_goal) - 
  (SELECT AVG(goals) 
   FROM (SELECT
           EXTRACT(MONTH FROM date) AS month,
           SUM(home_goal + away_goal) AS goals
         FROM match
         GROUP BY month) AS s) AS diff
FROM match AS m
GROUP BY month;

| month | total_goals | diff     |
|-------|-------------|----------|
| 01    | 5821        | -36.25   |
| 02    | 7448        | 1590.75  |
| 03    | 7298        | 1440.75  |
| 04    | 8145        | 2287.75  |

상관 중첩 하위 쿼리

중첩 하위 쿼리는 상관 또는 비상관일 수 있음
- 또는 둘의 조합
- 외부 하위 쿼리나 메인 쿼리의 정보를 참조할 수 있음

상관 중첩 하위 쿼리

2011/2012 시즌 각 국가의 평균 득점은?

SELECT
  c.name AS country,
  (SELECT AVG(home_goal + away_goal)
   FROM match AS m
   WHERE m.country_id = c.id
         AND id IN (
             SELECT id
             FROM match
             WHERE season = '2011/2012')) AS avg_goals 
FROM country AS c
GROUP BY country;

상관 중첩 하위 쿼리

2011/2012 시즌 각 국가의 평균 득점은?

SELECT
  c.name AS country,
  (SELECT AVG(home_goal + away_goal)
   FROM match AS m
   WHERE m.country_id = c.id
         AND id IN (
             SELECT id -- Begin inner subquery
             FROM match
             WHERE season = '2011/2012')) AS avg_goals 
FROM country AS c
GROUP BY country;

상관 중첩 하위 쿼리

2011/2012 시즌 각 국가의 평균 득점은?

SELECT
  c.name AS country,
  (SELECT AVG(home_goal + away_goal)
   FROM match AS m
   WHERE m.country_id = c.id -- 메인 쿼리와 상관
         AND id IN (
             SELECT id -- Begin inner subquery
             FROM match
             WHERE season = '2011/2012')) AS avg_goals 
FROM country AS c
GROUP BY country;

상관 중첩 하위 쿼리

| country     | avg_goals        |
|-------------|------------------|
| Belgium     | 2.87916666666667 |
| England     | 2.80526315789474 |
| France      | 2.51578947368421 |
| Germany     | 2.85947712418301 |
| Italy       | 2.58379888268156 |
| Netherlands | 3.25816993464052 |
| Poland      | 2.19583333333333 |
| Portugal    | 2.64166666666667 |
| Scotland    | 2.6359649122807  |
| Spain       | 2.76315789473684 |
| Switzerland | 2.62345679012346 |

Ayo berlatih!

SQL에서의 데이터 조작