グループ化集合、CUBE、および ROLLUP 照会の例
以下の例は、グループ化、CUBE、および ROLLUP 形式の副選択照会を示しています。
例 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 の行は、最初のグループ化集合のものであり、 それ以外の行は 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 表から選択された行の総計と、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 - 例 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 - - - - 1552 つの ROLLUP をグループ化集合として使用すると、結果に重複した行が組み入れられます。 総計行も 2 つになります。
ORDER BY を使用すると結果にどのような影響があるかを調べてみます。- 最初のグループ化集合では、week 53 が最後に位置変更されています。
- 2 番目のグループ化集合では、month 12 が最後に位置付けられ、 地域がアルファベット順になっています。
- NULL 値は上位にソートされます。
- 例 6-1:
- 例 7: 単一のパスで複数の ROLLUP を実行する照会 (例えば 例 6-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 の結果が非 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,(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 - 例 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