T-SQL:開窗函數（十二）

1.基本概念

開窗函數分為兩個部分分別是

1.聚合,排名,偏移,分佈函數 。

2.開窗分區，排序，框架。

下麵舉個例子

SELECT empid, ordermonth, val,
  SUM(val) OVER(PARTITION BY empid
                ORDER BY ordermonth
                ROWS BETWEEN UNBOUNDED PRECEDING
                         AND CURRENT ROW) AS runval
FROM Sales.EmpOrders;

sum(val)  就是集合函數

over() 就是開窗     PARTITION BY empid  就是開窗分區(分組)   ORDER BY ordermonth 開窗排序  

 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  開窗架構

2.排名開窗函數

 SQL SERVER 支持4個排名函數 ROW_NUMBER,RANK,DENSE_RANK ,NTLE   來看看它們分別的作用

SELECT orderid, custid, val,
ROW_NUMBER() OVER(ORDER BY val) AS rownum,
RANK() OVER(ORDER BY val) AS rank,
DENSE_RANK() OVER(ORDER BY val) AS dense_rank,
NTILE(10) OVER(ORDER BY val) AS ntile
FROM Sales.OrderValues
ORDER BY val;

可以看到 它們不同排序規則

ROW_NUMBER() 對排序欄位行號進行排序  

RANK() 對數值進行排序 對相同數值有行號占用

DENSE_RANK() 也是對數值排序 如果有相同數值 依舊會按照原先行號加

NTILE 分區排序 為每一行分配一個區號 如果分10區 會對所有數據進行分區  總數據/分區數  就是每多少數據為一區

ROW_NUMBER()  預設在 DISTINCT 篩選重覆項之前計算

2.偏移開窗函數 

sql server 中總共有4個偏移函數 LAG 和 LEAD, FIRST_VALUE 和 LAST_VALUE   

LAG函數在當前行之前查找

LEAD函數在當前行之後查找

LAST_VALUE    返回查找結果的最後一行

FIRST_VALUE  返回查找結果的第一行

 3.聚合開窗函數

SUM() OVER()

AVG() OVER()

COUNT() OVER()

MAX() OVER() 等等