[TIL] 24-10-11

1. 조회한 데이터에 값이 없다면?

테이블에 잘못된 값이 있거나 JOIN을 했을 때 값이 없는 경우

NULL을 이용해 없는 값 제외하기

SELECT restaurant_name,
       AVG(IF(rating<>'Not given', rating, NULL)) avg_rating2
FROM food_orders
GROUP BY 1

NULL을 이용할 경우, 연산할때 0이 아니라 연산에서 제외시킴

WHERE절에 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

이 경우, b.customer_id가 NULL이 아닌 데이터만 불러오라고 지정

COALEASCE로 값을 대체해주기

SELECT a.order_id,
       a.customer_id,
       a.restaurant_name,
       a.price,
       b.name,
       COALESCE(b.age, 20) "null 제거",
       b.gender
FROM food_orders a LEFT JOIN customers b ON a.customer_id=b.customer_id

“COALESCE(컬럼, 값) 별명” 형태로 사용하며, NULL을 COALESCE의 값으로 대체한다.

2. 조회한 값이 비상식적일 때 조건문 이용하기

SELECT name,
       age,
       CASE WHEN age<15 THEN 15
            WHEN age>=80 THEN 80
            ELSE age
            END re_age
FROM customers

3. SQL로 Pivot Table 만들기

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,
       SUBSTRING(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 SUBSTRING(b.time, 1, 2) BETWEEN 15 AND 20
GROUP BY a.restaurant_name, hh
) a
GROUP BY restaurant_name
ORDER BY 7 DESC;

(IF문에 MAX 사용하기)

4. Window Function 사용하기

RANK

SELECT cuisine_type,
       restaurant_name,
       cnt_order,
       RANK() OVER(PARTITION BY cuisine_type ORDER BY cnt_order DESC) ranking
FROM
(
SELECT cuisine_type,
       restaurant_name,
       COUNT(1) cnt_order
FROM food_orders
GROUP BY 1,2
) a

사용방법

RANK() OVER(PARTITION BY 컬럼 ORDER BY 컬럼)

SUM

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
GROUP BY 1,2
) a
ORDER BY cuisine_type, cnt_order

기준별 합계: SUM(컬럼) OVER(PARTITION BY 컬럼)
기준 누적합: SUM(컬럼) OVER(PARTITION BY 컬럼 ORDER BY 컬럼)

5. 날짜 포맷 함수

DATE

SELECT day,
       DATE(day) change_day
FROM payments

DATE 함수는 데이터를 날짜형식으로 바꿔줌

DATE_FORMAT

SELECT DATE(day) day_type,
       DATE_FORMAT(DATE(day), '%Y') "년",
       DATE_FORMAT(DATE(day), '%m') "월",
       DATE_FORMAT(DATE(day), '%d') "일",
       DATE_FORMAT(DATE(day), '%w') "요일"
FROM payments

DATE_FORMATE을 이용하면 DATE 함수로 인해 날짜 형식이 된 데이터에서 원하는 것을 뽑아낼 수 있음

날짜 함수를 이용한 데이터 조회

SELECT DATE_FORMAT(DATE(day), '%Y') "년",
       DATE_FORMAT(DATE(day), '%m') "월",
       DATE_FORMAT(DATE(day), '%Y%m') "년월",
       COUNT(1) "주문건수"
FROM food_orders f INNER JOIN payments p ON f.order_id=p.order_id
WHERE DATE_FORMAT(DATE(day), '%m')='03'
GROUP BY 1,2,3
ORDER BY 1