[230126] 멋쟁이사자처럼 AI SCHOOL 8기 [특강] SQL_이범재강사님' 복습

조세연·2023년 1월 26일
0

멋사 AI SCHOOL 8기

목록 보기
23/35
post-thumbnail

📝Today I learned

🚀 TIL 목차 🚀

  • 조건분기
    - CASE
    - IF
  • JOIN
    - INNER JOIN
    - LEFT (OUTER) JOIN
    - RIGHT (OUTER) JOIN
    - FULL (OUTER) JOIN
    - CROSS JOIN
  • 예제 오답노트

[특강] SQL

1) 조건 분기

CASE 🌟🌟🌟

CASE 
WHEN 조건1 THEN 조건1이 참일 때 결과값1 
WHEN 조건2 THEN 조건1은 아니고 조건2가 참일 때 결과값2 
ELSE 조건1, 조건2도 아닐 때 결과값3 
END 

🚨 CASE 하나 당 END를 붙임
.

  • CASE 문을 사용하는 경우
  1. 약어나 코드를 읽기 쉬운 값으로 바꿔 줄 때
# 물품 상태 한글로 변경하기
SELECT order_id, user_id,
CASE 
  WHEN status = 'Shipped' THEN '배송됨'
  WHEN status = 'Processing' THEN '처리중'
  WHEN status = 'Returned' THEN '반품됨'
  WHEN status = 'Cancelled' THEN '취소됨'
  WHEN status = 'Complete' THEN '완료됨'
  ELSE '기타'
END AS status_text
FROM `thelook_ecommerce.orders`
ORDER BY order_id;
  1. 데이터를 범주화 할 때
# 비용 범주화
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 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

.
.

IF 🌟🌟🌟

IF(조건문, 참일 때 출력값, 거짓일 때 출력값);
# 주문레벨 조회
SELECT 
    user_id,
    IF(COUNT(order_id) > 3, 'VIP', 'NORMAL') AS order_level,
    COUNT(order_id) AS order_count
FROM `thelook_ecommerce.orders`
GROUP BY user_id
ORDER BY user_id DESC;

.
🌟CASE문과 IF문, 둘 중 뭘 써야하지?

  • IF문 : 간단한 조건인 경우
  • CASE문 : 연달아 예외케이스를 많이 설정해야 하는 경우
  • 특히, 기다 아니다 두가지 경우라면 IF문을 간단히 사용해도 되지만 그 외 경우가 있다면 ELSE로 커버할 수 있는 CASE문을 사용하는 게 낫다
    (eg. 성별에서 F, M 뿐만 아니라 다른 성별도 존재하는 경우)

.
.

2) JOIN

INNER JOIN

: 두 테이블 모두에서 일치하는 값이 있는 행을 반환
: INNER은 생략 가능
: INNER JOIN == JOIN

SELECT table1.id, table2.id
FROM table1
INNER JOIN table2 
	ON table2.id=table1.id

LEFT (OUTER) JOIN 🌟🌟🌟

: 오른쪽 테이블의 해당 행과 함께 왼쪽 테이블의 모든 행을 반환
왼쪽 테이블에 해당하는 값이 오른쪽 테이블에 없으면 NULL 반환

SELECT table1.id, table2.id
FROM table1
LEFT JOIN table2
	ON table2.id = table1.id

RIGHT (OUTER) JOIN (잘안씀!)

: 왼쪽 테이블의 해당 행과 함께 오른쪽 테이블의 모든 행을 반환
오른쪽 테이블에 해당하는 값이 왼쪽 테이블에 없으면 NULL 반환

SELECT table1.id, table2.id
FROM table1
RIGHT JOIN table2
	ON table2.id = table1.id

FULL (OUTER) JOIN (잘안씀!)

: 왼쪽, 오른쪽 모든 행 반환
서로 값이 없는 경우 NULL 반환(이런 특성 때문에 NULL값이 많이 나옴)

SELECT table1.id, table2.id
FROM table1
FULL JOIN table2
	ON table2.id = table1.id

CROSS JOIN (잘안씀!)

:두 테이블에서 가능한 모든 행 조합을 반환

