Oracle 开窗函数

窗口函数

OVER用于为行定义一个窗口，它对一组值进行操作，不需要使用GROUP BY子句对数据进行分组，就能够在同一行中同时返回基础行的列和聚合列。

定义

• 必须与 聚合函数 或 排序函数一起使用

• 聚合函数一般指 SUM(),MAX(),MIN,COUNT(),AVG()等

• 排序函数一般指 ROW_NUMBER(),RANK(),DENSE_RANK(),NTILE()等

语法

over(partition column | order by column)

• partition by 子句用于分组

• order by 子句用于排序

--建立测试表和测试数据
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);

SUM后的开窗函数

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进行汇总处理

COUNT后的开窗函数

SELECT *,       
  COUNT(*) OVER(PARTITION BY Groupname ) 每个组的个数,        
  COUNT(*) OVER(PARTITION BY Groupname ORDER BY ID) 每个组的累积个数,        
  COUNT(*) OVER(ORDER BY ID) 累计个数 ,       
  COUNT(*) OVER() 总个数 from Employee

排序函数

ROW_NUMBER()

--先建立测试表和测试数据
WITH t AS(SELECT 1 StuID,'一班' ClassName,70 Score
UNION ALLSELECT 2,'一班',85
UNION ALLSELECT 3,'一班',85
UNION ALLSELECT 4,'二班',80
UNION ALLSELECT 5,'二班',74
UNION ALLSELECT 6,'二班',80)
SELECT * INTO Scores FROM t;
SELECT * FROM Scores

SELECT *,
  ROW_NUMBER() OVER (PARTITION BY ClassName ORDER BY SCORE DESC) 班内排序,
  ROW_NUMBER() OVER (ORDER BY SCORE DESC) AS 总排序 FROM Scores;

此外ROW_NUMBER()函数还可以取指定顺序的数据。

SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY SCORE DESC) AS 总排序FROM Scores) t WHERE t.总排序=2;

RANK()

• ROW_NUMBER()是排序，当存在相同成绩的学生时，ROW_NUMBER()会依次进行排序，他们序号不相同，而Rank()则不一样。如果出现相同的，他们的排名是一样的。

SELECT ROW_NUMBER() OVER (ORDER BY SCORE DESC) AS 总排序, * FROM Scores;
SELECT RANK() OVER (ORDER BY SCORE DESC) AS 总排序 , * FROM Scores;

DENSE_RANK()

• DENSE_RANK()排名是连续的，RANK()是跳跃的排名，一般情况下用的排名函数就是RANK() 我们看例子：

SELECT RANK() OVER (ORDER BY SCORE DESC) AS 总排序,* FROM Scores;
SELECT DENSE_RANK() OVER (ORDER BY SCORE DESC) AS 总排序,* FROM Scores;

NTILE()

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;

转载来源声明:
https://www.modb.pro/db/42912