共通テーブル式

共通テーブル式は、WITH 句を使用して定義します。WITH 句は、SELECT 文内の SELECT キーワードに先行します。句の内容は、1 つの SELECT 文のスコープ内のみで認識される 1 つ以上のテンポラリビューを定義します。これらのテンポラリビューは、文内の他の場所から参照できます。この句の構文は、CREATE VIEW 文の構文とよく似ています。

クエリに複数の集合関数が含まれる場合や、ストアドプロシージャー内でプログラム変数を参照するビューがクエリによって定義される場合に、共通テーブル式を使用すると便利であり、共通テーブル式を使用する必要がある場合もあります。さらに、共通テーブル式は値のセットを一時的に格納する際に便利です。

例

どの部署の従業員数が最も多いかを特定する問題を例にとります。SQL Anywhere サンプルデータベースの Employees テーブルは、架空の会社で働くすべての従業員をリストし、それぞれがどの部署で働いているかを示します。次のクエリは、部署 ID コードと各部署の従業員の総数をリストします。

SELECT DepartmentID, COUNT( * ) AS n
FROM Employees
GROUP BY DepartmentID;

このクエリは、次に示すように従業員の最も多い部署を抽出するために使用できます。

SELECT DepartmentID, n
FROM ( SELECT DepartmentID, COUNT( * ) AS n
       FROM Employees GROUP BY DepartmentID ) AS a
WHERE a.n =
  ( SELECT MAX( n )
    FROM ( SELECT DepartmentID, COUNT( * ) AS n
           FROM Employees GROUP BY DepartmentID ) AS b );

この文は正確な結果をもたらしますが、いくつか欠点もあります。最初の欠点は、サブクエリの繰り返しによってこの文の効率が悪くなることです。2 つ目の欠点は、この文ではサブクエリ間の関係が明確でないことです。

このような問題を回避する 1 つの方法として、ビューを作成し、そのビューを使用してクエリを再作成します。この方法によって、前述の問題を回避できます。

CREATE VIEW CountEmployees( DepartmentID, n ) AS
   SELECT DepartmentID, COUNT( * ) AS n
   FROM Employees GROUP BY DepartmentID; 

SELECT DepartmentID, n
   FROM CountEmployees
   WHERE n = ( SELECT MAX( n )
               FROM CountEmployees );

この方法の欠点は、ビューの作成時にデータベースサーバーがシステムテーブルを更新しなければならないため、オーバーヘッドが発生することです。ビューが頻繁に使用される場合は、この方法は合理的です。ただし、ビューが特定の SELECT 文内で 1 回のみ使用される場合は、代わりに次のように共通テーブル式を使用することをおすすめします。

WITH CountEmployees( DepartmentID, n ) AS
  ( SELECT DepartmentID, COUNT( * ) AS n
    FROM Employees GROUP BY DepartmentID )
SELECT DepartmentID, n
FROM CountEmployees
WHERE n = ( SELECT MAX( n )
            FROM CountEmployees );

また、従業員の最も少ない部署を検索するようにクエリを変更すると、クエリが複数のローを返す場合があることがわかります。

WITH CountEmployees( DepartmentID, n ) AS
  ( SELECT DepartmentID, COUNT( * ) AS n
    FROM Employees GROUP BY DepartmentID )
SELECT DepartmentID, n
FROM CountEmployees
WHERE n = ( SELECT MIN( n )
            FROM CountEmployees );

SQL Anywhere サンプルデータベースでは、従業員数が最も少ない 9 人編成の部署は 2 つあります。

参照

複数の相関名
 複数のテーブル式
 共通テーブル式を使用できる条件
 共通テーブル式の一般的な使用例
 再帰共通テーブル式
 構成部品の問題
 最短距離の問題

DocCommentXchange で意見交換できます