SQL Server 2005의 OVER절(펌)

주제 : SQL Server 2005의 OVER절

작성일 : 2009.01.21

작성자 : 유일환(ryu1hwan@empal.com)

첨부파일 :

[1hwansqltech]sql_server_2005의_over절_테이블생성스.sql

 

 

 

 

OVER절은 여러 가지 집계 함수와 같이 사용이 가능합니다.

SQL Server 2005로 넘어오면서 ROW_NUMBER, RANK, DENSE_RANK라는 함수가 추가되었는데, 이 함수들은 OVER절을 필수로 사용해야만 합니다.

이 OVER절은 분석 SQL을 생성해 내는데 있어서 굉장히 중요한 역할을 하는 문장이라고 할 수 있습니다.

여러분들이 SQL Server 2005에서 이 OVER절을 사용할 줄 모른다면 분석리포트들을 위해 OVER절을 사용한 SQL보다
더 복잡하고 길이가 긴 SQL을 작성해야 할 것입니다.

SQL Server 사용자로서 슬픈 것은 이러한 OVER절 구문이 오라클은 8i부터 지원이 되었다는 것입니다.

더군다나 현재 오라클의 OVER절은 LAG, LEAD와 같은 부러운 기능도 가지고 있습니다.

물론 이러한 기능이 없어도 SQL Server의 T-SQL만으로도 구현이 가능합니다.

어찌되었든 SQL Server도 더 빨리 이러한 구문을 지원했다면,

지금까지 개발하면서 제가 작성한 SQL들을 좀 더 간단히 구현했을 텐데라는 약간은 아쉬운 생각이 듭니다..

그렇다면, 이 OVER절에 대해 하나씩 살펴보도록 하겠습니다.

 

* 다음부터는 예제들이 나오게 됩니다.
첨부된 스크립트([1HwanSQLTech]SQL Server 2005의 OVER절_테이블생성스크립트.sql)를 사용해서,
예제를 실행하기 위한 테이블과 데이터들을 생성해 주세요.

● ROW_NUMBER

말씀 드렸듯이 OVER절은 ROW_NUMBER, RANK, DENSE_RANK 이 3가지 함수와는
항상 함께 사용되어집니다.

ROW_NUMBER라는 함수이름에서 느낄 수 있듯이 SQL 결과에 줄 번호를 매겨주는 기능을 하는 함수입니다. ROW_NUMBER의 간단한 구문은 다음과 같습니다.

<SQL-01>

SELECT ROW_NUMBER() OVER(ORDER BY T1.SaleYMD) R_NO
 ,T1.*
FROM Sale T1

 

<SQL-01>을 수행하면, 다음과 같은 결과를 얻을 수 있습니다.

보시는 것처럼 결과의 ROW_NUMBER를 사용해서 SQL 결과에 줄 번호를 매긴 것을 확인 할 수 있습니다.

여기서 OVER(ORDER BY T1.SaleYMD)라고 명시했기 때문에, 줄 번호는 SaleYMD순으로 정렬이 되어서 줄 번호가 생성이 됩니다. 만약에 동일한 SaleYMD를 가지는 경우는 먼저 나오는 데이터부터 낮은 줄번호를 가지게 됩니다.

이번에는 줄 번호를 ShopID, ModelID순서로 설정해 보도록 하겠습니다.

<SQL-02>

SELECT ROW_NUMBER() OVER(ORDER BY T1.ShopID, T1.ModelID) R_NO
 ,T1.*
FROM Sale T1

이와 같이 여러 개의 컬럼을 OVER절 안의 ORDER BY에 설정할 수 있습니다.

정말 간단하지요?

여러분들이 이 ROW_NUMBER를 잘 사용한다면, 개발시 페이징 처리를 손쉽게 구현해 내실 수 있습니다.

이러한 페이징 처리는 다음에 따로 다루어 보도록 하겠습니다.

● RANK와 DENSE_RANK

RANK와 DENS_RANK역시 ROW_NUMBER처럼 OVER절이 항상 함께 사용되어집니다.

RANK와 DENS_RANK는 데이터의 순위를 매겨주는 함수입니다. ROW_NUMBER는 OVER절의 ORDER BY에 기술된 컬럼값이 같더라도 다른 줄 번호를 매겨주지만, RANK와 DENSE_RANK는 순위함수이므로, ORDER BY기술된 컬럼값이 동일한 경우는 같은 순위를 매겨주게 됩니다.

다음 한 문장을 통해서 우리는 ROW_NUMBER, RANK, DENSE_RANK의 차이점을 명확히 알 수 있습니다.

<SQL-03>

