這是一道常見的面試題，在實(shí)際項(xiàng)目中經(jīng)常會用到。

需求：求出以產(chǎn)品類別為分組，各個分組里價格最高的產(chǎn)品信息。

實(shí)現(xiàn)過程如下：

declare @t table(
ProductID int,
ProductName varchar(20),
ProductType varchar(20),
Price int)

--測試數(shù)據(jù)

insert @t
select 1,'name1','P1',3 union all
select 2,'name2','P1',5 union all
select 3,'name3','P2',4 union all
select 4,'name4','P2',4

--做法一：找到每個組里,價格最大的值；然后再找出每個組里價格等于這個值的
--缺點(diǎn):要進(jìn)行一次join

select t1.*
 from @t t1
 join (select ProductType,
 max(Price) Price
 from @t
 group by ProductType) t2 on t1.ProductType = t2.ProductType
 where t1.Price = t2.Price
 order by ProductType

--做法二：利用over()，將統(tǒng)計(jì)信息計(jì)算出來，然后直接篩選結(jié)果集。
--over() 可以讓函數(shù)(包括聚合函數(shù))與行一起輸出。

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

--over() 的語法為：over([patition by ] <order by >)。需要注意的是，over() 前面是一個函數(shù)，如果是聚合函數(shù)，那么order by 不能一起使用。
--over() 的另一常用情景是與 row_number() 一起用于分頁。

現(xiàn)在來介紹一下開窗函數(shù)。

窗口函數(shù)OVER（）指定一組行，開窗函數(shù)計(jì)算從窗口函數(shù)輸出的結(jié)果集中各行的值。

開窗函數(shù)不需要使用GROUP BY就可以對數(shù)據(jù)進(jìn)行分組，還可以同時返回基礎(chǔ)行的列和聚合列。

1．排名開窗函數(shù)

ROW_NUMBER、DENSE_RANK、RANK、NTILE屬于排名函數(shù)。

排名開窗函數(shù)可以單獨(dú)使用ORDER BY 語句，也可以和PARTITION BY同時使用。

PARTITION BY用于將結(jié)果集進(jìn)行分組，開窗函數(shù)應(yīng)用于每一組。

ODER BY 指定排名開窗函數(shù)的順序。在排名開窗函數(shù)中必須使用ORDER BY語句。

例如查詢每個雇員的定單，并按時間排序

WITH OrderInfo AS
(
 SELECT ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY OrderDate) AS Number,
 OrderID,CustomerID, EmployeeID,OrderDate FROM Orders (NOLOCK)
)

SELECT Number,OrderID,CustomerID, EmployeeID ,OrderDate
From OrderInfo WHERE Number BETWEEN 0 AND 10

窗口函數(shù)根據(jù)PARTITION BY語句按雇員ID對數(shù)據(jù)行分組，然后按照ORDER BY 語句排序，排名函數(shù)ROW_NUMBER()為每一組的數(shù)據(jù)分從1開始生成一個序號。

ROW_NUMBER（）為每一組的行按順序生成一個唯一的序號

RANK（）也為每一組的行生成一個序號，與ROW_NUMBER()不同的是如果按照ORDER BY的排序，如果有相同的值會生成相同的序號，并且接下來的序號是不連序的。例如兩個相同的行生成序號3，那么接下來會生成序號5。

DENSE_RANK（）和RANK（）類似，不同的是如果有相同的序號，那么接下來的序號不會間斷。也就是說如果兩個相同的行生成序號3，那么接下來生成的序號還是4。

NTILE (integer_expression) 按照指定的數(shù)目將數(shù)據(jù)進(jìn)行分組，并為每一組生成一個序號。

2．聚合開窗函數(shù)

很多聚合函數(shù)都可以用作窗口函數(shù)的運(yùn)算，如SUM,AVG,MAX,MIN。

聚合開窗函數(shù)只能使用PARTITION BY子句或都不帶任何語句，ORDER BY不能與聚合開窗函數(shù)一同使用。

例如，查詢雇員的定單總數(shù)及定單信息

WITH OrderInfo AS
(
SELECT COUNT(OrderID) OVER(PARTITION BY EmployeeID) AS TotalCount,OrderID,CustomerID, EmployeeID,OrderDate FROM Orders (NOLOCK)
)
SELECT OrderID,CustomerID, EmployeeID ,OrderDate,TotalCount
From OrderInfo ORDER BY EmployeeID 

如果窗口函數(shù)不使用PARTITION BY 語句的話，那么就是不對數(shù)據(jù)進(jìn)行分組，聚合函數(shù)計(jì)算所有的行的值。

WITH OrderInfo AS
(
 SELECT COUNT(OrderID) OVER() AS Count,OrderID,CustomerID, EmployeeID,OrderDate FROM Orders (NOLOCK)
)

以上就是本文的全部內(nèi)容，希望大家可以學(xué)會sql server利用開窗函數(shù)進(jìn)行分組統(tǒng)計(jì)的方法，謝謝大家的閱讀。

版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非maisonbaluchon.cn所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。