SQL Server开窗函数over()的巧用row_number(),rank(),dense_rank(),以及sum(col1) over()等_sql窗口函数 row between和 range between preceding foll

  sql server中开窗函数相比于group by用的是少，但是相对于某些功能，开窗函数却能方便简单高效的实现，个人觉得开窗函数这个名字太高雅，不能体现他的功能性，应该叫分区计算，更能体现他的本质
  函数样式： 函数名(列1) OVER(partition by 列2 order by 列3)
  具体意思就是把一个表里面的数据按照列2的层次分成一小块一小块的区域，，每个区域里面是按照列3排序的，然后在各个区域内实现按照列1的函数计算，废话不多说，上案例，下面是一个每月每天的绩效表列子。

select * from dbo.myorderdateproduction 

  如果我们需要查询出每个月每天的绩效按从高到低排序，则有一下三个函数可供选择，row_number(),rank(),dense_rank()，如下图所示，row_number()是业绩相同的也又先后，rank()是业绩相同的排名一样但是会跳过重叠的业绩一样的人数排名，dense_rank()是业绩相同排名一致但不会跳过重叠人数的排名，三种情况应用场景各不相同，按需使用即可。

select 
  row_number() over(partition by monthkey order by productionamount )     row_number_productionamount
 ,rank() over(partition by monthkey order by productionamount ) rank_prodmonthly
 ,DENSE_RANK()  over(partition by monthkey order by productionamount) dense_rank_prodmonthly
 ,monthkey
 ,datekey
 ,productionamount
from dbo.myorderdateproduction 
order by monthkey,productionamount
1
2
3
4
5
6
7
8

  另一种情况的运用，就是累计每个月截止到当天的业绩总和，平均业绩，最大业绩等等，这里以求总和为例子，就是我们经常说的MTD业绩，具体如下图所示：

select 
  monthkey
 ,datekey
 ,productionamount
 ,SUM(productionamount) over(partition by monthkey order by monthkey,datekey RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) productionamountMTD
 ,SUM(productionamount) over(partition by monthkey order by monthkey,datekey rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) productionamountMTD
 from dbo.myorderdateproduction 
 order by monthkey,datekey
1
2
3
4
5
6
7
8

结果如下：

其实代码中的range和rows还是有点区别的，只不过说这个列子不明显，可参考下图的一个列子，range会把相同partition和order by的值显示为一样的最终结果，但是rows不会。

  range结果：

  rows结果：

rows和range后面的常用参数如下：

UNBOUNDED PRECEDING

The window starts at the first row of the partition.

UNBOUNDED FOLLOWING

The window ends at the last row of the partition.

CURRENT ROW

window begins at the current row or ends at the current row

n PRECEDING or n FOLLOWING

The window starts or ends n rows before or after the current row

for example,

ROWS BETWEEN Unbounded preceding AND 1 Preceding
1

means that
the window goes from the first row of the partition to the row that stands (in
the ordered set) immediatly before the current row…