次序函數

Posted on Edited on In Views: Disqus:

 

次序函數有四種。

先準備範例資料表

CREATE TABLE PERSON 
  ( 
     no   TINYINT NOT NULL, 
     name NVARCHAR(10) NOT NULL, 
     sex  NVARCHAR(5) NOT NULL, 
     age  TINYINT NOT NULL, 
     PRIMARY KEY (no) 
  ) 



INSERT PERSON (no,name,sex,age) VALUES (1,'Bill','boy',39); 
INSERT PERSON (no,name,sex,age) VALUES (2,'Mary','girl',54); 
INSERT PERSON (no,name,sex,age) VALUES (3,'Hen','boy',20); 
INSERT PERSON (no,name,sex,age) VALUES (4,'Junet','girl',20); 
INSERT PERSON (no,name,sex,age) VALUES (5,'Tom','boy',20); 
INSERT PERSON (no,name,sex,age) VALUES (6,'Izesuber','girl',10); 
INSERT PERSON (no,name,sex,age) VALUES (7,'Tim','boy',32); 
INSERT PERSON (no,name,sex,age) VALUES (8,'Kimberray','girl',40); 
INSERT PERSON (no,name,sex,age) VALUES (9,'Brown','boy',33); 
INSERT PERSON (no,name,sex,age) VALUES (10,'Rose','girl',29);

 

一、ROW_NUMBER-依指定條件重新編號

語法：

ROW_NUMBER() OVER ( [ PARTITION BY value_expression , ... [ n ] ] ORDER BY value_expression [ ASC | DESC ] )

1、依no欄位遞增順序重新編號

SELECT no,name,sex,age,Row_number () 
                         OVER ( 
                           ORDER BY no ASC) SN 
FROM   PERSON

 

2、以sex欄位作分割，依no欄位遞增順序重新編號

SELECT no,name,sex,age,Row_number () 
                         OVER ( 
                           PARTITION BY sex 
                           ORDER BY no ASC) SN 
FROM   PERSON

 

二、RANK-資料排序後可能有名次重複，後續名次將會跳掉

1、以sex欄位作分割，依age欄位遞增順序重新排名

SELECT no,name,sex,age,Rank() 
                         OVER ( 
                           PARTITION BY sex 
                           ORDER BY age ASC) SN 
FROM   PERSON

 

三、DENSE_RANK-資料排序後可能有名次重複，後續名次將不會跳掉

1、以sex欄位作分割，依age欄位遞增順序重新排名

SELECT no,name,sex,age,Dense_rank() 
                         OVER ( 
                           PARTITION BY sex 
                           ORDER BY age ASC) SN 
FROM   PERSON

 

四、NTILE

1、將所有資料分成3群

SELECT no,name,sex,age,NTILE(3) 
                         OVER ( 
                          ORDER BY age ASC) SN 
FROM   PERSON

 

2、以sex欄位作為分割，將資料分成3群

SELECT no,name,sex,age,Ntile(3) 
                         OVER ( 
                           PARTITION BY sex 
                           ORDER BY age ASC) SN 
FROM   PERSON

 

參考資料：

Ranking Functions (Transact-SQL)