GROUP BY句

GROUP BY句を指定すると、データベースで、選択した行を各行の式の値に基づいてグループ化し、各グループのサマリー情報を1行戻すことができます。GROUP BY句を省略した場合、問合せの結果全体が1つのグループとして扱われます。この句にCUBEまたはROLLUPを指定した場合、標準グループ化の他に超集合グループ化が結果に含まれます。

GROUP BY句の式では、次のことが可能です。

• 1つまたは複数の列を指定します。

• 算術演算、ROWID擬似列またはNULLを使用します。

• 日付、定数または動的パラメータを使用します。

• ROLLUPまたはCUBE句を使用すると、標準グループ化の他に超集合グループ化が結果で生成されます。超集合グループ化は、GROUP BY句で標準グループ化とともに返される、計算された小計および合計です。

• GROUPING SETS句を使用して、生成する超集合グループ化を指定します。

GROUP BY句を使用する場合、SelectListには集計関数およびGROUP BY句で参照される列のみを含めることができます。選択リストに構成要素*、TableName.*またはOwner.TableName.*が含まれている場合は、*に含まれているすべての列をGROUP BY句に含める必要があります。グループ化された行では、NULL値は同じ値とみなされます。他のすべての列が同じである場合、列中のすべてのNULL値は単一のグループにまとめられます。

ノート:

NULLグループ化を識別し、潜在的に超集合グループ化から削除するには、GROUPING関数を使用します。詳細は、「GROUPING」を参照してください。

SQL構文

GROUP BY句の一般的な構文は次のとおりです。

GROUP BY
 {Expression | RollupCubeClause | GroupingSetsClause }[,...]
 
GroupingSetsClause::=  GROUPING SETS 
 GroupingExpressionList | RollupCubeClause [,...]

RollupCubeClause
{ ROLLUP | CUBE } ( GroupingExpressionList ) }

GroupingExpressionList::=   
{ Expression | ExpressionList [, { Expression | ExpressionList } ] ...}
 
ExpressionList :: = ( Expression [, Expression ] ...)

パラメータ

パラメータ	説明
Expression	有効な式の構文。詳細は、「式」を参照してください。
RollupCubeClause	GROUP BY句には、1つ以上のROLLUPまたはCUBE句が含まれることがあります。
GroupingSetsClause	GROUP BY句には、1つ以上のGROUPING SETS句が含まれることがあります。GROUPING SETS句を使用すると、データベースが戻すデータのグループを明示的に指定できます。詳細は、「GROUPING SETS」を参照してください。
GroupingExpressionList	GROUP BY句には、複数の式または式リストを含めることができます。
ROLLUP GroupingExpressionList	ROLLUP句は、グループから超集合行を生成するために使用します。詳細は、「ROLLUP」を参照してください。
CUBE GroupingExpressionList	CUBE句は、CUBE句のグルーピング列の可能なすべての組合せの値に基づいて、選択された行をグループ化します。詳細は、「CUBE」を参照してください。
ExpressionList	カンマで区切られた、1つ以上の式のリスト。

例

次のGROUP BYの例では、employees表の従業員の給与を合計し、SUBSTR式を使用して、職務権限ごとにデータをグループ化します。

Command> SELECT SUBSTR (job_id, 4,10), SUM (salary) FROM employees
         GROUP BY SUBSTR (job_id,4,10);
< PRES, 24000 >
< VP, 34000 >
< PROG, 28800 >
< MGR, 24000 >
< ACCOUNT, 47900 >
< MAN, 121400 >
< CLERK, 133900 >
< REP, 273000 >
< ASST, 4400 >
9 rows found.

emp_details_viewを問い合せて、先頭の10部門およびその部門のマネージャを選択し、その部門で同じマネージャの管理下にある従業員の数をカウントします。GROUP BY句を使用して、結果を部門およびマネージャごとにグループ化します。

Command> columnlabels on;
Command> SELECT first 10 department_id AS DEPT, manager_id AS MGR,
         COUNT(employee_id) AS NUM_EMP
         FROM emp_details_view
         GROUP BY (department_id, manager_id)
         ORDER BY department_id, manager_id;

DEPT, MGR, NUM_EMP
< 10, 101, 1 >
< 20, 100, 1 >
< 20, 201, 1 >
< 30, 100, 1 >
< 30, 114, 5 >
< 40, 101, 1 >
< 50, 100, 5 >
< 50, 120, 8 >
< 50, 121, 8 >
< 50, 122, 8 >
10 rows found.

ROLLUP、CUBEおよびGROUPING SETS句

次の定義では、列をROLLUP、CUBEおよびGROUPING SETS句でグループ化する方法を説明します。

• グルーピング列: GROUP BY句で使用される1つの列。たとえば、次のGROUP BY句では、X、YおよびZはグループ列です。

  GROUP BY X, GROUPING SETS(Y, Z)
  

• 複合列: カッコ内のグルーピング列のリスト。たとえば、次の句では、(C1, C2)および(C3, C4)は複合列です。

  GROUP BY ROLLUP( (C1,C2), (C3,C4), C5);
  

