[📘 엑셀보다 쉽고 빠른 SQL] 5주차

📘 5주차에 배울 내용

1. 데이터가 없을 때 처리 방법
2. 상식적이지 않은 데이터 처리 방법
3. Window Function - RANK, SUM
4. 날짜 데이터 포맷하기
5. 숙제

---

💙 수업목표

• 데이터에서 예상하지 못한 값이 나왔을 때 (이상한 값, 값이 없음 등) 분석에 적절하에 처리한다.
• SQL 로 엑셀에서 자주 사용하는 형태로 데이터를 만든다.
• 업무에 활용할 수 있는 다양한 SQL 심화 문법을 익힌다.

---

1. 데이터가 없을 때 처리 방법

[방법 1] 

• 없는 값을 제외해주기
• null 문법을 이용하여, null 값 제거

SELECT a.order_id, a.customer_id, a.restaurant_name, a.price, b.name, b.age, b.gender
FROM food_orders a LEFT JOIN customers b ON a.customer_id=b.customer_id
WHERE b.customer_id IS NOT NULL;

 

[방법 2]

• 다른 값을 대신 사용하기
• 데이터 분석 시에는 평균값 혹은 중앙값 등 대표값을 이용하여 대체해주기도 한다.
• 다른 값으로 변경하는 문법
  • 조건문 이용하기 : if(rating>=1, rating, 대체값)
  • null 값일 때 : coalesce(age, 대체값)

SELECT a.order_id, a.customer_id, a.restaurant_name, a.price, b.name,
	b.age, coalesce(b.age, 20) "null 제거",
FROM food_orders a LEFT JOIN customers b ON a.customer_id=b.customer_id
WHERE b.age IS NULL;

---

2. 상식적이지 않은 데이터 처리 방법

값이 상식적이지 않을 때

ex) [음식 주문 데이터] age: 2, date: 1973-09-27

 

[방법]

• 조건문으로 값의 범위를 지정하기
• 조건문으로 상식적인 수준 안에서 범위를 지정해준다.

SELECT customer_id, name, email, gender, age,
       CASE WHEN age<15 THEN 15
            WHEN age>80 THEN 80
            ELSE age END "범위를 지정해준 age"
FROM customers;

---

3. Window Function - RANK, SUM

Window Function

각 행의 관계를 정의하기 위한 함수로 그룹 내의 연산을 쉽게 만들어준다.

 

 

Window Function의 기본 구조

SELECT WINDOW_FUNCTION(ARGUMENTS) OVER (PARTITION BY 컬럼명 ORDER BY 컬럼명)
FROM 테이블명;

• window_function : 기능 명을 사용해준다. (sum, avg 등)
• argument : 함수에 따라 작성하거나 생략한다.
• partition by : 그룹을 나누기 위한 기준. group by 절과 유사하다.
• order by : window function 을 적용할 때 정렬 할 컬럼 기준을 적어준다.

 

[실습] RANK 함수 적용하기

SELECT cuisine_type,
       restaurant_name,
       RANK() OVER (PARTITION BY cuisine_type ORDER BY order_count desc) rn,
       order_count
FROM
(
	SELECT cuisine_type, restaurant_name, COUNT(1) order_count
	FROM food_orders
	GROUP BY 1, 2
) a;

 

 

[실습] 누적합 구하기

SELECT
	cuisine_type, price,
	SUM(price) OVER (PARTITION BY cuisine_type) cuisine_type,
	SUM(price) OVER (PARTITION BY cuisine_type ORDER BY price) cum_price
FROM food_orders
ORDER BY cuisine_type, price;

---

4. 날짜 데이터 포맷하기

날짜 데이터

• 년, 월, 일, 시, 분, 초 등의 값을 모두 갖고 있다
• 목적에 따라 ‘월’, ‘주’, ‘일’ 등으로 포맷을 변경할 수도 있다.

 

yyyy-mm-dd 형식의 컬럼을 date type 으로 변경하기

SELECT DATE(date) date_type, date FROM payments;

 

 

date type 을 date_format 을 이용하여 년, 월, 일, 주 로 조회해보기

• 년 : Y (4자리), y(2자리)
• 월 : M, m
• 일 : d, e
• 요일 : w

SELECT date(date) date_type,
       date_format(date(date), '%Y') "년",
       date_format(date(date), '%m') "월",
       date_format(date(date), '%d') "일",
       date_format(date(date), '%w') "요일"
FROM payments;

---

5. 숙제

음식 타입별, 연령별 주문건수 pivot view 만들기 (연령은 10~59세 사이)

SELECT cuisine_type,
       MAX(IF(age=10, order_count, 0)) "10대",
       MAX(IF(age=20, order_count, 0)) "20대",
       MAX(IF(age=30, order_count, 0)) "30대",
       MAX(IF(age=40, order_count, 0)) "40대",
       MAX(IF(age=50, order_count, 0)) "50대"
FROM (
	SELECT cuisine_type,
       ( CASE WHEN age BETWEEN 10 AND 19 THEN 10
            WHEN age BETWEEN 20 AND 29 THEN 20
            WHEN age BETWEEN 30 AND 39 THEN 30
            WHEN age BETWEEN 40 AND 49 THEN 40
            WHEN age BETWEEN 50 AND 59 THEN 50
            END
		) age, count(1) order_count
	FROM food_orders a, customers b WHERE a.customer_id=b.customer_id
		AND age between 10 and 59
	GROUP BY 1, 2
) t
GROUP BY 1;

 

---

 

사전캠프 두번째 강의인 엑셀보다 쉽고 빠른 SQL 강의도 완강 ! 👏👏👏👏👏