SELECT T1.SalesmanID
 ,T1.SalePrice
 ,ROW_NUMBER() OVER(ORDER BY T1.SalePrice DESC) R_NO
 ,RANK() OVER(ORDER BY T1.SalePrice DESC) RNK
 ,DENSE_RANK() OVER(ORDER BY T1.SalePrice DESC) DEN_RNK
FROM Sale T1
WHERE T1.ModelID = 'NB-A01'

 

위 SQL은 ModelID가 NB-A01인 데이터에 대해서만 조회를 하면서, 판매가격이 높은 순서대로 ROW_NUMBER와 RANK, DENS_RANK를 각각 구해보는 SQL입니다.

다음과 같은 결과를 얻을 수 있습니다.

설명을 하지 않아도 차이점을 간파하신 분들이 있을 것이라 생각되어 집니다.

ROW_NUMBER를 사용한 R_NO인 경우 아무리 같은 SalePrice를 가지고 있어도 다른 줄 번호가 매겨집니다.

하지만, RANK를 사용한 RNK컬럼의 경우는 동일한 SalePrice를 가진 데이터는 같은 순위를 주게 되므로 1등이 2명이 존재합니다. 그리고, 3등이 3명이 존재합니다.

중요한 것은 RANK의 경우는 2등이 존재하지 않는다는 것입니다. 1등이 2명이므로 그 다음은 2등이 아닌 3등이 됩니다. 마찬가지로 3등이 3명이므로 4, 5등은 없고 바로 6등이 나오게 됩니다.

하지만 가끔은, 이러한 등수가 일련 되도록 나오기를 원하는 경우가 있습니다. 1등이 2명이어도 그 다음 등수는 3등이 아닌 2등이 나와야 되는 경우입니다. 이 경우 사용하는 것이 바로 DENSE_RANK입니다.

즉, DENSE_RANK를 사용하게 되면, 1등부터 마지막 등수까지 빠짐없이 숫자가 이어집니다.

이러한 특성을 잘 이해하고 경우에 따라서 RANK, DENSE_RANK, ROW_NUMBER를 적절하게 사용하시기 바랍니다.

● PARTITION BY

이번에는 OVER절 안에 사용할 수 있는 PARTITION BY절에 대해 알아보도록 하겠습니다.

OVER절안에 ORDER BY를 사용하는 것을 봤었는데, ORDER BY와 더불어 PARTITION BY라는 문장도
사용할 수 있습니다.

PARTITION BY는 PARTITION BY에 서술된 컬럼의 값 별로 데이터를 분류하는 기능을 합니다.

만약에 RANK() OVER(PARTITION BY SaleYMD ORDER BY SalePrice DESC)라고 사용을 하게 되면, 결과 집합은 SaleYMD별로 순위를 처음부터 다시 매기게 됩니다.

예제 SQL을 통해 좀 더 정확하게 알아보도록 하겠습니다.

<SQL-04>

SELECT RANK() OVER(PARTITION BY T1.SaleYMD ORDER BY T1.SalePrice DESC) RNK
 ,*
FROM Sale T1

 

다음과 같은 결과를 얻을 수 있습니다.

 

결과 중에 RNK 컬럼을 보시면, SaleYMD가 20090103인 데이터에 대해 1부터 15까지 순위가 매겨진 것을 볼 수 있습니다. 그리고, SaleYMD가 20090104로 바뀌자 순위가 새롭게 1부터 다시 시작하는 것을 볼 수 있습니다.

PARTITION에는 하나의 컬럼이 아니라 여러 개의 컬럼도 사용 가능합니다.

직접 여러개 컬럼을 PARTITION BY로 구현을 해보시면서 연습을 해보시기 바랍니다.

● SUM, MAX, MIN 그리고 OVER

RANK, ROW_NUMBER, DENSE_RANK는 2005에서 새롭게 나온 함수로서 분석 리포트 SQL을 만들 때 유용한 기능을 제공합니다.

하지만, SUM, MAX, MIN등의 함수에 OVER절을 같이 사용할 수 있다는 것은 분석 리포트를 만들기 위해 더욱 더 강력한 SQL을 구현하도록 해줍니다.

Sale 테이블을 조회를 하는데, SalePrice와 함께, Sale테이블 내의 모든 판매의 SalePrice의 총 합계를 각 로우마다 같이 보여주어야 하는 분석 리포트가 있다면, 다음과 같이 서브쿼리를 사용한 SQL을 구현할 수 있을 것입니다

<SQL-05>

SELECT (SELECT SUM(A.SalePrice) FROM Sale A) TTLSalePrice
 ,T1.*
FROM Sale T1

 

결과는 다음과 같습니다.

