sql server中怎么使用over()函數(shù)實現(xiàn)分組統(tǒng)計

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

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

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

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

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

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

--做法一：找到每個組里,價格最大的值；然后再找出每個組里價格等于這個值的--缺點:要進(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)計信息計算出來，然后直接篩選結(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ù)計算從窗口函數(shù)輸出的結(jié)果集中各行的值。

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

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

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

排名開窗函數(shù)可以單獨使用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 ,OrderDateFrom 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,TotalCountFrom OrderInfo ORDER BY EmployeeID

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

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

上述內(nèi)容就是sql server中怎么使用over()函數(shù)實現(xiàn)分組統(tǒng)計，你們學(xué)到知識或技能了嗎？如果還想學(xué)到更多技能或者豐富自己的知識儲備，歡迎關(guān)注億速云行業(yè)資訊頻道。