SQL 解説書

GROUP BY 文節

             .-,--------------------------.
             V                            |
>>-GROUP BY------+-grouping-expression-+--+--------------------><
                 +-grouping-sets-------+
                 '-super-groups--------'
 

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

R は、副選択のそれ以前の文節の結果です。

もっとも単純な形式では、 GROUP BY 文節に grouping expression (グループ化式) が含まれます。 グループ化式とは、R のグループ化の定義に使用される です。 グループ化式に含まれている各列名 は、 R の列を明確に識別しなければなりません (SQLSTATE 42702 または 42703)。 各グループ化式の長さ属性は、 255 バイトを超えてはなりません (SQLSTATE 42907)。 グループ化式には、スカラー全選択 (SQLSTATE 42822)、 または可変の関数または外部処理を伴う関数 (SQLSTATE 42845) を含めることはできません。

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

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

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

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

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

グループ化集合

                     .-,---------------------------------------------.
                     V                                               |
>>-GROUPING SETS--(------+--+-grouping-expression-+---------------+--+---)-->
                         |  '-super-groups--------'               |
                         |    .-,--------------------------.      |
                         |    V                            |      |
                         '-(------+-grouping-expression-+--+---)--'
                                  '-super-groups--------'
 
>--------------------------------------------------------------><
 

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

グループ化集合 の指定により、単純なグループ化式 を使用するか、 またはより複雑な形式のスーパー・グループ を使用することができます。 スーパー・グループ については、 スーパー・グループ を参照してください。

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

  GROUP BY a  

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

  GROUP BY GROUPING SET((a))

および

  GROUP BY a,b,c  

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

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

グループ化集合から除外される副選択の選択リストの非集約の列は、 そのグループ化集合用に生成される各行の列にヌル値を戻します。 これは、集約が列の値を考慮せずに行われたことを表しています。 実際のデータのヌル値の行と、 グループ化集合から生成されたヌル値の行とを区別する方法については、 GROUPING を参照してください。

例 C2例 C7 は、グループ化集合の使用法を示しています。

スーパー・グループ

                                            (1)
>>-+-ROLLUP--(--grouping-expression-list--)-------+------------><
   |                                      (2)     |
   +-CUBE--(--grouping-expression-list--)---------+
   '-| grand-total |------------------------------'
 
grouping-expression-list
 
    .-,-----------------------------------------.
    V                                           |
|-------+-grouping-expression----------------+--+---------------|
        |    .-,----------------------.      |
        |    V                        |      |
        '-(-----grouping-expression---+---)--'
 
grand-total
 
|---(--)--------------------------------------------------------|
 

注:

  1. GROUP BY 文節で単独で使用される代替仕様は、 grouping-expression-list WITH ROLLUP です。

  2. GROUP BY 文節で単独で使用される代替仕様は、 grouping-expression-list WITH CUBE です。

ROLLUP ( grouping-expression-list )
ROLLUP grouping (ROLLUP グループ化) は GROUP BY 文節の拡張であり、 「通常の」グループ化された行に加えて小計 行を含む結果セットを生成します。 小計53 は、グループ化行を入手するのに使用されたのと同じ列関数を適用して値が得られる集合体が入っている 「スーパー集約」行です。

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

  GROUP BY ROLLUP(C1,C2,...,Cn-1,Cn)

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

  GROUP BY GROUPING SETS(
 (C1,C2,...,Cn-1,Cn)
                         (C1,C2,...,Cn-1)
                         ...
                         (C1,C2)
                         (C1)
                         () )

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 文節は、結果セットの行の順序を保証する唯一の方法です。 例 C3 は ROLLUP の使用法を示しています。

CUBE ( grouping-expression-list )
CUBE grouping (CUBE グループ化)は GROUP BY 文節の拡張であり、 すべての ROLLUP 集約行に加えて、「クロス集計」行を含む結果セットを生成します。 クロス集計 行は、 小計による集約の一部ではない「スーパー集約行」です。

ROLLUP と同じように、CUBE グループ化も、 一連のグループ化集合 と見なすことができます。 CUBE の場合、グループ化式リスト のすべての順列が総計とともに計算されます。 したがって、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 文節は、結果セットの行の順序を保証する唯一の方法です。 例 C4 に、CUBE の使用例が示されています。

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

グループ化式 の規則については、 GROUP BY 文節 で説明しています。 たとえば、照会が、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)
                         () )

例 C2 でも、複合列値が使用されています。

grand-total
CUBE および ROLLUP は、全体の集約 (総計) である行を戻します。 これは、GROUPING SET 文節に空の括弧を使用して別々に指定することができます。 また、GROUP BY 文節に直接指定することもできます。 これは照会の結果には影響しません。 例 C4 では、総計構文を使用しています。

グループ化集合の組み合わせ

これは、任意のタイプの GROUP BY 文節を組み合わせるのに使用することができます。 単純なグループ化式 のフィールドを他のグループと組み合わせる場合、 結果のグループ化集合 の始めに「追加」されます。 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)
                         () )

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

  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)
                         () )

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

  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 乗されます。 したがって、上記の文節の結果は、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) )

HAVING 文節

>>-HAVING--search-condition------------------------------------><
 

HAVING 文節は、 search-condition (検索条件) が真である R のグループで構成される中間結果表を指定します。

R は、副選択のそれ以前の文節の結果です。 その文節が GROUP BY ではない場合、R はグループ化列のない単一のグループとみなされます。

検索条件内の各列名 は、以下のいずれかを満たすものであることが必要です。

検索条件が適用される R のグループは、 検索条件内の各列関数 (引き数が相関参照である関数を除く) の引き数を提供するものとなります。

検索条件に副照会が含まれる場合、その副照会は、 検索条件が R のグループに適用されるたびに実行され、 その結果は検索条件の適用において使用されるもの、とみなすことができます。 実際には、副照会が各グループごとに実行されるのは、 その中に相関参照が含まれる場合だけです。 この違いについては、例 A6 および 例 A7 を参照してください。

R のグループに対する相関参照は、グループ化列を指定するものであるか、 あるいは列関数に含まれるものでなければなりません。

HAVING が GROUP BY なしで使用される場合、 選択リストには、列関数内の列名、相関列参照、リテラル、 または特殊レジスターしか使えません。


脚注:

53
小計がもっとも一般的な用途なので小計行と呼ばれますが、 集約には任意の列関数を使用することができます。 たとえば、MAX および AVG は 例 C8 で使用されます。


[ ページのトップ | 前ページ | 次ページ | 目次 | 索引 ]