SQL 解説書

グループ化集合、CUBE、および ROLLUP の例

例 C1例 C4 の照会では、 述部 'WEEK(SALES_DATE) = 13' に基づいて SALES 表の行のサブセットを使用しています。

  SELECT WEEK(SALES_DATE) AS WEEK, 
        DAYOFWEEK(SALES_DATE) AS DAY_WEEK, 
         SALES_PERSON, SALES AS UNITS_SOLD 
  FROM SALES 
  WHERE WEEK(SALES_DATE) = 13

これは次の結果になります。

  WEEK        DAY_WEEK    SALES_PERSON    UNITS_SOLD 
  ----------- ----------- --------------- -----------
           13           6 LUCCHESSI                 3
           13           6 LUCCHESSI                 1
           13           6 LEE                       2
           13           6 LEE                       2
           13           6 LEE                       3
           13           6 LEE                       5
           13           6 GOUNOT                    3
           13           6 GOUNOT                    1
           13           6 GOUNOT                    7
           13           7 LUCCHESSI                 1
           13           7 LUCCHESSI                 2
           13           7 LUCCHESSI                 1
           13           7 LEE                       7
           13           7 LEE                       3
           13           7 LEE                       7
           13           7 LEE                       4
           13           7 GOUNOT                    2
           13           7 GOUNOT                   18
           13           7 GOUNOT                    1

例 C1: これは、3 つの列に対して基本の GROUP BY 文節を使用している照会です。

  SELECT WEEK(SALES_DATE) AS WEEK,
        DAYOFWEEK(SALES_DATE) AS DAY_WEEK,
        SALES_PERSON, SUM(SALES) AS UNITS_SOLD       
  FROM SALES
  WHERE WEEK(SALES_DATE) = 13
  GROUP BY WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE), SALES_PERSON
  ORDER BY WEEK, DAY_WEEK, SALES_PERSON

これは次のような結果になります。

  WEEK        DAY_WEEK    SALES_PERSON    UNITS_SOLD 
  ----------- ----------- --------------- -----------
           13           6 GOUNOT                   11
           13           6 LEE                      12
           13           6 LUCCHESSI                 4
           13           7 GOUNOT                   21
           13           7 LEE                      21
           13           7 LUCCHESSI                 4

例 C2: SALES 表の行の 2 つのグループ化集合に基づいて結果を生成します。

  SELECT WEEK(SALES_DATE) AS WEEK,
        DAYOFWEEK(SALES_DATE) AS DAY_WEEK,
        SALES_PERSON, SUM(SALES) AS UNITS_SOLD       
  FROM SALES 
  WHERE WEEK(SALES_DATE) = 13
  GROUP BY GROUPING SETS ( (WEEK(SALES_DATE), SALES_PERSON),
                           (DAYOFWEEK(SALES_DATE), SALES_PERSON))
  ORDER BY WEEK, DAY_WEEK, SALES_PERSON

これは次のような結果になります。

  WEEK        DAY_WEEK    SALES_PERSON    UNITS_SOLD 
  ----------- ----------- --------------- -----------
           13           - GOUNOT                   32
           13           - LEE                      33
           13           - LUCCHESSI                 8
            -           6 GOUNOT                   11
            -           6 LEE                      12
            -           6 LUCCHESSI                 4
            -           7 GOUNOT                   21
            -           7 LEE                      21
            -           7 LUCCHESSI                 4

WEEK 13 の行は、最初のグループ化集合のものであり、 それ以外の行は 2 番目のグループ化集合のものです。

例 C3: 例 C2 のグループ化集合に含まれる 3 つの特殊な列を使用して、 ROLLUP を実行する場合、(WEEK、DAY_WEEK、SALES_PERSON)、 (WEEK、DAY_WEEK)、(WEEK) および総計のグループ化集合を見ることができます。

       SELECT WEEK(SALES_DATE) AS WEEK,
        DAYOFWEEK(SALES_DATE) AS DAY_WEEK,
        SALES_PERSON, SUM(SALES) AS UNITS_SOLD       
  FROM SALES
  WHERE WEEK(SALES_DATE) = 13
  GROUP BY ROLLUP ( WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE), SALES_PERSON )
  ORDER BY WEEK, DAY_WEEK, SALES_PERSON

これは次のような結果になります。

  WEEK        DAY_WEEK    SALES_PERSON    UNITS_SOLD 
  ----------- ----------- --------------- -----------
           13           6 GOUNOT                   11
           13           6 LEE                      12
           13           6 LUCCHESSI                 4
           13           6 -                        27
           13           7 GOUNOT                   21
           13           7 LEE                      21
           13           7 LUCCHESSI                 4
           13           7 -                        46
           13           - -                        73
            -           - -                        73

