一次面试被问到开窗函数,懵逼了,赶紧补补总结一下。。。。
开窗函数也是函数,所以
比如在原来的查询上添加一个总数列
create table ztest(
id int identity,
c1 int ,
c2 varchar(10)
)
insert into ztest(c1,c2) values (1,'aa') ,(2,'aa'),(3,'bb'),(8,'cc'),(9,'cc')
SELECT * FROM ztest
--添加一个总数列
SELECT *,count(0) over() AS 总数 FROM ztest
子查询方式:
SELECT * ,(SELECT count(0) FROM ztest )[总数] FROM ztest
非常的nice好用
Sum 示例
SELECT *,sum(c1) over( ) AS [ΣC1] FROM ztest
我测试了自己创建的 function ,自己创建的不能开窗
可以开窗的函数有
Sum、Count、Max、Min 、First_Value、Last_Value
Row_Number
Cume_Rank
Percent_Rank
Rank
Dense_Rank
Lag--取上n行
Lead --取下n行取
窗口内分组排序
over(order by ) 排序
over(partition f) 按照 f 分组
SELECT *, min(c1) over(partition by c2 ORDER BY C1) AS [MinC1] FROM ztest
Rank & Dense_rank & Row_Number 并列排名与普通排名示例
--rank 排名 并列第 1 、3 、5、7、9
SELECT *, rank() over(ORDER BY c1 desc ) AS [rankC1] FROM ztest ORDER BY id
-- dense_rank 并列第 12345 名
SELECT *, dense_rank() over(ORDER BY c1 desc ) AS [rankC1] FROM ztest ORDER BY id
-- Row_Number -- 唯一排名
SELECT *, Row_Number() over(ORDER BY c1 desc ) AS [rankC1] FROM ztest ORDER BY id
我总结一下思路,先查出扁平数据,然后再处理成聚合的数据,如图所示。