group-by-clause

GROUP BY 文節は、R の行のグループ化で構成される中間結果表を指定します。 R は、副選択のそれ以前の節の結果です。

最も単純な形式では、 GROUP BY 節に grouping expression (グループ化式) が入っています。グループ化式とは、R のグループ化の定義で使用される expression です。 grouping-expression に含まれる各式または列名は、R の列を明確に識別していなければなりません (SQLSTATE 42702 または 42703)。グループ化式には、スカラー全選択または XMLQUERY または XMLEXISTS 式 (SQLSTATE 42822)、あるいは非決定論であるか外部アクションを持つ式または関数を含めることはできません (SQLSTATE 42845)。

注: 明示的な列参照を含まない以下の式は、R の列を識別するために グループ化式 で使用できます。

table-designator の行変更タイム・スタンプ
table-designator の ROW CHANGE TOKEN
RID_BIT または RID スカラー関数

複雑な形式の GROUP BY 節には、 grouping-sets (グループ化集合) および super-groups (スーパー・グループ) が組み入れられます。 grouping-setsの説明については、 grouping-setsを参照してください。 super-groupsについては、 super-groupsを参照してください。

GROUP BY の結果は、いくつかの行グループの集まりです。この結果の各行は、grouping-expression が等しい行の集合を表します。グループ化では、grouping-expression の NULL 値はすべて等しいものと見なされます。

grouping-expressionに 10 進浮動小数点列が含まれ、同じ数値の複数の表記がこれらの列に存在する場合、戻される数値は、数値の表記のうち任意のいずれかになります。

grouping-expression (グループ化式) は、HAVING 節の検索条件、SELECT 節の式で使用できます。または ORDER BY 節の sort-key-expression の中 (詳しくは、 order-by-clause を参照してください)。いずれの場合も、その参照は各グループの 1 つの値だけを指定します。例えば、grouping-expression が col1+col2 である場合、SELECT リストで使用できる式は col1+col2+3 になります。式の関連性規則では、類似した式 3+col1+col2 を使用することを許可しません。ただし、対応する式を同じ順序で評価するために括弧を使用する場合を除きます。したがって、SELECT リストでは 3+(col1+col2) も使用することができます。連結演算子を使用する場合、grouping-expression は、SELECT リストで指定された式とまったく同じように使用しなければなりません。

grouping-expression に末尾のブランクの付いた可変長ストリングが入っている場合、そのグループの値は末尾のブランクの数が異なる場合があり、必ずしも同じ長さになるとは限りません。そのような場合でも、grouping-expression への参照は各グループに 1 つの値だけを指定します。ただし、グループの値は使用可能な値の集合の中から任意に選択されるか、または使用可能な値の集合にある値またはない値の正規形です。したがって、結果値の実際の長さは予測できません。

前述のように、GROUP BY 節が、 SELECT 節で指定された列を式 (スカラー全選択、決定論的ではないもの、または外部処理関数) として直接参照することができない場合があります。そのような式を使用してグループ化を行うには、ネストした表式または共通表式を使用して、まず結果の列が式となる結果表を指定します。ネストされた表の式の使用例については、「副選択照会の例」の例 9 を参照してください。

grouping-sets

grouping-sets 指定を使用すると、単一のステートメントで複数のグループ化節を指定できます。これは、行の複数のグループを単一の結果セットにまとめたものと見なすことができます。これは、1 つのグループ化集合に対応する各副選択ごとに GROUP BY 節を持つ複数の副選択を合併したものと論理的に等しくなります。グループ化集合は、単一のエレメント、もしくは括弧によって区切られる複数のエレメントのリストになります。この場合、エレメントはグループ化式、またはスーパー・グループのいずれかです。グループは、grouping-sets を使用した基本表の単一パスによって計算できます。

grouping-sets 指定では、単純な grouping-expression またはより複雑な形式の super-groups がサポートされています。 super-groupsについては、 super-groupsを参照してください。

グループ化集合は、GROUP BY 演算の基礎的な構築ブロックであることに注意してください。単一の列を使用する単純な GROUP BY は、 1 つのエレメントを持つグループ化集合と見なすことができます。以下に例を示します。

   GROUP BY a

