分组集、汇总和多维数据集查询示例
在子查询中，您可以使用分组查询中的分组集、汇总和立方体子句。
以下示例说明了分组查询中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：
- 一个带有基本GROUP BY子句的查询，涉及3列：
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表中的两组不同的行分组生成结果。
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第13周的行来自第一组，其他行来自第二组。
- 示例 3：
- 如果您在汇总子句中使用示例2中分组集合涉及的三个不同列，则可以看到
(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相同的查询，使用CUBE代替ROLLUP，结果中
(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：
- 这个示例显示了两个简单的 ROLLUP 查询，后面一个查询将两个 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
使用两个ROLLUP子句作为分组集会导致结果中出现重复的行。 甚至有两个总计行。
观察 ORDER BY 的使用对结果的影响：- 在第一个分组集中，Week 53 已重新定位到末尾。
- 在第二个分组集中，Month 12 现已定位到末尾，并且区域现在按字母顺序显示。
- 空值排在较高的位置。
示例 7：
- 在一次性执行多次汇总操作的查询中（例如例6-3），您可能需要指出哪一组数据集产生了每一行。 以下步骤演示了如何提供一个列（称为GROUP），用于指示结果集中每行的来源。 起源是指结果集中哪一组数据在结果集中产生了该行。
- 步骤 1：
- 介绍一种生成新数据值的方法，使用从 SYSDUMMY1 表中选择的查询。 下面的查询显示了如何通过两个列
（
R1
和R2
，以及一行数据。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：
- 将
表格X
与SALES表格相乘。 以下查询添加了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
在结果中不为空，R2
为空，且每当R2
结果不为空，则R1
为空。 您可以使用COALESCE函数将这些列合并为单个列 （GROUP
）。 您还可以使用ORDER BY子句中的GROUP列
将两组分组的结果放在一起。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 子句时各种聚合函数的使用方法。 该示例还利用强制转换函数和舍入方法来生成具有合理精度和小数位的十进制结果。
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