window function 1

이번 시간에는 DBMS의 window function이라는 것을 mysql 중심으로 살펴보자.

https://dev.mysql.com/doc/refman/9.0/en/window-function-descriptions.html

MySQL :: MySQL 9.0 Reference Manual :: 14.20.1 Window Function Descriptions

 

window 함수

 

정의

window 함수란 특정 데이터 집합 내에서 각 행에 대해 계산을 수행하는 함수를 말한다. window 함수는 특정 범위의 행을 기준으로 계산하는데 이 범위를 'window'라고 부른다. 창문 밖에는 광활한 뷰가 펼쳐져 있지만 "창을 통해서 보이는 영역만을 대상으로 계산한다"는 뜻으로 해석하면 좋겠다.

window 함수는 각행에 대해서 조회하지만 다른 행들의 집계 데이터를 참조할 수 있는게 묘미이다. (group by를 통해 집계 데이터를 사용할 때는 각 행에 대해 참조하기가 어렵다.) window 함수는 마치 java의 stream api 처럼 원본 데이터는 건드리지 않고 각 행에 대해 추가적인 계산 결과만을 제공한다.

window 함수를 이용하게 되면 판매 데이터에서 각 제품의 월별 매출액 순위를 계산하거나 주식 데이터에서 특정 기간의 거래량 평균을 구하는 등 다양한 분석에 활용될 수 있다.

window 함수의 종류

window 함수는 대부분의 집계함수(sum, count, min, max, avg, ...)와 일부 비 집계함수(nonaggregate function)으로 구성된다. 다음은 자주 사용되는 non aggregate window 함수들이다.

함수명	설명
CUME_DIST()	누적 분포 값
DENSE_RANK(), RANK(), PERCENT_RANK()	공동순위를 1,1,3으로 따로 표시하거나(DENSE_RANK), 동일하게 1,1,2로 처리(RANK) 또는 백분율로 표시
FIRST_VALUE(), LAST_VALUE(), NTH_VALUE()	윈도우 프레임의 첫 번째 행 또는 마지막 행, 또는 N번째 행에서 가져온 값
ROW_NUMBER()	파티션 내 현재 행의 번호(페이징이 되더라도 전체 건수에서의 번호 유지)
LAG(), LEAD()	파티션 내 현재 행을 기준으로 이전 행(지연)의 값 또는 다음 행(선도)의 값

 

기본 문법

window 함수는 over 절과 함께 사용되며 over 절 안에 partition by와 order by를 갖는다.

함수명([대상컬럼]) OVER(Partition by 컬럼명 Order by 컬럼명 [ASC | DESC])

# 함수명: 위에서 언급한 함수명
# OVER: window 함수의 필수 항목으로 어떤 결과를 만들 것인지에 따라 partition by, order by 추가
# Partition by: 전체 집합에 대해 어떤 기준에 따라 데이터를 나눌지에 대한 설정
# Order by: 어떤 항목을 기준으로 정렬할 것인지를 넘어서 frame 정의에 사용됨

 

사용예

다음 예는 mysql의 sakila database를 기반으로 작성한다.

영화들의 길이가 적절한지 분석하기 위해서 동일 등급의 다른 영화들의 평균 길이와 비교해보자.

보유하고 있는 영화의 길이가 적절한지 분석하기 위해서 위와 같은 내용을 파악하기로 했다.

위 내용을 파악하기 위해서 필요한 컬럼을 별 생각 없이 나열해보면 아마도 title, length, rating, avg(length)정도가 될 것이다. 그런데 여기서 avg는 집계함수이고 나머지 컬럼들은 일반 컬럼이다. 집계함수와 함께 사용된 일반 컬럼은 group by 절에 나와야 한다. 따라서 다음과 같은 쿼리가 만들어진다.

select title, rating, length, avg(length)
from film
group by title, length, rating
order by title
limit 10, 5;

이게 최선입니까?

 

하지만 이 코드는 의미가 없다. 어차피 title, length, rating이 group by 절로 묶여 버리면 avg의 결과는 (title, length, rating) 별로의 평균이기 때문에 그냥 한 건에 대한 평균이다. ㅠㅠ

사실 원하는 결과는 다음이다.

select title, rating, length, 
       (select avg(length) 
        from film  
        where rating=f.rating) avg
from film f
order by title
limit 10, 5;

복잡하지만 결과는 맞다.

즉 sub query를 이용해서 집계함수에 대한 결과를 따로 조회해서 붙여줘야 한다. 컬럼 하나야 별 문제 없겠지만 이런 식으로 붙여줘야 할 내용이 많다면 쿼리가 심각하게 복잡해질 것이다. 이때  window 함수를 이용하면 다음과 같이 표현될 수 있다.

select 
  row_number() over() as `행번호`, 
  title, rating, length, 
  avg(length) over(partition by rating) `평균길이`      
from film f order by title limit 10, 5;

훨씬 간단해짐!

ROW_NUMBER() over_clause
AVG(대상컬럼) over_clause

이처럼 group by는 행을 그룹으로 압축해서 하나의 행을 반환하는 반면 window 함수는 행을 그대로 둔체 집계 내용을 행별로 추가하기 때문에 일반 컬럼과 함께 집계함수의 내용을 사용할 수 있다.

window 함수는 select 목록이나 order by 절에서만 사용할 수 있다. 따라서 where나 group by 등에서는 사용할 수 없다. 쿼리의 결과 행은 from 절에서 where, group by, having 처리 후에 결정된다. 즉 데이터를 가져온 후 where, group by, having으로 filtering을 거쳐서 생성된 row에 대해서 window 함수가 처리된다. window의 실행 이후 order by, limit, select distinct 동작이 진행된다.

 

날짜별 렌탈 건수를 이전일, 다음일까지 하나의 row에서 확인하자.

일별 렌탈 건수의 추세를 확인하고 싶다. 특정일의 렌탈 건수 뿐 아니라 이전날, 다음날까지 하나의 row에 출력해서 추세를 쉽게 비교해보자.

# 파티션 내에서 현재 행보다 앞/뒤에 있는 N행의 값 조회, N의 기본은 1
LAG(expr [, N[, default]]) [null_treatment] over_clause   
LEAD(expr [, N[, default]]) [null_treatment] over_clause

select  date_format(rental_date, '%Y/%m/%d(%a)') as "날짜", 
        LAG(count(rental_id)) over() as '이전날', 
        count(rental_id) as '당일렌탈건', 
        lead(count(rental_id)) over() as '다음날'
from rental
group by date_format(rental_date, '%Y/%m/%d(%a)');

토요일은 전일 대비 렌탈이 늘고 일요일에 줄었구나.