分析にウィンドウ関数を適用する
ヒント
詳細については、「 テキストと画像 」タブを参照してください。
多くの場合、分析クエリでは、個々の行の詳細を返しながら、複数の行にまたがる計算が必要になります。 従来の集計関数は、行をグループに折りたたみ、行レベルの情報を失います。 ウィンドウ関数は、結果セットを折りたたまずに、現在の行に関連する一連の行にわたって計算を実行することで、この課題を解決します。
ウィンドウ関数の構文について
ウィンドウ関数は、 OVER 句で定義された行の "ウィンドウ" 全体の値を計算します。 通常の集計関数とは異なり、ウィンドウ関数は行を 1 つの出力行にグループ化しません。 代わりに、結果内のすべての元の行を保持しながら、関連する行間で値を計算します。
ウィンドウ関数の一般的な構文は次のとおりです。
function_name(arguments) OVER (
[PARTITION BY partition_expression]
[ORDER BY order_expression [ASC | DESC]]
[ROWS | RANGE frame_specification]
)
OVER句コンポーネントは、ウィンドウの定義方法を制御します。
- PARTITION BY: 計算のために行をグループ (パーティション) に分割します
- ORDER BY: 各パーティション内の行の論理順序を決定します
- ROWS/RANGE: 現在の行に対するフレーム境界を定義します
次のクエリは、顧客ごとの注文金額の実行中の合計を計算する単純なウィンドウ関数を示しています。
SELECT
CustomerID,
SalesOrderID,
OrderDate,
TotalDue,
SUM(TotalDue) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS RunningTotal
FROM SalesLT.SalesOrderHeader
ORDER BY CustomerID, OrderDate;
注
フレーム指定なしで ORDER BY 句でOVERを指定すると、集計関数の既定のフレームがRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWされます。 これにより、累積計算が作成されます。
ランク付け関数を使用する
ランク付け関数は、パーティション内での位置に基づいて行に連続する番号を割り当てます。 SQL Server には、4 つのランク付け関数が用意されています。 各関数は、次の方法で異なる関係を処理します。
ROW_NUMBER() では、各行に一意の連続番号が割り当てられ、関連付けられた値に対しても重複はありません。
SELECT
ProductID,
Name,
ListPrice,
ROW_NUMBER() OVER (ORDER BY ListPrice DESC) AS PriceRank
FROM SalesLT.Product
WHERE ListPrice > 0;
結果セットは次のようになります。
ProductID Name ListPrice PriceRank
--------- ------------------------- --------- ---------
749 Road-150 Red, 62 3578.27 1
750 Road-150 Red, 44 3578.27 2
751 Road-150 Red, 48 3578.27 3
771 Mountain-100 Silver, 38 3399.99 4
このクエリでは、すべての製品が価格によって最高から最低の順にランク付けされます。 複数の製品が同じ価格を共有しているかどうかに関係なく、各製品は一意の番号を受け取ります。
RANK() は、同順位の値には同じランクを割り当て、その後、同順位の数を考慮して順位番号をスキップします。
SELECT
ProductID,
Name,
ListPrice,
RANK() OVER (ORDER BY ListPrice DESC) AS PriceRank
FROM SalesLT.Product
WHERE ListPrice > 0;
結果セットは次のようになります。
ProductID Name ListPrice PriceRank
--------- ------------------------- --------- ---------
749 Road-150 Red, 62 3578.27 1
750 Road-150 Red, 44 3578.27 1
751 Road-150 Red, 48 3578.27 1
771 Mountain-100 Silver, 38 3399.99 4
2 つの製品の価格が同じ場合、両方とも同じランクを受け取ります。 次の製品の順位は、上位にランク付けされた製品の合計数を反映し、シーケンスにギャップが生じます。
DENSE_RANK() は同じランクを関連付けられた値に割り当てますが、数値はスキップしません。
SELECT
ProductID,
Name,
ListPrice,
DENSE_RANK() OVER (ORDER BY ListPrice DESC) AS PriceRank
FROM SalesLT.Product
WHERE ListPrice > 0;
結果セットは次のようになります。
ProductID Name ListPrice PriceRank
--------- ------------------------- --------- ---------
749 Road-150 Red, 62 3578.27 1
750 Road-150 Red, 44 3578.27 1
751 Road-150 Red, 48 3578.27 1
771 Mountain-100 Silver, 38 3399.99 2
RANK()と同様に、関連付けられた値は同じランクを共有します。 ただし、 DENSE_RANK() は次の連続する数値で続行されるため、それを使用して個別の価格レベルをカウントできます。
NTILE(n) は、指定された数のほぼ等しいグループに行を分散します。
SELECT
ProductID,
Name,
ListPrice,
NTILE(4) OVER (ORDER BY ListPrice DESC) AS PriceQuartile
FROM SalesLT.Product
WHERE ListPrice > 0;
結果セットは次のようになります。
ProductID Name ListPrice PriceQuartile
--------- --------------------------- --------- -------------
749 Road-150 Red, 62 3578.27 1
771 Mountain-100 Silver, 38 3399.99 1
722 LL Road Frame - Black, 58 337.22 2
859 Half-Finger Gloves, S 24.49 4
このクエリは、価格に基づいて製品を 4 つのグループに分割します。 最高価格の製品は四分位1で、最低価格の製品は四分位4です。 パーセンタイル分析または均等に作業を分散する場合は、 NTILE() を使用します。
PARTITION BYとランク付け関数を組み合わせることで、グループごとのランク付けが可能になります。
SELECT
pc.Name AS Category,
p.Name AS Product,
p.ListPrice,
ROW_NUMBER() OVER (
PARTITION BY p.ProductCategoryID
ORDER BY p.ListPrice DESC
) AS CategoryPriceRank
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ListPrice > 0;
結果セットは次のようになります。
Category Product ListPrice CategoryPriceRank
--------------- ------------------------- --------- -----------------
Road Bikes Road-150 Red, 62 3578.27 1
Road Bikes Road-150 Red, 44 3578.27 2
Mountain Bikes Mountain-100 Silver, 38 3399.99 1
Mountain Bikes Mountain-100 Black, 38 3374.99 2
このクエリは、各カテゴリ内の製品を個別にランク付けします。 ランク付けはカテゴリごとに 1 で再開されるため、 CategoryPriceRank = 1をフィルター処理することで、カテゴリごとに最もコストの高い製品を特定できます。
ヒント
ROW_NUMBER()は、ランクごとに 1 つの行が必要な場合に使用します (グループごとの上位 N の検索など)。 レポートの目的でタイ情報を保持する必要がある場合は、 RANK() または DENSE_RANK() を使用します。
集計ウィンドウ関数を適用する
SUM、AVG、COUNT、MIN、MAXなどの標準集計関数は、OVER句を追加することでウィンドウ関数として使用できます。 これにより、個々の行の詳細を保持しながら集計を計算できます。
次のクエリは、実行中の合計と累積集計を計算する方法を示しています。
SELECT
SalesOrderID,
OrderDate,
TotalDue,
SUM(TotalDue) OVER (ORDER BY OrderDate, SalesOrderID) AS RunningTotal,
AVG(TotalDue) OVER (ORDER BY OrderDate, SalesOrderID) AS RunningAverage,
COUNT(*) OVER (ORDER BY OrderDate, SalesOrderID) AS OrderNumber
FROM SalesLT.SalesOrderHeader
ORDER BY OrderDate, SalesOrderID;
結果セットは次のようになります。
SalesOrderID OrderDate TotalDue RunningTotal RunningAverage OrderNumber
------------ ---------- --------- ------------ -------------- -----------
71774 2008-06-01 972.785 972.785 972.785 1
71776 2008-06-01 87.083 1059.868 529.934 2
71780 2008-06-01 42452.65 43512.518 14504.172 3
71782 2008-06-01 43962.79 87475.308 21868.827 4
Important
ORDER BY句でOVERせずに集計ウィンドウ関数を使用する場合、この関数はパーティション全体にわたって計算されます。
ORDER BYを追加すると、パーティションの先頭から現在の行までの実行中の計算が作成されます。
ROWSを使用してウィンドウ フレームを定義するRANGE
ウィンドウ フレームを使用すると、現在の行を基準とする行を計算に含める必要がある行を正確に指定できます。
ROWS句は物理行をカウントし、RANGEは値が等しい行をグループ化します。
フレーム境界は、次を使用して指定できます。
-
UNBOUNDED PRECEDING: パーティションの開始から -
n PRECEDING: 現在の行より前のn行 -
CURRENT ROW: 現在の行 -
n FOLLOWING: 現在の行の次にn行 -
UNBOUNDED FOLLOWING: パーティションの終端へ
次のクエリでは、過去 3 つの注文の移動平均が計算されます。
SELECT
SalesOrderID,
OrderDate,
TotalDue,
AVG(TotalDue) OVER (
ORDER BY OrderDate
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS MovingAvg3Orders
FROM SalesLT.SalesOrderHeader
ORDER BY OrderDate;
結果セットは次のようになります。
SalesOrderID OrderDate TotalDue MovingAvg3Orders
------------ ---------- --------- ----------------
71774 2008-06-01 972.785 972.785
71776 2008-06-01 87.083 529.934
71780 2008-06-01 42452.65 14504.172
71782 2008-06-01 43962.79 28834.174
このクエリでは、現在の行とその前の 2 つの行を含めることで、3 次移動平均を計算します。 最初の行では 1 つの値しか使用できないため、平均は TotalDueと等しくなります。 3 行目のウィンドウには、3 つの行がすべて含まれます。
分析関数を使用する
分析関数を使用すると、自己結合やサブクエリを使用せずに、他の行からデータにアクセスできます。 これらの関数は、時系列分析、傾向検出、および現在の値と履歴または将来の値の比較に役立ちます。 概要を計算する集計ウィンドウ関数とは異なり、分析関数はウィンドウ内の特定の行から特定の値を取得します。
LAG() は次のように、前または後続の行から値にアクセスします LEAD()。
SELECT
SalesOrderID,
OrderDate,
TotalDue,
LAG(TotalDue, 1, 0) OVER (ORDER BY OrderDate) AS PreviousOrderTotal,
LEAD(TotalDue, 1, 0) OVER (ORDER BY OrderDate) AS NextOrderTotal,
TotalDue - LAG(TotalDue, 1, 0) OVER (ORDER BY OrderDate) AS ChangeFromPrevious
FROM SalesLT.SalesOrderHeader
ORDER BY OrderDate;
結果セットは次のようになります。
SalesOrderID OrderDate TotalDue PreviousOrderTotal NextOrderTotal ChangeFromPrevious
------------ ---------- --------- ------------------ -------------- ------------------
71774 2008-06-01 972.785 0 87.083 972.785
71776 2008-06-01 87.083 972.785 42452.65 -885.702
71780 2008-06-01 42452.65 87.083 43962.79 42365.567
71782 2008-06-01 43962.79 42452.65 0 1510.14
LAG() は前の行から値を取得し、 LEAD() は次の行から取得します。 2 番目のパラメーターは、前を参照する行の数 (既定値は 1) を指定し、3 番目のパラメーターは行が存在しない場合 (たとえば、 LAG()を持つ最初の行) に既定値を提供します。 これらの関数を使用して、期間の変化の計算、傾向の特定、または連続データの異常の検出を行います。
FIRST_VALUE() と LAST_VALUE() フレームの最初または最後の行からの値を返します。
SELECT
ProductID,
Name,
ListPrice,
ProductCategoryID,
FIRST_VALUE(Name) OVER (
PARTITION BY ProductCategoryID
ORDER BY ListPrice DESC
) AS MostExpensiveInCategory,
LAST_VALUE(Name) OVER (
PARTITION BY ProductCategoryID
ORDER BY ListPrice DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS LeastExpensiveInCategory
FROM SalesLT.Product
WHERE ListPrice > 0;
結果セットは次のようになります。
ProductID Name ListPrice ProductCategoryID MostExpensiveInCategory LeastExpensiveInCategory
--------- ------------------------- --------- ----------------- ------------------------ ------------------------
749 Road-150 Red, 62 3578.27 5 Road-150 Red, 62 LL Road Frame - Red, 58
750 Road-150 Red, 44 3578.27 5 Road-150 Red, 62 LL Road Frame - Red, 58
722 LL Road Frame - Red, 58 337.22 5 Road-150 Red, 62 LL Road Frame - Red, 58
771 Mountain-100 Silver, 38 3399.99 6 Mountain-100 Silver, 38 Mountain-500 Black, 52
FIRST_VALUE() は、順序付けされたウィンドウの最初の行から値を返します。この場合、カテゴリごとに最も高価な製品です。
LAST_VALUE() は最もコストの低い値を返しますが、すべての行を含めるには明示的なフレームが必要です。 これらの関数を使用すると、各行を、グループ内の最も高い値、最低値、ベースライン値などのベンチマーク値と比較するのに役立ちます。
注
LAST_VALUE() には、現在の行の後に行を含める明示的なフレーム指定が必要です。 これを指定しない場合、既定のフレームには現在の行までの行のみが含まれるため、現在 LAST_VALUE() 行の値が返されます。
PERCENT_RANK() と CUME_DIST() は 、パーティション内の相対位置を計算します。
SELECT
Name,
ListPrice,
PERCENT_RANK() OVER (ORDER BY ListPrice) AS PercentRank,
CUME_DIST() OVER (ORDER BY ListPrice) AS CumulativeDistribution
FROM SalesLT.Product
WHERE ListPrice > 0
ORDER BY ListPrice;
結果セットは次のようになります。
Name ListPrice PercentRank CumulativeDistribution
------------------------- --------- ----------- ----------------------
Patch Kit/8 Patches 2.29 0.0 0.0081
Road Tire Tube 3.99 0.0081 0.0162
Touring Tire Tube 4.99 0.0162 0.0243
Road-150 Red, 62 3578.27 0.9919 1.0
PERCENT_RANK() は、行の割合が低い値を示す 0 ~ 1 の値を返します (0 は最小値、1 は最も高い値を意味します)。
CUME_DIST() は累積分布を示し、現在の行以下の値を持つ行の割合を示します。 これらの関数は、パーセンタイル分析、外れ値の識別、または分布レポートの作成に使用します。
ウィンドウ関数の詳細については、「 ウィンドウ関数 (Transact-SQL) 」および「 ランク付け関数」を参照してください。