（4.34）sql server窗口函数

关键词：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()

参考自：https://www.jianshu.com/p/bfc39dcb73f9
参考文献：https://www.cnblogs.com/CareySon/p/3411176.html