これは、以下と同じです。

   GROUP BY GROUPING SETS((a))

および

   GROUP BY a,b,c

これは、以下と同じです。

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

グループ化集合から除外される副選択の SELECT リストの非集約の列は、そのグループ化集合用に生成される各行の列に NULL 値を戻します。これは、集約が列の値を考慮せずに行われたことを表しています。

グループ化セットの使用法については、グループ化セット、キューブ、およびロールアップ照会の例の例 2 から例 7 を参照してください。

super-groups

grouping-expression-list

grand-total

Notes:

¹ Alternate specification when used alone in group-by-clause is: grouping-expression-list WITH ROLLUP.
² Alternate specification when used alone in group-by-clause is: grouping-expression-list WITH CUBE.

ROLLUP ( グループ化式リスト )

ROLLUP grouping は GROUP BY 節の拡張機能であり、通常のグループ化行に加えて、小計行を含む結果セットを生成します。小計行とは、グループ化行を入手するのに使用されたのと同じ集約関数を適用して値が得られる集合体が入っているsuper-aggregate「スーパー集約」行です。これらの行は小計のために使用されることが最も多いので小計行と呼ばれますが、集約には任意の集約関数を使用することができます。例えば、MAX と AVG は、グループ化セット、キューブ、およびロールアップ・クエリーの例の例 8 で使用されています。 GROUPING 集約関数を使用して、スーパー・グループによって行が生成されたかどうかを示すことができます。

ROLLUP グループ化は、一連のグループ化集合です。 n 個のエレメントを持つ ROLLUP の一般的な仕様は、次のとおりです。

  GROUP BY ROLLUP(C₁,C₂,...,C_n-1,C_n)

これは、以下と同じ意味になります。

  GROUP BY GROUPING SETS((C₁,C₂,...,C_n-1,C_n)
                         (C₁,C₂,...,C_n-1)
                         ...
                         (C₁,C₂)
                         (C₁)
                         () )

ROLLUP の n のエレメントは、 n +1 のグループ化集合に変換される点に注意してください。 グループ化式 が指定されている順序が、 ROLLUP にとって重要である点も注意してください。例えば、次の節について考慮します。

  GROUP BY ROLLUP(a,b)

これは、以下と同じ意味になります。

  GROUP BY GROUPING SETS((a,b)
                         (a)
                         () )

同様に、次の節について考慮します。

  GROUP BY ROLLUP(b,a)

これは、以下と同じ意味になります。

  GROUP BY GROUPING SETS((b,a)
                         (b)
                         () )

ORDER BY 節は、結果セットの行の順序を確定する唯一の方法です。「グループ化セット、キューブ、およびロールアップ照会の例」の例 3 は、ROLLUP の使用法を示しています。

CUBE ( グループ化式リスト )

CUBE grouping (CUBE グループ化) は GROUP BY 節の拡張であり、すべての ROLLUP 集約行に加えて、「クロス集計」行の入った結果セットを生成します。 クロス集計 行は、小計による集約の一部ではない、追加の「スーパー集約」行です。 GROUPING 集約関数を使用して、スーパー・グループによって行が生成されたかどうかを示すことができます。

ROLLUP と同じように、CUBE グループ化も、一連の grouping-sets と見なすことができます。 CUBE の場合、3 乗される grouping-expression-list のすべての順列が総計とともに計算されます。したがって、CUBE の n 個のエレメントは、 2**n (2 の n 乗) のグループ化集合に変換されます。例えば、以下のように指定する場合を考えてみます。

   GROUP BY CUBE(a,b,c)

これは、以下と同じ意味になります。

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

CUBE の 3 個のエレメントは、8 個のグループ化集合に変換されることに注意してください。

エレメントの指定の順序は、CUBE の場合、重要ではありません。 'CUBE (DayOfYear, Sales_Person)' と 'CUBE (Sales_Person, DayOfYear)' は同じ結果セットを生成します。「同じ」とは、結果セットの順序ではなく、結果セットの内容を指します。 ORDER BY 節は、結果セットの行の順序を確定する唯一の方法です。 CUBE の使用法は、グループ化セット、キューブ、およびロールアップ照会の例の例 4 に示されています。

