Go [SQL Server] Pay けます Shimonoseki number

Original link: http://memo.itsysgroup.com/?p=362

Long しぶりにsql serverの Open Souel registration ですので. seoばかりアップしてて, open Souel registration では Eiken Cable, が stains いので, open Souel Memo registration more めに Books きます^^

で, けます shimonoseki number ですが, the first day colleague が make にっては see るのを, organize てきに think しようと. To pay けます Shimonoseki number には,

1. Row_number
2. Rank
3. Dense_rank
4. Ntile

の4 species が have 掛ります.

Basic な Use method は, Shimonoseki number () over (order byカラム name) Ntile (number of) over (order byカラム name) 様 な Sense じで books きます. Overの in the には, パーティションも into the れることができ, the えば department やカテゴリ the での to pay けもできます. そんな は, Shimonoseki number () over (partition by department, カテゴリorder byカラム name) のようになります.

それぞれの Shimonoseki number return bamboo blind ですが,

1. row_numberは, overで specify された condition セットに base づいて Yobitsugi beauty number of します. なので, oder byで designated したカラムの が with waists field, どちらの line が occupies to るかは the guarantee は have りません.
2. rankは, ランキングなので, oder byで specify したカラムの が with waists, same となり, sub-occupies to る line は fly ぶことになります. 1-bit, 1-bit, 3-bit という sense じですね.
3. dense_rankは, rankに-like ていますが, が fly ぶことがなく 1-bit 1-bit 2-bit のようになります.
4. ntileは, グループ けをしてくれる sense じです. Ntile (4) とするとoverで specified された condition セットに base づいて4 row づつ with waists number of してくれます. ただし, line が4で cut れないと after ろの square のグループで3 line とかに adjustment されます.

The specific な example of the following occupies shown します. Sdts: 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)

Eiken: Select Row_number () over (order by Rieki_kingaku Desc, Uriage_kingaku desc) as Row_number, rank () over (order by Rie Ki_kingaku desc, Uriage_kingaku desc) as rank, Dense_rank () over (order by Rieki_kingaku Desc, Uriage_kingaku desc) as Den Se_rank, Ntile (3) over (order by Rieki_kingaku Desc, Uriage_kingaku desc) as Ntile, * from @tbl1 S1

Outcome: 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   &Nbsp;  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

Less expands see Xin いですが, こんな sense じに and 錆びます. Partitionを plus えた fruit は out しませんが, Sqlはこんな sense じになります.

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 SE CTION_CD ORDER BY Rieki_kingaku Desc, Uriage_kingaku desc) as Dense_rank, Ntile (3) through (partition by SECTION_CD ORDER BY Rieki_kingaku desc, Uriage_kingaku desc), * from @tbl1 S1

Go [SQL Server] Pay けます Shimonoseki number