程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> 更多數據庫知識 >> sql server如何利用開窗函數over()進行分組統計,sqlover

sql server如何利用開窗函數over()進行分組統計,sqlover

編輯:更多數據庫知識

sql server如何利用開窗函數over()進行分組統計,sqlover


這是一道常見的面試題,在實際項目中經常會用到。

需求:求出以產品類別為分組,各個分組裡價格最高的產品信息。

實現過程如下:

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利用開窗函數進行分組統計的方法,謝謝大家的閱讀。

您可能感興趣的文章:

  • SQLserver 實現分組統計查詢(按月、小時分組)
  • SQL2005 學習筆記 窗口函數(OVER)
  • sqlserver2005使用row_number() over分頁的實現方法
  • SQL2005利用ROW_NUMBER() OVER實現分頁功能
  • Sql Server 分組統計並合計總數及WITH ROLLUP應用
  • Sql Server:多行合並成一行,並做分組統計的兩個方法
  • SQL進行排序、分組、統計的10個新技巧分享
  • 開窗函數有淺入深詳解(一)

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved