sql server如何利用開窗函數over()進行分組統計_MsSql

這是一道常見的面試題，在實際項目中經常會用到。

需求：求出以產品類別為分組，各個分組裡價格最高的產品資訊。

實現過程如下：

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

--測試資料

insert @tselect 1,'name1','P1',3 union allselect 2,'name2','P1',5 union allselect 3,'name3','P2',4 union allselect 4,'name4','P2',4

--做法一：找到每個組裡,價格最大的值；然後再找出每個組裡價格等於這個值的
--缺點:要進行一次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()，將統計資訊計算出來，然後直接篩選結果集。
--over() 可以讓函數(包括彙總函式)與行一起輸出。

;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() 前面是一個函數，如果是彙總函式，那麼order by 不能一起使用。
--over() 的另一常用情景是與 row_number() 一起用於分頁。

現在來介紹一下開窗函數。

視窗函數OVER（）指定一組行，開窗Function Compute從視窗函數輸出的結果集中各行的值。 

開窗函數不需要使用GROUP BY就可以對資料進行分組，還可以同時返回基礎行的列和彙總列。  

1．排名開窗函數

ROW_NUMBER、DENSE_RANK、RANK、NTILE屬於次序函數。

排名開窗函數可以單獨使用ORDER BY 語句，也可以和PARTITION BY同時使用。

PARTITION BY用於將結果集進行分組，開窗函數應用於每一組。

ODER BY 指定排名開窗函數的順序。在排名開窗函數中必須使用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 ,OrderDateFrom OrderInfo WHERE Number BETWEEN 0 AND 10

視窗函數根據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 OrderInfo AS(SELECT COUNT(OrderID) OVER(PARTITION BY EmployeeID) AS TotalCount,OrderID,CustomerID, EmployeeID,OrderDate FROM Orders (NOLOCK))SELECT OrderID,CustomerID, EmployeeID ,OrderDate,TotalCountFrom OrderInfo ORDER BY EmployeeID 

如果視窗函數不使用PARTITION BY 語句的話，那麼就是不對資料進行分組，彙總函式計算所有的行的值。

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

以上就是本文的全部內容，希望大家可以學會sql server利用開窗函數進行分組統計的方法，謝謝大家的閱讀。