分析にウィンドウ関数を適用する

完了済み

ヒント

詳細については、「 テキストと画像 」タブを参照してください。

多くの場合、分析クエリでは、個々の行の詳細を返しながら、複数の行にまたがる計算が必要になります。 従来の集計関数は、行をグループに折りたたみ、行レベルの情報を失います。 ウィンドウ関数は、結果セットを折りたたまずに、現在の行に関連する一連の行にわたって計算を実行することで、この課題を解決します。

ウィンドウ関数の構文について

ウィンドウ関数は、 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) 」および「 ランク付け関数」を参照してください。