일환달콤한sql_20080516_복잡한순위리포트의해.doc작성일 :
작성자 : 유일환
제 목 : 복잡한 순위 리포트의 해결
(첨부파일로 보시면 좀 더 깨끗하게 보실 수 있습니다.)
안녕하세요~. SQL을 너무나 많이 좋아하지만, 경력이 쌓여갈수록 점점 싫어하고 있는
하지만, 우리 가족의 유일한 생계수단이기에.. 오늘도 이렇게 SQL에 대해 연구하고 정리해서 글을 올리게 됩니다.
오늘은 제가 실제 일을 하면서 만들게 된 어이없는 리포트 SQL에 대해 정리해볼까 합니다.
<시나리오>
전자제품을 판매하는 판매점에서 직원별 월별 판매현황을 분석하고자 합니다.
분석하려는 형태는 다음과 같습니다.
전체 판매(TTL)가 좋은 1위부터 3위까지의 영업사원ID를 제일 위에 보여주고,
그 다음에는 전체 판매에 대한 순위 및 판매금액을 보여줍니다.
그 다음 마지막으로 제품별 순위 및 판매금액을 출력합니다.
이와 같은 시나리오에 대해 SQL을 작성하는 방법을 연구해 보도록 하겠습니다.
위의 시나리오의 결과를 보시면, John이 전체 판매금액이 784000달러로 1위지만, DVD제품과 TV제품에 대해서는 판매가 저조하여 4위인 것을 볼 수 있습니다. 즉, 사용자는 전체순위와 제품별 순위가 같지 않음에 대해서도 분석하고자 하는 것 같습니다.
위와 같은 결과를 만들어 내는 것은 테이블의 구조에 따라 SQL이 복잡해 질 수도 있고 간단해 질 수도 있습니다. 여기서는 다음과 같은 구조의 테이블이라고 가정을 하고 진행을 하도록 하겠습니다.
<테이블 구조>
매우 간단한 구조입니다. 여기서는 이와 같은 간단한 구조를 사용하도록 하겠습니다.
테이블을 생성하는 SQL은 다음과 같습니다.
<SQL-1>
|
CREATE TABLE dbo.EmployeeMonthSales ( SaleYM nchar(6) COLLATE Korean_Wansung_CI_AS NOT NULL, EmployeeID nvarchar(30) COLLATE Korean_Wansung_CI_AS NOT NULL, SaleProductID nvarchar(30) COLLATE Korean_Wansung_CI_AS NOT NULL, SaleQty int NULL, SaleAmt numeric(18,2) NULL ) go ALTER TABLE EmployeeMonthSales ADD CONSTRAINT PK_EmployeeMonthSales PRIMARY KEY NONCLUSTERED (SaleYM ASC, EmployeeID ASC, SaleProductID ASC) go |
위의 테이블에 들어갈 데이터는 다음과 같습니다.
|
SaleYM |
EmployeeID |
SaleProduct |
SaleQty |
SaleAmt |
|
200804 |
|
TV |
20 |
21000 |
|
200804 |
|
Notebook |
15 |
30500 |
|
200804 |
|
DVD |
10 |
4000 |
|
200804 |
Peter |
TV |
10 |
11000 |
|
200804 |
Peter |
Notebook |
8 |
16500 |
|
200804 |
Peter |
DVD |
10 |
4000 |
|
200804 |
David |
TV |
15 |
20000 |
|
200804 |
David |
Notebook |
20 |
41000 |
|
200804 |
David |
DVD |
15 |
5500 |
|
200804 |
John |
TV |
15 |
18000 |
|
200804 |
John |
Notebook |
30 |
58000 |
|
200804 |
John |
DVD |
10 |
3400 |
|
200804 |
Larry |
TV |
20 |
21000 |
|
200804 |
Larry |
Notebook |
15 |
26000 |
|
200804 |
Larry |
DVD |
3 |
900 |
|
200804 |
Mark |
TV |
4 |
4100 |
|
200804 |
Mark |
Notebook |
10 |
19000 |
|
200804 |
Mark |
DVD |
3 |
1000 |
<SQL-2>를 사용해 테이블에 데이터를 입력하도록 합니다.
<SQL-2>
|
INSERT INTO EmployeeMonthSales ( SaleYM ,EmployeeID ,SaleProductID ,SaleQty ,SaleAmt ) SELECT '200804', ' SELECT '200804', ' SELECT '200804', ' SELECT '200804', 'Peter', 'TV', 10, 11000 SELECT '200804', 'Peter', 'Notebook', 8, 16500 SELECT '200804', 'Peter', 'DVD', 10, 4000 SELECT '200804', 'David', 'TV', 15, 20000 SELECT '200804', 'David', 'Notebook', 20, 41000 SELECT '200804', 'David', 'DVD', 15, 5500 SELECT '200804', 'John', 'TV', 15, 18000 SELECT '200804', 'John', 'Notebook', 30, 58000 SELECT '200804', 'John', 'DVD', 10, 3400 SELECT '200804', 'Larry', 'TV', 20, 21000 SELECT '200804', 'Larry', 'Notebook', 15, 26000 SELECT '200804', 'Larry', 'DVD', 3, 900 SELECT '200804', 'Mark', 'TV', 4, 4100 SELECT '200804', 'Mark', 'Notebook', 10, 19000 SELECT '200804', 'Mark', 'DVD', 3, 1000 |
이제 SQL을 작성하기 위한 준비사항은 모두 갖추어졌습니다.
제 설명을 보기 전에 여러분들이 직접 문제에 도전해 보시기를 당부 드리고 싶습니다.
제가 일하는 곳에 개발자들이 10명 정도 있는데, 실제로 이 문제를 푼 분은 딱 한 분밖에 없었습니다.
여러분들도 그런 한 분이 될 수 있도록 열심히 생각하셔서 문제를 풀어보시기 바랍니다.
최대한 오래 고민, 고민하셔서 문제를 풀어보신다면, 자신의 SQL능력이 크게 향상되는 것을 느끼실 수 있을 것입니다.
이 SQL을 여러 문장으로 해서 작성해도 되겠지만, 여기서는 한 문장으로 작성을 하도록 하겠습니다.
(실제로는 UNION ALL과 SELF JOIN등이 계속 사용되므로, 한 문장이라 해도 동일한 테이블의 접근은 계속 발생됩니다.)
여러분들도 한 문장으로 SQL을 작성하는 것을 기준으로 풀어보시기 바랍니다.
혹시 프라모델 조립을 해보신 적이 있는지요? 예를 들어 건담 프라모델을 조립해 보면, 가장 효율적인 방법은 두 팔을 조립하고, 두 다리를 조립하고, 머리를 조립하고 그 다음 조립된 팔과 다리 머리를 몸통에 연결시키는 방법이 가장효율적인 방법이 될 수 있습니다. (물론, 조립 설명서대로 하는 것이 쵝오!)
SQL도 이와 마찬가지입니다. 각 부분별로 조립을 하고 조립된 부분을 최종적으로 연결시키는 것입니다. 이와 같은 방법으로 하면, 아무리 어려운 SQL도 착착 만들어 내실 수 있습니다.
그럼 제일 먼저 <결과-1>과 같은 부분을 만들어 내는 SQL을 작성해 보도록 하겠습니다.
<결과-1>
<결과-1>과 같은 결과를 얻기 위해서는 영업사원 별 전체 판매금액을 구해서, 판매순위를 만들어 내어야 합니다.
SQL-2000이라면, 순위를 구하기 위해 서브쿼리나 셀프조인등의 귀찮은 방법을 사용했겠지만, SQL-2005에서는 RANK라는 멋진 함수를 제공하므로, RANK 함수를 사용하도록 하겠습니다.
<SQL-3>
|
SELECT T1.EmployeeID ,SUM(SaleAmt) SaleAmt ,RANK() OVER(ORDER BY SUM(SaleAmt) DESC) TTLSaleRank FROM EmployeeMonthSales T1 GROUP BY T1.EmployeeID |
<SQL-3>을 수행하면, <결과-2>와 같이 각 영업사원별 판매순위를 얻어 낼 수 있습니다.
<결과-2>
<SQL-3>을 이용해서 <결과-1>과 같이 1위부터 3위까지만, 한 로우에 나오도록 변형을 하도록 합니다.
이와 같이 밑으로 떨어져 있는 데이터를 옆으로 끌어올리기 위해서는 CASE WHEN을 사용한다는 것은 공식처럼 외우시면 됩니다.
<SQL-4>
|
SELECT 'EmployeeID' SaleProductID ,MAX(CASE WHEN T1.TTLSaleRank = 1 THEN T1.EmployeeID END) Rank1Sales ,MAX(CASE WHEN T1.TTLSaleRank = 2 THEN T1.EmployeeID END) Rank2Sales ,MAX(CASE WHEN T1.TTLSaleRank = 3 THEN T1.EmployeeID END) Rank3Sales FROM ( SELECT T1.EmployeeID ,SUM(SaleAmt) SaleAmt ,RANK() OVER(ORDER BY SUM(SaleAmt) DESC) TTLSaleRank FROM EmployeeMonthSales T1 GROUP BY T1.EmployeeID ) T1 |
<SQL-4>를 통해 <결과-1>과 동일한 결과를 얻으실 수 있습니다.
SQL-2005에서는 이와 같이 밑으로 떨어진 데이터를 옆으로 끌어 올리기 위해서 CASE문 뿐만 아니라, PIVOT이란 문법을 사용할 수 있습니다. 2005에서 소개된 새로운 기능으로서 CASE문을 대체해서 간략하게 사용할 수 있습니다. <SQL-5>는 PIVOT을 사용해 <결과-1>을 만드는 과정입니다.
<SQL-5>
|
SELECT 'EmployeeID' SaleProductID ,MAX([1]) Rank1Sales ,MAX([2]) Rank2Sales ,MAX([3]) Rank3Sales FROM (SELECT T1.EmployeeID ,SUM(SaleAmt) SaleAmt ,RANK() OVER(ORDER BY SUM(SaleAmt) DESC) TTLSaleRank FROM EmployeeMonthSales T1 GROUP BY T1.EmployeeID ) T1 PIVOT ( MAX(T1.EmployeeID) FOR T1.TTLSaleRank IN ( [1] ,[2] ,[3] ) ) as PVT |
<SQL-5>에서는 PIVOT이라는 문법을 사용해서 CASE WHEN을 사용하지 않고 <결과-1>을 만들어 냈습니다.
<결과-1>을 만들기 위해 2가지 방법을 살펴보았습니다. 그럼 다른 방법은 없을까요? 물론 있습니다. 그것도 아주 다양한 방법이 있을 것입니다. 그런 방법 모두 수행해 보는 것이 SQL을 공부하는데 매우 많은 도움이 됩니다. 그러므로 여러분들이 시간이 있으시다면, 더욱더 다양하고 기발한 방법을 연구해서 <결과-1>을 만들어 보시는 것이 큰 도움이 될 것입니다.
자, 그러면 이번에는 <결과-3>을 보도록 하겠습니다.
<결과-3>
<결과-3>은 전체판매(TTL)에 대해 1순위부터 3순위까지의 판매금액과 판매순위를 보여주고 있습니다.
이 결과를 얻기 위해서는 위에서 사용했던, <SQL-3>을 다시 사용해야 합니다. <SQL-3>을 통해 영업사원별, 판매 금액별, 판매순위를 얻어낸 다음에, <SQL-4>나 <SQL-5>를 응용해서, 1위부터 3위까지를 보여줄 때, 영업사원ID가 아닌, 판매금액과 순위를 보여주도록 합니다.
<결과-3>을 얻는 방법 역시 여러 가지가 있는데, 먼저, UNION ALL을 사용해서 SaleAmt와 Rank를 따로 구해서 합치는 방법을 살펴보도록 하겠습니다.
<SQL-6>
|
SELECT 'TTL' SaleProductID ,'SaleAmt' DataTP ,MAX(CASE WHEN T1.TTLSaleRank = 1 THEN T1.SaleAmt END) Rank1Sales ,MAX(CASE WHEN T1.TTLSaleRank = 2 THEN T1.SaleAmt END) Rank2Sales ,MAX(CASE WHEN T1.TTLSaleRank = 3 THEN T1.SaleAmt END) Rank3Sales FROM ( SELECT T1.EmployeeID ,SUM(SaleAmt) SaleAmt ,RANK() OVER(ORDER BY SUM(SaleAmt) DESC) TTLSaleRank FROM EmployeeMonthSales T1 GROUP BY T1.EmployeeID ) T1 UNION ALL SELECT 'TTL' SaleProductID ,'SaleAmt' DataTP ,MAX(CASE WHEN T1.TTLSaleRank = 1 THEN T1.TTLSaleRank END) Rank1Sales ,MAX(CASE WHEN T1.TTLSaleRank = 2 THEN T1.TTLSaleRank END) Rank2Sales ,MAX(CASE WHEN T1.TTLSaleRank = 3 THEN T1.TTLSaleRank END) Rank3Sales FROM ( SELECT T1.EmployeeID ,SUM(SaleAmt) SaleAmt ,RANK() OVER(ORDER BY SUM(SaleAmt) DESC) TTLSaleRank FROM EmployeeMonthSales T1 GROUP BY T1.EmployeeID ) T1 |
이전의 <SQL-4>를 완벽히 이해하셨다면, <SQL-6>을 쉽게 이해하실 수 있을 것입니다. 완전히 같은 유형이니까요. <SQL-6>은 각 영업사원별 순위와 각 영업사원별 판매금액을 각각 구해서, 두 개의 집합을 하나의 집합으로 UNION ALL을 한 것입니다.
이와 같은 방법의 단점은 EmployeeMonthSales테이블에 두 번 접근을 해야 한다는 단점이 있습니다.
EmployeeMonthSales테이블에 많은 데이터가 있다면, 많은 비효율이 발생하게 되는 것입니다.
그러므로 UNION ALL을 제거하기 위한 다른 방법을 없을지 살펴보도록 하겠습니다.
우리는 <결과-4>와 같은 데이터를 EmployeeMonthSales테이블에 한 번만 접근해서 만들어 낼 수 있습니다.
<결과-4>
<결과-4>와 같이 한 줄에 판매금액과 판매순위가 모두 나오게 된다면, 하나의 로우를 2줄의 로우로 분리해서 한 줄에는 판매금액을 한 줄에는 판매순위를 보여줌으로써 EmployeeMonthSales에 두 번 접근하는 것을 줄일 수 있습니다.
<SQL-7>
|
SELECT CONVERT(nvarchar, MAX(CASE WHEN T1.TTLSaleRank = 1 THEN T1.SaleAmt END)) SaleAmt1 ,CONVERT(nvarchar, MAX(CASE WHEN T1.TTLSaleRank = 2 THEN T1.SaleAmt END)) SaleAmt2 ,CONVERT(nvarchar, MAX(CASE WHEN T1.TTLSaleRank = 3 THEN T1.SaleAmt END)) SaleAmt3 ,CONVERT(nvarchar, MAX(CASE WHEN T1.TTLSaleRank = 1 THEN T1.TTLSaleRank END)) SaleRank1 ,CONVERT(nvarchar, MAX(CASE WHEN T1.TTLSaleRank = 2 THEN T1.TTLSaleRank END)) SaleRank2 ,CONVERT(nvarchar, MAX(CASE WHEN T1.TTLSaleRank = 3 THEN T1.TTLSaleRank END)) SaleRank3 FROM ( ` SELECT T1.EmployeeID ,SUM(SaleAmt) SaleAmt ,RANK() OVER(ORDER BY SUM(SaleAmt) DESC) TTLSaleRank FROM EmployeeMonthSales T1 GROUP BY T1.EmployeeID ) T1 |
<SQL-7>처럼 6개의 CASE문을 사용해서, <결과-4>를 만들어 냅니다.
그러면 <결과-4>를 <결과-3>과 같이 판매금액과 판매순위로 나누기 위해서는 어떻게 해야 할까요?
바로 CROSS JOIN이라는 것을 사용합니다.
CROSS JOIN은 아무 조건 없이 두 개의 집합을 결합하는 것으로 CROSS JOIN을 잘못 사용하게 되면, 아주 많은 성능의 부하를 주게 됩니다. 그러므로 CROSS JOIN을 사용할 때는 성능에 지장을 주지 않을지 주의가 필요합니다.
<SQL-8>
|
SELECT 'TTL' SaleProductID ,T2.DataTP ,CASE WHEN T2.DataTP = 'SaleAmt' THEN T1.SaleAmt1 ELSE T1.SaleRank1 END Rank1Sales ,CASE WHEN T2.DataTP = 'SaleAmt' THEN T1.SaleAmt2 ELSE T1.SaleRank2 END Rank2Sales ,CASE WHEN T2.DataTP = 'SaleAmt' THEN T1.SaleAmt3 ELSE T1.SaleRank3 END Rank3Sales FROM ( SELECT CONVERT(nvarchar, MAX(CASE WHEN T1.TTLSaleRank = 1 THEN T1.SaleAmt END)) SaleAmt1 ,CONVERT(nvarchar, MAX(CASE WHEN T1.TTLSaleRank = 2 THEN T1.SaleAmt END)) SaleAmt2 ,CONVERT(nvarchar, MAX(CASE WHEN T1.TTLSaleRank = 3 THEN T1.SaleAmt END)) SaleAmt3 ,CONVERT(nvarchar, MAX(CASE WHEN T1.TTLSaleRank = 1 THEN T1.TTLSaleRank END)) SaleRank1 ,CONVERT(nvarchar, MAX(CASE WHEN T1.TTLSaleRank = 2 THEN T1.TTLSaleRank END)) SaleRank2 ,CONVERT(nvarchar, MAX(CASE WHEN T1.TTLSaleRank = 3 THEN T1.TTLSaleRank END)) SaleRank3 FROM ( SELECT T1.EmployeeID ,SUM(SaleAmt) SaleAmt ,RANK() OVER(ORDER BY SUM(SaleAmt) DESC) TTLSaleRank FROM EmployeeMonthSales T1 GROUP BY T1.EmployeeID ) T1 ) T1 CROSS JOIN ( SELECT 'SaleAmt' DataTP SELECT 'Rank' DataTP ) T2 |
<SQL-8>을 사용해서 하나의 로우를 2개의 로우로 분리한 것을 볼 수 있습니다.
여기서 CROSS JOIN을 사용하면 <결과-3>과 <결과-1>이 결합된 다음과 같은 결과도 얻어 낼 수 있습니다.
즉, EmployeeMonthSales의 접근을 한 번 덜 줄이는 것입니다.
<결과-5>
<SQL-9>
|
SELECT T2.DataTP ,CASE WHEN T2.DataTP = 'SaleAmt' THEN T1.SaleAmt1 WHEN T2.DataTP = 'Employee' THEN T1.Employee1 ELSE T1.SaleRank1 END Rank1 ,CASE WHEN T2.DataTP = 'SaleAmt' THEN T1.SaleAmt2 WHEN T2.DataTP = 'Employee' THEN T1.Employee2 ELSE T1.SaleRank2 END Rank2 ,CASE WHEN T2.DataTP = 'SaleAmt' THEN T1.SaleAmt3 WHEN T2.DataTP = 'Employee' THEN T1.Employee3 ELSE T1.SaleRank3 END Rank3 FROM ( SELECT CONVERT(nvarchar, MAX(CASE WHEN T1.TTLSaleRank = 1 THEN T1.SaleAmt END)) SaleAmt1 ,CONVERT(nvarchar, MAX(CASE WHEN T1.TTLSaleRank = 2 THEN T1.SaleAmt END)) SaleAmt2 ,CONVERT(nvarchar, MAX(CASE WHEN T1.TTLSaleRank = 3 THEN T1.SaleAmt END)) SaleAmt3 ,CONVERT(nvarchar, MAX(CASE WHEN T1.TTLSaleRank = 1 THEN T1.TTLSaleRank END)) SaleRank1 ,CONVERT(nvarchar, MAX(CASE WHEN T1.TTLSaleRank = 2 THEN T1.TTLSaleRank END)) SaleRank2 ,CONVERT(nvarchar, MAX(CASE WHEN T1.TTLSaleRank = 3 THEN T1.TTLSaleRank END)) SaleRank3 ,CONVERT(nvarchar, MAX(CASE WHEN T1.TTLSaleRank = 1 THEN T1.EmployeeID END)) Employee1 ,CONVERT(nvarchar, MAX(CASE WHEN T1.TTLSaleRank = 2 THEN T1.EmployeeID END)) Employee2 ,CONVERT(nvarchar, MAX(CASE WHEN T1.TTLSaleRank = 3 THEN T1.EmployeeID END)) Employee3 FROM ( SELECT T1.EmployeeID ,SUM(SaleAmt) SaleAmt ,RANK() OVER(ORDER BY SUM(SaleAmt) DESC) TTLSaleRank FROM EmployeeMonthSales T1 GROUP BY T1.EmployeeID ) T1 ) T1 CROSS JOIN ( SELECT 'SaleAmt' DataTP SELECT 'Rank' DataTP SELECT 'Employee' DataTP ) T2 ORDER BY CASE WHEN T2.DataTP = 'Employee' THEN 0 WHEN T2.DataTP = 'Rank' THEN 1 WHEN T2.DataTP = 'SaleAmt' THEN 2 END |
EmployeeID를 표시하기 위해 CASE문을 하나 더 추가하고, 3줄로 분리하기 위해 CROSS JOIN되는 T2집합에 Employee라는DataTP를 하나 더 추가를 함으로서 <결과-5>를 얻어낼 수 있습니다.
이제 거의 다 최종 결과에 가까이 다가온 듯 합니다.
마지막으로 <결과-5>에 추가적으로 제품별 순위와 제품별 판매금액과 순위를 얻어서 결합시켜주기만 하면 됩니다.
<결과-6>과 제품별 판매금액과 순위를 얻는 SQL을 만들어 보도록 합시다.
<결과-6>
<결과-6>을 보시면, 전체순위 1위가 DVD는 순위가 4위이고, 전체순위 2위가 DVD 판매는 1위인 것을 볼 수 있습니다. 이처럼 <결과-6>의 핵심은 어떻게 DVD 판매 4위가 전체순위 1위이고, DVD판매 1위가 전체순위 2위라는 것을 어떻게 알고 해당 위치에 값을 보여주느냐가 핵심내용입니다. 뿐만 아니라 제품별 판매순위를 구하는 방법 역시 알고 있어야 합니다.
하나씩 차례대로 해보도록 하겠습니다. 일단, 제품별, 판매순위를 구하는 과정부터 살펴보도록 하겠습니다.
<결과-7>
<결과-7>은 제품별 판매순위를 나타내고 있습니다. 이와 같이 제품별 판매순위를 구하기 위해서는 RANK()함수에 PARTITION BY라는 구문을 합니다. PARTITION BY를 사용하게 되면, 파티션 별로 순위를 구할 수 가 있습니다.
<SQL-10>
|
SELECT T1.EmployeeID ,T1.SaleProductID ,T1.SaleAmt ,RANK() OVER(PARTITION BY T1.SaleProductID ORDER BY T1.SaleAmt DESC) ProdSaleRank FROM EmployeeMonthSales T1 ORDER BY T1.SaleProductID |
PARTITION BY란 구문을 사용함으로써 아주 손쉽게 제품별 순위까지 만들어 낼 수 있었습니다.
그러면, <SQL-10>을 사용해, <결과-6>을 만들어 보도록 하겠습니다.
<결과-6>을 만들기 위해서는 대입이라는 개념을 사용해야 합니다.
여기서 잠깐, <결과-1>을 다시 보면 다음과 같습니다.
<결과-1>은 전체 판매순위가 1위부터 3위인 영업사원ID를 보여주고 있습니다.
전체순위 1위는 John이고, 전체순위 2위는 David이고, 전체순위 3위는 Anderson입니다.
대입법을 설명하기 전에 전체순위 1위부터 3위를 이와 같이 알고 있다는 가정하에 <결과-6>을 만들어 내는 SQL을 작성해 보도록 합니다.
<SQL-11>
|
SELECT T1.SaleProductID ,T2.DataTP ,CASE WHEN T2.DataTP = 'SaleAmt' THEN T1.SaleAmt1 ELSE T1.SaleRank1 END Rank1 ,CASE WHEN T2.DataTP = 'SaleAmt' THEN T1.SaleAmt2 ELSE T1.SaleRank2 END Rank2 ,CASE WHEN T2.DataTP = 'SaleAmt' THEN T1.SaleAmt3 ELSE T1.SaleRank3 END Rank3 FROM ( SELECT T1.SaleProductID ,CONVERT(nvarchar, MAX(CASE WHEN T1.EmployeeID = 'John' THEN T1.SaleAmt END)) SaleAmt1 ,CONVERT(nvarchar, MAX(CASE WHEN T1.EmployeeID = 'David' THEN T1.SaleAmt END)) SaleAmt2 ,CONVERT(nvarchar, MAX(CASE WHEN T1.EmployeeID = 'Anderson' THEN T1.SaleAmt END)) SaleAmt3 ,CONVERT(nvarchar, MAX(CASE WHEN T1.EmployeeID = 'John' THEN T1.ProdSaleRank END)) SaleRank1 ,CONVERT(nvarchar, MAX(CASE WHEN T1.EmployeeID = 'David' THEN T1.ProdSaleRank END)) SaleRank2 ,CONVERT(nvarchar, MAX(CASE WHEN T1.EmployeeID = 'Anderson' THEN T1.ProdSaleRank END)) SaleRank3 FROM ( SELECT T1.EmployeeID ,T1.SaleProductID ,T1.SaleAmt ,RANK() OVER(PARTITION BY T1.SaleProductID ORDER BY T1.SaleAmt DESC) ProdSaleRank FROM EmployeeMonthSales T1 ) T1 GROUP BY T1.SaleProductID ) T1 CROSS JOIN ( SELECT 'SaleAmt' DataTP SELECT 'Rank' DataTP ) T2 GROUP BY T1.SaleProductID ,T2.DataTP |
<SQL-11>을 사용하면, <결과-6>을 얻어 낼 수 있습니다. 어렵지 않게 <결과-6>을 얻어냈습니다.
하지만, 우리는 1위가 누구이고, 2위가 누구이고, 3위가 누구인지 알고 있기 때문에, 이와 같은 SQL을 만들어 낼 수 있었습니다. 하지만, 매달, 순위는 변경될 수 있으며, 실시간으로 EmployeeMonthSales 테이블의 데이터가 계속해서 추가, 삭제, 변경이 된다면 순위는 계속해서 변경이 될 것입니다. 즉, 우리는 누가 1위가 될지 모르는데, <SQL-11>처럼 EmployeeID를 미리 박아서 SQL을 작성해 놓을 수는 없는 것입니다.
그러므로 우리는 대입법이라는 방법을 사용하는 것입니다.
대입법이라고 말은 거창하게 했지만, 특별히 어렵거나 대단한 방법은 아닙니다.
단지, EmployeeID대신에 순위를 나타내는 1, 2, 3등의 고정된 숫자가 대신 올 수 있도록 하는 것입니다.
즉, John이라는 값 대신 1이 오도록, David 대신에 2라는 값이 오도록 하는 것입니다. 만약에 David가 판매를 많이 해서 1위로 올라온다면, David한테 1이라는 값이 설정이 되어야 겠죠.
이렇게 변경하는 EmployeeID를 순위를 나타내는 숫자로 변경하는 방법은 다음과 같습니다.
<SQL-12>
|
SELECT * FROM ( SELECT T1.EmployeeID ,T1.SaleProductID ,T1.SaleAmt ,RANK() OVER(PARTITION BY T1.SaleProductID ORDER BY T1.SaleAmt DESC) ProdSaleRank FROM EmployeeMonthSales T1 ) T1 INNER JOIN ( SELECT T1.EmployeeID ,SUM(SaleAmt) SaleAmt ,RANK() OVER(ORDER BY SUM(SaleAmt) DESC) TTLSaleRank FROM EmployeeMonthSales T1 GROUP BY T1.EmployeeID ) T2 ON T1.EmployeeID = T2.EmployeeID ORDER BY T2.TTLSaleRank |
<SQL-12>를 실행하면 다음과 같은 <결과-8>을 볼 수 있습니다.
<결과-8>
<결과-8>을 보면, 제일 끝에 TTLSaleRank가 보일 것입니다.
즉,John이란 EmployeeID는 TTLSaleRank가 1로 대입되는 것이고, David는 2로, Anderson은 3으로 대입이 될 수 있도록 만들어진 결과입니다.
제품별 순위를 구한 결과와, 전체판매순위를 구한 결과를 JOIN함으로서 이와 같은 결과를 얻어 낼 수 있었습니다. 자, 그러면, 이젠 <SQL-11>의 EmployeeID부분을 고정된 숫자로 변경해서 SQL을 작성할 수 있습니다.
<SQL-13>
|
SELECT T1.SaleProductID ,T2.DataTP ,CASE WHEN T2.DataTP = 'SaleAmt' THEN T1.SaleAmt1 ELSE T1.SaleRank1 END Rank1 ,CASE WHEN T2.DataTP = 'SaleAmt' THEN T1.SaleAmt2 ELSE T1.SaleRank2 END Rank2 ,CASE WHEN T2.DataTP = 'SaleAmt' THEN T1.SaleAmt3 ELSE T1.SaleRank3 END Rank3 FROM ( SELECT T1.SaleProductID ,CONVERT(nvarchar, MAX(CASE WHEN T2.TTLSaleRank = 1 THEN T1.SaleAmt END)) SaleAmt1 ,CONVERT(nvarchar, MAX(CASE WHEN T2.TTLSaleRank = 2 THEN T1.SaleAmt END)) SaleAmt2 ,CONVERT(nvarchar, MAX(CASE WHEN T2.TTLSaleRank = 3 THEN T1.SaleAmt END)) SaleAmt3 ,CONVERT(nvarchar, MAX(CASE WHEN T2.TTLSaleRank = 1 THEN T1.ProdSaleRank END)) SaleRank1 ,CONVERT(nvarchar, MAX(CASE WHEN T2.TTLSaleRank = 2 THEN T1.ProdSaleRank END)) SaleRank2 ,CONVERT(nvarchar, MAX(CASE WHEN T2.TTLSaleRank = 3 THEN T1.ProdSaleRank END)) SaleRank3 FROM ( SELECT T1.EmployeeID ,T1.SaleProductID ,T1.SaleAmt ,RANK() OVER(PARTITION BY T1.SaleProductID ORDER BY T1.SaleAmt DESC) ProdSaleRank FROM EmployeeMonthSales T1 ) T1 INNER JOIN ( SELECT T1.EmployeeID ,SUM(SaleAmt) SaleAmt ,RANK() OVER(ORDER BY SUM(SaleAmt) DESC) TTLSaleRank FROM EmployeeMonthSales T1 GROUP BY T1.EmployeeID ) T2 ON T1.EmployeeID = T2.EmployeeID GROUP BY T1.SaleProductID ) T1 CROSS JOIN ( SELECT 'SaleAmt' DataTP SELECT 'Rank' DataTP ) T2 |
<SQL-13>을 사용하면 EmployeeMonthSales 테이블의 데이터가 변경되더라도 언제든지 사용할 수 있는 SQL이 됩니다.
그러면 우리가 할 일은 <결과-6>과 <결과-5>를 합치는 일만 남았습니다. 각각의 결과를 만드는 SQL을 UNION ALL을 사용해서 합쳐주기만 하면 최종적으로 우리가 원하는 SQL이 나옵니다.
최종 SQL은 생략하도록 하겠습니다. 충분히 여러분들이 하실 수 있으실 것이고, 가뜩이나 중복된 SQL문과 중복된 설명으로 글이 길어졌는데, 더 길게 만들면 양만 채우는 느낌이라..^^;;
실제로 이 SQL을 시스템에서 응용하시려면 몇가지 문제들을 더 해결하셔야 합니다.
순위가 중복된 경우 어떻게 할 것인지, 순위가 중복일 경우 위의 SQL들을 고치지 않고 사용하면, 결과가 이상하게 나올 것입니다. 해결 방법은 여러분들이 골똘히 생각해보시면 찾으실 수 있을 것입니다.^^
그러면 오늘은 여기까지 적기로 하겠습니다.
다음에 어떤 글을 적어야 하나 생각중입니다.^^;; 좋은 글로 찾아뵙도록 하겠습니다.
[출처] 복잡한 순위 리포트의 해결|작성자 일환
'연구개발 > DBA' 카테고리의 다른 글
| 순위함수 (0) | 2009.06.29 |
|---|---|
| 각종 형식의 일련번호 만들기 (0) | 2009.06.29 |
| SQL Server의 SQL작성 자동화 프로시저 (0) | 2009.06.29 |
| SQL Login failed because the account is currently locked out (0) | 2009.06.29 |
| SQL Server 2005의 OVER절(문제편) (0) | 2009.06.29 |