TIL(Today I Learned) 24.06.26

SQL로 Pivot table 만들기

 - pivot table  : 2개 이상의 기준으로 데이터를 집계할 때, 보기 쉽게 배열하여 보여주는 것을 의미

 

• pivot 테이블 예시
  • 집계기준 : 일자, 시간 

	1시	2시	3시	4시
10월 1일	5	3	5	2
10월 2일	7	10	1	8
10월 3일	3	3	9	4
10월 4일	9	16	70	1

 

1. 음식점 별, 시간별 주문 건수 집계하기

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

 

2. Pivot view 구조 만들기

 

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 - RANK, SUM

 - 기본 구조

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

• window_fuction : 기능명을 사용해준다. 
• argument: 함수에 따라 작성하거나 생략
• partition by  : 그룹을 나누기 위한 기준, group by 절과 유사
• order by  : window function 을 적용할 때 정렬할 컬럼 기준을 적어준다.

- Rank 함수 적용하기(특정 기준으로 순위를 매겨주는 기능)

함수	차이점
rank	공동 순위가 있으면 다음 순서로 건너 뜀
dense_rank	공동 순위가 있어도 다음 순위를 뛰어 넘지 않음
row_number	공동 순위를 무시함

 

만약, rank() 를 썼을 때는 값이 ,

d_date	revenue	rank_rev
2020-07-01	195800	1
2020-07-01	195800	1
2020-07-01	194100	3

 

dense_rank()를 썼을 때는,

 

d_date	revenue	rank_rev
2020-07-01	195800	1
2020-07-01	195800	1
2020-07-01	194100	2

 

row_number()를 썼을 때는 ,

 

d_date	revenue	rank_rev
2020-07-01	195800	1
2020-07-01	195800	2
2020-07-01	194100	3

 

-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 (window function으로  누적합이 필요하거나 카테고리별 합계 컬럼과 원본 컬럼을 함께 이용할 때 유용)

 

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

 

날짜 포맷과 조건(포맷함수) - 데이터에 날짜를 지정 or 조건에 날짜를 사용해야 할 때 활용

  - 문자타입, 숫자타입과 같이 날짜 데이터도 특정한 타입을 가지고 있다.

  - 년, 월, 일, 시, 분, 초 등의 값을 모두 갖고 있고, 목적에 따라 '월', '주', '일' 등으로 포맷변경 가능

 

1. yyyy-mm-dd 형식을 data type으로 변경할 때

select date(date) date_type,
       date
from payments

 

2. date_format 을 이용하여 년, 월,일 ,주 조회 

 

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_format 함수 사용시 formatting parameter에 원하는 규칙대로 입력

 

 

sql 복습하고 python 기초 문법 공부를 조금 더 하기로 했다.

 

https://justdata.tistory.com/4