5주차

Suhyeon Lee·2024년 8월 30일

[수업 목표]

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

[복습]

Subquery
- Query 결과를 Query에 다시 활용하는 것
- 기본 형식

    SELECT column1, special_column
    FROM (/*subquery*/
         SELECT column1, column2 special_column
         FROM table1
         ) a

JOIN
- 두 개 이상의 테이블을 결합하여 사용하는 것
- JOIN의 형태에 따라 Left join, Inner join 등이 있음
- 기본 형식

    -- LEFT JOIN
    SELECT 조회할 컬럼
    FROM 테이블1 a LEFT JOIN 테이블2 b ON a.공통컬럼명 = b.공통컬럼명
    
    -- INNER JOIN
    SELECT 조회할 컬럼
    FROM 테이블1 a INNER JOIN 테이블2 b ON a.공통컬럼명 = b.공통컬럼명

A. 예상하지 못한 값이 Query 결과에 나올 때: 조회한 데이터에 아무 값이 없는 경우

1. 데이터가 없을 때의 연산 결과 변화 케이스

테이블에 잘못된 값이 들어 있을 수 있음
JOIN을 했을 때 값이 없을 수 있음
→ 사용할 수 없는 데이터가 들어 있거나, 값이 없는 경우 어떻게 처리해줘야 할까?
(데이터 사용할 때 매우 흔하게 일어남)

2. 방법 1: 없는 값 제외

MySQL에서는 사용할 수 없는 값일 때 해당 값을 연산에서 제외 → 0으로 간주
즉, 평균 rating을 구하는 Query를 아래와 같이 작성했을 때

SELECT restaurant_name,
       AVG(rating) average_of_rating,
       AVG(IF(rating<>'NOT given', rating, NULL)) average_of_rating2
FROM food_orders
GROUP BY 1

실제 연산에 사용되는 데이터는 다음과 같음

명확하게 연산을 지정해주기 위해 NULL 문법 이용하기

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

→ NULL 제거를 한 결과는 INNER JOIN 결과와 동일

3. 방법2: 다른 값을 대신 사용

사용할 수 없는 값 대신 다른 값을 대체해서 사용할 수 있음
데이터 분석 시에는 평균값 혹은 중앙값 등 대표값을 이용하여 대체하기도 함
다른 값으로 변경하고 싶을 때 두 개의 문법을 이용할 수 있음
- 다른 값이 있을 때 조건문 이용하기
  IF(rating>=1. rating, 대체값)
- NULL값일 때
  COALESCE(age, 대체값)
값의 변경

SELECT	fo.order_id ,
    	fo.customer_id ,
    	fo.restaurant_name ,
    	fo.price ,
    	c.name ,
    	c.age ,
    	COALESCE(c.age,20) "null 제거",
    	c.gender 
FROM food_orders fo LEFT JOIN customers c ON fo.customer_id = c.customer_id 
WHERE c.age IS NULL

B. 조회한 데이터가 상식적이지 않은 값을 가지고 있는 경우

1. 상식적이지 않은 데이터의 예시

주문 고객의 나이가 2세
결제 일자가 1970년대

2. 방법: 조건문으로 값의 범위를 지정하기

조건문으로 가장 큰 값, 가장 작은 값의 범위를 지정 → 상식적인 수준 안에서
나이의 경우 아래와 같이 범위 지정 가능

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

C. SQL로 Pivot Table 만들어보기

1. Pivot table 구조 소개

Pivot Table
- 2개 이상의 기준으로 데이터를 집계할 때, 보기 쉽게 배열하여 보여주는 것
- 데이터를 뽑아 엑셀로 가공하지 않고 Pivot table을 만들면 업무를 효율적으로 할 수 있음
기본 구조

예시
- 집계 기준: 일자, 시간

2. [실습] 음식점별 시간별 주문 건수 Pivot Table 뷰 만들기

15~20시 사이, 20시 주문건수 기준 내림차순

a. 음식점별, 시간별 주문건수 집계

SELECT fo.restaurant_name,
	   SUBSTRING(p.time,1,2) hh,
       COUNT(1) cnt_order
FROM food_orders fo INNER JOIN payments p ON fo.order_id = p.order_id
WHERE SUBSTRING(p.time,1,2) BETWEEN 15 AND 20
GROUP BY 1, 2

b. Pivot view 구조 만들기

SELECT restaurant_name,
       MAX(IF(hh='15', cnt_order, 0)) "15",
       MAX(IF(hh='16', cnt_order, 0)) "16",
       MAX(IF(hh='17', cnt_order, 0)) "17",
       MAX(IF(hh='18', cnt_order, 0)) "18",
       MAX(IF(hh='19', cnt_order, 0)) "19",
       MAX(IF(hh='20', cnt_order, 0)) "20"