例 C4: 例 C3 と同じ照会を実行して ROLLUP を CUBE に置き換える場合、結果に (WEEK、SALES_PERSON)、 (DAY_WEEK、SALES_PERSON)、 (DAY_WEEK)、(SALES_PERSON) の追加のグループ化集合を見ることができます。

  SELECT WEEK(SALES_DATE) AS WEEK,
        DAYOFWEEK(SALES_DATE) AS DAY_WEEK,
        SALES_PERSON, SUM(SALES) AS UNITS_SOLD       
  FROM SALES
  WHERE WEEK(SALES_DATE) = 13
  GROUP BY CUBE ( WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE), SALES_PERSON )
  ORDER BY WEEK, DAY_WEEK, SALES_PERSON

これは次のような結果になります。

  WEEK        DAY_WEEK    SALES_PERSON    UNITS_SOLD 
  ----------- ----------- --------------- -----------
           13           6 GOUNOT                   11
           13           6 LEE                      12
           13           6 LUCCHESSI                 4
           13           6 -                        27
           13           7 GOUNOT                   21
           13           7 LEE                      21
           13           7 LUCCHESSI                 4
           13           7 -                        46
           13           - GOUNOT                   32
           13           - LEE                      33
           13           - LUCCHESSI                 8
           13           - -                        73
            -           6 GOUNOT                   11
            -           6 LEE                      12
            -           6 LUCCHESSI                 4
            -           6 -                        27
            -           7 GOUNOT                   21
            -           7 LEE                      21
            -           7 LUCCHESSI                 4
            -           7 -                        46
            -           - GOUNOT                   32
            -           - LEE                      33
            -           - LUCCHESSI                 8
            -           - -                        73

例 C5: SALES 表から選択された行の総計と、 SALES_PERSON および MONTH によって集計された行のグループを含む結果セットを入手します。

  SELECT SALES_PERSON,
         MONTH(SALES_DATE) AS MONTH,
         SUM(SALES) AS UNITS_SOLD
  FROM SALES
  GROUP BY GROUPING SETS ( (SALES_PERSON, MONTH(SALES_DATE)),
                           ()        
                         )
  ORDER BY SALES_PERSON, MONTH

これは次のような結果になります。

  SALES_PERSON    MONTH       UNITS_SOLD 
  --------------- ----------- -----------
  GOUNOT                    3          35
  GOUNOT                    4          14
  GOUNOT                   12           1
  LEE                       3          60
  LEE                       4          25
  LEE                      12           6
  LUCCHESSI                 3           9
  LUCCHESSI                 4           4
  LUCCHESSI                12           1
            - -                       155

例 C6: この例では、2 つの単純な ROLLUP 照会を示し、 その後に、2 つの ROLLUP を単一結果セットのグループ化集合として扱い、 グループ化集合に含まれている列ごとに行の順序を指定する照会を示しています。

例 C6-1:

  SELECT WEEK(SALES_DATE) AS WEEK,
        DAYOFWEEK(SALES_DATE) AS DAY_WEEK,
         SUM(SALES) AS UNITS_SOLD
  FROM SALES
  GROUP BY ROLLUP ( WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE) )
  ORDER BY WEEK, DAY_WEEK

結果は次のようになります。

  WEEK        DAY_WEEK    UNITS_SOLD 
  ----------- ----------- -----------
           13           6          27
           13           7          46
           13           -          73
           14           1          31
           14           2          43
           14           -          74
           53           1           8
           53           -           8
            - -                       155

例 C6-2:

  SELECT MONTH(SALES_DATE) AS MONTH,
         REGION,
         SUM(SALES) AS UNITS_SOLD
  FROM SALES
  GROUP BY ROLLUP ( MONTH(SALES_DATE), REGION );
  ORDER BY MONTH, REGION

結果は次のようになります。

  MONTH       REGION          UNITS_SOLD 
  ----------- --------------- -----------
            3 Manitoba                 22
            3 Ontario-North             8
            3 Ontario-South            34
            3 Quebec                   40
            3 -                       104
            4 Manitoba                 17
            4 Ontario-North             1
            4 Ontario-South            14
            4 Quebec                   11
            4 -                        43
           12 Manitoba                  2
           12 Ontario-South             4
           12 Quebec                    2
           12 -                         8
            - -                       155

例 C6-3:

  SELECT WEEK(SALES_DATE) AS WEEK,
        DAYOFWEEK(SALES_DATE) AS DAY_WEEK,
         MONTH(SALES_DATE) AS MONTH,
         REGION,
         SUM(SALES) AS UNITS_SOLD
  FROM SALES
  GROUP BY GROUPING SETS ( ROLLUP( WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE) ),
                           ROLLUP( MONTH(SALES_DATE), REGION )  )
  ORDER BY WEEK, DAY_WEEK, MONTH, REGION

