[MySQL] WINDOW 함수

WINDOW 함수

참고: https://learn.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver16

OVER 절로 window 함수를 어떻게 적용할지 정의한다. (파티션, 정렬 순서, 범위… 등) > Row

윈도우 안에서 각 행의 값을 계산하고 하나의 결과를 반환 (그룹 별로 하나의 결과를 반환하는 집계 함수와 다르다)

• offset (옵션) : 값을 가져올 행의 위치. (default) 1
• PARTITION BY (옵션) : WINDOW 함수를 적용할 그룹을 나누는 기준
• ORDER BY : 파티션 내 정렬 기준

[WINDOW함수](적용할_컬럼[, offset])
OVER (   
       [ <PARTITION BY 파티션> ]  
       [ <ORDER BY clause> ]   
       [ <ROW or RANGE clause> ]  
      )

// PARTITION BY clause
PARTITION BY value_expression , ... [ n ]  
  
// ORDER BY clause  
ORDER BY order_by_expression  
    [ COLLATE collation_name ]   
    [ ASC | DESC ]   
    [ ,...n ]  
  
// ROW or RANGE clause
{ ROWS | RANGE } <window frame extent>

1. 그룹 내 순위 관련 함수
   • RANK : 파티션 내 현재 행의 순위 부여, 동일값인 경우 같은 순위 부여 후 동일값 수만큼 순위를 건너뛴다.
   • DENSE_RANK : 파티션 내 현재 행의 순위 부여, 동일값인 경우 같은 순위 부여하고 순위를 건너뛰지 않고 순차적으로 부여
   • ROW_NUMBER: 파티션 내 각 행에 1부터 하나씩 증가하는 번호 생성
2. 그룹 내 집계 관련 함수
   • SUM, MIN, MAX, AVG, COUNT
3. 그룹 내 행 순서 관련 함수
   • LAG/LEAD : 이전 행/다음 행 필드 읽기
   • FIRST_VALUE / LAST_VALUE: 파티션 내 첫 번째/현재까지 읽은 행 중에서 마지막 값 (항상 자기 자신)
   • NTH_VALUE: 파티션 내 N번째 행의 값, 없으면 NULL 반환
4. 그룹 내 비율 관련 함수
   • PERCENT_RANK: 파티션 내 현재 행의 백분율 순위, 0 ~ 1 사이 값
   • NTILE, CUME_DIST

(필요한 함수 찾아서 사용하기)

ex) 사용자의 국가 내 나이 순서

select 
  id,
  first_name,
  last_name,
  country,
  DENSE_RANK() OVER ( PARTITION BY country ORDER BY age ) AS dense_rank_number_in_country,
from `thelook_ecommerce.users`
where id between 1 and 20
order by country, age

ex) 판매 총액의 순위

select 
  user_id,
  sum(sale_price) as total,
  rank() over(ORDER BY sum(sale_price) DESC) as rnk,
  dense_rank() over(ORDER BY sum(sale_price) DESC) as dense_rnk
from `thelook_ecommerce.order_items`
group by user_id
order by rnk

윈도우 함수를 적용할 범위를 직접 지정할 수 있다.

• 범위 지정 옵션
  • ROWS: 각 행마다 계산
  • RANGE: 정렬된 값 중 같은 값이 있다면 한 번에 연산
  • UNBOUNDED PRECEDING: 파티션의 맨 첫 번째 ROW
  • UNBOUNDED FOLLOWING: 파티션의 맨 마지막 ROW
  • CURRENT ROW: 현재 ROW 값

ex)

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING : 현재 행을 기준으로 파티션의 시작 ROW부터 파티션 끝 ROW 까지를 범위로 지정

SELECT
  country_name,
  refresh_date, 
  FIRST_VALUE(confirmed) OVER (PARTITION BY country_name ORDER BY confirmed) as first,
  LAST_VALUE(confirmed) OVER (PARTITION BY country_name ORDER BY confirmed) as mid,
  LAST_VALUE(confirmed) OVER (PARTITION BY country_name ORDER BY confirmed ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as last
FROM `dataflix_covid_dataset.world_covid`
ORDER BY country_name

숫자를 사용해서 원하는 값으로 지정할 수도 있다.

SELECT
	SUM(sal) OVER (ORDER BY sal ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS "위아래 합계"
FROM empl