グループ化式リストgrouping-expression-list (グループ化式リスト) は、 CUBE または ROLLUP 演算のエレメント数を定義するために、 CUBE または ROLLUP 節で使用されます。これは、複数の grouping-expression を持つエレメントを区切るために括弧を使用して制御されます。

例えば、照会が、County ではなく Province 内の City の ROLLUP について合計費用を戻すものと想定します。しかし、以下の節の場合、

   GROUP BY ROLLUP(Province, County, City)

County についての不要な小計行が生じます。以下の節では、

   GROUP BY ROLLUP(Province, (County, City))

複合 (County, City) が ROLLUP の 1 つのエレメントを形成するので、この節を使用する照会は、望ましい結果を生成します。つまり、次のように 2 つのエレメントからなる ROLLUP の場合、

   GROUP BY ROLLUP(Province, (County, City))

以下が生成されます。

   GROUP BY GROUPING SETS((Province, County, City)
                          (Province)
                          () )

さらに、3 つのエレメントから成る ROLLUP の場合、次が生成されます。

   GROUP BY GROUPING SETS((Province, County, City)
                          (Province, County)
                          (Province)
                          () )

「グループ化セット、キューブ、およびロールアップ照会の例」の例 2 も、複合列の値を使用します。

grand-total: CUBE および ROLLUP は、全体の集約 (総計) である行を戻します。これは、GROUPING SET 節に空の括弧を使用して別々に指定することができます。また、GROUP BY 節に直接指定することもできます。これは照会の結果には影響しません。「グループ化セット、キューブ、およびロールアップ照会の例」の例 4 では、総計構文を使用します。

グループ化集合の結合

これは、任意のタイプの GROUP BY 節を組み合わせるのに使用することができます。単純な grouping-expression のフィールドを他のグループと組み合わせる場合、結果の grouping sets の始めに「追加」されます。 ROLLUP 式または CUBE 式を組み合わせる場合、それらの式は、残りの式では「乗数」のように動作し、 ROLLUP または CUBE の定義に応じて追加のグループ化集合項目を生成します。

例えば、グループ化式のエレメントを組み合わせると、次のようになります。

  GROUP BY a, ROLLUP(b,c)

これは、以下と同じ意味になります。

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

もしくは

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

これは、以下と同じ意味になります。

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

ROLLUP エレメントを組み合わせると、次のようになります。

  GROUP BY ROLLUP(a), ROLLUP(b,c)

これは、以下と同じ意味になります。

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

同様に、

  GROUP BY ROLLUP(a), CUBE(b,c)

これは、以下と同じ意味になります。

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

CUBE と ROLLUP のエレメントの組み合わせは次のようになります。

  GROUP BY CUBE(a,b), ROLLUP(c,d)

これは、以下と同じ意味になります。

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

単純な grouping-expression の場合のように、グループ化集合を組み合わせると、各グループ化集合内で重複したものが除去されます。例えば、次のようになります。

  GROUP BY a, ROLLUP(a,b)

これは、以下と同じ意味になります。

  GROUP BY GROUPING SETS((a,b)
                         (a) )

グループ化集合を組み合わせる場合の詳しい例は、完全な CUBE 集約について戻される特定行を除去する結果セットを構成する場合です。

例えば、以下の GROUP BY 節について考えてみます。

  GROUP BY Region,
           ROLLUP(Sales_Person, WEEK(Sales_Date)),
           CUBE(YEAR(Sales_Date), MONTH (Sales_Date))

GROUP BY のすぐ右側にリストされている列はグループ化され、 ROLLUP の後の括弧内の列がロールアップされ、 CUBE の後の括弧内の列は 3 乗されます。したがって、GROUP BY 節の結果として、YEAR 内の MONTH のキューブが生成されてから、 REGION 内の Sales_Person 内の WEEK の集約内でロールアップが行われます。この結果は、Region、 Sales_Person または WEEK(Sales_Date) の総計行にもクロス集計行にもならないため、生成される行は、以下の節より少なくなります。

  GROUP BY ROLLUP (Region, Sales_Person, WEEK(Sales_Date),
                   YEAR(Sales_Date), MONTH(Sales_Date) )