이와 같은 분석은 각 Sale이 전체 Sale에서 차지하는 비중이 얼마나 되는지를 분석할 수 있도록 해줍니다. 즉, SaleSeq가 1번인 데이터는 전체 판매금액 71740000중에 1550000을 차지하고 있는 것입니다. 사용자들은 이와 같은 정보를 판매비중이라는 컬럼으로 추가해서 보기를 원할 것입니다.

기존, 위의 서브쿼리를 사용한다면, 다음과 같이 구현할 수 있습니다.

<SQL-06>

SELECT (SELECT SUM(A.SalePrice) FROM Sale A) TTLSalePrice
 ,CONVERT(numeric(18,2), T1.SalePrice / (SELECT SUM(A.SalePrice) FROM Sale A) * 100.00) SaleRate
 ,T1.*
FROM Sale T1

 

SaleRate를 추가한 결과는 다음과 같습니다.

 

즉, SaleSeq 1번인 데이터의 판매 비중은 2.16%입니다. SaleSeq 2번은 전체 판매중에 2.27%의 판매비중을 차지하고 있습니다.

결과는 사용자가 원하는 결과이지만, 우리가 작성한 SQL에는 서브쿼리가 포함되어 있어, SQL Server 내부적으로 Sale테이블을 3번이나 접근해야 되는 상황이 발생되어집니다.

우리는 서브쿼리를 피하기 위해 다음과 같이 OVER절을 사용할 수 있습니다.

<SQL-07>
SELECT SUM(SalePrice) OVER() TTLSalePrice
 ,T1.*
FROM Sale T1

 

 

<SQL-07>은 <SQL-05>와 동일한 결과를
출력하게 됩니다. 하지만, SQL은 좀 더 간단해 졌습니다.

SUM(SalePrice)를 통해 SalePrice를 집계를 하는데 뒤에 OVER()를 표시 했으므로 해당 로우의 SalePrice의 SUM이 아닌 조회되는 전체 데이터의 SalePrice의 SUM이 되어 집니다.

OVER절을 사용해서 <SQL-06>과 동일한 SQL을 만들어 보도록 하겠습니다.

<SQL-08>

SELECT SUM(SalePrice) OVER() TTLSalePrice
 ,CONVERT(numeric(18,2), T1.SalePrice / SUM(SalePrice) OVER() * 100.00) SaleRate
 ,T1.*
FROM Sale T1

 

아! 이 얼마나 아름답습니까? 더 이상 불필요하게 서브쿼리를 사용하지 않아도 된다는 이 행복감은 서브쿼리를 어쩔 수 없이 많이 사용해야만 했던 아픔을 아시는 분 들만이 아실 겁니다.

그럼 성능은 어떠할까요? 당연히 서브쿼리를 사용하지 않았으니 더 좋은 성능을 낼 것이라 생각이 드는군요, 한번 같이 확인해 보도록 하겠습니다.

 

서브쿼리를 사용한 <SQL-06>과 OVER절을 사용한 <SQL-08>의 실행계획을 살펴본 그림입니다.

69%로 나타난 첫 번째 것이 서브쿼리를 사용한 <SQL-06>의 실행계획입니다.

반면에 <SQL-08>은 31%라는 실행비용을 사용하고 있습니다. 이것만을 보고 생각했을 때는 OVER절을 사용한 SQL의 성능이 더 좋다고 할 수 있습니다.

하지만, 저는 페이지 I/O도 체크해 보았습니다. 다음과 같은 결과가 나왔습니다.

--<SQL-06> 테이블'Sale'. 검색수3, 논리적읽기수3, 물리적읽기수0, 미리읽기수0, LOB 논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기수0. --<SQL-08> 테이블'Worktable'. 검색수3, 논리적읽기수93, 물리적읽기수0, 미리읽기수0, LOB 논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기수0. 테이블'Sale'. 검색수1, 논리적읽기수1, 물리적읽기수0, 미리읽기수0, LOB 논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기수0.

보시면, 서브쿼리를 사용한 경우는 Sale테이블의 논리적 읽기수가 3입니다.(데이터 양이 매우 적으므로 테이블의 모든 데이터가 1페이지에 들어가 있습니다.)

반면에 <SQL-08>은 Sale테이블의 논리적 읽기수가 1입니다. 한 번만, Sale테이블에 접근을 했다는 것입니다. 하지만, 여기서 주의할 것은 <SQL-08>은 OVER절을 처리하기 위해 Worktable을 사용했다는 것입니다.

Worktable은 SQL Server에서 임시적으로 만드는 테이블로서 데이터의 정렬등을 위해 사용되어 집니다.

