SQL 开窗函数
一次面试被问到开窗函数,懵逼了,赶紧补补总结一下。。。。
开窗函数也是函数,所以
比如在原来的查询上添加一个总数列
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 分组
示例1:查询最小值
SELECT *, min(c1) over(partition by c2 ORDER BY C1) AS [MinC1] FROM ztest
示例2:查询每组第一个c1(换成id,就等同于查询每组第一行了)
代码:
SELECT c2,c1 FROM ztest ORDER BY c2 GO WITH t as ( SELECT *, First_Value(c1) over(partition by c2 ORDER BY C1) AS [first_c1] FROM ztest ) SELECT t.c2,t.first_c1 FROM t GROUP BY t.c2,t.first_c1
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
总结:先查出扁平数据,然后再处理成聚合的数据,如图所示。