WINDOW FUCTION (MySQL)

SQL에는 Window function이 존재한다.

 

Window Fuction

• 행과 행 사이의 관계를 표현해준다.
• 테이블의 특정 조건의 집계 , 순위 등을 쉽게 표현해준다.

 

1. 기본 문법

Select 윈도우함수 over (partition by 칼럼 order by 칼럼 윈도잉절) from 테이블명

• Partition by 그룹핑 기준
• Order by 순위 지정 기준
• 윈도잉절 : 함수의 대상이되는 행 범위 지점
•  Unbounded preceding  - 이전행
• Unbounded following-  다음행

 

1-(1) 집계함수

AVG ,COUNT,SUM,MAX, MIN 등 가능합니다.

 

SUM 예시를 한번 들어보도록 하겠습니다.

테이블은 Mysql 기본 데이터인 sakila 중 payment를 사용하도록 하겠습니다.

 

- sakila 데이터베이스에서 payment 테이블을 모습입니다.

 

- 이제 윈도우함수를 사용보도록 하겠습니다. 제가 원하는 결과는 customer_id 기준으로 amount값을 현재행과 바로 위에 행과 아래 행의 합계를 구하는 것 입니다.

select customer_id ,sum(amount) over(          ### 윈도우 함수 
partition by customer_id order by customer_id    # 어떤 칼럼을 기준으로 윈도우함수를 받을건지 지정
rows between 1 unbounded preceding and 1 unbounded following) from payment ;  ## 범위 지정

• select customer_id ,sum(amount) over(    -> amount값을 합산하겠다는 구문
• partition by customer_id order by customer_id   -> 기준은 custormer_id 이며, 정렬역시 customer_id 기준이다.
• rows between 1 preceding and 1 following) from payment   -> 현재행 기준으로 1개의 이전행과 1개의 다음행값으로 

이렇게 결과가 잘나오게 된다. 첫행을 기준으로 생각해보자면 첫행은 이전행이 없기때문에 자기값과 다음 한개의 값을 더하는 것이다.  즉 2.99 + 0.99 를 더하여 3.98이 나오게된다.

 

 

1 -(2) 순위함수

• Rank - 순위를 매기는데 동일 값이 있으면 동일 순위를 부여한다. 즉 1 2 2 4 5 이 순으로 반영된다.
• Dense_Rank - 동일 값이 있으면 동일 순위를 부여하지만 중복순위를 무시한다. 즉 1 2 2 3 4 5 이 순으로 반영된다.
• Row_number - 중복값이 존재 하지 않고 숫자를 부여한다.(순위 x)

select amount , rank() over(partition by customer_id order by amount) as rank2, 

dense_rank() over(partition by customer_id order by amount) as dense_rank2 from payment ;

rank와 dense rank을 확인하기 위해 두가지 다 해보았다. 이를 통해 customer_id 별로 등수매겨진다.

 

 

1 -(3) 행순서 함수

• first_value , last_value : 첫 값 , 끝값
• LAG , LEAD : 이전행 , 이후행

1 -(4) 비율

• percent_rank() : 백분율 순서
• cume_dist() : 현재 행 이하 값을 포함한 누적 백분율
• Ntile(a) : 전체 데이터 a 등분
• ratio_to_report : 총 합계에 대한 백분율