[2024.07.30 TIL] sql 윈도우 함수

Window Function

각 행의 관계를 정의하기 위한 함수. 그룹 내의 연산을 쉽게 만든다.
(subquery, 연산 반복 등 복잡한 기본 sql 구조를 편리하게 이용가능)

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

window_function - 기능 명 ex) sum, avg
over() - window_function 사용 할 때 필수로 사용
argument - 함수에 따라 생략 (함수 or 메소드의 입력값(value) (전달인자))
partition by - 그룹을 나누는 기준 (group by와 유사)
order by - 함수를 적용 때 정렬한 컬럼 기준

• RANK
  특정 기준으로 순위를 매기는 함수

  rank() over (partition by 그룹 기준 컬럼 order by 정렬 기준 desc 내림차순)

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

  select cuisine_type,
  		restaurant_name,
  		rank() over(partition by cuisine_type order by cnt_order desc) rnk,
  		cnt_order
  from
  (
  SELECT cuisine_type,
  		restaurant_name,
  		count(1) cnt_order
  FROM food_orders
  group by 1, 2
  ) a

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

  select cuisine_type,
  		restaurant_name,
  		rnk,
  		cnt_order
  from
  (
  select cuisine_type,
  		restaurant_name,
  		rank() over(partition by cuisine_type order by cnt_order desc) rnk,
  		cnt_order
  from
  (
  SELECT cuisine_type,
  		restaurant_name,
  		count(1) cnt_order
  FROM food_orders
  group by 1, 2
  ) a
  ) b
  where rnk < 4
  order by 1, 3

  

• sum
  전체에서 차지하는 비율, 누적합을 구할 때
  sum() 함수와 동일
  누적합이 필요하거나 카테고리별 합계 컬럼, 원본 컬럼을 함께 이용할 때 유용

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

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

  

날짜 포맷과 조건 (포맷 함수(형식))

문자, 숫자같이 날짜도 날짜 데이터 타입을 가짐
date_format

yyyy-mm-dd
년 : Y (4자리), y(2자리)
월 : M, m
일 : d, e
요일 : w (일 ~ 토 = 0 ~ 6)

• 년도별 3월의 주문건수 구하기

  SELECT date_format(date(date),'%Y') y,
  		date_format(date(date),'%m') m,
  		COUNT(1) cnt_order 
  FROM payments p join food_orders fo on p.order_id = fo.order_id
  where date_format(date(date),'%m') = '03'
  group by 1, 2
  order by 1

  

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

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 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 fo join customers c on fo.customer_id = c.customer_id
where age between 10 and 59
group by 1, 2
) a
group by 1
order by 1