sql server中怎么使用over()函數實現分組統計

本篇文章為大家展示了sql server中怎么使用over()函數實現分組統計，內容簡明扼要并且容易理解，絕對能使你眼前一亮，通過這篇文章的詳細介紹希望你能有所收獲。

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

實現過程如下：

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（）指定一組行，開窗函數計算從窗口函數輸出的結果集中各行的值。

開窗函數不需要使用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中怎么使用over()函數實現分組統計，你們學到知識或技能了嗎？如果還想學到更多技能或者豐富自己的知識儲備，歡迎關注億速云行業資訊頻道。