ランキング（SQLServer 2005 分析関数の利用）

SQLServer2005の分析関数を利用して売上ランキングを作る

--売上テーブル（非現実的なテーブルですが・・・ご勘弁ください）
--ここではテーブル変数を利用します
DECLARE @UriageData TABLE (
GroupCD SMALLINT DEFAULT 0, --グループ
TantosyaCD SMALLINT DEFAULT 0, --担当者
UriageDate DATETIME, --売上日
Uriagegaku MONEY DEFAULT 0 --売上額
)

--売上データ
INSERT INTO @UriageData(GroupCD, TantosyaCD, UriageDate, Uriagegaku) VALUES(1, 1, '2008/01/01', 10000)
INSERT INTO @UriageData(GroupCD, TantosyaCD, UriageDate, Uriagegaku) VALUES(1, 2, '2008/01/02', 20000)
INSERT INTO @UriageData(GroupCD, TantosyaCD, UriageDate, Uriagegaku) VALUES(1, 3, '2008/01/01', 9000)
INSERT INTO @UriageData(GroupCD, TantosyaCD, UriageDate, Uriagegaku) VALUES(2, 4, '2008/01/01', 30000)
INSERT INTO @UriageData(GroupCD, TantosyaCD, UriageDate, Uriagegaku) VALUES(2, 5, '2008/01/03', 7000)
INSERT INTO @UriageData(GroupCD, TantosyaCD, UriageDate, Uriagegaku) VALUES(1, 1, '2008/01/10', 20000)
INSERT INTO @UriageData(GroupCD, TantosyaCD, UriageDate, Uriagegaku) VALUES(1, 2, '2008/01/12', 15000)
INSERT INTO @UriageData(GroupCD, TantosyaCD, UriageDate, Uriagegaku) VALUES(1, 3, '2008/01/11', 10000)
INSERT INTO @UriageData(GroupCD, TantosyaCD, UriageDate, Uriagegaku) VALUES(2, 4, '2008/01/11', 9000)
INSERT INTO @UriageData(GroupCD, TantosyaCD, UriageDate, Uriagegaku) VALUES(2, 5, '2008/01/10', 25000)

--ランキング（担当者別に売上額の高い順にソートして順位を付ける）
SELECT RANK() OVER(ORDER BY SUM(Uriagegaku) DESC) AS 'ランク',
　　　　TantosyaCD AS '担当者', SUM(Uriagegaku) AS '売上額'
FROM @UriageData
GROUP BY TantosyaCD






--順位が同じだったら・・・
--担当者１と２を同額になるようにデータを調整
INSERT INTO @UriageData(GroupCD, TantosyaCD, UriageDate, Uriagegaku) VALUES(1, 1, '2008/01/11', 5000)



担当者１と２共に２位となります。そのとき３位は歯抜けとなる



--RANK()の変わりにDENSE_RANK()を使うと歯抜けをしない形で返る
SELECT DENSE_RANK() OVER(ORDER BY SUM(Uriagegaku) DESC) AS 'ランク',
　　　　TantosyaCD AS '担当者', SUM(Uriagegaku) AS '売上額'
FROM @UriageData
GROUP BY TantosyaCD





--結果からトップ３を抽出する
SELECT AllData.rank AS 'ランク', AllData.tanto AS '担当者', AllData.uriage AS '売上額'
FROM (SELECT DENSE_RANK() OVER(ORDER BY SUM(Uriagegaku) DESC) AS rank,
TantosyaCD AS tanto, SUM(Uriagegaku) AS uriage
FROM @UriageData
GROUP BY TantosyaCD
) AS AllData
WHERE AllData.rank BETWEEN 1 AND 3





--グループ内でそれぞれランキングを付ける（PARTITION BYを付加します）
--その前にわかりやすい結果になるようちょっとデータを直します
INSERT INTO @UriageData(GroupCD, TantosyaCD, UriageDate, Uriagegaku) VALUES(1, 1, '2008/01/11', -5000)

SELECT RANK() OVER(PARTITION BY GroupCD ORDER BY SUM(Uriagegaku) DESC) AS 'ランク', GroupCD AS 'グループ', TantosyaCD AS '担当者', SUM(Uriagegaku) AS '売上額'
FROM @UriageData
GROUP BY TantosyaCD, GroupCD





--余談ですがROW_NUMBER()を使うと単純に結果レコードに番号を振ります
SELECT ROW_NUMBER() OVER(ORDER BY SUM(Uriagegaku) DESC) AS 'Ｎｏ',
　　　　TantosyaCD AS '担当者', SUM(Uriagegaku) AS '売上額'
FROM @UriageData
GROUP BY TantosyaCD