Sql Server 开窗函数Over()的使用实例详解

建立实例所需的表

CREATE TABLE [dbo].[Products](
    [ProductID][char](20) NOT NULL,
    [ProductName][varchar](20) NOT NULL,
    ProductType varchar(20),
    Price INT 
PRIMARY KEY CLUSTERED ( [ProductID]ASC)WITH(IGNORE_DUP_KEY=OFF)ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO Products(ProductID,ProductName,ProductType,Price)
select 1,'name1','P1',20 union all
select 2,'name2','P1',30 union all
select 3,'name3','P2',15 union all
select 4,'name4','P2',25 UNION ALL
SELECT 5,'name5','p3',13 UNION ALL 
SELECT 6,'name6','p3',14 UNION ALL 
SELECT 7,'name7','p3',15 UNION ALL 
SELECT 8,'name8','p3',31 UNION ALL 
SELECT 9,'name9','p4',45 UNION ALL 
SELECT 10,'name10','p4',58 UNION ALL 
SELECT 11,'name11','p5',16 UNION ALL 
SELECT 12,'name12','p6',48 UNION ALL 
SELECT 13,'name13','p7',66 UNION ALL 
SELECT 14,'name14','p8',8 UNION ALL 
SELECT 15,'name15','p8',71 

 

查询要求：查出每类产品中价格最高的信息

--做法一：找到每个组里,价格最大的值；然后再找出每个组里价格等于这个值的
--缺点:要进行一次join    

SELECT T1.* FROM Products T1
INNER JOIN (SELECT ProductType,MAX(Price) Price FROM dbo.Products WHERE 1=1
GROUP BY ProductType) T2 ON t1.ProductType = t2.ProductType
WHERE t1.Price = T2.Price

 

--做法二：利用over()，将统计信息计算出来，然后直接筛选结果集。
--over() 可以让函数(包括聚合函数)与行一起输出。

;with cte as(select *, max(Price) over(partition by (ProductType)) MaxPrice from Products)
select ProductID,ProductName,ProductType,Price from cte where Price = MaxPrice
 order by ProductType

-over() 的语法为：over([patition by ] <order by >)。需要注意的是，over() 前面是一个函数，如果是聚合函数，那么order by 不能一起使用。

--over() 的另一常用情景是与 row_number() 一起用于分页。

 

现在来介绍一下开窗函数。

 

窗口函数OVER（）指定一组行，开窗函数计算从窗口函数输出的结果集中各行的值。

开窗函数不需要使用GROUP BY就可以对数据进行分组，还可以同时返回基础行的列和聚合列。 

1．排名开窗函数

ROW_NUMBER、DENSE_RANK、RANK、NTILE属于排名函数。

排名开窗函数可以单独使用ORDER BY 语句，也可以和PARTITION BY同时使用。

PARTITION BY用于将结果集进行分组，开窗函数应用于每一组。

ODER BY 指定排名开窗函数的顺序。在排名开窗函数中必须使用ORDER BY语句。

例如查询每个产品的定单，并按价格排序

 ;WITH ProductInfo AS
(
 SELECT ROW_NUMBER() OVER(PARTITION BY ProductType ORDER BY Price ) AS number,
 ProductID,ProductName,ProductType,Price FROM Products (NOLOCK) 
)
SELECT *
From ProductInfo 

窗口函数根据PARTITION BY语句按雇员ID对数据行分组，然后按照ORDER BY 语句排序，排名函数ROW_NUMBER()为每一组的数据分从1开始生成一个序号。

ROW_NUMBER（）为每一组的行按顺序生成一个唯一的序号

RANK（）也为每一组的行生成一个序号，与ROW_NUMBER()不同的是如果按照ORDER BY的排序，如果有相同的值会生成相同的序号，并且接下来的序号是不连序的。例如两个相同的行生成序号3，那么接下来会生成序号5。

DENSE_RANK（）和RANK（）类似，不同的是如果有相同的序号，那么接下来的序号不会间断。也就是说如果两个相同的行生成序号3，那么接下来生成的序号还是4。

NTILE (integer_expression) 按照指定的数目将数据进行分组，并为每一组生成一个序号。

 

2．聚合开窗函数

很多聚合函数都可以用作窗口函数的运算，如SUM,AVG,MAX,MIN。

聚合开窗函数只能使用PARTITION BY子句或都不带任何语句，ORDER BY不能与聚合开窗函数一同使用。

;WITH ProductInfo AS
(
SELECT COUNT(ProductID) OVER(PARTITION BY ProductType) AS TotalCount,
	ProductID,ProductName,ProductType,Price FROM Products (NOLOCK)
)
SELECT * From ProductInfo 

如果窗口函数不使用PARTITION BY 语句的话，那么就是不对数据进行分组，聚合函数计算所有的行的值

;WITH ProductInfo AS
(
SELECT COUNT(ProductID) OVER() AS Count,ProductID,ProductName,ProductType,Price FROM Products (NOLOCK)
)
SELECT * From ProductInfo