이와 같은 Worktable의 논리적 읽기수가 93인 것을 볼 수 있는데, 이것은 대용량 데이터가 조회될 경우 해당 방법을 사용하면, 급격한 성능저하를 가져올 수도 있음을 나타냅니다. (물론, 서브쿼리도 대용량 데이터에서는 급격한 성능저하를 가져오게 될 것입니다.)

결론은, 전체 비용으로는 OVER절을 사용한 SQL이 승리했지만, Worktable의 읽기수가 높은 점을 감안하면, 대용량 데이터의 조회해서는 OVER절을 사용하면 성능저하가 예상이 된다고 할 수 있습니다. 그러므로 대용량의 경우에서는 서브쿼리를 사용한 경우와 OVER절을 사용한 경우의 성능비교를 다시 한번 해봐야 합니다. 서버의 환경, 데이터의 분포도, 인덱스 설정, 구현하고자 하는 결과에 따라 완전히 다른 성능차이가 나오리라 예상됩니다.

경우에 따라 서브쿼리가 더 좋을 수도 있다는 것입니다. 즉, 단순히 OVER가 항상 좋다고 말씀드릴 수는 없으며, 그때 그 때 다른 성능이 나올 것입니다.

우리는 OVER절에 PARTITION을 사용할 수 있다는 것을 알고 있습니다. 그러므로 SUM() OVER(PARTITION BY col1, …)과 같은 문법을 사용할 수 있씁니다.

<SQL-08>은 OVER절을 사용해서 전체 판매금액을 구했는데, 이번에는 전체판매금액과 더불어서 각 일자별 판매금액도 같이 표현해보도록 하겠습니다.

<SQL-09>

SELECT SUM(SalePrice) OVER() TTLSalePrice
 ,SUM(SalePricE) OVER(PARTITION BY SaleYMD) DaySalePrice
 ,T1.*
FROM Sale T1

 

결과는 다음과 같습니다.

결과에서 첫 번째 컬럼은 전체 판매금액(TTLSalePrice)이고 두 번째 컬럼은 판매데이터의 해당 판매일자에 대한 판매금액(DaySalePrice)입니다. 이와 같이 PARTITION을 통해서, 각 일자별 판매금액을 구해서, 판매일자별 판매비중 역시 구할 수 있습니다.

OVER절을 사용할 때 주의할 점이 있는데, OVER절 앞에 오는 집계함수(SUM, MAX, MIN)에서 사용할 수 있는 컬럼은 SQL구문상 SELECT절에 명시 가능한 내용만 사용이 가능합니다. 좀 어려운 얘기인데, SELECT문장에 GROUP BY ShopID가 명시되어 있다면, “SUM(SalePriice) OVER()”와 같은 문장은 에러가 발생됩니다. 왜냐하면, GROUP BY ShopID가 명시되어 있다면, SalePrice는 집계함수를 사용하지 않는한 SELECT절에 사용할 수 없습니다. OVER절을 사용할 때는 SUM(SUM(SalePrice)) OVER()라고 명시를 해야 합니다.

다음 예제를 통해 설명해 보도록 하겠습니다.

<SQL-10>

SELECT T1.ShopID
 ,SUM(SalePrice) ShopSalePrice
 ,SUM(SalePrice) OVER() TTLSalePrice
FROM Sale T1
GROUP BY T1.ShopID

 

<SQL-10>을 실행하면 다음과 같은 에러메세지가 나옵니다.

메시지8120, 수준16, 상태1, 줄1 열'Sale.SalePrice'이(가) 집계함수나GROUP BY 절에없으므로SELECT 목록에서사용할수없습니다.

<SQL-10>에는 GROUP BY ShopID가 있으므로 SELECT절에 SUM(SalePrice)는 사용할 수 있지만, SalePrice를 단독으로 사용할 수는 없습니다. 즉, OVER절의 SUM()에는 SELECT절에 사용가능한 내용만이 올 수 있으므로 다음과 같이 SQL을 구현해야 합니다

<SQL-11>
SELECT T1.ShopID
 ,SUM(SalePrice) ShopSalePrice
 ,SUM(SUM(SalePrice)) OVER() TTLSalePrice
FROM Sale T1
GROUP BY T1.ShopID

감이 오시는 분들도 있고 감이 안 오시는 분들도 있으리라 생각됩니다. OVER절을 사용한 집계함수를 사용할 때는 집계함수 안에는 SELECT절에서 사용 가능한 것만 올 수 있다는 것을 꼭 기억해 주시기 바랍니다.

여기서는 SUM만을 보여드렸는데 MAX와, MIN, AVG도 OVER절과 함께 사용이 가능합니다. SUM과 마찬가지로 SELECT절에 올 수 있는 내용만이 OVER절의 집계함수에 안에 올 수 있습니다.

[출처] SQL Server 2005의 OVER절|작성자 일환