FROM 절의 서브쿼리

SQL 데이터 조작

Mona Khalil

Data Scientist, Greenhouse Software

FROM 절 안의 서브쿼리

데이터를 재구성하고 변환하기
- 선택 전에 데이터를 세로형에서 가로형으로 변환
- 데이터 사전 필터링
집계의 집계 계산
- 홈 경기 득점 평균이 가장 높은 3개 팀은 어디인가?
  1. 각 팀별 AVG 계산
  2. 상위 3개 AVG 값 추출

FROM 서브쿼리에서...

SELECT
  t.team_long_name AS team,
  AVG(m.home_goal) AS home_avg
FROM match AS m
LEFT JOIN team AS t
ON m.hometeam_id = t.team_api_id
WHERE season = '2011/2012'
GROUP BY team;

| team                 | home_avg         |
|----------------------|------------------|
| 1. FC Köln           | 1.13725490196078 |
| 1. FC Nürnberg       | 1.27058823529412 |
| 1. FSV Mainz 05      | 1.43697478991597 |
| AC Ajaccio           | 1.12280701754386 |

...메인 쿼리로!


FROM (SELECT
          t.team_long_name AS team,
          AVG(m.home_goal) AS home_avg
      FROM match AS m
      LEFT JOIN team AS t
      ON m.hometeam_id = t.team_api_id
      WHERE season = '2011/2012'
      GROUP BY team)

...메인 쿼리로!


FROM (SELECT
          t.team_long_name AS team,
          AVG(m.home_goal) AS home_avg
      FROM match AS m
      LEFT JOIN team AS t
      ON m.hometeam_id = t.team_api_id
      WHERE season = '2011/2012'
      GROUP BY team) AS subquery

...메인 쿼리로!

SELECT team, home_avg
FROM (SELECT
          t.team_long_name AS team,
          AVG(m.home_goal) AS home_avg
      FROM match AS m
      LEFT JOIN team AS t
      ON m.hometeam_id = t.team_api_id
      WHERE season = '2011/2012'
      GROUP BY team) AS subquery

...메인 쿼리로!

SELECT team, home_avg
FROM (SELECT
          t.team_long_name AS team,
          AVG(m.home_goal) AS home_avg
      FROM match AS m
      LEFT JOIN team AS t
      ON m.hometeam_id = t.team_api_id
      WHERE season = '2011/2012'
      GROUP BY team) AS subquery
ORDER BY home_avg DESC
LIMIT 3;

| team           | home_avg |
|----------------|----------|
| FC Barcelona   | 3.8421   |
| Real Madrid CF | 3.6842   |
| PSV            | 3.3529   |

기억할 점

하나의 FROM 절에서 여러 서브쿼리를 만들 수 있습니다
- 별칭을 지정하세요!
- 서로 조인하세요!
FROM의 서브쿼리와 테이블을 조인할 수 있습니다
- 양쪽 테이블 모두에 조인할 열을 포함하세요!

연습해봅시다!

SQL 데이터 조작