結果は次のようになります。

  WEEK        DAY_WEEK    MONTH       REGION          UNITS_SOLD 
  ----------- ----------- ----------- --------------- -----------
           13           6           - -                        27
           13           7           - -                        46
           13           -           - -                        73
           14           1           - -                        31
           14           2           - -                        43
           14           -           - -                        74
           53           1           - -                         8
           53           -           - -                         8
            -           -           3 Manitoba                 22
            -           -           3 Ontario-North             8
            -           -           3 Ontario-South            34
            -           -           3 Quebec                   40
            -           -           3 -                       104
            -           -           4 Manitoba                 17
            -           -           4 Ontario-North             1
            -           -           4 Ontario-South            14
            -           -           4 Quebec                   11
            -           -           4 -                        43
            -           -          12 Manitoba                  2
            -           -          12 Ontario-South             4
            -           -          12 Quebec                    2
            -           -          12 -                         8
            -           -           - -                       155
            -           -           - -                       155

2 つの ROLLUP をグループ化集合として使用すると、結果に重複した行が含まれます。 総計行も 2 つになります。

ORDER BY を使用すると結果にどのような影響があるかを調べてみます。

例 C7: 単一のパスで複数の ROLLUP を実行する照会 (たとえば 例 C6-3) では、 各行を生成したのがどのグループ化集合であるかを示すことができます。 以下のステップは、 結果セット内の各行の発生点を示す列 (GROUP と呼ばれます) を提供する方法を示しています。 結果セットの行を生成したのが、2 つのグループ化集合のいずれであるかということです。

ステップ 1: VALUES 文節から選択する照会 (代替形式の全選択) を使用して、 新しいデータ値を「生成する」方法を導入します。 この照会は、2 つの列 "R1" と "R2"、および 1 行のデータがある、 "X" と呼ばれる表を得る方法を示しています。

  SELECT R1,R2 
  FROM (VALUES('GROUP 1','GROUP 2')) AS X(R1,R2);

結果は次のようになります。

  R1      R2     
  ------- -------
  GROUP 1 GROUP 2

ステップ 2: SALES 表を使ってこの表 "X" のクロス・プロダクトを生成します。 これにより、各行に列 "R1" および "R2" が追加されます。

  SELECT R1, R2, WEEK(SALES_DATE) AS WEEK,
        DAYOFWEEK(SALES_DATE) AS DAY_WEEK,
         MONTH(SALES_DATE) AS MONTH,
         REGION,
         SALES AS UNITS_SOLD
  FROM SALES,(VALUES('GROUP 1','GROUP 2')) AS X(R1,R2)

これにより、各行に列 "R1" および "R2" が追加されます。

ステップ 3: これで、これらの列をグループ化集合と組み合わせて、 ROLLUP 分析にこれらの列を含めることができるようになりました。

  SELECT R1, R2, 
         WEEK(SALES_DATE) AS WEEK,
        DAYOFWEEK(SALES_DATE) AS DAY_WEEK,
         MONTH(SALES_DATE) AS MONTH,
         REGION, SUM(SALES) AS UNITS_SOLD
  FROM SALES,(VALUES('GROUP 1','GROUP 2')) AS X(R1,R2)
  GROUP BY GROUPING SETS ((R1, ROLLUP(WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE))),
                          (R2,ROLLUP( MONTH(SALES_DATE), REGION ) )  )
  ORDER BY WEEK, DAY_WEEK, MONTH, REGION

結果は次のようになります。

  R1      R2      WEEK     DAY_WEEK  MONTH     REGION          UNITS_SOLD 
  ------- ------- -------- --------- --------- --------------- -----------
  GROUP 1 -             13         6         - -                        27
  GROUP 1 -             13         7         - -                        46
  GROUP 1 -             13         -         - -                        73
  GROUP 1 -             14         1         - -                        31
  GROUP 1 -             14         2         - -                        43
  GROUP 1 -             14         -         - -                        74
  GROUP 1 -             53         1         - -                         8
  GROUP 1 -             53         -         - -                         8
  -       GROUP 2        -         -         3 Manitoba                 22
  -       GROUP 2        -         -         3 Ontario-North             8
  -       GROUP 2        -         -         3 Ontario-South            34
  -       GROUP 2        -         -         3 Quebec                   40
  -       GROUP 2        -         -         3 -                       104
  -       GROUP 2        -         -         4 Manitoba                 17
  -       GROUP 2        -         -         4 Ontario-North             1
  -       GROUP 2        -         -         4 Ontario-South            14
  -       GROUP 2        -         -         4 Quebec                   11
  -       GROUP 2        -         -         4 -                        43
  -       GROUP 2        -         -        12 Manitoba                  2
  -       GROUP 2        -         -        12 Ontario-South             4
  -       GROUP 2        -         -        12 Quebec                    2
  -       GROUP 2        -         -        12 -                         8
  -       GROUP 2        -         -         - -                       155
  GROUP 1 -              -         -         - -                       155

