SQL Server 窗口函数

Window Function 包含了 4 个大类。分别是:

1 - Rank Function

2 - Aggregate Function

3 - Offset Function

4 - Distribution Function.


1 - Rank Function 估计是平常用到最多的一类 window Function.

1.1 Rank() Over()

1.2 Row_Number() Over()

1.3 Dense_Rank() Over()

1.4 NTILE(N) Over()


这四个函数,要注意的地方有两点:

a. Rank() Over() 与 Row_Number() Over() :

两者唯一的区别,就在于Row_Number() Over() 真正实现了相同条件的两条或者多条记录是用唯一值来区别的

b. Rank() Over() 与 Dense_Rank() Over() :

这两者的区别,在于他们对位于相同排名之后的名次,是接着相同排名的连续数(Rank) 还是相隔 N 个相同记录个数之后的连续数(Dense_Rank)。

所以 Rank 出来的结果都是连续数字,而 Dense_Rank 出来的结果有可能有跳格数。


除了有用法上的区别外,顺带说说分页的实现:


第一种,我们平常用 Row_Number() 加 Top (N) 来实现 :

select top(100) *

from ( select

OrderId

, OrderMonth

, OrderAmount

, Row_Number() Over(

OrderBy OrderAmount DESC)

AS Amt_Order

from FctSales) tmp

Where Amt_Order between 2000 and 3000


第二种,SQL Server 2012 之后的新功能:


OFFSET & FETCH::

Select OrderId

, OrderMonth

, OrderAmount

From FctSales

Order by OrderAmount Desc


OffSet 2000 ROWS

Fetch Next 100 ROWS Only


按照量的大小倒序排,取第 2000 条后的记录中前 100 条。



2 - Aggregate Function. 用于聚合数据

2.1 - Sum() Over()

2.2 - Count() Over()

2.3 - AVG() Over()

2.4 - MIN() Over()

2.5 - MAX() Over()


在使用 Aggregation 函数的时候,唯一要注意的地方就是 Order 子句。


function_name(<arguments>) Over(

[ <window partition clause>]

[ <window Order clause>

[ <window frame clause>]

])


Over::

Over(

[ <PARTITION BY clause> ]

[ <ORDER BY clause> ]

[ <ROW or RANGE clause> ]

)


<window frame clause>::窗口中的窗口

ROWS | RANGE

BETWEEN

UNBOUNDED PRECDEDING |

<N> PRECEDING |

<N> FOLLOWING |

CURRENT ROW

AND

UNBOUNDED FOLLOWING |

<N> PRECEDING |

<N> FOLLOWING |

CURRENT ROW


举一个例子:


select custid

, ordermonth

, ordervolume

, sum(ordervolume)

over( partition by custid

order by ordermonth asc

rows between

unbounded preceding

and current row)

as cumulatedVolume

from FctSales


统计了截止到目前为止,每一天的累计总量。



3 - Offset Function:定位记录

3.1 Lead()

3.2 LAG()

3.3 First_Value()

3.4 Last_Value()

3.5 Nth_Value()


这一类比较好理解,根据当前的记录,获取前后 N 条数据。


4 - Distribution Function: 分布函数

4.1- PERCENT_RANK()

4.2 - CUME_DIST()

4.3 - PERCENT_COUNT()

4.4 - PERCENT_DISC()


这一类应用,到目前为止,未用过。适用于财会类的统计。


如有兴趣深入研究,我们一起来读《Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions》。Ben 大神的又一著作。

编辑于 2017-11-13

文章被以下专栏收录