[SQL] 조건분기(CASE, IF)

1. 조건분기

SELECT
CASE
	
    WHEN 조건 
    THEN 참일 경우_실행구문
    ELSE 거짓말일 경우_실행구문
    
END

 

SELECT 
CASE 
	WHEN floor = 1 THEN '1층 입니다.'
	WHEN floor = 2 THEN '2층 입니다.'
	WHEN floor = 3 THEN '3층 입니다.'
	WHEN floor = 4 THEN '4층 입니다.'
ELSE 
	'층수가 없어요' 
END;

이렇게 여러 조건이 있을 때 첫번째 조건에서 바로 충족되면 첫번째 조건에서 끝남 

• Oracle의 경우에는 DECODE, CASE WHEN
• MsSQL의 경우에는 CASE WHEN
• MySQL의 경우에는 IF, CASE WHEN

 

SELECT order_id, user_id, status,
case
  when status = 'shipped' then '배송됨'
  when status = 'Complete' then '완료됨'
  when status = 'Returned' then '반품됨'
  when status = 'Cancelled' then '취소됨'
  when status = 'Processing' then '처리중'
  else '기타'
end as status_text
FROM `thelook_ecommerce.orders`

-> when이 두 번 들어가게 되면 첫 번째 when은 우리가 흔히 알고 있는 if 문이 되고, 두 번째 when은 elif(else if)가 된다 

 

select
  id,
  cost,
  CASE
    WHEN cost <= 20 THEN '저비용' 
    WHEN cost <= 50 THEN '중비용'
    WHEN cost > 50 THEN '고비용'
    ELSE '없음'
  END as `cost_level`
from `thelook_ecommerce.products`
order by id

연도별 남성여성 가입자수 조회

select extract(year from created_at) as year,
  count(case when gender = 'M' then gender end) as male,
  count(case when gender = 'F' then gender end) as female,
  count(id) as total
from `thelook_ecommerce.users`
group by year
order by year

else를 따로 지정해주지 않으면 null 값이 나올 수 있음(조건이 맞지 않는 경우)  but, null값은 count되지 않음 

select
  id,
  gender,
  CASE WHEN gender = 'F' THEN gender END as F,
  CASE WHEN gender = 'M' THEN gender END as M
from `thelook_ecommerce.users`
order by id

이렇게 ! 

case 하나당 end가 하나 붙는 것 when에 붙는 것이 xxx

 

연도별 분기별 가입자

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

연도별 월별 가입자

SELECT 
  EXTRACT(year FROM created_at) as YEAR,
  count(CASE WHEN EXTRACT(month FROM created_at) = 1 THEN id END)AS M1,
  count(CASE WHEN EXTRACT(month FROM created_at) = 2 THEN id END)AS M2,
  count(CASE WHEN EXTRACT(month FROM created_at) = 3 THEN id END)AS M3,
  count(CASE WHEN EXTRACT(month FROM created_at) = 4 THEN id END)AS M4,
  count(CASE WHEN EXTRACT(month FROM created_at) = 5 THEN id END)AS M5,
  count(CASE WHEN EXTRACT(month FROM created_at) = 6 THEN id END)AS M6,
  count(CASE WHEN EXTRACT(month FROM created_at) = 7 THEN id END)AS M7,
  count(CASE WHEN EXTRACT(month FROM created_at) = 8 THEN id END)AS M8,
  count(CASE WHEN EXTRACT(month FROM created_at) = 9 THEN id END)AS M9,
  count(CASE WHEN EXTRACT(month FROM created_at) = 10 THEN id END)AS M10,
  count(CASE WHEN EXTRACT(month FROM created_at) = 11 THEN id END)AS M11,
  count(CASE WHEN EXTRACT(month FROM created_at) = 112 THEN id END)AS M12,
  count(id) as TOTAL
FROM `thelook_ecommerce.users`
GROUP BY YEAR
ORDER BY YEAR

-> 월별

 

2. IF

비용 레벨 조회

select
  if(1 = 1, '참입니다', '거짓입니다.');
  
  >> 참입니다
  
  select
  if(1 = 2, '참입니다', '거짓입니다.');
  
  >> 거짓입니다.

select id, cost,
  if(cost <= 50, '저비용', '고비용') as cost_level
from `thelook_ecommerce.products`
order by id

cost가 50달러 이하인 경우 저비용, 아니면 고비용

 

주문 레벨 조회

select user_id,
count(order_id) as order_count,
if (count(order_id) >= 4, 'VIP', 'NORMAL') as order_level

from `thelook_ecommerce.orders`

group by user_id
order by user_id

 

주문횟수가 4회 이상인 사람 -> vip 아니면 normal

 

SELECT 
  EXTRACT(year FROM created_at) as YEAR,
  count(if (EXTRACT(quarter FROM created_at) = 1, id, null)) as Q1,
  count(if (EXTRACT(quarter FROM created_at) = 2, id, null)) as Q2,
  count(if (EXTRACT(quarter FROM created_at) = 3, id, null)) as Q3,
  count(if (EXTRACT(quarter FROM created_at) = 4, id, null)) as Q4,
count(id) as TOTAL
FROM `thelook_ecommerce.users`
GROUP BY YEAR
ORDER BY YEAR

 

---

case문과 if문 사용하는 경우 차이는? 

특별히 차이 x 간단히 쓰는 경우 if문 좋고, else if 처럼 연달아 예외케이스를 많이 설정해야 하는 경우 case 를 사용하는 게 좋음 

=> 선호도 차이