グループ化セット、ロールアップ、およびキューブ照会の例
副選択照会では、GROUP BY 文節の GROUPING SETS、ROLLUP、および CUBE 節を使用できます。
以下の例では、副選択照会における GROUP BY 節の GROUPING SETS 節、ROLLUP 節、および CUBE 節の使用法を示します。 例1～4のクエリは、述語 '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- 例 1:
- 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 - 例 2:
- 以下の照会では、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 4WEEK 13 の行は 1 番目のグループ化セットからのもので、その他の行は 2 番目のグループ化セットからのものです。
- 例 3:
- 例 2 のグループ化セットに関係する 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 - 例 4:
- 例 3 と同じ照会で 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 - 例 5:
- 以下の照会では、SALES_PERSON と MONTH で集約された行のグループに加えて、SALES 表から選択された行の grand-total を含む結果セットが返されます。
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 - 例 6:
- この例では、まず 2 つの単純な ROLLUP 照会を示します。その後の照会では、単一の結果セットにおいてそれらの 2 つの ROLLUP 節をグループ化セットとして扱い、それらのグループ化セットに関わる各列の行の順序を指定します。
- 例 6-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- 例 6-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- 例 6-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 を使用すると結果にどのような影響があるかを調べてみます。- 最初のグループ化集合では、week 53 が最後に位置変更されています。
- 2 番目のグループ化集合では、month 12 が最後に位置付けられ、 地域がアルファベット順になっています。
- NULL 値は上位にソートされます。
- 例 7:
- 単一のパスで複数の ROLLUP 操作を実行する照会 (例 6-3 など) では、各行がどのグループ化セットによって生成されたかを示すことが必要な場合があります。 以下のステップでは、結果セット内の各行の起点を示す列 (GROUP という名前) を設定する方法を示します。 起点 とは、結果セット内の行を生成した、2 つのグループ化セットのうちの一方を表しています。
- ステップ 1:
- SYSDUMMY1 表から選択する照会を使用して、新しいデータ値を生成する方法を導入します。 以下の照会は、2 つの列 (
R1
およびR2
) と 1 つのデータ行を使用して、表 (X
という名前) を派生させる方法を示しています。SELECT R1,R2 FROM (SELECT 'GROUP 1' as R1, 'GROUP 2' as R2 FROM SYSIBM.SYSDUMMY1) 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, (SELECT 'GROUP 1' as R1, 'GROUP 2' as R2 FROM SYSIBM.SYSDUMMY1) AS X(R1,R2); - ステップ 3:
R1
列とR2
列をグループ化セットと組み合わせて、R1
とR2
をロールアップ分析に含めることができるようになりました。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, (SELECT 'GROUP 1' as R1, 'GROUP 2' as R2 FROM SYSIBM.SYSDUMMY1) 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
が結果内で NULL 以外の場合、R2
は NULL になり、R2
が結果内で NULL 以外の場合、R1
は NULL になります。 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, (SELECT 'GROUP 1' as R1, 'GROUP 2' as R2 FROM SYSIBM.SYSDUMMY1) 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
- 例 8:
- 以下の例では、CUBE 節を使用するときに各種の集約関数を使用する方法を示します。 また、この例は、cast 関数および round 関数を利用して、
妥当な精度と位取りで 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