SELECT table1.id, table2.id
FROM table1
CROSS JOIN table2

📌트랜잭션 데이터(transaction data)
: 주문 데이터

📌마스터 데이터(master data)
: 회원데이터, 상품데이터

📌 데이터 마트를 위한 비정규화 테이블 만들기
: 분석, 시각화를 위해서 매번 JOIN을 하기 귀찮으니 주문데이터(트랜잭션)에 회원데이터(마스터)와 상품데이터(마스터)를 결합하여 비정규화 테이블을 만들어서 저장해놓고 사용함
.

3) 예제 오답 노트

.
1. SQL 연습문제 7-1
<문제>
주문정보(orders) 테이블에서 order_id, gender, gender_label(gender의 값에 따른 성별을 한글로 표시)을 해주세요.

  • 필드명 : gender_label
    • gender가 F 이면 '여성'
    • gender가 M 이면 '남성'
  • 결과로 표시할 필드
    • order_id
    • gender
    • gender_label
  • 정렬순서 : 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;

👉 혹시 gender 필드에서 F, M 말고 다른 요소가 있을 수 있으니 IF문을 사용하지 말고 CASE문을 사용하는 것이 좋음
👉 CASE문에서는 행마다 ‘,’를 붙이지 않음
👉 END AS 로 라벨링을 할 땐 따옴표를 붙이지 않음
.
2. 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;

👉 CASE문에서 순서를 주의할 것! GOLD 조건부터 적으면 COUNT(order_id)가 4 이상인 값도 GOLD로 출력됨.
.
3. SQL 연습문제 7-6
<문제>
order_items 테이블에서 각 연도의 분기별 매출합계을 표시하세요.
order_items의 status가 Complete 인 항목만 포함합니다.
표시항목

  • year
  • quarter
  • sum_sale_price - 소수점 2자리 반올림
    정렬 순서
  • year 오름차순
  • quarter 오름차순
# 나의 답
SELECT 
  EXTRACT(YEAR FROM created_at) AS year,
  EXTRACT(QUARTER FROM created_at) AS quarter,
  ROUND(SUM(CASE WHEN status = 'Complete' THEN sale_price END), 2) AS sum_sale_price
FROM `thelook_ecommerce.order_items`
GROUP BY year, quarter
ORDER BY year, quarter;

# 강사님의 답
SELECT 
  EXTRACT(year FROM created_at) AS year,
  EXTRACT(quarter FROM created_at) AS quarter,
  ROUND(SUM(sale_price), 2) AS sum_sale_price
FROM `thelook_ecommerce.order_items`
WHERE status = 'Complete'
GROUP BY year, quarter
ORDER BY year ASC, quarter ASC

👉 SQL 처리 순서 상 CASE문보다 WHERE에서 status를 이미 한 번 걸러주는 것이 좋음.

❗이것만은 외우고 자자 Top 3

📌 IF문 CASE문 선택하는 팁: 예외처리가 많은 경우, 두 가지 경우 이상을 표현하기 위해 ELSE를 활용해야하는 경우 CASE를 사용!

📌 SELECT에서 이미 t1.country라고 한 경우 GROUP BY할 때 그냥 country라고 해도 됨

📌 SELECT문에서 필드마다 콤마 찍어주는 것을 잊지 말자!!

🌟데일리 피드백

1. 오늘의 칭찬&반성

수업을 들으며 궁금했던 내용 2가지를 강사님께 질문했다. 덕분에 SQL문 처리 순서와 코드를 짜는 나만의 시각이 생겼다.
하지만 아직 몸이 완전히 회복되지 않아 수업을 듣는 동안 좀 힘들었다. 얼른 원래 건강한 몸으로 돌아왔으면 좋겠다.

2. 내가 부족한 부분

사소한 콤마, 따옴표를 자꾸 놓친다. 지금은 코드를 급하게 짤 때가 아니라 정확하게 짜는 연습을 해야겠다.

3. 내일의 목표

일주일의 끝! 금요일 수업도 잘 듣고 TIL 무사히 적기

profile
HR Analyst가 되고 싶은

0개의 댓글