sqlserver 开窗函数over RANK(),DENSE_RANK(),ROW_NUMBER()三个排序的不同

一、开窗函数over()

开窗函数适用于在每一行的最后一列添加聚合函数的结果。

常用开窗函数：
1.为每条数据显示聚合信息.(聚合函数() over())
2.为每条数据提供分组的聚合函数结果(聚合函数() over(partition by 字段) as 别名) --按照字段分组，分组后进行计算
3.与排名函数一起使用(row number() over(order by 字段) as 别名)

常用分析函数：（最常用的应该是1.2.3 的排序）
1、row_number() over(partition by ... order by ...)
2、rank() over(partition by ... order by ...)
3、dense_rank() over(partition by ... order by ...)
4、count() over(partition by ... order by ...)
5、max() over(partition by ... order by ...)
6、min() over(partition by ... order by ...)
7、sum() over(partition by ... order by ...)
8、avg() over(partition by ... order by ...)
9、first_value() over(partition by ... order by ...)
10、last_value() over(partition by ... order by ...)
11、lag() over(partition by ... order by ...)
12、lead() over(partition by ... order by ...)
lag 和lead 可以 获取结果集中，按一定排序所排列的当前行的上下相邻若干offset 的某个行的某个列(不用结果集的自关联）；
lag ，lead 分别是向前，向后；
lag 和lead 有三个参数，第一个参数是列名，第二个参数是偏移的offset，第三个参数是 超出记录窗口时的默认值）

example:

CREATE TABLE STU(  
  id INT IDENTITY,
  name VARCHAR(50),
  score INT,
  subject VARCHAR(50)
)
INSERT INTO bi.dbo.STU
( name, score,subject)
VALUES
( 'tom', 20,'数学'),
('jerry',60,'英语'),
('bob',30,'语文'),
('marry',30,'语文'),
('lucy',60,'物理'),
('bob',90,'英语'),
('tom',50,'英语'),
('jerry',50,'物理')

SELECT *,AVG(score) over(PARTITION BY name  （ORDER BY id）) FROM  bi.dbo.STU    结果为：

tips:1、如果为每条数据提供分组的聚合函数结果(聚合函数() over(partition by 字段) as 别名)，就不要加order by ,

会导致每个分组的最后一行才有聚合结果

2、与排名函数一起使用(row number() over(order by 字段) as 别名)，加order by

 

和group by的区别，

SELECT *,AVG(score) over(PARTITION BY name ) FROM  bi.dbo.STU;select后面可以加未分组的字段，因为over把聚合结果变成多行
SELECT *, SUM(score) OVER () FROM bi.dbo.STU group by name 会报错，因为sum(score）是一行，而*是多行

在分页中的运用

 用子查询：

 10 , 5

 SELECT top 5 * FROM  bi_dirty.dbo.policy_report WHERE ID NOT IN

 (SELECT top 10 ID FROM bi_dirty.dbo.policy_report ORDER BY publish_date)  ORDER BY publish_date

 用over（）

SELECT * FROM

( SELECT *,ROW_NUMBER() OVER (ORDER BY publish_date) AS num FROM bi_dirty.dbo.policy_report) t

 where t.num BETWEEN 11 AND 15

二、RANK(),DENSE_RANK(),ROW_NUMBER()三个排序的不同

SELECT 

s.deptno,s.ename,s.sal,
RANK() over(partition by s.deptno order by s.sal) as rank,
DENSE_RANK() over(partition by s.deptno order by s.sal) as dense_rank,
ROW_NUMBER() over(partition by s.deptno order by s.sal) as row_number
FROM emp s;

这是一个查询每个部门员工工资的排序情况

从查询结果中很明显的发现规律：

RANK()                        发生不持续的编号 例如数据值 1,2,2,3 发生的编号将是1,2,2,4

DENSE_RANK()        发生持续的编号 例如数据值 1,2,2,3 发生的编号将是1,2,2,3

ROW_NUMBER()     发生持续的编号(不重复) 例如数据值 1,2,2,3 发生的编号将是1,2,3,4

RANK()  和 DENSE_RANK() 排序的差异就是排序存在并列的情况下。