window function(순위, 순서, 분산)
1. Window Function?
1) 개요
(1) 행과 행간의 관계를 쉽게 정의하기 위해 제공되는 함수
(2) 분석함수 또는 순위함수로도 불림
(3) 기존의 집계함수 또한 window function으로 통합됨
(4) 서브쿼리 사용 가능함
(5) 기본 문법
2) 종류
(1) 순위 함수
A. Rank : 각 행의 순위를 반환
SELECT RANK () OVER(
PARTITION BY SalesOrderID
ORDER BY UnitPrice DESC
) AS Ranking
, SalesOrderID
, SalesOrderDetailID
, ProductID
, UnitPrice
FROM AdventureWorks2019.Sales.SalesOrderDetail
SalesOrderID로 그룹 지어 순위를 책정할 것이고 순위는 UnitPrice 역순으로 순위를 반환하는 예제
다음과 같이 SalesOrderID이 달라질 때 마다 새로운 순위가 반환되는 것을 볼 수 있으며 UnitPrice이 높을수록 낮은 순위를 반환하는 것을 볼 수 있다.
또한, 값이 같으면 같은 순위가 반환되고 그 다음 순위는 같은 순위의 개수 이후의 값을 반환하는 것을 확인할 수 있다.
B. DENSE_RANK : 두 개 이상의 행이 동일한 순위 값을 갖는 경우 동일한 순위를 반환
SELECT DENSE_RANK () OVER(
PARTITION BY SalesOrderID
ORDER BY UnitPrice DESC
) AS Ranking
, SalesOrderID
, SalesOrderDetailID
, ProductID
, UnitPrice
FROM AdventureWorks2019.Sales.SalesOrderDetail
SalesOrderID로 그룹 지어 순위를 책정할 것이고 순위는 UnitPrice 역순으로 순위를 반환하는 예제
RANK 함수와 같이 SalesOrderID이 달라질 때 마다 새로운 순위가 반환되는 것을 볼 수 있으며 UnitPrice이 높을수록 낮은 순위를 반환하는 것을 볼 수 있다.
하지만 RANK 함수와 다르게 다음 순위가 상위 순위의 개수 이후의 값이 아닌 상위 순위의 바로 다음 순위로 반환되는 것을 확인할 수 있다.
C. ROW_NUMBER : 결과 집합의 출력 변호를 지정 반환
SELECT ROW_NUMBER () OVER(
PARTITION BY SalesOrderID
ORDER BY UnitPrice DESC
) AS RowNum
, SalesOrderID
, SalesOrderDetailID
, ProductID
, UnitPrice
FROM AdventureWorks2019.Sales.SalesOrderDetail
SalesOrderID로 그룹 지어 순서를 책정할 것이고 순위는 UnitPrice 역순으로 순서를 반환하는 예제
SalesOrderID이 달라질 때 마다 순서가 반환되는 것을 볼 수 있으며 UnitPrice이 높을수록 낮은 순서 반환하는 것을 볼 수 있다.
RANK함수와 달리 번호가 순차적으로 반환되는 것을 볼 수 있는데 ORDER BY 구문과 같이 값이 같을 경우 먼저 입력된 순서로 반환되는 것을 확인할 수 있다.
D. NTILE : 지정된 숫자만큼의 그룹으로 분산
SELECT NTILE (3) OVER(
PARTITION BY SalesOrderID
ORDER BY UnitPrice DESC
) AS NT
, SalesOrderID
, SalesOrderDetailID
, ProductID
, UnitPrice
FROM AdventureWorks2019.Sales.SalesOrderDetail
SalesOrderID로 그룹 지어 순서를 책정할 것이고 순위는 UnitPrice 역순으로 정렬한 뒤 함수의 파라미터(3)로 정의된 수만큼으로 나누는 예제
위 그림과 같이 총 개수를 센 후 나누게 되는데 나누었을 때 모자란 분량은 마지막 분산 숫자에 반영한다.
위 예제 같은 경우 SalesOrderID=43664 일 때 NT 가 1과 2는 3행, 3은 2행인 것을 확인할 수 있다.
2. 출처