FROM (
      SELECT fo.restaurant_name,
        	 SUBSTRING(p.time,1,2) hh,
        	 COUNT(1) cnt_order
      FROM food_orders fo INNER JOIN payments p ON fo.order_id = p.order_id
      WHERE SUBSTRING(p.time,1,2) BETWEEN 15 AND 20
      GROUP BY 1, 2
     ) a
GROUP BY 1
ORDER BY 7 DESC

3. [실습] 성별, 연령별 주문건수 Pivot table 뷰 만들기

나이는 10~59세 사이, 연령 순으로 내림차순
a. 성별, 연령별 주문건수 집계

SELECT c.gender ,
	   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 range_of_age,
       COUNT(1) 
FROM food_orders fo INNER JOIN customers c ON fo.customer_id = c.customer_id
WHERE c.age BETWEEN 10 AND 59
GROUP BY 1, 2

b. Pivot view 구조 만들기

SELECT	range_of_age,
        MAX(IF(gender='female',order_count,0)) female,
        MAX(IF(gender='male',order_count,0)) male
FROM (
     SELECT	c.gender ,
         	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 range_of_age,
        		COUNT(1) order_count
	FROM food_orders fo INNER JOIN customers c ON fo.customer_id = c.customer_id
	WHERE c.age BETWEEN 10 AND 59
	GROUP BY 1, 2
	) t
        GROUP BY 1
        ORDER BY range_of_age

D. 업무 시작을 단축시켜 주는 마법의 문법 (Window Function - RANK, SUM)

1. Window Function의 사례와 기본 구조

Window Function
- 각 행의 관계를 정의하기 위한 함수
- 그룹 내의 연산을 쉽게 만들어 줌
활용 예시
- 한식 식당 중에서 주문건수가 많은 순으로 순위 매기기
- 한식 식당 전체 주문건수 중에서 A 식당이 차지하는 비율
- 2건 이상 주문을 한 소비자가 처음 주문한 식당과 두 번째로 주문한 식당 같이 조회
기본 SQL 구조로 해결하기 위해서는 복잡하게 Subquery문을 이용하거나 여러 번의 연산을 수행해야 하지만 자체적으로 제공되는 Window function 기능을 이용하면 조금 더 편리함
기본 구조

WINDOW_FUNCTION(argument) OVER (PARTITION BY 그룹 기준 컬럼 ORDER BY 정렬 기준)

WINDOW_FUNCTION: 기능명을 사용해 줍니다(SUM, AVG와 같이 기능명이 있음)
argument: 함수에 따라 작성하거나 생략
PARTITION BY: 그룹을 나누기 위한 기준(GROUP BY 절과 유사)
ORDER BY: window function을 적용할 때 정렬할 컬럼 기준을 적기

2. N번째까지의 대상을 조회하고 싶을 때: RANK

RANK: 특정 기준으로 순위를 매겨주는 기능
[실습] 음식 타입별로 주문 건수가 가장 많은 상점 3개씩 조회하기
a. 음식 타입별, 음식점별 주문 건수 집계

SELECT cuisine_type , restaurant_name , COUNT(1) order_count
FROM food_orders fo 
GROUP BY 1, 2

b. 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 fo 
	 GROUP BY 1, 2
	 ) a

c. 3위까지 조회하고 음식 타입별, 순위별 정렬

SELECT  cuisine_type,
        restaurant_name,
        order_count,
        rn "순위"
FROM (
     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 fo 
          GROUP BY 1, 2
         ) a
    ) b
WHERE rn <= 3
ORDER BY 1, 4

3. 전체에서 차지하는 비율, 누적합을 구할 때: SUM

누적합이 필요하거나 카테고리별 합계 컬럼과 원본 컬럼을 함께 이용할 때 유용
[실습] 각 음식점의 주문건이 해당 음식 타입에서 차지하는 비율을 구하고, 주문건이 낮은 순으로 정렬했을 때 누적 합 구하기
a. 음식 타입별, 음식점별 주문 건수 집계

            SELECT cuisine_type , restaurant_name , count(1) order_count
            FROM food_orders fo 
            GROUP BY 1, 2

b. 카테고리별 합, 카테고리별 누적합

            SELECT  cuisine_type ,
            		restaurant_name ,
            		cnt_order ,
            		SUM(cnt_order) OVER (PARTITION BY cuisine_type) sum_cuisine,
            		SUM(cnt_order) OVER (PARTITION BY cuisine_type ORDER BY cnt_order) cum_cuisine
            FROM (
            	 SELECT cuisine_type , restaurant_name , count(1) cnt_order
            	 FROM food_orders fo 
            	 GROUP BY 1, 2
            	 ) a
            ORDER BY cuisine_type , cnt_order

