SQL#5

codataffee·2024년 4월 13일

sql

SQL

목록 보기

6/19

#SQL

SUBQUERY, JOIN 복습

- 5주차

1. 데이터 결측값 처리방법

없는 값을 제외하기

IS NOT NULL

SELECT *
FROM food_orders
WHERE customer_id **IS NOT NULL** 

> customer_is 속성에서 NULL 값이 아닌 데이터들만 조회

다른 값을 대신 사용하기

IF(rating >=1, rating, 대체값) ; 데이터 값이 존재할 때
COALESCE(age, 대체값) ; 데이터가 NULL값일 때

SELECT COALESCE(age, 20) "null 제거",
FROM food_orders
WHERE age IS NULL

> age 속성에서 NULL 값인 데이터들만 조회 후
  age 속성 내 데이터가 NULL 값일 경우 20 으로 대체

2. 데이터 이상치(값) 처리방법

데이터의 값이 상식적이지 않은 경우 예시
1-1. 주문 고객의 나이

1-2. 결제 일자
조건문으로 값의 범위를 지정하기

조건문을 이용해 가장 큰 값, 가장 작은 값의 범위를 지정 > 상식적인 수준 안에서 범위 지정.

# 1-1. 주문 고객의 나이 처리.

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

3. SQL로 PIVOT TABLE 만들어보기 (실습)

PIVOT TABEL : 2개 이상의 기준으로 데이터를 집계할 때, 보기 쉽게 배열하여 보여주는 것.
기본 구조

가. 음식점별 시간별 주문건수 PIVOT TABLE 뷰 만들기

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

가-1. 음식점별, 시간별 주문건수 집계하기

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

가-2. 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 a.restaurant_name,
              SUBSTR(b.time, 1, 2) hh,
              COUNT(1) cnt_order
       FROM food_orders a INNER JOIN payments b ON a.order_id = b.order_id
       WHERE SUBSTR(b.time, 1, 2) BETWEEN 15 AND 20
       GROUP BY 1, 2
       ) a
GROUP BY 1
ORDER BY 7 DESC

가-3. PIVOT VIEW : 집계 기준(음식점명, 주문시간)

나. 성별, 연령별 주문건수 PIVOT TABLE 뷰 만들기

나이는 10~59세 사이 / 연령 기준 내림차순

나-1. 성별, 연령별 주문건수 집계하기

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

나-2. PIVOT VIEW 구조 만들기

SELECT age,
       MAX(IF(gender='male', order_count, 0)) male,
       MAX(IF(gender='female', order_count, 0)) female
FROM 
       ( # 성별, 연령별 주문건수 집계 서브쿼리
       SELECT b.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 age,
              COUNT(1)
       FROM food_orders a INNER JOIN customers b ON a.customer_id = b.customer_id
       WHERE b.age BETWEEN 10 AND 59
       GROUP BY 1, 2
       ) t
GROUP BY 1
ORDER BY age

나-3. PIVOT VIEW : 집계 기준(성별, 연령별)

4. 윈도우 함수

기본 구조

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

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

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

주문 건수별 순위 매기기, 결제 시간이 빠른 순으로 순위 매기기 등 가능
(음식 타입별 주문 건수가 가장 많은 상점 3개씩 조회하기)

가-1. 음식 타입별, 음식점별 주문 건수 집계하기

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

가-2. 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

가-3. 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
       GROUP BY 1, 2
       ) a
) b
WHERE rn<=3
ORDER BY 1, 4

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

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

나-1. 음식 타입별, 음식점별 주문 건수 집계하기

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

나-2. 카테고리별 합, 카테고리별 누적합 구하기

SELECT cuisine_type,
       restaurant_name,
       order_count,
       SUM(order_count) OVER (PARTITION BY cuisine_type) sum_cuisine_type,
       SUM(order_count) OVER (PARTITION BY cuisine_type ORDER BY order_count, restaurant_name) cumulative_sum
FROM
       (
       SELECT cuisine_type, 
              restaurant_name, 
              COUNT(1) order_count
       FROM food_orders
       GROUP BY 1, 2
       ) a

5. 날짜 포맷과 조건, 포맷 함수

날짜 데이터의 이해
- 문자타입, 숫자타입과 같이 날짜 데이터도 특정한 타입을 가지고 있다.
- 년, 월, 일, 시, 분, 초 등의 값을 모두 갖고 있으며 목적에 따라
  ‘월’, ‘주’, ‘일’ 등으로 포맷을 변경할 수도 있음.

가. 날짜 데이터의 여러 포맷

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

SELECT DATE(date) date_type,
       date
FROM payments

가-2. 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

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

SELECT DATE_FORMAT(DATE(date), '%Y') y,
       DATE_FORMAT(DATE(date), '%m') m,
       COUNT(1) order_count
FROM food_orders a INNER JOIN payments b ON a.order_id = b.order_id
WHERE DATE_FORMAT(DATE(date), '%m') = '03'
GROUP BY 1, 2
ORDER BY 1

- 과제풀이

음식 타입별, 연령별 주문건수 PIVOT VIEW 만들기
☑️

☑️ 작성 쿼리

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 f.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 f INNER JOIN customers c ON f.customer_id = c.customer_id 
WHERE age BETWEEN 10 AND 59
GROUP BY 1, 2
) a 
GROUP BY 1

☑️ POINT!
서브쿼리(Subquery):
먼저, food_orders 테이블과 customers 테이블을 조인하고,
연령(age)이 10에서 59 사이인 고객들의 주문 수를 연령 그룹별로 계산.
CASE 문을 사용하여 age 값을 연령 그룹으로 매핑.
결과는 cuisine_type, 할당된 age 그룹 및 해당 연령 그룹의 주문 수로 그룹화.

메인쿼리(Main Query):
서브쿼리의 결과를 가져와 cuisine_type별로 그룹화.
각 요리 유형에 대해 10대, 20대, 30대, 40대 및 50대의 연령 그룹 중 가장 많은 주문 수 선택

결과적으로, 요리 유형별로 다양한 연령 그룹에서 최대 주문 수를 보여주는 PIVOT VIEW를 생성