예외값과 null 처리

MySQL에서는 연산 중 위의 Not given과 같이 사용할 수 없는 값이 있다면 해당 값은 0으로 간주해서 계산한다. 때문에 연산에 유효하지 않은 데이터가 포함되어 의도치 않은 결과가 나올 수 있다.

select restaurant_name,
       avg(if(rating<>'Not given', rating, null)) average_of_rating
from food_orders
group by 1

이 스크립트는 Not given이라는 값을 null로 대체한다. MySql에서는 null 값을 가지고 있는 row는 계산에서 제외하므로 유효한 데이터만 가지고 계산할 수 있다. 아니면 WHERE rating IS NOT NULL 같은 조건을 걸어도 되고

피벗 테이블

엑셀에서도 가끔 썼던 피벗 테이블의 형태를 SQL을 사용해서 구현할 수 있다.

select age,
	   max(if(gender='male', order_count, 0)) "male",
       max(if(gender='female', order_count, 0)) "female"
from (
	select c.gender, 
		   case when age between 10 and 19 then 10
	            when age between 20 and 29 then 20
	            when age between 30 and 39 then 30
	            when age between 40 and 49 then 40
	            when age between 50 and 59 then 50 end age,
	       count(*) "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 1, 2
) sub
group by 1
order by age;

성별, 연령별 주문건수를 Pivot Table 뷰로 만들었다. 이 때 고객의 나이는 10~59세이며, 연령 순으로 내림차순 정렬한다. 서브쿼리에서 먼저 성별과 연령대를 묶어 주문건수를 조회했고, 이를 활용해 피벗 테이블의 집계 기준을 구현했다.
(max()를 사용해서 기술적으로 피벗 뷰를 만든다는데 솔직히 이 부분은 이해가 잘 되지 않아 넘어갔다.)

Window Function (RANK, SUM)

window_function(argument) over (partition by 그룹 기준 컬럼 order by 정렬 기준)

Window Function의 기본 구조다.

• window_function: rank, sum, avg 등이 올 수 있다.
• argument : 함수에 따라 작성하거나 생략한다.
• partition by : 그룹을 나누기 위한 기준이다. group by 절과 유사하다고 생각하면 된다.
• order by : window_function을 적용할 때 정렬할 컬럼 기준을 기입한다.

RANK

select cuisine_type,
	   restaurant_name,
	   cnt_order,
	   ranking	
from (
	select sub.cuisine_type, 
		   sub.restaurant_name,
		   sub.cnt_order,
		   rank() over (partition by cuisine_type order by cnt_order desc) "ranking"
	from (
		select fo.cuisine_type, fo.restaurant_name, count(*) "cnt_order"
		from food_orders fo
		group by 1, 2
	) sub
) sub_ranking
where ranking <= 3

음식 타입별로 주문 건수가 가장 많은 상점을 3개씩 조회한다.

SUM

select cuisine_type,
       restaurant_name,
       cnt_order,
       sum_cuisine,
       concat((cnt_order / sum_cuisine * 100), "%") "ratio"
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 fo.cuisine_type, fo.restaurant_name, count(*) "cnt_order"
				from food_orders fo
				group by 1, 2
	) sub
	order by cuisine_type, cnt_order, sub.restaurant_name
) sub_sum;

각 음식점의 주문건이 해당 음식 타입에서 차지하는 비율을 구하고, 주문건이 낮은 순으로 정렬했을 때 누적 합과 각 음식 타입의 합에서 차지하는 비율을 구한다.