→ cnt_order가 동일한 값을 일괄로 더해 cum_cuisine으로 출력되는 문제가 있음
(cum_cuisine이 1,2,3,..,,13,16,…이 아닌 9,9,9,…,13,37,…로 나옴)

c. 누적합 수정

문제 상황 이해
- SQL에서 WINDOW 함수(SUM 등)를 사용할 때, SUM으로 동일한 cnt_order 값을 가진 여러 행이 있을 경우 SQL 엔진이 이 값을 한꺼번에 더하는 현상
- cnt_order의 순서를 결정할 명확한 기준이 없으므로 발생한 문제임
해결 방법
- ORDER BY 절에 cnt_order 외에 추가적인 열에 순서를 부여할 수 있는 restaurant_name을 포함시키기
- 동일한 cnt_order 값을 가진 행들이 명확하게 순서가 정해져 누적합이 정상적으로 처리됨
누적합을 순서대로 표기하기 위해 마지막 ORDER BY 에 cum_cuisine을 추가

SELECT  cuisine_type ,
        restaurant_name ,
        cnt_order ,
        SUM(cnt_order) OVER (PARTITION BY cuisine_type) sum_cuisine,
        SUM(cnt_order) OVER (PARTITION BY cuisine_type ORDER BY cnt_order, restaurant_name) cum_cuisine
FROM (
      SELECT cuisine_type , restaurant_name , count(1) cnt_order
      FROM food_orders fo 
      GROUP BY 1, 2
     ) a
ORDER BY cuisine_type , cnt_order , cum_cuisine

이 외의 Window Function을 알고 싶다면 여러 가지를 검색해서 사용해보는 것을 추천

E. 날짜 포맷과 조건까지 SQL로 한 번에 끝내기 (포맷 함수)

1. 날짜 데이터의 이해

문자 타입, 숫자 타입과 같이 날짜 데이터도 특정한 타입을 가지고 있음
- 년, 월, 일, 시, 분, 초 등의 값을 모두 갖고 있음
- 목적에 따라 ‘월’, ‘주’, ‘일’ 등으로 포맷 변경 가능
아래와 같은 형식의 데이터라면 날짜로 변경 가능

2. [실습1] 날짜 데이터의 여러 포맷

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

        SELECT date(date) date_type,
        			 date
        FROM payments

b. 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 p

3. [실습2] 3월 조건으로 지정하고, 년도별로 주문 건수 정렬하기

a. 년도, 월을 포함하여 데이터 가공하기

SELECT  DATE_FORMAT(DATE(date), '%Y') y,
        DATE_FORMAT(DATE(date), '%m') m,
        count(1) cnt_order
FROM food_orders fo LEFT JOIN payments p ON fo.order_id = p.order_id

b. 년도, 월별 주문 건수 구하기

SELECT  DATE_FORMAT(DATE(date), '%Y') y,
        DATE_FORMAT(DATE(date), '%m') m,
        count(1) order_count
FROM food_orders fo LEFT JOIN payments p ON fo.order_id = p.order_id 
GROUP BY 1, 2

c. 3월 조건으로 지정하고, 년도별로 정렬하기

SELECT  DATE_FORMAT(DATE(date), '%Y') y,
        DATE_FORMAT(DATE(date), '%m') m,
        count(1) order_count
FROM food_orders fo LEFT JOIN payments p ON fo.order_id = p.order_id 
WHERE DATE_FORMAT(DATE(date), '%m') = '03'
GROUP BY 1, 2
ORDER BY 1

5주차 숙제

💡 음식 타입별, 연령별 주문건수 pivot view 만들기

SELECT	cuisine_type ,
		MAX(IF(age=10, cnt_order, 0)) "10대",
		MAX(IF(age=20, cnt_order, 0)) "20대",
		MAX(IF(age=30, cnt_order, 0)) "30대",
		MAX(IF(age=40, cnt_order, 0)) "40대",
		MAX(IF(age=50, cnt_order, 0)) "50대"
FROM (
	SELECT 	fo.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) cnt_order 
	FROM food_orders fo INNER JOIN customers c ON fo.customer_id = c.customer_id 
	WHERE age BETWEEN 10 AND 59
	GROUP BY 1, 2
	) a
GROUP BY 1

Suhyeon Lee

2 B R 0 2 B

이전 포스트

1주차(2)

다음 포스트

5주차