关键词：sql server窗口函数，窗口函数，分析函数

如果分析函数不可用，那么可能是版本还不支持

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

1 - Rank Function　　

1.1 Rank() Over()

1.2 Row_Number() Over()

1.3 Dense_Rank() Over()

1.4 NTILE(N) Over()

2 - Aggregate Function

2.1 - Sum() Over()

2.2 - Count() Over()

2.3 - AVG() Over()

2.4 - MIN() Over()

2.5 - MAX() Over()

3 - Offset Function

3.1 Lead()

3.2 LAG()

3.3 First_Value()

3.4 Last_Value()

3.5 Nth_Value()

4 - Distribution Function.

4.1- PERCENT_RANK()

4.2 - CUME_DIST()

4.3 - PERCENT_COUNT()

4.4 - PERCENT_DISC()

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

举一个例子：

--利用嵌套统计累加和

;with temp1 as (

select 1 as id ,1 as num union all

select 1 as id ,2 as num union all

select 1 as id ,3 as num union all

select 2 as id ,4 as num union all

select 2 as id ,5 as num union all

select 2 as id ,6 as num

)

select *,sum(num) over(partition by id order by num asc rows between unbounded preceding and current row) from temp1

3 - Offset Function:定位记录

3.1 Lead() --自上而下（lead lag 参考：https://blog.csdn.net/leewhoee/article/details/20264653/）

3.2 LAG() --自下而上

3.3 First_Value() --第一个值

3.4 Last_Value() --最后一个值

3.5 Nth_Value() --第N个值

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

举例：

　　LEAD ( scalar_expression [ ,offset ] , [ default ] )     OVER ( [ partition_by_clause ] order_by_clause )

　　LEAD(score,1,0) over(order by score) as next_score

　　根据score排序，第1行之后开始的1行（即第2行的score列值），default对应的是，如果是末行或者值为null，则给个默认值

4 - Distribution Function: 分布函数

4.1- PERCENT_RANK()

4.2 - CUME_DIST()

4.3 - PERCENT_COUNT()

4.4 - PERCENT_DISC()