[SQL] 강의 5주차

MySQL에서 평균의 계산

계산할 수 없는 값을 '0'으로 간주한다

ex)
1, 2, 3, string --> (1+2+3+0) / 4

1, 2, 3, null --> (1+2+3) / 3

==> 상황에 따라 null로 처리해서 제외할 지, 0값으로 사용할 지 판단하고 케이스에 따라 활용해야 할 것

coalesce 함수

• 인자로 주어진 컬럼들 중 null이 아닌 첫 번째 값을 반환
  예를 들어 칼럼 score에 대해 coalesce(score, 0)으로 입력하면, 점수가 있는 값은 점수를 그대로 반환하되 null에 대해서는 대체값인 0으로 반환하게 된다

이상치 제외

age 칼럼에 15 이하 or 80 이상을 이상치라고 판단하고 15, 80으로 고정하고 싶을 때

case
	when age < 15 then 15
    when age > 80 then 80
    else age
end

이렇게 조건문으로 처리하면 모든 값이 between 15 and 80으로 통일된다

피벗 테이블 만들기

이건 그냥... 일단은 공식처럼 외워야하나 싶다
max()를 쓰는 이유를 잘 모르겠네

일단 row data를 만들고 (이하 코드 중 서브쿼리 부분)
그걸 가공해서 피벗 테이블 형태로 만들어주는 방식이다.

#성별, 연령별 주문건수 (나이 10~59, 연령순 내림차순)
SELECT re_age, #행을 더 범주가 많은 값으로
	
    # 이하 열. if문을 작성한 뒤 max로 묶어줘야 한다
	max(if(gender='male', cnt_order, 0)) 'male',
	max(if(gender='female', cnt_order, 0)) 'female'
FROM 
(
	select c.gender, 
		CASE 
			when c.age between 10 and 19 then '10s'
			when c.age between 20 and 29 then '20s'
			when c.age between 30 and 39 then '30s'
			when c.age between 40 and 49 then '40s'
			when c.age between 50 and 59 then '50s'
		END re_age,
		count(f.customer_id) cnt_order
	from customers c inner join food_orders f
		on c.customer_id = f.customer_id 
	where c.age between 10 and 59
	group by 1, 2
) aa
group by 1
order by 1 desc
;

window 함수

rank() over (partition by 칼럼명 order by 칼럼명 asc/desc) 별칭

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(1) cnt_order
	from food_orders f
	group by 1, 2
	) aa
) bb
where ranking <= 3
;

특정 ranking까지만 출력하기 위해서 한 번 더 서브쿼리로 묶고
where ranking <= 3으로 순위 조건을 설정해주면 된다

누적합을 구할 때 group by를 쓰지 않고도 window 함수만으로도 처리할 수 있다 (물론 서브쿼리 내에서 group by를 쓰고 있긴 하지만)

SELECT cuisine_type,
	restaurant_name,
	cnt_order,
	sum_cuisine,
	round(cnt_order / cum_cuisine, 4) * 100 percent_cuisine
from 
	(
	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 restaurant_name,
			cuisine_type,
			count(1) cnt_order
		from food_orders f
		group by 1, 2
		) aa
	order by cuisine_type, cnt_order
	)bb
;

날짜 데이터

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_format() 함수는 날짜 타입의 데이터에 포맷을 설정해주는 함수

• '%Y'로 하면 2024, '%y'로 하면 24 형태로 반환한다
• '%w'의 결과는 일요일=0, 월요일=1 방식으로 반환한다

SELECT date_format(date(date), '%Y') "Year",
	date_format(date(date), '%m') "Month",
	# date_format(date(date), '%Y%m' "YearMonth"
	count(1) "cnt_order"
FROM food_orders f inner join payments p 
	ON f.order_id = p.order_id
where date_format(date(date), '%m') = '03' # date_format 함수 결과 문자형 타입이 됨
group by 1, 2
order by 1
;

마무리 - 숙제

음식 타입별, 연령대별 피벗 테이블 만들기

SELECT cuisine_type,
	max(if(re_age='10s', cnt_order, 0)) "10s",
	max(if(re_age='20s', cnt_order, 0)) "20s",
	max(if(re_age='30s', cnt_order, 0)) "30s",
	max(if(re_age='40s', cnt_order, 0)) "40s",
	max(if(re_age='50s', cnt_order, 0)) "50s"
FROM 
	(
	SELECT f.cuisine_type,
		CASE 
			when c.age between 10 and 19 then '10s'
			when c.age between 20 and 29 then '20s'
			when c.age between 30 and 39 then '30s'
			when c.age between 40 and 49 then '40s'
			when c.age between 50 and 59 then '50s'
		END "re_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
	) aa
group by 1
;