수업 목표

1. 예상하지 못한 값이 Query 결과에 나올 때 어떻게 처리해야 할까?
2. 엑셀에서 해야하는 Pivot, SQL 로 한 번에 구현할 수 있을까?
3. SQL로 이런 것까지 할 수 있었다니! 업무 시간이 엄청 줄어들 것 같은데!

컬럼에 데이터가 엄서용

방법1

테이블이 원하는 데이터를 가지고 있지 않은 경우, if()문을 사용한 null값으로 없는 값 제외.

SELECT restaurant_name,
	   floor(avg(rating)) avg_rating,
	   floor(avg(if(rating<>'Not given',rating, null))) --0을 null값으로 변경
from food_orders fo
group by 1

Q. avg_raing과 예외처리한 avg_rating2 의 값이 더 큰 이유
A. 연산 할 수 없는 값의 경우(EX.문자열 'Not given') mySql에서는 0으로 간주

Null값 까지 제거해 주고 싶은 경우 where (컬럼) is not null 사용하기.

방법2

다른 값을 대신 사용해주기
coalesce(컬럼, 변경하고 싶은 값) "null 제거",

상식적이지 않은 값

측정 한 키의 cm 가 2.30 일때, 어플 사용자의 나이가 2세

select name, age,
	case
		when age < 15 then 15
		when age > 80 then 80
		else age
	end re_age	
from customers c

SQL Pivot Table

엑셀 대신 SQL문으로 Pivot Table을 만드는 이유는? 프로세스를 단축시키기 위해.

예시1

음식점별 시간별 주문건수 Pivot Table 뷰 만들기 (15~20시 사이, 20시 주문건수 기준 내림차순)

서브쿼리: 음식점의 시간별 주문건수

select f.restaurant_name,
	substr(p.time, 1, 2) hh,
	count(1) cnt_order
from food_orders f 
inner join payments p --공통된 값만 보여주기
on f.order_id = p.order_id
where substr(p.time, 1, 2) BETWEEN 15 and 20 
GROUP by 1,2 

피봇테이블 구조. 깔끔하게 보여주기 위해서 max 붙이기

select restaurant_name, --행축(음식점 명)
       max(if(hh='15', cnt_order, 0)) "15", --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 
(서브쿼리)
group by 1 --max,sum,avg와 같이 계산식이 들어가면 group by 구문이 꼭 들어가야 한다.
order by 7 desc --20시 기준 내림차순

예시2

성별, 연령별 주문건수 Pivot Table 뷰 만들기 (나이는 10~59세 사이, 연령 순으로 내림차순)

select age,
	   max(if(gender='male', cnt_order, 0)) 'male',
	   max(if(gender='female', cnt_order, 0)) 'female'
from
(
SELECT 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(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
) sub1
group by 1
order by 1

업무에서 유용한 문법

Window_function()

예시

• 한식 식당 중에서 주문건수가 많은 순으로 순위를 매기기
• 한식 식당 전체 주문건수 중에서 A 식당이 차지하는 비율 구하기
• 2건 이상 주문을 한 소비자 중에, 처음 주문한 식당과 2번째로 주문한 식당을 같이 조회

구조

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

• wiondw 함수는 함수와 over가 한 쌍으로 간다.
• over 뒤엔, 구분을 위한 파티션 컬럼을 작성해 준다. (어떤 단위)

실습1. Rank()

1.음식 타입별, 음식점별 주문 건수 집계하기

음식 타입별로 주문 건수가 가장 많은 상점 3위까지 조회하기

SELECT
	 ranking,
	 cuisine_type,
	 restaurant_name,
	 cnt_order
from
(
SELECT
rank() over (
				partition by cuisine_type 
				order by cnt_order desc
	) ranking, -- 괄호 안에 내용 없어도 괜찮음
	cuisine_type,
	restaurant_name,
	cnt_order
from
(
SELECT cuisine_type,	
	   restaurant_name,
	   COUNT(1) cnt_order 
from food_orders f
group by 1,2
) sub1 --상점 별 주문 건수 구하기
) sub2 --ranking
where ranking <= 3

실습2. 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 f
group by 1,2
) sub1 -- 상정 별 주문 건수
order by cuisine_type, cnt_order

포맷 함수

날짜

문자열 형식을 날짜 형식으로 바꾸기

SELECT date, 
	   date(date) change_date
FROM payments

아이콘이 문자열(abc)에서 시계 모양으로 변경되었다.

포맷 형식

• 년 : Y (4자리), y(2자리)
• 월 : M, m
• 일 : d, e
• 요일 : 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

예시

3월 조건으로 지정하고, 년-월 별로 정렬하기

select date_format(date(date), '%Y') '년',
	   date_format(date(date), '%m') '월',
	   date_format(date(date), '%Y-%m') '년월',
       count(1) '주문건수'
from food_orders f 
inner join payments p 
on f.order_id = p.order_id
WHERE date_format(date(date), '%m') = '03'
group by 1, 2, 3

마지막 과제

음식 타입별, 연령별 주문건수 pivot view 만들기

• 가로: 음식 타입
• 세로: 연령별 (10,20,30,40...)

SELECT cuisine_type,
	   max(if(age = 10, cnt_order, 0)) '10대',
	   max(if(age = 20, cnt_order, 0)) '20대',
	   max(if(age = 30, cnt_order, 0)) '30대',
	   max(if(age = 40, cnt_order, 0)) '40대',
	   max(if(age = 50, cnt_order, 0)) '50대'
from
(
select f.cuisine_type,
       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
        else 0
       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 -- 음식 종류와, 연령 별로 그룹화
) sub1
group by 1; -- 피벗 테이블의 가로 열 음식 종류

완강 소감

좀 더 실무에 가까운 쿼리문들을 다양한 예시들로 학습해 볼 수 있어서 좋았다. 선생님이 차근차근 잘 가르쳐 주신 덕분에, 쿼리 작성 할 때도 차근차근 잘 쓰고 있다는 것이 쓰면서도 느껴진다. 코드카타 가보자고~