Sql Server中如何使用Over()函數(shù)

今天就跟大家聊聊有關(guān)Sql Server中如何使用Over()函數(shù)，可能很多人都不太了解，為了讓大家更加了解，小編給大家總結(jié)了以下內(nèi)容，希望大家根據(jù)這篇文章可以有所收獲。

利用over()，將統(tǒng)計信息計算出來，然后直接篩選結(jié)果集

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

查詢要求：查出每類產(chǎ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ù)進行分組，還可以同時返回基礎(chǔ)行的列和聚合列。

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

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

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

PARTITION BY用于將結(jié)果集進行分組，開窗函數(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ù)進行分組，并為每一組生成一個序號。

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

很多聚合函數(shù)都可以用作窗口函數(shù)的運算，如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ù)進行分組，聚合函數(shù)計算所有的行的值

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

看完上述內(nèi)容，你們對Sql Server中如何使用Over()函數(shù)有進一步的了解嗎？如果還想了解更多知識或者相關(guān)內(nèi)容，請關(guān)注億速云行業(yè)資訊頻道，感謝大家的支持。