[내배캠 베이스캠프] 엑셀보다 쉽고 빠른 SQL 강의 | 5주차

 

원하는 데이터가 없는 경우

 

-- rating 값이 'Not given'이면 null 처리해서 제외
IF(rating <> 'Not given', rating, 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

-- 값을 변경하기 (COALESCE)
SELECT a.order_id,
       a.customer_id,
       a.restaurant_name,
       a.price,
       b.name,
       b.age,
       COALESCE(b.age, 20) "null 제거",
       b.gender
FROM food_orders a LEFT JOIN customers b ON a.customer_id=b.customer_id
where b.age is null

 

 

 

Pivot 테이블 만들기

 

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

 

 

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

 

-- 방식 1
SELECT
    cuisine_type,
    MAX(IF(age_group = 10, order_count, 0)) AS "10대",
    MAX(IF(age_group = 20, order_count, 0)) AS "20대",
    MAX(IF(age_group = 30, order_count, 0)) AS "30대",
    MAX(IF(age_group = 40, order_count, 0)) AS "40대",
    MAX(IF(age_group = 50, order_count, 0)) AS "50대"
FROM (
    SELECT
        fo.cuisine_type,
        CASE
            WHEN c.age BETWEEN 10 AND 19 THEN 10
            WHEN c.age BETWEEN 20 AND 29 THEN 20
            WHEN c.age BETWEEN 30 AND 39 THEN 30
            WHEN c.age BETWEEN 40 AND 49 THEN 40
            WHEN c.age BETWEEN 50 AND 59 THEN 50
        END AS age_group,
        COUNT(1) AS 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
        fo.cuisine_type,
        age_group
) t
GROUP BY cuisine_type;


-- 방식 2
SELECT
    a.cuisine_type,
    SUM(CASE WHEN b.age BETWEEN 10 AND 19 THEN 1 ELSE 0 END) AS "10대",
    SUM(CASE WHEN b.age BETWEEN 20 AND 29 THEN 1 ELSE 0 END) AS "20대",
    SUM(CASE WHEN b.age BETWEEN 30 AND 39 THEN 1 ELSE 0 END) AS "30대",
    SUM(CASE WHEN b.age BETWEEN 40 AND 49 THEN 1 ELSE 0 END) AS "40대",
    SUM(CASE WHEN b.age BETWEEN 50 AND 59 THEN 1 ELSE 0 END) AS "50대"
FROM food_orders a
JOIN customers b
    ON a.customer_id = b.customer_id
WHERE b.age BETWEEN 10 AND 59
GROUP BY a.cuisine_type;

 

 

 

윈도우 함수 (RANK, SUM)

 

-- 기존 구조
RANK() OVER (PARTITION BY 그룹 기준 컬럼 ORDER BY 정렬 기준)
SUM(argument) OVER (PARTITION BY 그룹 기준 컬럼 ORDER BY 정렬 기준)

 

 

 

날짜 포맷

 

SELECT DATE(date) date_type, -- 날짜 타입으로 변경
       DATE_FORMAT(DATE(date), '%Y') "년",
       DATE_FORMAT(DATE(date), '%d') "일",
       DATE_FORMAT(DATE(date), '%m') "월",
       DATE_FORMAT(DATE(date), '%w') "요일"
FROM payments