sql server若何應用開窗函數over()停止分組統計

sql server若何應用開窗函數over()停止分組統計。本站提示廣大學習愛好者：（sql server若何應用開窗函數over()停止分組統計）文章只能為提供參考，不一定能成為您想要的結果。以下是sql server若何應用開窗函數over()停止分組統計正文

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

需求：求出以產物種別為分組，各個分組裡價錢最高的產物信息。

完成進程以下：

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

--測試數據

insert @t
select 1,'name1','P1',3 union all
select 2,'name2','P1',5 union all
select 3,'name3','P2',4 union all
select 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 ,OrderDate

From 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應用開窗函數停止分組統計的辦法，感謝年夜家的浏覽。