BigQuery8. 데이터 변환4 (조건문 함수 CASE WHEN, IF)

1. 조건문 함수

1.1. 조건문

• 조건문 : 만약 특정 조건이 충족되면 -> 어떤 행동을 하도록 만드는 구문이다

  • 조건에 따른 카테고리화가 필요한 경우에 사용한다.
  • 조건에 따라 다른 값을 표시하고 싶을 때 사용한다.

• 조건문 함수 :

  • CASE WHEN
  • IF

• 조건문 함수가 사용되는 이유
  : 데이터 분석을 하다보면, 특정 카테고리를 하나로 합치는 전처리가 필요할 수 있다

  예시) 학년 별(1, 2, 3, 4, 5, 6) 성적에 대해
       -> 저학년(1, 2, 3)  / 고학년(4, 5, 6) 별 성적 평균 차이를 알고 싶은 경우
       => 저학년 / 고학년으로 카테고리화
       
       요일 별(월, 화, 수, 목, 금, 토, 일) 택시 수요에 대해
       -> 주중(월~금) / 주말(토, 일) 별 수요 패턴의 차이를 알고 싶은 경우
       => 주중 / 주말로 카테고리화

-> 보통 데이터를 저장하는 팀(데이터 엔지니어링) 과 데이터를 분석하는 팀으로 나뉘는데,
-> 저장할 때부터 특정 카테고리를 합쳐서 저장하면, 분석할 때 쪼개서 보는 것이 번거로워진다
=> 저장할 땐 카테고리를 나눠서 저장하고,
=> 분석할 때 필요한 부분에서 조건 설정하여 특정 카테고리를 하나로 합치는 것이 더 유용하다

1.2. CASE WHEN

• 여러 조건이 있을 때 유용하다

문법)

SELECT
	CASE
		WHEN 조건1 THEN 조건1이 참일 경우의 결과
		WHEN 조건2 THEN 조건2가 참일 경우의 결과
		ELSE 그 외 조건일 경우의 결과
	END AS 새로운_컬럼_이름
FROM

• CASE WHEN 순서

  • CASE WHEN 구문 작성 시 조건문의 순서에 주의해야 한다
  • 조건1과 조건2에 중복해서 해당하는 데이터는, 앞선 순서를 따른다
  • 문자열 함수(특정 단어 추출)에서 해당 이슈가 자주 발생한다

예시) 각 포켓몬의 공격력(attack)을 기준으로 50 이상이면 'Strong', 100 이상이면 'Very Strong', 그 이하면 'Weak'으로 분류해주세요

1.
SELECT
	eng_name,
    attack,
    CASE
    	WHEN attack >= 100 THEN 'Very Strong'
        WHEN attack >= 50 THEN 'Strong'
        ELSE 'Weak'
    END AS attack_level
FROM basic.pokemon
ORDER BY attack DESC

2.
SELECT
	eng_name,
    attack,
    CASE
    	WHEN attack >= 50 THEN 'Strong'
        WHEN attack >= 100 THEN 'Very Strong'
        ELSE 'Weak'
    END AS attack_level
FROM basic.pokemon
ORDER BY attack DESC

=> 조건문의 순서만 바뀐건데, 다른 결과가 나왔다

• Why?
  • CASE WHEN은 Row 순서대로 실행되기 때문에
    -> 앞 순서의 조건문에서 row가 조건을 만족하면
    -> 다음 순서의 조건문을 거치지 않고 END로 반환한다
    => 때문에, 순서에 따라 결과가 달라질 수 있다

1.3. IF

• 단일 조건일 때 유용하다

문법)

IF(조건문, True일 때의 결과, False일 때의 결과) AS 새로운_컬럼_이름

---

연습문제

1. 포켓몬의 'speed'가 70 이상이면 '빠름', 그렇기 않으면 '느림'으로 표시하는 새로운 컬럼 'Speed_Category'를 만들어주세요.

-- 쿼리를 작성하는 목표, 확인할 지표 : speed 컬럼을 사용해 새로운 Speed_Category 생성
-- 쿼리 계산 방법 : case when, if => 조건이 단일이다. if. 70 이상
-- 데이터의 기간 : X
-- 사용할 테이블 : pokemon
-- Join KEY : X
-- 데이터 특징 : speed의 min, max 확인
--
min, max 확인)
SELECT
    min(speed) as min_speed,
    max(speed) as max_speed
  FROM basic.pokemon

문제 풀이)
SELECT
  id,
  kor_name,
  speed,
  IF(speed >=70, "빠름", "느림") AS Speed_Category
FROM basic.pokemon

2. 포켓몬의 'type1'에 따라 'Water', 'Fire', 'Electric' 타입은 각각 '물', '불', '전기'로, 그 외 타입은 '기타'로 분류하는 새로운 컬럼 'type_Korean'을 만들어주세요.

