一次面试被问到开窗函数,懵逼了,赶紧补补总结一下。。。。

开窗函数也是函数,所以

理解 SQL 开窗函数-LMLPHP

理解 SQL 开窗函数-LMLPHP

比如在原来的查询上添加一个总数列

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

理解 SQL 开窗函数-LMLPHP

子查询方式:

 SELECT * ,(SELECT count(0) FROM ztest )[总数] FROM ztest

非常的nice好用

Sum 示例

SELECT *,sum(c1) over( ) AS [ΣC1] FROM ztest 

理解 SQL 开窗函数-LMLPHP

我测试了自己创建的 function ,自己创建的不能开窗

理解 SQL 开窗函数-LMLPHP

可以开窗的函数有

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 

理解 SQL 开窗函数-LMLPHP

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 

理解 SQL 开窗函数-LMLPHP

-- dense_rank 并列第 12345 名

SELECT *, dense_rank() over(ORDER BY   c1 desc ) AS [rankC1] FROM ztest ORDER BY id 

理解 SQL 开窗函数-LMLPHP

-- Row_Number -- 唯一排名

SELECT *, Row_Number() over(ORDER BY   c1 desc ) AS [rankC1] FROM ztest ORDER BY id 

理解 SQL 开窗函数-LMLPHP

我总结一下思路,先查出扁平数据,然后再处理成聚合的数据,如图所示。

理解 SQL 开窗函数-LMLPHP

05-17 03:43