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

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()

米霖微信.PNG

添加我的微信吧