(─━┘_└━─)/ 머리만1톤(Head1Ton)

보시는 것처럼 결과의

여기서 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

결과 중에

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

결과는 다음과 같습니다.

이와 같은 분석은 각

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

<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를 추가한 결과는 다음과 같습니다.

즉

결과는 사용자가 원하는 결과이지만, 우리가 작성한 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

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

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

서브쿼리를 사용한

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

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

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

보시면, 서브쿼리를 사용한 경우는 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절을 사용해서 전체 판매금액을 구했는데, 이번에는 전체판매금액과 더불어서 각 일자별 판매금액도 같이 표현해보도록 하겠습니다.

결과는 다음과 같습니다.