MAX 関数の例

相関サブクエリの削除

場合によっては、特定のカラムの値を最大値や最小値と比較する必要があります。このようなクエリは、相関属性 (外部参照とも呼ばれる) のあるネストされたクエリとして作成されることがよくあります。たとえば、次のクエリでは、その製品の注文 1 回あたりの最大数が製品の在庫数を超えているような製品について、すべての注文を製品情報とともにリストします。

SELECT o.ID, o.OrderDate, p.*
FROM SalesOrders o, SalesOrderItems s, Products p
WHERE o.ID = s.ID AND s.ProductID = p.ID
       AND p.Quantity < ( SELECT MAX( s2.Quantity )
                         FROM SalesOrderItems s2
                         WHERE s2.ProductID = p.ID )
ORDER BY p.ID, o.ID;

このクエリのグラフィカルなプランは、下の図のようにプラン・ビューワに表示されます。このネストされたクエリが、クエリ・オプティマイザによってどのように変形されたかを確認してください。クエリは、Products テーブルと、Window 関数を含む派生テーブル (相関名 DT) のある SalesOrders テーブルとのジョインに変形されています。

オプティマイザに依存して相関サブクエリを派生テーブルによるジョインに変形するのではなく (セマンティック分析は複雑なので、この方法は単純な場合にしか使用できない)、Window 関数を使用して、このようなクエリを作成できます。

SELECT order_qty.ID, o.OrderDate, p.*
  FROM ( SELECT s.ID, s.ProductID,
           MAX( s.Quantity ) OVER ( 
             PARTITION BY s.ProductID
             ORDER BY s.ProductID ) 
           AS max_q
           FROM SalesOrderItems s )
  AS order_qty, Products p, SalesOrders o
  WHERE p.ID = ProductID 
    AND o.ID = order_qty.ID 
    AND p.Quantity < max_q
  ORDER BY p.ID, o.ID;