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

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

利用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,TotalCount From 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()函數有進一步的了解嗎？如果還想了解更多知識或者相關內容，請關注億速云行業資訊頻道，感謝大家的支持。