조건 분기(CASE, IF)

조건문

CASE

• 기본 방식

  SELECT 
  	CASE 
  		WHEN 조건문 THEN 참일 때 출력될 내용
  		ELSE 조건문이 거짓일때 출력될 내용
  	END AS 별칭
  

• 여러 조건일 경우

  SELECT
  	CASE
  		WHEN 조건문1 THEN 참1
  		WHEN 조건문2 THEN 참2
  		...
  		WHEN 조건문 THEN 참
  		ELSE 거짓
  	END AS 별칭
  

• CASE문이 ELSE 없이 거짓일 경우

  SELECT
  	CASE
  		WHEN FALSE THEN 'TRUE'
  	END # null
  

  • 아무 값도 받아오지 않음
  • count하기 좋음

CASE 예제

연도별 연령대별 가입자 수

• 처음 코드 (실패)

select
  extract(year from created_at) as year
  count(case when age between 10 and 19 then id end) as age_10s
  count(case when age < 30 then id end) as age_20s
  count(case when age < 40 then id end) as age_30s
  count(case when age < 50 then id end) as age_40s
  count(case when age < 60 then id end) as age_50s
from `thelook_ecommerce.users`

• case문 안에 들어가 있는 거만 가능한
  • 그러니까 case문안에 조건이 참이 아닐 경우 다음 조건으로 넘어가는데
  • 이건 case문이 각각 있기 때문에 참이 아님

select
  extract(year from created_at) as year,
  count(case when age between 10 and 19 then id end) as age_10s,
  count(case when age between 20 and 29 then id end) as age_20s,
  count(case when age between 30 and 39 then id end) as age_30s,
  count(case when age between 40 and 49 then id end) as age_40s,
  count(case when age between 50 and 59 then id end) as age_50s,
  count(id) as total
from `thelook_ecommerce.users`
group by year

연도별 분기별 가입자수

-- YEAR  Q1  Q2  Q3  Q4  TOTAL
-- 2019  111 111 111 111 444
-- 2020  111 111 111 111 444
-- 2021  111 111 111 111 444
-- 2022  111 111 111 111 444

이런식으로 나오게 해!!

🐰져니 코드

select
  extract(year from created_at) as year,
  count(case when extract(QUARTER from created_at)=1 then id end) as Q1,
  count(case when extract(QUARTER from created_at)=2 then id end) as Q2,
  count(case when extract(QUARTER from created_at)=3 then id end) as Q3,
  count(case when extract(QUARTER from created_at)=4 then id end) as Q4,
  count(id) as total
from `thelook_ecommerce.users`
group by year
order by year

연도별 월별 가입자수