• グルーピング: グルーピングは、グルーピング・セット内の1つの集計レベルです。たとえば、次の文では、(C1)および(C2, C3)は個別のグルーピングです。

  GROUP BY GROUPING SETS(C1, (C2,C3));
  

• グルーピング・セット: カッコ内のグルーピングのコレクション。たとえば、次の文では、(C1, (C2, C3))および(C2, (C4, C5))は、2つの個別のグルーピング・セットです。

  GROUP BY GROUPING SETS(C1, (C2,C3)), GROUPING SETS(C2, (C4, C5));
  

• 連結グルーピング・セット: 複数のグルーピング・セットをカンマで区切ります。結果は、各グルーピング・セットからのグループ化のクロス積です。

• 総計または空のセット列: 総計または空のセットのグルーピングは、すべての行を1つのグループとみなすことによって集計を計算します。ROLLUPおよびCUBE句では、総計は自動的に結果に含まれますが、GROUPING SETS句では、空のカッコ( )を指定することで要求します。

重複したグルーピング列をROLLUP、CUBEまたはGROUPING SETSで使用できます。ただし、結果行は重複します。

ROLLUP、CUBEおよびGROUPING SETS句は、マテリアライズド・ビュー定義ではサポートされていません。

次の項では、GROUPING SETS、ROLLUPおよびCUBE句について説明します。

• GROUPING SETS

• ROLLUP

• CUBE

GROUPING SETS

GROUPING SETS句を使用すると、データベースが戻すデータのグループを明示的に指定できます。目的の超集合サマリーのみが生成されるように、必要なグループのみをカッコで囲んで指定します。

次の文は3つのグループを生成し、1つのグループは各性別と年の列について結果を返し、2番目のグループは各月のサマリー超集合について結果を返し、最後のグループは総計について結果を返します。

SELECT GENDER, YEAR, MONTH,
   SUM (NUM_OF_STUDENTS) AS TOTAL
   FROM INSTRUCTOR_SUMMARY
   GROUP BY GROUPING SETS ((GENDER, YEAR),  -- 1ST GROUP
                           (MONTH), -- 2ND GROUP
                           ()); -- 3RD GROUP
 

複数のGROUPING SETSを組み合せて、複数のGROUPING SETS間の特定の組合せを生成できます。次の文には、2つのGROUPING SETS句があります。

GROUP BY GROUPING SETS (YEAR, MONTH),
         GROUPING SETS (WEEK, DAY);

これは、次のGROUPING SETS文と同等です。

GROUP BY GROUPING SETS (YEAR, WEEK), 
 (YEAR, DAY), 
 (MONTH, WEEK), 
 (MONTH, DAY);

次のように、GROUP BY句に通常のグルーピング列とGROUPING SETS句の両方がある場合、結果は通常のグルーピング列でグループ化されます。

GROUP BY a, b GROUPING SETS(c, d);

これは、次のルールと同等です。

GROUP BY GROUPING SETS((a, b, c), (a, b, d));

次の例は、(region_name, country_name)、state_provinceおよび総計のグルーピング・セットを指定します。

Command> SELECT region_name AS Region,
           country_name AS Country,
           state_province AS State,
            COUNT(employee_id) AS "Total Emp"
         FROM regions r, countries c, locations l, departments d, employees e
         WHERE r.region_id = c.region_id AND
           l.country_id = c.country_id AND
           d.location_id = l.location_id AND
           d.department_id = e.department_id
         GROUP BY grouping sets((region_name, country_name), state_province, ())
         ORDER BY region_name, state_province;
 
REGION, COUNTRY, STATE, TOTAL EMP
< Americas, Canada, <NULL>, 2 >
< Americas, United States of America, <NULL>, 68 >
< Europe, Germany, <NULL>, 1 >
< Europe, United Kingdom, <NULL>, 35 >
< <NULL>, <NULL>, Bavaria, 1 >
< <NULL>, <NULL>, California, 45 >
< <NULL>, <NULL>, Ontario, 2 >
< <NULL>, <NULL>, Oxford, 34 >
< <NULL>, <NULL>, Texas, 5 >
< <NULL>, <NULL>, Washington, 18 >
< <NULL>, <NULL>, <NULL>, 106 >
< <NULL>, <NULL>, <NULL>, 1 >
12 rows found.

ROLLUP

ROLLUPは、GROUP BY句で使用します。ROLLUPをSUMとともに使用すると、最も詳細なレベルの小計(ROLLUP句で指定したすべての列)から総計レベルまでが、レベルごとに列を1つずつ削除して生成されます。これらは超集合行と呼ばれます。

ROLLUP句では次の項目が返されます。

• ROLLUPを使用しないでGROUP BYによって生成される通常の集計行。

• ROLLUP句で指定されたグルーピング・リストに基づく小計。ROLLUPは、その引数として、グルーピング列の順序付けリストを取ります。各小計は、グルーピング列の順序付けリストに対して、総計に達するまで最も右にあるグルーピング列を削除しながら作成されます。たとえば、GROUP BY ROLLUP(x, y, z)を指定した場合、返される超集合グループは(x,y,z)、(x,y)、(x)、( )のようになります。

  作成される小計の数はn+1個の集計レベルであり、nはグルーピング列の数です。たとえば、3つの式(n=3)がROLLUP句にある場合、n+1 = 3+1、つまり4個のグルーピングになります。

