天天看點

Transact-SQL學習筆記21——排名視窗函數

作者:包家三少

将OVER()子句和排名函數連用,就是排名視窗函數,它們隻能用在SELECT子句或ORDER BY子句之後。如果放在 SELECT之後,它運作的邏順序在 DISTINCT 之前。

邏輯處理順序如下:

SELECT(7)<DISTINCT>(9)TOP (5)select list()<OVER0

(1)FROM...JOIN ...

(2)WHERE

(3)GROUP BY

(4)HAVING

(8)ORDER BY

它們的使用方法格式如下:

order_function OVER( [PARTITION BY expression]<ORDER BY CIase> )

order_finction 是指排名函數,包括ROW NUMBER()、RANK()、DENSE_RANK()和NTILE()與OVER()連用,OVER子句裡面使用PARTITION BY關鍵字對輸入行進行視窗劃分(即分區劃分),如果OVER()子句中不寫PARTITION BY,則表示對所有行進行計算,這裡的所有行不是 FROM 後面表的所有行,而是經過 WHERE GROUP BY和HAVING運作之後的所有行。在PARTITION BY之後還跟上ORDER BY子句對分區内的資料進行排序,它不可以省略,否則無順序的區内資料由于不知道排名而無法使用排名函數。

實驗環境如下兩表:

Transact-SQL學習筆記21——排名視窗函數

employee表

Transact-SQL學習筆記21——排名視窗函數

salary 表

ROW NUMBER()進行分區編号

SELECT ROW_NUMBER() OVER(PARTITION BY employee_id ORDER BY salary) AS ID ,* FROM salary
Transact-SQL學習筆記21——排名視窗函數

如果沒有使用PARTITION BY,就隻有一個視窗

SELECT ROW_NUMBER() OVER(ORDER BY salary) AS ID ,* FROM salary
Transact-SQL學習筆記21——排名視窗函數
SELECT ROW_NUMBER() OVER(PARTITION BY employee_id ) AS ID ,* FROM salary
Transact-SQL學習筆記21——排名視窗函數

如果不适用ORDER BY 會報錯,但也許此時并不希望做排序,可以使用SELECT 0 來變通

SELECT ROW_NUMBER() OVER(PARTITION BY employee_id ORDER BY (SELECT 0)) AS ID ,* FROM salary

RANK() 和DENSE_RANK()

  • rank()排序相同時會重複,總數不變,即會出現1、1、3這樣的排序結果;
  • dense_rank()排序相同時會重複,總數會減少,即會出現1、1、2這樣的排序結果;
  • row_number()排序相同時不會重複,會根據順序排序。

--更新實驗環境

UPDATE salary

SET salary =60

where employee_id = 1 and salary_type ='崗位工資'

SELECT

ROW_NUMBER() OVER(PARTITION BY employee_id ORDER BY salary) AS 編号,

RANK() OVER(PARTITION BY employee_id ORDER BY salary) AS 排名,

DENSE_RANK() OVER(PARTITION BY employee_id ORDER BY salary) AS 密集排名

,* FROM salary

Transact-SQL學習筆記21——排名視窗函數

NTIEL()進行資料分鐘

NTILE()的功能是進行“均分”分組,括号内接受一個代表要分組組數量的參數,然後以組為機關進行編号,對于組内每一行資料,NTILE 都傳回此行所在組的組編号。

簡單地說就是NTILE 函數将每一行資料關聯到組,并為每一行配置設定一個所屬組的編号。

假設一個表的某列值為1~10 的整數,要将這 10 行分成兩組,則每個組都有5 行,表示方式為NTILE(2)。如果表某列是 1~11的整數,這11行要分成3 組的表示方式為NTILE(3)但是這時候無法“均分”,它的配置設定方式是先分成3 組,每組3 行資料,剩下的兩行資料從前向後均分,即第一組和第二組都有 4 行資料,第三組隻有3 行資料。

假設1~11的整數,分為3組(1,2,3,4)(5,6,7,8)(9,10,11)

SELECT NTILE(4) OVER(ORDER BY salary) AS 分組

,* FROM salary

Transact-SQL學習筆記21——排名視窗函數

學習參考資料:《跟韓老師學 SQL Server 資料庫設計與開發》

繼續閱讀