ステップ 4: R1 および R2 が異なるグループ化集合で使用されるため、 R1 の結果が非ヌル値である場合は常に R2 はヌル値であり、 R2 の結果が非ヌル値である場合は常に R1 はヌル値になることに注意してください。 つまり、COALESCE 関数を使用すれば、 これらの列を単一列に統合することができるということです。 また、ORDER BY 文節でこの列を使用すれば、 2 つのグループ化集合の結果をまとめることもできます。

  SELECT COALESCE(R1,R2) AS GROUP,
         WEEK(SALES_DATE) AS WEEK,
        DAYOFWEEK(SALES_DATE) AS DAY_WEEK,
         MONTH(SALES_DATE) AS MONTH,
         REGION, SUM(SALES) AS UNITS_SOLD
  FROM SALES,(VALUES('GROUP 1','GROUP 2')) AS X(R1,R2)
  GROUP BY GROUPING SETS ((R1, ROLLUP(WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE))),
                          (R2,ROLLUP( MONTH(SALES_DATE), REGION ) )  )
  ORDER BY GROUP, WEEK, DAY_WEEK, MONTH, REGION;

結果は次のようになります。

  GROUP   WEEK        DAY_WEEK    MONTH       REGION          UNITS_SOLD 
  ------- ----------- ----------- ----------- --------------- -----------
  GROUP 1          13           6           - -                        27
  GROUP 1          13           7           - -                        46
  GROUP 1          13           -           - -                        73
  GROUP 1          14           1           - -                        31
  GROUP 1          14           2           - -                        43
  GROUP 1          14           -           - -                        74
  GROUP 1          53           1           - -                         8
  GROUP 1          53           -           - -                         8
  GROUP 1           -           -           - -                       155
  GROUP 2           -           -           3 Manitoba                 22
  GROUP 2           -           -           3 Ontario-North             8
  GROUP 2           -           -           3 Ontario-South            34
  GROUP 2           -           -           3 Quebec                   40
  GROUP 2           -           -           3 -                       104
  GROUP 2           -           -           4 Manitoba                 17
  GROUP 2           -           -           4 Ontario-North             1
  GROUP 2           -           -           4 Ontario-South            14
  GROUP 2           -           -           4 Quebec                   11
  GROUP 2           -           -           4 -                        43
  GROUP 2           -           -          12 Manitoba                  2
  GROUP 2           -           -          12 Ontario-South             4
  GROUP 2           -           -          12 Quebec                    2
  GROUP 2           -           -          12 -                         8
  GROUP 2           -           -           - -                       155

例 C8: 以下の例は、CUBE を実行する場合の種々の列関数の使用例を示しています。 また、この例は、cast 関数および丸めを利用して、 妥当な精度と位取りで 10 進数の結果を生成します。

  SELECT MONTH(SALES_DATE) AS MONTH,
         REGION,
         SUM(SALES) AS UNITS_SOLD, 
         MAX(SALES) AS BEST_SALE, 
         CAST(ROUND(AVG(DECIMAL(SALES)),2) AS DECIMAL(5,2)) AS AVG_UNITS_SOLD 
  FROM SALES 
  GROUP BY CUBE(MONTH(SALES_DATE),REGION)
  ORDER BY MONTH, REGION

これは次のような結果になります。

MONTH       REGION          UNITS_SOLD  BEST_SALE   AVG_UNITS_SOLD
----------- --------------- ----------- ----------- --------------
          3 Manitoba                 22           7           3.14
          3 Ontario-North             8           3           2.67
          3 Ontario-South            34          14           4.25
          3 Quebec                   40          18           5.00
          3 -                       104          18           4.00
          4 Manitoba                 17           9           5.67
          4 Ontario-North             1           1           1.00
          4 Ontario-South            14           8           4.67
          4 Quebec                   11           8           5.50
          4 -                        43           9           4.78
         12 Manitoba                  2           2           2.00
         12 Ontario-South             4           3           2.00
         12 Quebec                    2           1           1.00
         12 -                         8           3           1.60
          - Manitoba                 41           9           3.73
          - Ontario-North             9           3           2.25
          - Ontario-South            52          14           4.00
          - Quebec                   53          18           4.42
          - -                       155          18           3.87


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