• 総計行。

カッコ内の複合列を使用して列をグループ化できます。たとえば、次のような文があるとします。

GROUP BY ROLLUP( (a, b), (c, d), e);

(a, b)および(c, d)複合列は、データベースによってROLLUPの結果が生成される際に、1つの単位として処理されます。この例では、返されるグルーピング・セットは、((a, b), (c, d), e )、((a, b), (c, d))、(a, b)および()となります。

次のように、複数のROLLUP句をSELECT文で実行できます。

SELECT C1, COUNT(*)
FROM T
GROUP BY ROLLUP(a, b), ROLLUP(c, d);

これは、次の文と同じです。

SELECT C1, COUNT(*)
FROM T
GROUP BY GROUPING SETS((a, b),(a),()), 
 GROUPING SETS((c, d),(c), ());

この例では、employees表を問い合せて、先頭の10部門を選択し、各部門の各マネージャの管理下にある従業員数を戻します。ROLLUPを使用して、各部門の従業員数の小計を算出し、会社のすべての従業員の総計を戻します。

Command> SELECT first 10 department_id AS Dept,
          manager_id AS Mgr,
         COUNT(employee_id) AS "Total emp"
         FROM employees
         GROUP BY ROLLUP(department_id, manager_id)
         ORDER BY department_id, manager_id;
 
DEPT, MGR, TOTAL EMP
< 10, 101, 1 >
< 10, <NULL>, 1 >
< 20, 100, 1 >
< 20, 201, 1 >
< 20, <NULL>, 2 >
< 30, 100, 1 >
< 30, 114, 5 >
< 30, <NULL>, 6 >
< 40, 101, 1 >
< 40, <NULL>, 1 >
10 rows found.

次の問合せによって、各地域、国および州または都道府県の従業員数が返されます。ロールアップによって、各州または都道府県および各国の全従業員の小計の超集合行、および会社の全従業員の総計が返されます。地域と国を(カッコに入れて)独自の単位として組み合せることによって、ロールアップでは各地域の全従業員は返されません。

Command> SELECT region_name AS Region,
          country_name AS Country,
          state_province AS State,
          COUNT(employee_id) AS "Total Emp"
         FROM regions r, countries c, locations l, departments d, employees e
         WHERE r.region_id = c.region_id
         AND l.country_id = c.country_id
         AND d.location_id = l.location_id
         AND d.department_id = e.department_id
         GROUP BY rollup((region_name, country_name), state_province)
         ORDER BY region_name;

REGION, COUNTRY, STATE, TOTAL EMP
< Americas, Canada, Ontario, 2 > 
< Americas, United States of America, Texas, 5 > 
< Americas, United States of America, California, 45 > 
< Americas, United States of America, Washington, 18 > 
< Americas, Canada, <NULL>, 2 > 
< Americas, United States of America, <NULL>, 68 > 
< Europe, Germany, Bavaria, 1 > 
< Europe, United Kingdom, <NULL>, 1 > 
< Europe, United Kingdom, Oxford, 34 > 
< Europe, Germany, <NULL>, 1 > 
< Europe, United Kingdom, <NULL>, 35 > 
< <NULL>, <NULL>, <NULL>, 106 > 
12 rows found. 

CUBE

CUBE句は、CUBE句のグルーピング列の可能なすべての組合せの値に基づいて、選択された行をグループ化します。グループごとに1つのサマリー情報行が戻されます。たとえば、3つの式(n=3)がCUBE句にある場合、2ⁿ = 2³、つまり8個のグルーピングになります。n個の式の値でグループ化した行を標準行、その他のすべてを超集合行といいます。複合列を使用してグループ化できます。たとえば、一般的に要求されるCUBE操作は、月、州および販売製品のすべての組合せに基づいた州の売上小計用です。

GROUP BY CUBE(a, b, c)を指定した場合、結果として生成される集計グルーピングは、(a,b,c)、(a,b)、(a,c)、(b,c)、a、b、c、( )です。

この例では、各地域および国の従業員数を戻し、次の問合せを発行します。

Command> SELECT region_name AS Region,
          country_name AS Country,
          COUNT(employee_id) AS "Total Emp"
       FROM regions r, countries c, locations l, departments d, employees e
       WHERE r.region_id = c.region_id
       AND l.country_id = c.country_id
       AND d.location_id = l.location_id
       AND d.department_id = e.department_id
       GROUP BY CUBE(region_name, country_name)
       ORDER BY region_name;
 
REGION, COUNTRY, TOTAL EMP
< Americas, Canada, 2 >
< Americas, United States of America, 68 >
< Americas, <NULL>, 70 >
< Europe, Germany, 1 >
< Europe, United Kingdom, 35 >
< Europe, <NULL>, 36 >
< <NULL>, Canada, 2 >
< <NULL>, Germany, 1 >
< <NULL>, United Kingdom, 35 >
< <NULL>, United States of America, 68 >
< <NULL>, <NULL>, 106 >
11 rows found.