[DAY 22] SQL CASE, IF 조건분기, JOIN

어제 배운 함수 내용을 가볍게 훑고 강의가 시작되었다.

CASE문, IF문을 통해 SQL에서 조건분기를 구현하는 방법과 JOIN을 공부했다.

조건분기 - CASE

사용법

CASE
  WHEN 조건문1 THEN 값
  [WHEN 조건문2 THEN 값]
  .
  .
  .
  [ELSE 값]
END

CASE를 쓰고 그 뒤로 WHEN ~ THEN을 1개 이상 사용한다.
WHEN에 모두 해당하지 않을 경우의 값을 원할 때 ELSE를 사용할 수 있다.
CASE문 마지막에 END는 필수이다.
Python의 if ~ elif~ else 문과 흡사하다.

사용 예시
orders 테이블에서 order_id, user_id, status를 조회한 결과는 아래와 같다

orders

status에 따라 한글로 된 컬럼을 추가하고 싶다면 CASE문을 사용할 수 있다.

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`
order by order_id

위의 SQL문을 실행하여 아래와 같은 결과를 얻을 수 있다.

status_text 추가

또한, 비용을 구간에 따라 고, 중, 저 구분을 할 경우

select
  id,
  cost,
  case
    when cost <= 30 then '저비용'
    when cost <= 60 then '중비용'
    when cost > 60 then '고비용'
    else '없음'
  end as cost_level
from `thelook_ecommerce.products`

연도별, 성별 가입자 수를 구할 경우

select 
  EXTRACT(year from created_at) as year,
  count(CASE WHEN gender = 'F' THEN id END) as female,
  count(CASE WHEN gender = 'M' THEN id END) as male,
  count(id) as total
from `thelook_ecommerce.users`
group by year;

각 연도의 분기별 가입자 수를 구할 경우

select
  EXTRACT(year from created_at) as YEAR,
  count(case when EXTRACT(quarter from created_at) = 1 then 1 end) as Q1,
  count(case when EXTRACT(quarter from created_at) = 2 then 1 end) as Q2,
  count(case when EXTRACT(quarter from created_at) = 3 then 1 end) as Q3,
  count(case when EXTRACT(quarter from created_at) = 4 then 1 end) as Q4,
  count(id) TOTAL
from `thelook_ecommerce.users`
group by year

실행 결과는 아래와 같다

CASE문은 여러 경우에 유용하게 사용된다.

조건분기 - IF

사용법

IF(조건문, 참인 경우 값, 거짓인 경우 값)
ex
if(true, '참', '거짓')  # 참
if(false, '참', '거짓')  # 거짓
if(1=1, '참', '거짓')  # 참
if(1=2, '참', '거짓')  # 거짓

사용 예시
주문 횟수가 4회 이상인 회원은 VIP 지정

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

 

조건분기 연습문제와 프로그래머스 문제

### SQL 연습문제 7-1
주문정보(orders) 테이블에서 order_id, gender, gender_label(gender의 값에 따른 성별을 한글로 표시)을 해주세요.
- 정렬순서 : order_id 오름차순
select 
  order_id,
  gender,
  CASE
    WHEN gender = 'F' THEN '여성'
    WHEN gender = 'M' THEN '남성'
    ELSE '없음'
  END as gender_label
from `thelook_ecommerce.orders`
order by order_id

 

### SQL 연습문제 7-3
회원(users) 테이블에서 가입연도별 이용경로(traffic_source)별 가입자수를 조회하세요.
정렬 : year 오름차순
select 
  extract(year from created_at) as `year`,
  count(case when traffic_source = 'Search' then 1 end) as `Search`,
  count(case when traffic_source = 'Organic' then 1 end) as `Organic`,
  count(case when traffic_source = 'Email' then 1 end) as `Email`,
  count(case when traffic_source = 'Display' then 1 end) as `Display`,
  count(case when traffic_source = 'Facebook' then 1 end) as `Facebook`,
  count(id) as `Total`
from `thelook_ecommerce.users`
group by year
order by year

 

### SQL 연습문제 7-4
주문정보(orders) 테이블에서 주문을 3번 이상 구매한 사람의 등급을 ‘Gold’, 4번 이상 구매한 사람의 등급은 ‘VIP’, 그 외에는 'Silver'라고 등급을 지정해 줍니다.
select user_id,
  count(order_id) as order_count,
  case
    when count(order_id) >= 4 then 'VIP'
    when count(order_id) >= 3 then 'Gold'
    else 'Silver'
  end as Grade
from `thelook_ecommerce.orders`
group by user_id
order by user_id

프로그래머스 문제
Level 2
중성화 여부 파악하기

Level 3
조건별로 분류하여 주문상태 출력하기

JOIN(조인)

JOIN의 종류
1. INNER JOIN
2. LEFT OUTER JOIN
3. RIGHT OUTER JOIN
4. FULL OUTER JOIN
5. CROSS JOIN

그림으로 나타내면 아래와 같다

INNER 와 OUTER는 생략하여 JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN으로 쓸 수 있다.

일반적으로 FULL JOIN이나 RIGHT JOIN은 잘 사용하지 않고 INNER JOIN과 LEFT JOIN이 활용도가 높다.

CROSS JOIN은 두 테이블 레코드가 각각 n, m개라면 조인 결과 테이블은 n * m개의 레코드가 된다.
모든 값의 조합인 셈이다. 따라서 on 조건절을 사용하지 않는다.


JOIN 연습문제

### SQL 연습문제 8-1
회원(users) 테이블과 주문정보(orders) 테이블을 이용하여 모든 주문내역에 회원정보를 표시하세요.
- 조회 항목 : 주문ID(order_id), 주문한 상품 수량(num_of_item), 회원 이름(first_name + last_name), 주소(street_address),  도시(city)

select 
  t1.order_id,
  t1.num_of_item,
  concat(t2.first_name, ' ', t2.last_name) as user_name,
  t2.street_address,
  t2.city,
from `thelook_ecommerce.orders` t1
left join `thelook_ecommerce.users` t2 on t1.user_id = t2.id

 

### SQL 연습문제 8-2
회원(users) 테이블과 주문정보(orders) 테이블을 이용하여 상품을 주문한 회원의 국가가 ‘United States’이면서 주문 상태가 처리중(Processing)인 정보를 조회하세요.
- 조회 항목
    - 주문ID(order_id)
    - 회원 이름(first_name, last_name)
    - 주소(street_address)
    - 우편번호(postal_code)
    - 도시(city)
    - 국가(country)
    - 주문한 상품 수량(num_of_item)

select
  t1.order_id,
  t2.first_name || ' ' ||t2.last_name as user_name,
  t2.street_address,
  t2.postal_code,
  t2.city,
  t2.country,
  t1.num_of_item
from `thelook_ecommerce.orders` t1
join `thelook_ecommerce.users` t2 on t1.user_id = t2.id
where t2.country = 'United States'
and t1.status = 'Processing'

 

### SQL 연습문제 8-3
회원(users) 테이블과 주문정보(orders) 테이블을 이용하여 국가별 총 상품 주문수를 조회하세요
- 정렬 : 국가별 총 상품 주문수(total_order_count)이 많은 순으로 정렬
select 
  t2.country,
  count(order_id) as total_order_count
from `thelook_ecommerce.orders` t1
left join `thelook_ecommerce.users` t2 on t1.user_id = t2.id
group by country
order by total_order_count desc

위처럼 조인 테이블에 별칭을 지정하여 사용하는것이 간편하다.
on 조건절에는 조인 조건을 명시한다. 이 경우에도 별칭을 사용할 수 있다.

본격적으로 어려운 부분이 시작된 것 같다. 공부를 열심히 해야겠다고 생각했다.