2 つのウィンドウ (現在のローと直前のローのそれぞれに対するウィンドウ) を使用すると、隣接ローのデルタ (変化量) を計算できます。たとえば、次のクエリの結果では、ある従業員とその直前の従業員の給与のデルタ (Delta) を計算します。

SELECT EmployeeID AS EmployeeNumber, 
   Surname AS LastName,
   SUM( Salary ) OVER ( ORDER BY BirthDate 
      ROWS BETWEEN CURRENT ROW AND CURRENT ROW ) 
      AS CurrentRow,
   SUM( Salary ) OVER ( ORDER BY BirthDate
      ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING ) 
      AS PreviousRow,
   ( CurrentRow - PreviousRow ) AS Delta
FROM Employees
WHERE State IN ( 'NY' );

CurrentRow ウィンドウでは、ウィンドウのサイズが ROWS BETWEEN CURRENT ROW AND CURRENT ROW に設定されているため、SUM は現在のローのみに対して実行されます。同様に、PreviousRow ウィンドウでは、ウィンドウのサイズが ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING に設定されているため、SUM は直前のローのみに対して実行されます。最初のローには先行するローがないため、このローの PreviousRow の値は NULL です。そのため Delta の値も NULL になります。

次のクエリを考えてみます。このクエリは、データベース内で製品ごとに最上位の営業担当者 (総売り上げで定義) をリストします。

SELECT s.ProductID AS Products, o.SalesRepresentative, 
     SUM( s.Quantity ) AS total_quantity,
     SUM( s.Quantity * p.UnitPrice ) AS total_sales
  FROM SalesOrders o KEY JOIN SalesOrderItems s 
    KEY JOIN Products p
  GROUP BY s.ProductID, o.SalesRepresentative
  HAVING total_sales = ( 
    SELECT First SUM( s2.Quantity * p2.UnitPrice ) 
        AS sum_sales
      FROM SalesOrders o2 KEY JOIN
        SalesOrderItems s2 KEY JOIN Products p2
      WHERE s2.ProductID = s.ProductID
      GROUP BY o2.SalesRepresentative
      ORDER BY sum_sales DESC )
  ORDER BY s.ProductID;

このクエリは、次の結果を返します。

元のクエリは、ProductID をサブクエリの相関外部参照として、ある製品の最高売り上げを判断する相関サブクエリを使用して作成されています。ただし、この場合のようにネストされたクエリを使用すると、コストの高いオプションになることがあります。これは、サブクエリに GROUP BY 句だけでなく、GROUP BY 句内の ORDER BY 句も含まれるからです。そのため、クエリオプティマイザーは、同じセマンティックを保持したままになり、このネストされたクエリをジョインとして書き換えることができません。したがって、クエリの実行中は、外部ブロック内で計算される派生ローごとにサブクエリが評価されます。

グラフィカルプランでコストの高い Filter 述部に注意してください。オプティマイザーは、クエリの実行コストの 99% がこのプラン演算子に起因するものであると推定します。サブクエリのプランでは、メインブロックのフィルター演算子のコストが高くなる理由を明確にしています。サブクエリには、ネストループジョインが 2 つと、ハッシュ GROUP BY 演算が 1 つ、ソートが 1 つ含まれます。

ランキング関数を使用した同じクエリの書き換えでは、同じ結果が非常に効率よく計算されます。

SELECT v.ProductID, v.SalesRepresentative, 
  v.total_quantity, v.total_sales
  FROM ( SELECT o.SalesRepresentative, s.ProductID,
            SUM( s.Quantity ) AS total_quantity,
            SUM( s.Quantity * p.UnitPrice ) AS total_sales,
            RANK() OVER ( PARTITION BY s.ProductID 
              ORDER BY SUM( s.Quantity * p.UnitPrice ) DESC ) 
              AS sales_ranking
           FROM SalesOrders o KEY JOIN SalesOrderItems s KEY JOIN Products p
           GROUP BY o.SalesRepresentative, s.ProductID ) 
           AS v
  WHERE sales_ranking = 1
  ORDER BY v.ProductID;

この書き換えられたクエリは、プランがより単純になります。

GROUP BY 句が処理されてから SELECT リスト項目の評価とクエリの ORDER BY 句の処理が行われるまでに、ウィンドウ演算子が計算されます。グラフィカルなプランでわかるように、3 つのテーブルのジョイン後、ジョインされたローは SalesRepresentative 属性と ProductID 属性の組み合わせでグループ化されます。したがって、total_quantity と total_sales の SUM 集合関数は、SalesRepresentative と ProductID の組み合わせごとに計算できます。

GROUP BY 句の評価に続いて、中間結果のローを total_sales の降順にランク付けするために、ウィンドウを使用して RANK 関数が計算されます。WINDOW 指定には PARTITION BY 句が含まれます。それによって、GROUP BY 句の結果が、今度は ProductID ごとに再分割 (再グループ化) されます。そのため、RANK 関数は、総売り上げの降順で製品ごとにローをランク付けしますが、その製品を販売した営業担当者はすべてまとめられます。このランキングでは、最上位の営業担当者を特定するには、ランクが 1 ではない派生テーブルのローを拒否するように、派生テーブルの結果を制限することのみが必要となります。同順の場合 (結果セットのロー 7 と 8)、RANK は同じ値を返します。したがって、690 と 949 の両方の営業担当者が最終結果に出現します。

• SUM 関数 [集合]