엑셀보다 쉽고 빠른 SQL 5주차(Pivot table, Window Function, 포맷 함수)

배울 내용

• 예상하지 못한 상황에 대처하는 법
• 엑셀에서 사용하는 Pivot 기능, SQL에서 구현
• 업무 시간을 단축할 수 있는 기능

---

예상하지 못한 상황에 대처하는 법

 

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

• 테이블에 잘못된 값이 들어있을 수 있다
• Join을 했을 때 값이 없는 경우도 존재

 

방법1: 없는 값을 제거

• 일반적으로 평균 구하는 함수는 Not given인 경우에 0으로 치고 나눌때도 개수로 포함
• null로 변경하고 평균 구하는 함수는 null이기 때문에 더하지도 않고 나눌때도 개수로 포함하지 않음

select restaurant_name,
       # 일반적인 평균구하는 함수
       avg(rating) avg_rating,
       # if문을 이용해 rating이 Not given인 경우에는 null로 변경
       avg(if(rating<>"Not given", rating, null)) avg_rating2
from food_orders
group by 1

 

방법2: 아예 null로 된 값들을 지워버림

• where절을 이용해 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

 

방법3: 다른 값을 대신 사용하기

• coalesce(a, b) : a(컬럼)에 값이 없다면 b(값)로 대체
• where절을 이용해 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

 

2. 조회한 데이터가 상식적이지 않은 값을 가지고 있다면?

• 상식적으로 말이 안되는 데이터가 있는 경우
  • 주문 고객 나이 - 보통 음식 주문한 고객은 20세 이상은 성인인 경우가 많은데 2세와 같은 데이터가 있음
  • 결제 일자 - 비교적 최근 일자가 있어야 하지만 데이터에 1970년대와 같은 데이터가 있음

 

방법: 조건문으로 값의 범위를 지정하기

• 나이가 15 미만의 경우에는 15로 설정
• 나이가 80 이상의 경우에는 80으로 설정

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

---

SQL로 Pivot Table 만들기

 

구조

• 2개 이상의 기준으로 데이터 집계할 때, 보기 쉽게 배열해 보여주는 것
• 행(집계 기준), 열(구분 컬럼), 데이터

 

pivot view 만드는 단계

1. 베이스 데이터 생성

• food_orders 테이블의 restaurant_name 컬럼을 가져옴
• payments 테이블의 time 컬럼에서 시간 데이터만 가져옴

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	# 15에서 20시 사이만 가져옴
group by 1, 2	# 레스토랑 명과 시간으로 그룹화

 

2. 베이스 데이터 이용해 pivot view 생성

• pivot view를 깔끔하게 만들기 위해서는 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 
(
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
group by 1
order by 7 desc		# 20시 기준 내림차순 정렬

 

실습

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

select age,
       max(if(gender='male', order_count, 0)) male,
       max(if(gender='female', order_count, 0)) female
from 
(
select b.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) order_count
from food_orders a inner join customers b on a.customer_id=b.customer_id
where b.age between 10 and 59
group by 1, 2
) t
group by 1
order by age

---

업무 시작을 단축시켜주는 문법 (Window Function - RANK, SUM)

 

Window Function

• 각 행의 관계를 정의하기 위한 함수로 그룹 내의 연산을 쉽게 만들어줌

예시

• 한식 식당 중에서 주문건수가 많은 순으로 순위를 매기고 싶다
• 한식 식당 전체 주문건수 중에서 A 식당이 차지하는 비율을 알고 싶다
• 2건 이상 주문을 한 소비자 중에, 처음 주문한 식당과 두번째로 주문한 식당을 같이 조회한다.
  • 기본 SQL 구조로 해결하기 위해서는 복잡하게 서브쿼리 문을 이용하거나, 여러번의 연산 수행해야 하지만, 자체적으로 제공해주는 기능을 이용하면 편리

구조

• window_finction : 기능 명(sum, avg 등)
• argument  : 함수에 따라 작성하거나 생략
• partition by : 그룹 나누기 위한 기준으로 group by 절과 유사
• order by : window function 적용할 때 정렬 할 기준 컬럼

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

 

N번째까지의 대상 조회하고 싶을 때 Rank

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

1. 베이스 데이터 생성 : 음식 타입별, 음식점별, 주문 건수 집계

2. Rank 함수 적용

• partition : 구분, 어떤 단위로 묶어줄지를 결정
• 음식 타입별로 랭킹을 구함
• order by : 어떤 순서로 정렬을 할지
• 주문 건수가 많으면 위

select cuisine_type,
       restaurant_name,
       cnt_order,
       rank() over (partition by cuisine_type order by cnt_order desc) ranking
       # rank 함수 안에는 아무것도 작성하지 않아도됨
from
(
select cuisine_type,
       restaurant_name,
       count(1) cnt_order
from food_orders
group by 1, 2
) a

 

3. 3위까지 조회하고 음식 타입별, 순위별로 정렬

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
       # rank 함수 안에는 아무것도 작성하지 않아도됨
from
(
select cuisine_type,
       restaurant_name,
       count(1) cnt_order
from food_orders
group by 1, 2
) a
) b
where ranking<=3

 

전체에서 차지하는 비율, 누적합을 구하고 싶을 때 Sum

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

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

select cuisine_type,
       restaurant_name,
       cnt_order,
       sum(cnt_order) over (partition by cuisine_type) sum_cuisine,
       # sum에 대한 window 함수 (cuisine_type의 각 타입 별 합)
       sum(cnt_order) over (partition by cuisine_type order by cnt_order) cum_cuisine 
       # 누적합 (1번값 2번값 더한 식으로 순차적으로 내려옴 => 정렬)
from
(
select cuisine_type,
       restaurant_name,
       count(1) cnt_order
from food_orders
group by 1, 2
) a
order by cuisine_type, cnt_order

2. 카테고리별 합, 카테고리별 누적합 구하기

3. 각 음식점이 차지하는 비율 구하고, 음식점별 주문건수 오름차순 정렬

---

날짜 포맷과 조건을 이용

 

포맷 함수 : 데이터에 날짜 지정/조건에 날짜를 사용할 수 있는 기능

날짜 데이터의 형태

• date: xxxx-xx-xx
• time: xx:xx:xx

 

날짜 데이터를 date 형식으로 변환해주는 함수 date

select date,
       date(date) change_date	# date 함수
from payments

 

date 타입의 컬럼을 포맷팅해주는 함수 date_format

• 요일은 일요일이 0, 월요일이 1 ... 

select date(date) change_date	# date 함수
       date_format(date(date), '%Y') "년",
       date_format(date(date), '%m') "월",
       date_format(date(date), '%d') "일",
       date_format(date(date), '%w') "요일",
from payments

 

• 년도, 월 별로 주문 건수 구하기

select data_format(date(data), '%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
order by 1

---

과제

1. 음식 타입별, 연령별 주문건수 pivot view 만들기 (연령은 10~59세 사이)

1. SQL 기본구조 작성하기
2. Pivot view 를 만들기 위해 필요한 데이터 가공하기
3. Pivot view 문법에 맞추어 수정하기

SELECT cuisine_type,
	   SUM(IF(age=10, cnt_order, 0)) "10대",
	   SUM(IF(age=20, cnt_order, 0)) "20대",
	   SUM(IF(age=30, cnt_order, 0)) "30대",
	   SUM(IF(age=40, cnt_order, 0)) "40대",
	   SUM(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 end age,
       count(1) cnt_order
FROM food_orders f inner join customers c on f.customer_id = c.customer_id
WHERE c.age between 10 and 59
GROUP BY 1, 2
) a
GROUP BY 1