중급 SQL에 오신 것을 환영합니다!

SQL에서의 데이터 조작

Mona Khalil

Data Scientist, Greenhouse Software

학습 내용:

CASE 구문
단순 서브쿼리
상관 서브쿼리
윈도 함수

선수 지식

데이터 선택, 필터링, 그룹화

SELECT user_id, SUM(sales) 
FROM sales_data
WHERE user_id BETWEEN 300 AND 400
GROUP BY user_id;

조인 사용

SELECT c.country, c.team, SUM(m.goals)
FROM countries AS c
LEFT JOIN matches AS m
ON c.team_id = m.home_team_id
WHERE m.year > 1990
GROUP BY c.country, c.team;

European Soccer Database에서 선택하기

SELECT 
    l.name AS league,
    COUNT(m.country_id) as total_matches
FROM league AS l
LEFT JOIN match AS m
ON l.country_id = m.country_id
GROUP BY l.name;

| league                 | total_matches |
|------------------------|---------------|
| Belgium Jupiler League | 732           |
| England Premier League | 1520          |
| France Ligue 1         | 1520          |
| Germany 1. Bundesliga  | 1224          |

European Soccer Database에서 선택하기

SELECT 
    date,
    id,
    home_goal,
    away_goal
FROM match
WHERE season = '2013/2014';

| date                | id   | home_goal | away_goal |
|---------------------|------|-----------|-----------|
| 2014-03-29 00:00:00 | 1237 | 2         | 0         |
| 2014-03-29 00:00:00 | 1238 | 0         | 1         |
| 2014-04-05 00:00:00 | 1239 | 1         | 0         |
| 2014-04-05 00:00:00 | 1240 | 0         | 0         |

European Soccer Database에서 선택하기

SELECT 
    date,
    id,
    home_goal,
    away_goal
FROM match
WHERE season = '2013/2014'
      AND home_team_goal > away_team_goal;

| date                | id   | home_goal | away_goal |
|---------------------|------|-----------|-----------|
| 2014-03-29 00:00:00 | 1237 | 2         | 0         |
| 2014-04-05 00:00:00 | 1239 | 1         | 0         |
| 2014-04-12 00:00:00 | 1241 | 2         | 1         |
| 2014-04-12 00:00:00 | 1242 | 2         | 0         |

CASE 구문

WHEN, THEN, ELSE로 구성되며 END로 종료

CASE WHEN x = 1 THEN 'a' 
     WHEN x = 2 THEN 'b'
     ELSE 'c' END AS new_column

CASE WHEN

SELECT 
  id,
  home_goal,
  away_goal,
  CASE WHEN home_goal > away_goal THEN 'Home Team Win'
       WHEN home_goal < away_goal THEN 'Away Team Win'
       ELSE 'Tie' END AS outcome
FROM match
WHERE season = '2013/2014';

| id   | home_goal | away_goal | outcome       |
|------|-----------|-----------|---------------|
| 1237 | 2         | 0         | Home Team Win |
| 1238 | 0         | 1         | Away Team Win |
| 1239 | 1         | 0         | Home Team Win |
| 1240 | 0         | 0         | Tie           |

연습해 봅시다!

SQL에서의 데이터 조작