서브쿼리는 어디에나! 그리고 모범 사례!

SQL에서의 데이터 조작

Mona Khalil

Data Scientist, Greenhouse Software

원하는 만큼 서브쿼리 사용...

SELECT, FROM, WHERE에 여러 서브쿼리 포함 가능

SELECT 
    s.stage,
    ROUND(s.avg_goals,2) AS avg_goal,
    (SELECT AVG(home_goal + away_goal)
     FROM match WHERE season = '2013/2014') AS overall_avg
FROM 
    (SELECT
         stage,
         AVG(home_goal + away_goal) AS avg_goals
     FROM match
     WHERE season = '2013/2014'
     GROUP BY stage) AS s
WHERE 
    s.avg_goals > (SELECT AVG(home_goal + away_goal) 
                   FROM match WHERE season = '2013/2014');

쿼리 서식을 맞추세요

SELECT, FROM, WHERE, GROUP BY를 정렬하세요

SELECT
    col1,
    col2,
    col3
FROM table1
WHERE col1 = 2;

쿼리에 주석을 달아주세요

/* 이 쿼리는 col1 = 2로 필터링하고
table1의 데이터만 선택합니다 */
SELECT
    col1,
    col2,
    col3
FROM table1
WHERE col1 = 2;

쿼리에 주석을 달아주세요

SELECT
    col1,
    col2,
    col3
FROM table1 -- 이 테이블은 10,000행
WHERE col1 = 2; -- WHERE 값 2로 필터

쿼리는 들여쓰기 하세요

서브쿼리는 들여쓰기!

SELECT
    col1,
    col2,
    col3
FROM table1
WHERE col1 IN
        (SELECT id
         FROM table2
         WHERE year = 1991);

쿼리는 들여쓰기 하세요

SELECT 
  date, 
  hometeam_id, 
  awayteam_id,
  CASE WHEN hometeam_id = 8455 AND home_goal > away_goal 
            THEN 'Chelsea home win'
       WHEN awayteam_id = 8455 AND home_goal < away_goal
            THEN 'Chelsea away win'
       WHEN hometeam_id = 8455 AND home_goal < away_goal
            THEN 'Chelsea home loss'
       WHEN awayteam_id = 8455 AND home_goal > away_goal
            THEN 'Chelsea away loss'
       WHEN (hometeam_id = 8455 OR awayteam_id = 8455) 
            AND home_goal = away_goal THEN 'Chelsea Tie'
       END AS outcome
FROM match
WHERE hometeam_id = 8455 OR awayteam_id = 8455;

Holywell의 SQL 스타일 가이드

서브쿼리가 꼭 필요할까?

서브쿼리는 연산 비용이 듭니다
- 데이터베이스 크기는?
- 조회하는 테이블 크기는?
그 서브쿼리, 정말 필요한가요?

각 서브쿼리에 올바르게 필터링!

필터를 주의하세요!

SELECT 
    s.stage,
    ROUND(s.avg_goals,2) AS avg_goal,
    (SELECT AVG(home_goal + away_goal)
     FROM match WHERE season = '2013/2014') AS overall_avg
FROM 
    (SELECT
         stage,
         AVG(home_goal + away_goal) AS avg_goals
     FROM match
     WHERE season = '2013/2014'
     GROUP BY stage) AS s
WHERE 
    s.avg_goals > (SELECT AVG(home_goal + away_goal) 
                   FROM match WHERE season = '2013/2014');

Ayo berlatih!

SQL에서의 데이터 조작