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

--方法1:各グループの中で、価格が最大の値を見つけます.そして、各グループの価格がこの値に等しいことを見つけます.欠点:joinを1回行うことです.

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

--方法2: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])です.over()の前には関数があり、集約関数であればorder byは一緒に使用できません.--over()のもう一つの一般的なシナリオはrow_とnumber()は一緒にページングに使用されます.
窓を開ける関数を紹介します.
ウィンドウ関数OVER()は、ウィンドウ関数から出力された結果セットの各行の値を計算する一連の行を指定します. 
開窓関数はGROUP BYを使用することなくデータをグループ化し、ベースラインの列と集約列を同時に返すことができます.  
1.ランキング開窓関数
ROW_NUMBER、DENSE_RANK、RANK、NTILEはランキング関数に属する.
ランキング開窓関数は、ORDER BY文を単独で使用してもよいし、PARTION 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

ウィンドウ関数は、PARTION BY文に従って従業員IDでデータ行をグループ化し、ORDER BY文に従ってソートされ、ランキング関数ROW_NUMBER()は,各グループのデータに対して1から1つのシーケンス番号を生成する. 
ROW_NUMBER()は、各グループのローに対して一意のシーケンス番号を順番に生成します.
RANK()はまた、各グループのローに対して、ROW_とともにシーケンス番号を生成する.NUMBER()が異なるのは、ORDER BYのソートに従った場合、同じ値がある場合は同じシーケンス番号が生成され、次のシーケンス番号はシーケンスされません.たとえば、2つの同じ行がシーケンス番号3を生成すると、次にシーケンス番号5が生成されます.
DENSE_RANK()はRANK()と同様であり、異なるのは、同じシーケンス番号がある場合、次のシーケンス番号は途切れないことである.すなわち、2つの同じ行がシーケンス番号3を生成する場合、次に生成されるシーケンス番号は4である.
NTILE(integer_expression)は、指定された数でデータをグループ化し、各グループにシーケンス番号を生成します.
2.集約開窓関数
多くの集約関数は、SUM、AVG、MAX、MINなどのウィンドウ関数の演算として使用できます.
集約開窓関数は、PARTION 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 

ウィンドウ関数がPARTION BY文を使用しない場合、データをグループ化せず、集約関数はすべてのローの値を計算します.

WITH OrderInfo AS

(

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

)

以上が本稿のすべてですが、sql serverが開窓関数を利用してグループ統計を行う方法を学んでほしいです.読んでくれてありがとう.