久しぶりにSQL Serverの開発備忘録です。
seoばかりアップしてて、開発備忘録では検索順位が悪いので、開発備忘録を多めに書きます＾＾
で、順位付け関数ですが、先日同僚が使って居るのを見て、整理しようと思いたちました。
順位付け関数には、
- row_number
- rank
- dense_rank
- ntile
の4種類が有ります。
基本的な使用方法は、
関数() over(order by カラム名)
ntile(数値）over(order by カラム名)
の様な感じで書きます。
overの中には、パーティションも入れることができ、例えば部門やカテゴリ内での順位付けもできます。
そんな場合は、関数() over(partition by 部門,カテゴリ order by カラム名) のようになります。
それぞれの関数の返す値ですが、
- row_numberは、overで指定された条件の結果セットに基づいて連続した数値を返します。なので、oder byで指定したカラムの値が同じ場合、どちらの行が上に来るかは保証は有りません。
- rankは、ランキングなので、oder byで指定したカラムの値が同じ場合、同位となり、次に来る行の順位は飛ぶことになります。1位1位3位という感じですね。
- dense_rankは、rankに似ていますが、順位が飛ぶことがなく1位1位2位のようになります。
- ntileは、グループ分けをしてくれる感じです。ntile(4)とするとoverで指定された条件の結果セットに基づいて４行づつ同じ数値を返してくれます。ただし、行が4で割れないと後ろの方のグループで3行とかに調整されます。
具体的な例を以下に示します。
データ：
declare @tbl1 table(employ_cd int,section_cd int, uriage_kingaku int, rieki_kingaku int)
insert into @tbl1 (employ_cd, section_cd, uriage_kingaku,rieki_kingaku) values (000000001,001,180,10)
insert into @tbl1 (employ_cd, section_cd, uriage_kingaku,rieki_kingaku) values (000000011,001,150,9)
insert into @tbl1 (employ_cd, section_cd, uriage_kingaku,rieki_kingaku) values (000000012,001,220,15)
insert into @tbl1 (employ_cd, section_cd, uriage_kingaku,rieki_kingaku) values (000000013,001,210,14)
insert into @tbl1 (employ_cd, section_cd, uriage_kingaku,rieki_kingaku) values (000000014,001,180,11)
insert into @tbl1 (employ_cd, section_cd, uriage_kingaku,rieki_kingaku) values (000000015,001,190,12)
insert into @tbl1 (employ_cd, section_cd, uriage_kingaku,rieki_kingaku) values (000000016,001,200,13)
insert into @tbl1 (employ_cd, section_cd, uriage_kingaku,rieki_kingaku) values (000000017,001,210,14)
insert into @tbl1 (employ_cd, section_cd, uriage_kingaku,rieki_kingaku) values (000000018,001,220,15)
insert into @tbl1 (employ_cd, section_cd, uriage_kingaku,rieki_kingaku) values (000000019,001,200,10)
insert into @tbl1 (employ_cd, section_cd, uriage_kingaku,rieki_kingaku) values (000000020,002,200,11)
insert into @tbl1 (employ_cd, section_cd, uriage_kingaku,rieki_kingaku) values (000000030,002,250,15)
insert into @tbl1 (employ_cd, section_cd, uriage_kingaku,rieki_kingaku) values (000000040,002,220,15)
insert into @tbl1 (employ_cd, section_cd, uriage_kingaku,rieki_kingaku) values (000000050,002,210,10)
insert into @tbl1 (employ_cd, section_cd, uriage_kingaku,rieki_kingaku) values (000000060,002,220,11)
insert into @tbl1 (employ_cd, section_cd, uriage_kingaku,rieki_kingaku) values (000000070,002,230,12)
insert into @tbl1 (employ_cd, section_cd, uriage_kingaku,rieki_kingaku) values (000000080,002,240,13)
insert into @tbl1 (employ_cd, section_cd, uriage_kingaku,rieki_kingaku) values (000000090,002,250,14)
検索：
select row_number() over(order by rieki_kingaku desc, uriage_kingaku desc) as row_number
, rank() over(order by rieki_kingaku desc, uriage_kingaku desc) as rank
, dense_rank() over(order by rieki_kingaku desc, uriage_kingaku desc) as dense_rank
, ntile(3) over(order by rieki_kingaku desc, uriage_kingaku desc) as ntile, * from @tbl1 S1
結果：
row_number rank dense_rank ntile employ_cd section_cd uriage_kingaku rieki_kingaku
———- —- ———- —– ——— ———- ————– ————-
1 1 1 1 30 2 250 15
2 2 2 1 40 2 220 15
3 2 2 1 12 1 220 15
4 2 2 1 18 1 220 15
5 5 3 1 90 2 250 14
6 6 4 1 17 1 210 14
7 6 4 2 13 1 210 14
8 8 5 2 80 2 240 13
9 9 6 2 16 1 200 13
10 10 7 2 70 2 230 12
11 11 8 2 15 1 190 12
12 12 9 2 60 2 220 11
13 13 10 3 20 2 200 11
14 14 11 3 14 1 180 11
15 15 12 3 50 2 210 10
16 16 13 3 19 1 200 10
17 17 14 3 1 1 180 10
18 18 15 3 11 1 150 9
少し見辛いですが、こんな感じに並びます。
partitionを加えた結果は出しませんが、SQLはこんな感じになります。
select row_number() over(partition by section_cd order by rieki_kingaku desc, uriage_kingaku desc) as row_number
, rank() over(partition by section_cd order by rieki_kingaku desc, uriage_kingaku desc) as rank
, dense_rank() over(partition by section_cd order by rieki_kingaku desc, uriage_kingaku desc) as dense_rank
, ntile(3) over(partition by section_cd order by rieki_kingaku desc, uriage_kingaku desc), * from @tbl1 S1