Sql Server开窗函数

1关于开窗函数

• 在数据库中聚合函数一般和group by一起用。如果在查询时，想使用聚合函数和基础列 （不是分组字段），可以考虑开窗函数
• 查询时，如果想生成排序列，可以考虑开窗函数

 2普通开窗函数

• 对整个查询结果进行聚合运算
• 语法：聚合函数 over()

select
    COUNT(1)over()as'Count',
    Min(UserID)over()as'Min',
    Max(UserID)over()as'Max',
    AVG(UserID)over()as'Avg',
    *
from AllUser where UserID>20000

3聚合开窗函数

•  对该行记录所在分区进行聚合运算
• 每个开窗函数都是独立的，各自可以按照不同的字段进行分区
• 语法：聚合函数 over(partition by 分区字段)

select
    COUNT(1)over(partition by Sex)as'Count',
    Min(UserID)over(partition by PartnerID)as'Min',
    Max(UserID)over(partition by PartnerID)as'Max',
    AVG(UserID)over(partition by Sex)as'Avg',
    *
from AllUser where UserID>20000

4排序开窗函数

• 排序开窗函数支持的函数有：row_number()、rank()、dense_rank()、ntile()
• 语法：排序函数 over(order by 排序字段 asc|desc)
• 排序开窗函数中，也可以使用[partition by 分区字段]，每行记录根据所在分区进行排序
• 语法：排序函数 over([partition by 分区字段] order by 排序字段 asc|desc)
• rank() 与dense_rank()区别：有三个人，其中两个人并列第一，使用rank() 第三个人为第三名；使用dense_rank()第三人为第二名

select
    ROW_NUMBER()over(partition by PartnerID order by UserID asc)as'RowNumber',
    RANL()over(partition by Sex order by UserID asc)as'Rank',
    DENSE_RANK()over(order by UserID asc)as'DenseRank',
    NTILE(6)over(order by UserID asc)as'Ntile',
    *
from AllUser where UserID>20000