Microsoft SQL Server
OVER句

パラメーター

備考

OVERでの集計関数の使用

車のテーブルを使用して、我々は修理のために車を持ってきた多くの時間（COUNT）を費やし、それぞれの消費者が費やした合計、最大、最小、および平均金額を計算します。

Id CustomerId MechanicIdモデルのステータス総コスト

SELECT CustomerId,  
       SUM(TotalCost) OVER(PARTITION BY CustomerId) AS Total,
       AVG(TotalCost) OVER(PARTITION BY CustomerId) AS Avg,
       COUNT(TotalCost) OVER(PARTITION BY CustomerId) AS Count,
       MIN(TotalCost) OVER(PARTITION BY CustomerId) AS Min,
       MAX(TotalCost) OVER(PARTITION BY CustomerId) AS Max
  FROM CarsTable
 WHERE Status = 'READY'

この方法でOVERを使用すると、返された行が集計されないことに注意してください。上記のクエリは次の結果を返します：

重複した行は、レポートの目的には有効ではありません。

単純にデータを集計する場合は、適切な集計関数EgとともにGROUP BY句を使用する方が良いでしょう。

SELECT CustomerId,  
       SUM(TotalCost) AS Total,
       AVG(TotalCost) AS Avg,
       COUNT(TotalCost) AS Count,
       MIN(TotalCost) AS Min,
       MAX(TotalCost)  AS Max
  FROM CarsTable
 WHERE Status = 'READY'
GROUP BY CustomerId

累積合計

アイテム販売テーブルを使用して、 アイテムの売上が日付を通じてどのように増加しているかを調べます。これを行うために、商品注文ごとの合計売上の累積合計を販売日で計算します。

SELECT item_id, sale_Date 
       SUM(quantity * price) OVER(PARTITION BY item_id ORDER BY sale_Date ROWS BETWEEN UNBOUNDED PRECEDING) AS SalesTotal
  FROM SalesTable

集計機能を使用して最新のレコードを検索する

ライブラリデータベースを使用して、著者ごとにデータベースに最後に追加された本を見つけようとします。この簡単な例では、追加される各レコードのIDが常に増加すると仮定します。

SELECT MostRecentBook.Name, MostRecentBook.Title
FROM ( SELECT Authors.Name,
              Books.Title,
              RANK() OVER (PARTITION BY Authors.Id ORDER BY Books.Id DESC) AS NewestRank
       FROM Authors
       JOIN Books ON Books.AuthorId = Authors.Id
     ) MostRecentBook
WHERE MostRecentBook.NewestRank = 1

RANKの代わりに2つの他の機能を使用して注文することができます。前の例では結果は同じですが、順序によって各ランクに複数の行が与えられたときの結果は異なります。

• RANK() ：重複が同じランクを取得し、次のランクは前のランクの重複の数を考慮に入れます
• DENSE_RANK() ：重複は同じランクを取得し、次のランクは常に前のランクより1高い
• ROW_NUMBER() ：各行にユニークな「ランク」を与え、ランダムにランク付けする

たとえば、テーブルに一意でない列CreationDateがあり、それに基づいて順序付けが行われた場合、次のクエリが実行されます。

SELECT Authors.Name,
       Books.Title,
       Books.CreationDate,
       RANK() OVER (PARTITION BY Authors.Id ORDER BY Books.CreationDate DESC) AS RANK,
       DENSE_RANK() OVER (PARTITION BY Authors.Id ORDER BY Books.CreationDate DESC) AS DENSE_RANK,
       ROW_NUMBER() OVER (PARTITION BY Authors.Id ORDER BY Books.CreationDate DESC) AS ROW_NUMBER,
FROM Authors
JOIN Books ON Books.AuthorId = Authors.Id

結果：

NTILEを使用して等分割されたバケットにデータを分割する

いくつかの試験の試験のスコアがあり、試験ごとに四分位数に分割したいとします。

-- Setup data:
declare @values table(Id int identity(1,1) primary key, [Value] float, ExamId int)
insert into @values ([Value], ExamId) values
(65, 1), (40, 1), (99, 1), (100, 1), (90, 1), -- Exam 1 Scores
(91, 2), (88, 2), (83, 2), (91, 2), (78, 2), (67, 2), (77, 2) -- Exam 2 Scores

-- Separate into four buckets per exam:
select ExamId, 
       ntile(4) over (partition by ExamId order by [Value] desc) as Quartile, 
       Value, Id 
from @values 
order by ExamId, Quartile

ntileは本当にバケツのセット数が必要で、それぞれがほぼ同じレベルに満たされているときに効果的です。単純にntile(100)を使用することで、これらのスコアをパーセンタイルに分けることは自明であることに注意してください。