SQLServer 視窗函數

標籤：

一、視窗函數的作用                                                                                                      

　　視窗函數是對一組值進行操作，不需要使用GROUP BY 子句對資料進行分組，還能夠在同一行中同時返回基礎行的列和彙總列。視窗函數，基礎列和彙總列的查詢都非常簡單。

二、文法格式                                                                                                               

　　視窗函數的文法格式如下：

OVER([PARTITION BY value_expression,..[n] ] <ORDER BY BY_Clause>)

• PARTITION：分組;
• ORDER BY：排序;

　　首先建一張調試表如下：

 

CREATE TABLE [dbo].[xxx](　　[Id] [int] NULL,　　[Name] [nvarchar](50) NULL,　　[Operate] [nvarchar](50) NULL,　　[Score] [int] NULL,　　[CreateTime] [datetime] NULL) ON [PRIMARY]

 

　　往裡面添加如下資料。

　　

三、應用情境                                                                                                               

　　1、彙總列與資料列共同顯示

　--查詢姓名、分數、以及全體平均分　SELECT Name, Score, CAST(AVG(Score) OVER() AS decimal(5,2) )AS ‘平均分‘ FROM xxx

 

　　2、分組日期最新

--對每個人查詢日期最新列SELECT * FROM (    SELECT row_number() OVER(PARTITION BY Name ORDER BY CreateTime) AS part ,Score, Name, CreateTime FROM xxx) AS CWHERE C.part = 1

 

　　3、分頁

　　返回結果集內的行號，每個分區從1開始，ORDER BY可確定在特定分區中為行分配唯一 ROW_NUMBER 的順序。

四、次序函數                                                                                                              

　　1、ROW_NUMBER()

　　返回結果集內的行號，每個分區從1開始計算，ORDER BY可確定在特定分區中為行分配唯一 ROW_NUMBER 的順序。

    SELECT row_number() OVER(PARTITION BY Name ORDER BY CreateTime) ,Score, Name, CreateTime    FROM xxx

 　　輸出如下：

　　

　　2、RANK()

　　返回結果集的分區內每行的排序。行的排名是從1開始算。如果兩個或多個行與一個排名關聯，則每個關聯線將得到相同的排名。

　　SELECT RANK() OVER(PARTITION BY Name ORDER BY SCORE) ,Score, Name, CreateTime　　FROM xxx

　　下面一張圖片很好地說明了Rank與ROW_NUMBER的區別。

　　

　　3、DENSE_RANK()

　　返回結果集分區中行的排名，與Rank()類似，只是對並列的處理稍有不同，詳見樣本。

SELECT DENSE_RANK() OVER(PARTITION BY Name ORDER BY SCORE) ,Score, Name, CreateTimeFROM xxx

　　下面的樣本展示了Rank()與Dense_Rank()的區別。

　　

　　4、NTILE()

 　　NTILE函數把結果中的行關聯到組，並為每一行分配一個所屬的組的編號，編號從1開始。對於每一個行，NTILE 將返回此行所屬的組的編號。

　　如果分區的行數不能被 integer_expression(就是傳入的那個參數,表示分幾個組的意思) 整除，則將導致一個成員有兩種大小不同的組。按照 OVER 子句指定的順序，較大的組排在較小的組前面。

--每個分區分2個組，該列是改行所屬的組名SELECT NTILE(2) OVER(PARTITION BY Name ORDER BY SCORE) ,Score, Name, CreateTimeFROM xxx

　　樣本如下：

　　

SQLServer 視窗函數