[TIL] 241121 | SQL | Null | Pivot Table | Window Function | DATE | DATE_FORMAT

혜·2024년 11월 21일

DATE_FORMAT Date Window Function null pivot table sql

TIL

목록 보기

7/88

아티클 스터디 2개

엑셀보다 쉽고 빠른 SQL 5주차

엑셀보다 쉽고 빠른 SQL 5주차

조회한 데이터에 아무 값이 없다면?

NULL

SELECT restaurant_name,
	avg(rating) avg_rating,
	avg(if(rating <> 'Not given', rating, null)) null_rating
FROM food_orders fo 
GROUP BY 1

avg_rating의 경우 'Not given'값을 0으로 처리
null_rating의 경우 'Not given'인 값을 null값 처리하여 제외함

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

null값 대체하기

1) IF문 활용

IF(rating >= 1, rating, 대체값)
2) coalesce
coalesce(null값 대체할 칼럼, 대체할 값)

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

조회한 데이터가 상식적이지 않다면?

if문을 활용하여 데이터 바꿔주기
- 만약 배달음식을 주문한 사람의 나이가 3살이거나 주문일자가 1649년이라면

SQL로 PIVOT 테이블 만들기

Pivot table이란?

두 개 이상의 기준으로 데이터를 집계할 때, 보기 쉽게 배열하여 보여주는 것
순서: 베이스 데이터 만들기 -> 피벗테이블 만들기

[예시1]

select restaurant_name,
       max(if(hh='15', cnt_order, 0)) "15", -- 15시 칼럼
       max(if(hh='16', cnt_order, 0)) "16", -- 16시 칼럼
       max(if(hh='17', cnt_order, 0)) "17", -- 17시 칼럼
       max(if(hh='18', cnt_order, 0)) "18", -- 18시 칼럼
       max(if(hh='19', cnt_order, 0)) "19", -- 19시 칼럼
       max(if(hh='20', cnt_order, 0)) "20" -- 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 1, 2
) a - 이 서브쿼리가 DB
group by 1
order by 7 desc

MAX()를 사용하여 0이 아닌 값(해당 레스토랑의 해당 시간 주문 개수)을 추출

[예시2]

SELECT a.age,
	MAX(IF(a.gender = 'male', cnt_order, 0)) 'male',
	MAX(IF(a.gender = 'female', cnt_order, 0)) 'female'
FROM (
SELECT c.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(*) cnt_order
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
) a
GROUP BY 1 
ORDER BY age

Window Function

단위 안에서의 연산을 쉽게 할 수 있도록 도와주는 함수

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

RANK

순위를 매김

SELECT cuisine_type, restaurant_name, cnt_order, ranking
FROM 
(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(*) cnt_order
FROM food_orders fo 
GROUP BY 1, 2
) a
) b
WHERE ranking <= 3

음식 타입 별로, cnt_order에 대한 순위를 매기
마지막 WHERE절로 ranking 3위 이내만 표시

누적합 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

날짜 포맷과 조건

DATE()

DATE(변경할 칼럼 명)
날짜 형식으로 데이터 타입을 변경해 줌

DATE_FORMAT()

DATE_FORMAT(date type 칼럼, '%__')
```
	- %Y 연도 (두 자리)
```
- %y 연두 (뒤 두 자리)
- %M 월 (영문)
- %m 월 (숫자)
- %D 일 (서수 ex. 22nd)
- %d 일 (숫자만)
- %W 요일
- %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