TSQL基础(二)
over() 开窗函数
排名函数必须和over()一起使用,所以先了解over()。
OVER 子句定义查询结果集内的窗口或用户指定的行集。 然后,开窗函数将计算窗口中每一行的值。
您可以将 OVER 子句与函数一起使用,以便计算各种聚合值,例如移动平均值、累积聚合、运行总计或每组结果的前 N 个结果。
select *,COUNT(*) over() '全班人数' from Student
select *,COUNT(*) over(partition by Ssex) '性别人数' from Student --over()中加入partition by Ssex表示 按Ssex字段分组 男女分别为4人。
排名函数
4种排名:1.ROW_NUMBER() , 2.RANK(), 3.dense_rank(), 4.NTILE(N)
row_number() over(order by field) 根据over中order by 字段进行排名 输出结果为有序的行号
rank() over(order by field) 根据over中order by 字段进行排名 输出结果为排名序号有并列而且会断号
dense_rank() over(order by field) 根据over中order by 字段进行排名 输出结果为排名序号有并列不会断号
ntile(N) over(order by field) ) 根据over中order by 字段进行排名 并根据ntile(N)中类型为int参数N进行分组
select *,ROW_NUMBER() over(order by year(sage)) 'rownumber行号', RANK() over(order by year(sage)) 'rank有并列而且会出现断号', dense_rank() over (order by year(sage)) 'dense_rank密集排名', NTILE(4) over(order by year(sage)) '分组排名' from Student
也可以在over中加入partition by
select *,ROW_NUMBER() over(partition by year(sage) order by year(sage)) 'rownumber行号' , RANK() over( partition by year(sage) order by year(sage)) 'rank有并列而且会出现断号' , dense_rank() over ( partition by year(sage) order by year(sage)) 'dense_rank密集排名', NTILE(4) over(partition by year(sage) order by year(sage)) '分组排名'from Student
补充:with ties
指定从基本结果集中返回附加的行,这些行包含与出现在
TOP
n (PERCENT) 行最后的
ORDER
BY
列中的值相同的值。
select top 3 with ties * from Student order by year(sage) --只取前3行,如果最后第3行中sage的年份和后面的年份相同,则附加随后的行