-- 쿼리를 작성하는 목표, 확인할 지표 : type1을 사용해서 새로운 컬럼 생성
-- 쿼리 계산 방법 : case when, if => 여러 조건이 있음 => case when
-- 데이터의 기간 : X
-- 사용할 테이블 : pokemon
-- Join KEY : X
-- 데이터 특징 : 타입이 두가지가(type1, type2) 있다. 그러나 type1만 활용하는 문제 
--
문제풀이)
SELECT
  id,
  kor_name,
  type1,
  CASE
    WHEN type1 = "Water" then "물"
    WHEN type1 = "Fire" then "불"
    WHEN type1 = "Electric" then "전기"
  ELSE "기타"
  END AS type1_Korean
FROM basic.pokemon

3. 각 포켓몬의 총점(total)을 기준으로, 300 이하면 'Low', 301에서 500사이면 'Medium', 501 이상이면 'High'로 분류해주세요.

# 쿼리를 작성하는 목표, 확인할 지표 : total 컬럼 => 조건에 맞는 값을 변경! 모두 다 숫자!
# 쿼리 계산 방법 : case when
# 데이터의 기간 :
# 사용할 테이블 : pokemon
# Join KEY :
# 데이터 특징 : total 컬럼이 정수(INTEGER)
--
문제풀이)
SELECT
  id,
  kor_name,
  total,
  CASE
    WHEN total >= 501 then "High"
    WHEN total between 301 and 500 then "Medium"
  ELSE "Low"
  END AS total_grade
FROM basic.pokemon
(-- WHERE    
--   total_grade = "Low")

결과가 잘 나왔는지 확인하기 위해 WHERE절에 total_grade를 넣으면 => 오류 => WHERE절은 SELECT절 전에 수행되기 때문 => 서브쿼리로 해결

SELECT
  *
FROM (
  SELECT
    id,
    kor_name,
    total,
    CASE
      WHEN total >= 501 then "High"
      WHEN total between 301 and 500 then "Medium"
    ELSE "Low"
    END AS total_grade
  FROM basic.pokemon
)
WHERE
  total_grade = "Low"

4. 각 트레이너의 배지 개수(badge_count)를 기준으로, 5개 이하면 'Beginner', 6개에서 8개 사이면 'Intermediate', 그 이상이면 'Advanced'로 분류해주세요.

# 쿼리를 작성하는 목표, 확인할 지표 : badge_count => 조건에 만족하는 값을 변경
# 쿼리 계산 방법 : case when
# 데이터의 기간 : x
# 사용할 테이블 : trainer
# Join KEY : x
# 데이터 특징 : x
--
문제풀이)
SELECT
  trainer_level,
  count(distinct id) as trainer_cnt
FROM (
SELECT
  id,
  name,
  badge_count,
  CASE
    WHEN badge_count>=9 then "Advanced"
    WHEN badge_count BETWEEN 6 AND 8 then "Intermediate"
  ELSE "Beginner"
  END AS trainer_level
FROM basic.trainer
)
GROUP BY 
  trainer_level

-> 아직 Advanced 단계는 없다!

5. 트레이너가 포켓몬을 포획한 날짜(catch_date)가 '2023-01-01' 이후이면 'Recent', 그렇지 않으면 'Old'로 분류해주세요.

# 쿼리를 작성하는 목표, 확인할 지표 : 포획한 날짜 기준으로 값을 변경
# 쿼리 계산 방법 : if
# 데이터의 기간 : X
# 사용할 테이블 : trainer_pokemon
# Join KEY : X
# 데이터 특징 : catch_date는 UTC 기준. catch_datetime은 TIMESTAMP
--
문제풀이)
SELECT
  recent_or_old,
  count(id) as cnt
FROM(
  SELECT
    id,
    trainer_id,
    pokemon_id,
    catch_datetime,
    IF(DATE(catch_datetime, "Asia/Seoul") > "2023-01-01", "Recent", "Old") as recent_or_old
  FROM basic.trainer_pokemon
)
GROUP BY
  recent_or_old

-> 이처럼 모든 조건이 Recent로 반환된다면,
-> SELECT절에 "Recent" as recent_value를 작성하여
-> 모든 컬럼에 동일한 값을 줘도 된다

6. 배틀에서 승자(winner_id)가 player1_id와 같으면 'Player 1 Wins', player2_id와 같으면 'Player 2 Wins', 그렇지 않으면 'Draw'로 결과가 나오게 해주세요.

# 쿼리를 작성하는 목표, 확인할 지표 : 승패 여부를 알 수 있는 컬럼을 만들고 싶다!
# 쿼리 계산 방법 : case when
# 데이터의 기간 : X
# 사용할 테이블 : battle
# Join KEY : X
# 데이터 특징 : X
--
문제풀이)
SELECT
  id,
  winner_id,
  player1_id,
  player2_id,
  CASE
    WHEN winner_id = player1_id then "Player 1 Wins"
    WHEN winner_id = player2_id then "Player 2 Wins"
  ELSE "Draw"
  END AS battle_result 
FROM basic.battle