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 

总结：先查出扁平数据，然后再处理成聚合的数据，如图所示。