Sql Server資料庫開窗函式Over()的使用例項詳解

天豪發表於2019-01-01
SQLServer資料庫函式
文章主要介紹了Sql Server 開窗函式Over()的使用，非常不錯，具有一定的參考借鑑價值,需要的朋友可以參考下

利用over()，將統計資訊計算出來，然後直接篩選結果集

  1. declare @t table(
  2. ProductID int,
  3. ProductName varchar(20),
  4. ProductType varchar(20),
  5. Price int)
  6. insert @t
  7. select 1,'name1','P1',3 union all
  8. select 2,'name2','P1',5 union all
  9. select 3,'name3','P2',4 union all
  10. select 4,'name4','P2',4

查詢要求：查出每類產品中價格最高的資訊

--做法一：找到每個組裡,價格最大的值；然後再找出每個組裡價格等於這個值的
--缺點:要進行一次join    

  1. select t1.* from @t t1
  2.  join (select ProductType, max(Price) Price from @t group by ProductType) t2
  3.  on t1.ProductType = t2.ProductType
  4.  where t1.Price = t2.Price
  5.  order by ProductType

--做法二：利用over()，將統計資訊計算出來，然後直接篩選結果集。
--over() 可以讓函式(包括聚合函式)與行一起輸出。 

  1. ;with cte as(select *, max(Price) over(partition by (ProductType)) MaxPrice from @t)
  2. select ProductID,ProductName,ProductType,Price from cte where Price = MaxPrice
  3.  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語句。

例如查詢每個僱員的定單，並按時間排序

  1. ;WITH OrderInfo AS
  2. (
  3.  SELECT ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY OrderDate) AS Number,
  4.  OrderID,CustomerID, EmployeeID,OrderDate FROM Orders (NOLOCK)
  5. )
  6. SELECT Number,OrderID,CustomerID, EmployeeID ,OrderDate
  7. 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不能與聚合開窗函式一同使用。

例如，查詢僱員的定單總數及定單資訊

  1. WITH OrderInfo AS
  2. (
  3. SELECT COUNT(OrderID) OVER(PARTITION BY EmployeeID) AS TotalCount,OrderID,CustomerID, EmployeeID,OrderDate FROM Orders (NOLOCK)
  4. )
  5. SELECT OrderID,CustomerID, EmployeeID ,OrderDate,TotalCount From OrderInfo ORDER BY EmployeeID

如果視窗函式不使用PARTITION BY 語句的話，那麼就是不對資料進行分組，聚合函式計算所有的行的值

  1. WITH OrderInfo AS
  2. (
  3. SELECT COUNT(OrderID) OVER() AS Count,OrderID,CustomerID, EmployeeID,OrderDate FROM Orders (NOLOCK)
  4. )

總結

以上所述是小編給大家介紹的Sql Server 開窗函式Over()的使用例項詳解，希望對大家有所幫助，

相關文章