這篇文章為大家帶來了關於SQL server的相關知識,開窗函數也叫分析函數有兩類,一類是聚合開窗函數,一類是排序開窗函數,下面這篇文章主要給大家介紹了關於SQL中開窗函數的相關資料,文中透過實例程式碼介紹的非常詳細,需要的朋友可以參考下。
推薦學習:《SQL教程》
OVER用於為行定義一個窗口,它對一組值進行操作,不需要使用GROUP BY子句對資料進行分組,能夠在同一行中同時傳回基礎行的列和聚合列。
OVER ( [ PARTITION BY column ] [ ORDER BY culumn ] )
#PARTITION BY 子句進行分組;
ORDER BY 子句進行排序。
視窗函數OVER()指定一組行,而開窗函數計算從視窗函數輸出的結果集中各行的值。
開窗函數不需要使用GROUP BY就可以將資料分組,也可以同時傳回基礎行的列和聚合列。
OVER開窗函數必須與聚合函數或排序函數一起使用,聚合函數一般指SUM(),MAX(),MIN,COUNT(),AVG()等常見函數。排序函數一般指RANK(),ROW_NUMBER(),DENSE_RANK(),NTILE()等。
我們以SUM和COUNT函數作為範例來給大家示範。
--建立测试表和测试数据 CREATE TABLE Employee ( ID INT PRIMARY KEY, Name VARCHAR(20), GroupName VARCHAR(20), Salary INT ) INSERT INTO Employee VALUES(1,'小明','开发部',8000), (4,'小张','开发部',7600), (5,'小白','开发部',7000), (8,'小王','财务部',5000), (9, null,'财务部',NULL), (15,'小刘','财务部',6000), (16,'小高','行政部',4500), (18,'小王','行政部',4000), (23,'小李','行政部',4500), (29,'小吴','行政部',4700);
SELECT *, SUM(Salary) OVER(PARTITION BY Groupname) 每个组的总工资, SUM(Salary) OVER(PARTITION BY groupname ORDER BY ID) 每个组的累计总工资, SUM(Salary) OVER(ORDER BY ID) 累计工资, SUM(Salary) OVER() 总工资 from Employee
(提示:可以左右滑動程式碼)
結果如下:
其中開窗函數的每個意義不同,我們來具體解讀一下:
SUM(Salary) OVER (PARTITION BY Groupname)
只對PARTITION BY後面的列Groupname進行分組,分組後求解Salary的和。
SUM(Salary) OVER (PARTITION BY Groupname ORDER BY ID)
#對PARTITION BY後面的欄位Groupname進行分組,然後按ORDER BY 後的ID進行排序,然後在群組內對Salary進行累加處理。
SUM(Salary) OVER (ORDER BY ID)
只對ORDER BY後的ID內容進行排序,對排完序後的Salary進行累加處理。
SUM(Salary) OVER ()
對Salary進行總處理
SELECT *, COUNT(*) OVER(PARTITION BY Groupname ) 每个组的个数, COUNT(*) OVER(PARTITION BY Groupname ORDER BY ID) 每个组的累积个数, COUNT(*) OVER(ORDER BY ID) 累积个数 , COUNT(*) OVER() 总个数 from Employee
傳回的結果如下圖:
後面的每個開窗函數就不再一一解讀了,可以對照上面SUM後的開窗函數一一對照。
我們對4個排序函數一一示範
--先建立测试表和测试数据 WITH t AS (SELECT 1 StuID,'一班' ClassName,70 Score UNION ALL SELECT 2,'一班',85 UNION ALL SELECT 3,'一班',85 UNION ALL SELECT 4,'二班',80 UNION ALL SELECT 5,'二班',74 UNION ALL SELECT 6,'二班',80 ) SELECT * INTO Scores FROM t; SELECT * FROM Scores
## 定義:ROW_NUMBER()函數作用就是將SELECT查詢到的資料進行排序,每一個資料加上一個序號,他不能用做於學生成績的排名,一般多用於分頁查詢,例如查詢前10個查詢10- 100個學生。 ROW_NUMBER()必須與ORDER BY一起使用,否則會報錯。
對學生成績排序SELECT *, ROW_NUMBER() OVER (PARTITION BY ClassName ORDER BY SCORE DESC) 班内排序, ROW_NUMBER() OVER (ORDER BY SCORE DESC) AS 总排序 FROM Scores;
SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY SCORE DESC) AS 总排序 FROM Scores ) t WHERE t.总排序=2;
SELECT ROW_NUMBER() OVER (ORDER BY SCORE DESC) AS [RANK],* FROM Scores; SELECT RANK() OVER (ORDER BY SCORE DESC) AS [RANK],* FROM Scores;
定义:DENSE_RANK()函数也是排名函数,和RANK()功能相似,也是对字段进行排名,那它和RANK()到底有什么不同那?特别是对于有成绩相同的情况,DENSE_RANK()排名是连续的,RANK()是跳跃的排名,一般情况下用的排名函数就是RANK() 我们看例子:
示例
SELECT RANK() OVER (ORDER BY SCORE DESC) AS [RANK],* FROM Scores; SELECT DENSE_RANK() OVER (ORDER BY SCORE DESC) AS [RANK],* FROM Scores;
结果如下:
上面是RANK()的结果,下面是DENSE_RANK()的结果
定义:NTILE()函数是将有序分区中的行分发到指定数目的组中,各个组有编号,编号从1开始,就像我们说的'分区'一样 ,分为几个区,一个区会有多少个。
SELECT *,NTILE(1) OVER (ORDER BY SCORE DESC) AS 分区后排序 FROM Scores; SELECT *,NTILE(2) OVER (ORDER BY SCORE DESC) AS 分区后排序 FROM Scores; SELECT *,NTILE(3) OVER (ORDER BY SCORE DESC) AS 分区后排序 FROM Scores;
结果如下:
就是将查询出来的记录根据NTILE函数里的参数进行平分分区。
OVER开窗函数是我们工作中经常要使用到的,特别是在做数据分析计算的时候,经常要对数据进行分组排序。上面我们额外介绍了聚合函数和排序函数的与OVER结合的使用方法,此外还有很多与OVER一起使用的函数,比如LEAD函数,LAG函数,STRING_AGG函数等等都会使用到开窗函数OVER,其使用方法也要务必掌握。
推荐学习:《SQL教程》
以上是一文搞懂SQL中的開窗函數的詳細內容。更多資訊請關注PHP中文網其他相關文章!