Go [SQL Server] Pay けます Shimonoseki number

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.