[SQL] 기초 5. 피봇 테이블, 윈도우 함수, 날짜 함수

유의사항: 해당 글은 MySQL 문법을 기반으로 작성된 글입니다. 다른 SQL과 다른 부분이 있을 수 있습니다.

 

개요

 

서브쿼리와 조인을 사용해서 좀 더 다양한 테이블 검색이 가능해졌다. 하지만 복잡한 조건에 맞는 테이블 검색을 하기에는 아직 부족해보인다. 여러 기준에 맞는 집계 데이터를 얻고 싶다면?  그룹별로 집계를 내고 싶다면 어떻게 해야할까? 이번에는 복잡한 조건에 맞는 테이블 검색에 도움이 되는 피봇 테이블, 윈도우 함수, 날짜 함수에 대해 알아보자.

---

피봇 테이블

피봇 테이블(Pivot table)은 2개 이상의 기준으로 데이터를 집계할 때 보기 쉽게 배열하여 보여주는 것을 의미한다.

피봇 테이블 기본 구조

 

피봇 테이블 예시

 

피봇 테이블 예시

 

• 식당 별, 시간별 주문건수 구하기(15시~21시 사이, 20~21시 기준 내림차순 포함)

select restaurant_name,
       max(if(hh='15', cnt_order, 0)) "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

 

---

윈도우 함수

윈도우 함수(Window Function)은 각 행의 관계를 정의하기 위한 함수로, 그룹 내의 연산을 쉽게 만들어주는 역할을 한다.

다음과 같은 작업이 필요할 때 사용한다. 

 

• 전체 식당 중, 한식 식당들을 주문건수 순으로 순위를 매기는 경우
• 한식 식당 전체 주문건수 중에서 A 식당이 차지하는 비율을 알고 싶은 경우
• 2건 이상 주문을 한 소비자들 중에서 1번째와 2번째로 주문한 식당을 조회하고 싶은 경우

윈도우 함수 없이 기본 SQL구조로 해결하기 위해서는 복잡한 서브 쿼리문을 사용하거나, 여러 번의 연산을 수행해줘야 하지만 윈도우 함수를 사용하면 보다 쉽게 해결할 수 있다.

 

기본구조

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

• window_function : 윈도우 함수 이름. ex) sum, avg
• argument : 함수를 사용하는데 필요한 입력 값.
• partition by : 그룹을 나누기 위한 기준입니다. group by 절과 유사하다고 생각해주시면 됩니다.
• order by : 윈도우 함수를 적용할 때 정렬 할 컬럼 기준을 적어줍니다.

윈도우 함수 예시

 

• 음식 타입별 주문건수 순으로 순위를 매기는 경우: rank()

select cuisine_type,
       restaurant_name,
       rank() over (partition by cuisine_type order by order_count desc) rn,
       order_count
from
(
select cuisine_type, restaurant_name, count(1) order_count
from food_orders
group by 1, 2
) a

 

 

 

• 음식 타입별 주문건수 총 합, 누적 합 구하기: sum()

select cuisine_type,
       restaurant_name,
       order_count,
       sum(order_count) over (partition by cuisine_type) sum_cuisine_type,
       sum(order_count) over (partition by cuisine_type order by order_count, restaurant_name) cumulative_sum
from
(
select cuisine_type, restaurant_name, count(1) order_count
from food_orders
group by 1, 2
) a

 

---

날짜 포맷 함수

 

날짜 데이터는 date 타입으로 따로 타입이 만들어져 있다. date 타입을 통해서 년, 월, 일 등 일부 값만 가져올 수 있다.

 

date()

값을 넣어주면  date 타입으로 변환(casting)한다.

넣어주는 값의 형식에 따라 변환되는 형식이 다르다. 예를 들어, 2024-04-12와 같이 yyyy-mm-dd형식의 값이라면 년 / 월 / 일이 각각 2024 / 04 / 12로 변환된다. 

date(date_value)

 

date_format()

넣어준 date 타입변수에서 지정한 값만 가져온다. 형식 지정자의 종류에 따라 가져오는 값이 다르다.

date_format(date 타입변수, '%형식 지정자')

 

날짜 예시

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

 

예시 결과