<TIL> 2023-12-11

내일배움캠프 사전학습캠프 다섯째 날

본 캠프 시작 일주일 전

 

스파르타코딩클럽 / 엑셀보다 쉽고 빠른 SQL 5주차 수강완료(완)

서브쿼리 만큼 난이도가 복잡한 구문은 없었고 실무에서 용이한 SQL문을 배웠다.

 

배운 것 

  - 조건에 따라 값을 대체하거나 제외하는 SQL문

  - SQL문으로 피봇 테이블 형식의 데이터 만들기

  - DATE 형식 데이터 다루기

 

 SQLD 사전 성적 발표가 오늘 났고 60점 커트라인데 78점으로 합격 예정이다! 

 게을러서 늦게 신청하는 바람에 대전에서 칠 수 있는걸 서울까지 버스타고 시험치러 다녀왔지만

 합격하니 만사 오케이 기분 좋다.

 

---

조건에 따라 값을 대체하는 SQL문

# 조건에 따른 값을 제외하는 구문
SELECT restaurant_name, 
       AVG(rating) avg_rating, 
       AVG(IF(rating<>'Not given', rating, NULL)) avg_rating2
FROM food_orders
GROUP BY restaurant_name;

 

IF문을 이용하여 rating의 값이 Not given이 아니면 rating값을 rating이 Not given이면 NULL값을 부여하도록 하여 avg_rating2를 계산한다. 

ave_rating에서는 Not_given값을 0으로 하여 평균 계산에 포함시키지만

NULL값은 집계에서 빠지기 때문에 avg_rating2에서는 집계에 빠짐

---

# 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.customer_id IS NULL;

# NVL(컬럼명, 대체값) at Oracle

 

COALESCE() 함수는 주어진 값 중 처음으로 NULL이 아닌 값을 반환하는 함수이다. 

따라서 b.age가 NULL이면 바로 그 뒤인 20을 반환하고 b.age가 NULL이 아니면 b.age값을 반환하도록 되는 것이다.

위의 IF문에서 IS NULL로 비교하는 것을 통해 같은 결과를 얻을 수도 있다.

---

# 값의 범위를 지정, 이상치 정제
SELECT name, 
       age,
       CASE WHEN age < 15 THEN 15
            WHEN age > 80 THEN 80
            ELSE age
            END re_age
FROM customers

 

CASE WHEN 구문을 이용하여 나이가 15세 미만이면 15로 바꾸고, 80세를 초과하면 80세로 바꾼다. 그리고 그 외의 범위 안에 있는 나이는 해당 나이 값을 그대로 사용하도록 한다. 이는 나이의 범위를 15~80으로 제한하여 해당 범위를 벗어난 이상치를 것과 같다. 

---

피벗 테이블(Pivot Table)

SELECT age, 
       MAX(IF(gender='male', cnt_order, 0)) 'male', 
       MAX(IF(gender='female', cnt_order, 0)) 'female'
FROM 
(
SELECT gender, 
       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 age, 
       COUNT(1) cnt_order
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
ORDER BY 1, 2

음식 주문테이블과 손님 테이블을 내부조인하여서 성별과 나이대에 따른 음식 주문 횟수를 계산하는

서브쿼리를 이용한다.

 

집계함수 MAX를 사용하는 이유는 집계함수를 사용하지 않았을 때는 NULL값을 포함한 상태에서 결과가 출력되기 때문이다. MAX 이외의 다른 집계함수도 사용이 가능하다. 

 

해당 SQL실행 결과는 피벗 테이블의 형태로 아래와 같다. 

 

참고자료 :  https://data-kindergarten.tistory.com/46

---

DATE_FORMAT

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

 

DATE형식의 데이터를 다룰 때 사용하는 함수인 DATE, DATE_FORMAT을 간략하게 배웠다. MySQL과 Oracle에서 사용되는 형식이 각각 다 다르고 외우기 까다롭기 때문에 해당하는 부분에 대해서는 추후에 블로그 글로 정리하고자한다.