`
fyd222
  • 浏览: 106551 次
  • 性别: Icon_minigender_1
  • 来自: 杭州
社区版块
存档分类
最新评论

SQL 基础--> ROLLUP与CUBE运算符实现数据汇总

 
阅读更多

--=============================================

-- SQL 基础--> ROLLUP与CUBE运算符实现数据汇总

--=============================================

在使用ROLLUP与CUBE运算符实现数据的汇总是，Oracle与SQL Server使用了不同的写法，但其实质是一样的，都遵循了SQL规范。

ROLLUP与CUBE运算符都是对GROUP BY 子句的扩展

SQL Server中的用法

GROUP BY col1,col2

WITH ROLLUP | CUBE ;

Oracle 中的用法

GROUP BY

ROLLUP | CUBE (col1,col2);

ROLLUP：为每个分组返回小计记录以及为所有分组返回总计记录

CUBE：返回列中所有组合的小计记录以及为所有分组返回总计记录

关于在SQL Sever中ROLLUP与CUBE运算符，请参照：ROLLUP 与CUBE 运算符的使用

一、演示ROLLUP 及CUBE 的使用

--使用常规的GROUP BY 实现数据的汇总

SQL> SELECT deptno,SUM(sal) FROM emp WHERE deptno IS NOT NULL

2 GROUP BY deptno;

DEPTNO SUM(SAL)

---------- ----------

30 9400

20 10875

10 8750

--增加ROLLUP运算符实现对数据的汇总，增加了对DEPTNO列的总计

SQL> SELECT deptno,SUM(sal) FROM emp WHERE deptno IS NOT NULL

2 GROUP BY ROLLUP (deptno);

DEPTNO SUM(SAL)

---------- ----------

10 8750

20 10875

30 9400

29025

--使用ROLLUP实现对多列数据进行汇总

--以下示例，先按deptno,job分组，并实现sum函数的聚合，接下来按从右向左的方向实现更高层次的聚合，如对同一个

--部门的作汇总，最后对所有部门作汇总。汇总层数为n+1,其中n为rollup中的列数

--因表emp存在deptno为空的记录，故以下所有演示中使用了where 子句过滤空值

SQL> SELECT deptno,job, SUM(sal) FROM emp WHERE deptno IS NOT NULL

2 GROUP BY ROLLUP (deptno,job)

3 ORDER BY deptno,job;

DEPTNO JOB SUM(SAL)

---------- --------- ----------

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 5000

10 8750

20 ANALYST 6000

20 CLERK 1900

20 MANAGER 2975

20 10875

30 CLERK 950

30 MANAGER 2850

30 SALESMAN 5600

DEPTNO JOB SUM(SAL)

---------- --------- ----------

30 9400

29025

--交换deptno,job列查看不同的列的顺序导致了不同的结果，原因是不同的分组产生了不同的结果

SQL> SELECT deptno,job, SUM(sal) FROM emp WHERE deptno IS NOT NULL

2 GROUP BY ROLLUP (job,deptno)

3 ORDER BY job,deptno;

DEPTNO JOB SUM(SAL)

---------- --------- ----------

20 ANALYST 6000

ANALYST 6000

10 CLERK 1300

20 CLERK 1900

30 CLERK 950

CLERK 4150

10 MANAGER 2450

20 MANAGER 2975

30 MANAGER 2850

MANAGER 8275

10 PRESIDENT 5000

DEPTNO JOB SUM(SAL)

---------- --------- ----------

PRESIDENT 5000

30 SALESMAN 5600

SALESMAN 5600

29025

--ROLLUP ,CUBE可以配合不同的聚合函数来使用

SQL> SELECT deptno,job, SUM(sal),ROUND(AVG(sal),2) FROM emp WHERE deptno IS NOT NULL

2 GROUP BY ROLLUP (deptno,job)

3 ORDER BY deptno,job;

DEPTNO JOB SUM(SAL) ROUND(AVG(SAL),2)

---------- --------- ---------- -----------------

10 CLERK 1300 1300

10 MANAGER 2450 2450

10 PRESIDENT 5000 5000

10 8750 2916.67

20 ANALYST 6000 3000

20 CLERK 1900 950

20 MANAGER 2975 2975

20 10875 2175

30 CLERK 950 950

30 MANAGER 2850 2850

30 SALESMAN 5600 1400

DEPTNO JOB SUM(SAL) ROUND(AVG(SAL),2)

---------- --------- ---------- -----------------

30 9400 1566.67

29025 2073.21

13 rows selected.

--使用CUBE子句实现对数据的汇总

--从结果集中可以看出CUBE对不同的维度也实现了数据汇总，本例中多出的列即为不同的JOB也产生了汇总数据

SQL> SELECT deptno,job, SUM(sal) FROM emp WHERE deptno IS NOT NULL

2 GROUP BY CUBE (deptno,job)

3 ORDER BY deptno,job;

DEPTNO JOB SUM(SAL)

---------- --------- ----------

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 5000

10 8750

20 ANALYST 6000

20 CLERK 1900

20 MANAGER 2975

20 10875

30 CLERK 950

30 MANAGER 2850

30 SALESMAN 5600

DEPTNO JOB SUM(SAL)

---------- --------- ----------

30 9400

ANALYST 6000

CLERK 4150

MANAGER 8275

PRESIDENT 5000

SALESMAN 5600

29025

18 rows selected.

二、使用GROUPING函数处理汇总结果中的空值

GROUPING函数仅在使用ROLLUP和CUBE查询中使用，可以接受一列，其结果返回为或者，如果列值为空，则返回，否则返回

--单列使用GROUPING函数

SQL> SELECT GROUPING(deptno),deptno, SUM(sal) FROM emp WHERE deptno IS NOT NULL

2 GROUP BY ROLLUP(deptno)

3 ORDER BY deptno;

GROUPING(DEPTNO) DEPTNO SUM(SAL)

---------------- ---------- ----------

0 10 8750

0 20 10875

0 30 9400

1 29025

--使用CASE子句转换GROUPING函数的返回值

SQL> SELECT CASE GROUPING(deptno) WHEN 1 THEN 'All Deptno' ELSE TO_CHAR(deptno) END AS deptnumber, SUM(sal)

2 FROM emp WHERE deptno IS NOT NULL

3 GROUP BY ROLLUP(deptno)

4 ORDER BY deptno;

DEPTNUMBER SUM(SAL)

---------------------------------------- ----------

10 8750

20 10875

30 9400

All Deptno 29025

--使用CASE子句转换多列GROUPING函数的返回值

SQL> SELECT CASE GROUPING(deptno) WHEN 1 THEN 'All Deptno' ELSE TO_CHAR(deptno) END AS "DeptNo",

2 CASE GROUPING(job) WHEN 1 THEN 'All Job' ELSE job END AS "Job",

3 SUM(sal)

4 FROM emp WHERE deptno IS NOT NULL

5 GROUP BY ROLLUP(deptno,job);

DeptNo Job SUM(SAL)

---------------------------------------- --------- ----------

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 5000

10 All Job 8750

20 CLERK 1900

20 ANALYST 6000

20 MANAGER 2975

20 All Job 10875

30 CLERK 950

30 MANAGER 2850

30 SALESMAN 5600

DeptNo Job SUM(SAL)

---------------------------------------- --------- ----------

30 All Job 9400

All Deptno All Job 29025

--CUBE与GROUPING函数的结合使用

SQL> SELECT CASE GROUPING(deptno) WHEN 1 THEN 'All Deptno' ELSE TO_CHAR(deptno) END AS "DeptNo",

2 CASE GROUPING(job) WHEN 1 THEN 'All Job' ELSE job END AS "Job",

3 SUM(sal)

4 FROM emp WHERE deptno IS NOT NULL

5 GROUP BY CUBE(deptno,job)

6 ORDER BY deptno,job;

DeptNo Job SUM(SAL)

---------------------------------------- --------- ----------

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 5000

10 All Job 8750

20 ANALYST 6000

20 CLERK 1900

20 MANAGER 2975

20 All Job 10875

30 CLERK 950

30 MANAGER 2850

30 SALESMAN 5600

DeptNo Job SUM(SAL)

---------------------------------------- --------- ----------

30 All Job 9400

All Deptno ANALYST 6000

All Deptno CLERK 4150

All Deptno MANAGER 8275

All Deptno PRESIDENT 5000

All Deptno SALESMAN 5600

All Deptno All Job 29025

三、使用GROUPING SETS 子句列出小计

--GROUPING SETS 子句仅返回小计的记录

--注意GROUPING SETS的用法是替换掉ROLLUP 或CUBE

SQL> SELECT deptno,job,SUM(sal)

2 FROM emp

3 WHERE deptno IS NOT NULL

4 GROUP BY GROUPING SETS(deptno,job)

5 ORDER BY deptno,job;

DEPTNO JOB SUM(SAL)

---------- --------- ----------

10 8750

20 10875

30 9400

ANALYST 6000

CLERK 4150

MANAGER 8275

PRESIDENT 5000

SALESMAN 5600

四、使用GROUPING_ID函数和HAVING子句过滤结果集

--GROUPING_ID函数接受一列或多列输入，返回一个十进制的值

--GROUPING_ID返回值为调用GROUPING函数的组合结果

--如0 0 ==> 00 ,0 1 ==> 1 , 1 0 ==> 2 , 1 1 ==> 3(左边为GROUPING函数得到的列值，右边为转换结果)

SQL> SELECT deptno,job,GROUPING(deptno) dept_grp,GROUPING(job) job_grp,

2 GROUPING_ID(deptno,job),SUM(sal)

3 FROM emp

4 WHERE deptno IS NOT NULL

5 GROUP BY CUBE(deptno,job)

6 ORDER BY deptno,job;

DEPTNO JOB DEPT_GRP JOB_GRP GROUPING_ID(DEPTNO,JOB) SUM(SAL)

---------- --------- ---------- ---------- ----------------------- ----------

10 CLERK 0 0 0 1300

10 MANAGER 0 0 0 2450

10 PRESIDENT 0 0 0 5000

10 0 1 1 8750

20 ANALYST 0 0 0 6000

20 CLERK 0 0 0 1900

20 MANAGER 0 0 0 2975

20 0 1 1 10875

30 CLERK 0 0 0 950

30 MANAGER 0 0 0 2850

30 SALESMAN 0 0 0 5600

DEPTNO JOB DEPT_GRP JOB_GRP GROUPING_ID(DEPTNO,JOB) SUM(SAL)

---------- --------- ---------- ---------- ----------------------- ----------

30 0 1 1 9400

ANALYST 1 0 2 6000

CLERK 1 0 2 4150

MANAGER 1 0 2 8275

PRESIDENT 1 0 2 5000

SALESMAN 1 0 2 5600

1 1 3 29025

--使用HAVING子句和GROUPING_ID过滤非小计和总计记录

SQL> SELECT deptno,job,

2 GROUPING_ID(deptno,job),SUM(sal)

3 FROM emp

4 WHERE deptno IS NOT NULL

5 GROUP BY CUBE(deptno,job)

6 HAVING GROUPING_ID(deptno,job) > 0

7 ORDER BY deptno,job;

DEPTNO JOB GROUPING_ID(DEPTNO,JOB) SUM(SAL)

---------- --------- ----------------------- ----------

10 1 8750

20 1 10875

30 1 9400

ANALYST 2 6000

CLERK 2 4150

MANAGER 2 8275

PRESIDENT 2 5000

SALESMAN 2 5600

3 29025

五、GROUP BY ,CUBE 或ROLLUP 中同时使用一列的处理

--即某些列同时在GROUP BY 子句和CUBE(ROLLUP)中存在

--结果中可以看出多出了一些重复的列

SQL> SELECT deptno,job,SUM(sal)

2 FROM emp

3 WHERE deptno IS NOT NULL

4 GROUP BY deptno,ROLLUP(deptno,job)

5 ORDER BY deptno,job;

DEPTNO JOB SUM(SAL)

---------- --------- ----------

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 5000

10 8750

10 8750

20 ANALYST 6000

20 CLERK 1900

20 MANAGER 2975

20 10875

20 10875

30 CLERK 950

DEPTNO JOB SUM(SAL)

---------- --------- ----------

30 MANAGER 2850

30 SALESMAN 5600

30 9400

30 9400

六、使用GROUP_ID函数过滤结果集

--GROUP_ID函可以消除GROUP BY 子句返回的重复记录

--GROUP_ID函数不接受任何参数

--对于特定分组结果中重复出现n次，GROUP_ID()函数返回到n-1 之间的一个整数

SQL> SELECT deptno,job,GROUP_ID(),SUM(sal)

2 FROM emp

3 WHERE deptno IS NOT NULL

4 GROUP BY deptno,ROLLUP(deptno,job)

5 ORDER BY deptno,job;

DEPTNO JOB GROUP_ID() SUM(SAL)

---------- --------- ---------- ----------

10 CLERK 0 1300

10 MANAGER 0 2450

10 PRESIDENT 0 5000

10 1 8750

10 0 8750

20 ANALYST 0 6000

20 CLERK 0 1900

20 MANAGER 0 2975

20 1 10875

20 0 10875

30 CLERK 0 950

DEPTNO JOB GROUP_ID() SUM(SAL)

---------- --------- ---------- ----------

30 MANAGER 0 2850

30 SALESMAN 0 5600

30 1 9400

30 0 9400

--使用HAVING子句和GROUP_ID函数过滤结果集

SQL> SELECT deptno,job,GROUP_ID(),SUM(sal)

2 FROM emp

3 WHERE deptno IS NOT NULL

4 GROUP BY deptno,ROLLUP(deptno,job)

5 HAVING GROUP_ID() = 0

6 ORDER BY deptno,job;

DEPTNO JOB GROUP_ID() SUM(SAL)

---------- --------- ---------- ----------

10 CLERK 0 1300

10 MANAGER 0 2450

10 PRESIDENT 0 5000

10 0 8750

20 ANALYST 0 6000

20 CLERK 0 1900

20 MANAGER 0 2975

20 0 10875

30 CLERK 0 950

30 MANAGER 0 2850

30 SALESMAN 0 5600

DEPTNO JOB GROUP_ID() SUM(SAL)

---------- --------- ---------- ----------

30 0 9400

七、总结：

ROLLUP 、CUBE 是对GROUP BY 子句的扩展

ROLLUP：根据grouping list 的rollup条款创建小计，然后从左向右再实现聚合。

1.首先按group by (col1,col2)标准分组聚合

2.按照rollup(col1,col2)中的列从右向左的顺序进行更高层次的聚合

3.创建n+1层的总计，n = rollup(col1,col2...)中的列数

CUBE: 创建所有可能的小计

1.结果集将会包括相应rollup的所有值并加上额外的合并

2.如果有n类在cube中规定，将会有的n次方的小计返回

GROUPING()函数：

仅仅接受CUBE或ROLLUP中的单列，不能使用复合列，如果列值为空，将返回，否则返回

通常配合CASE WHEN 用于替换空值

GROUPING SETS子句：

用于只返回小计记录

GROUPING_ID() 函数：

接受ROLLUP或CUBE中的多列，返回一个十进制值，实际上是GROUPING()函数值的组合

可以配合HAVING子句来过滤掉不需要的统计信息

GRUOUP_ID() 函数:

不接受任何参数

对于特定出现n次的分组， GRUOUP_ID()返回从到n-1之间的一个整数

可以配合HAVING子句消除group by 子句返回的重复记录

八、更多

ROLLUP 与 CUBE 运算符的使用

VmWare6.5.2下安装RHEL 5.4（配置Oracle安装环境）

Oracle 10g SGA 的自动化管理

Oracle 表空间与数据文件

SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)

Oracle 常用目录结构(10g)

分享到：
| Oracle 常用目录结构(10g)
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    Sql学习第四天——SQL 关于with cube,with rollup和grouping解释及演示

    1. **CUBE运算符**： `CUBE`生成的结果集包含了所有可能的子集，即所选列中值的所有组合的聚合。这意味着如果你有两个列`A`和`B`，`CUBE`会计算`A`和`B`的所有组合，包括`A`、`B`、以及单独的`A`和`B`的组合，以及...

    SQLSERVER中union,cube,rollup,cumpute运算符使用说明

    在SQLSERVER中，有四种特殊的运算符用于处理和汇总数据：UNION、CUBE、ROLLUP和COMPUTE。这些运算符在数据查询和分析时非常有用，尤其在处理多个数据源合并、多维数据分析和自定义汇总计算时。 首先，我们来详细...

    精通SQL--结构化查询语言详解

    8.3.3 rollup运算符和cube运算符 151 8.3.4 group by子句中的null值处理 153 8.3.5 having子句 153 8.3.6 having子句与where子句 154 8.3.7 select语句各查询子句总结 156 第9章 多表查询 157 9.1 本章用到的...

    SQLServer中汇总功能的使用GROUPING,ROLLUP和CUBE

    然而，GROUP BY还可以与GROUPING、ROLLUP和CUBE运算符结合使用，以实现更复杂的多维度汇总功能。这些高级的汇总方法在数据分析和报表生成中非常有用，尤其是在处理大型数据集时。 GROUPING运算符主要用于识别汇总行...

    sql-Group-by.rar_oracle

    7. **CUBE运算符**: CUBE生成所有可能的分组组合，包括单个值和所有值的组合，这在分析数据的不同维度时很有用。 8. **GROUPING SETS**: GROUPING SETS结合了ROLLUP和CUBE的功能，允许你指定一组特定的分组组合。 ...

    Microsoft SQL Server 2008技术内幕：T-SQL查询(第二卷)

    主要内容包括SQL的基础理论、查询优化、查询算法及复杂度，以及在使用子查询、表表达式、排名函数、数据聚合和透视转换、TOP和APPLY、数据修改、分区表、特殊数据结构等实际应用时会遇到的各种高级查询问题和解决...

    数据库管理与开发项目教程-教案-07项目四：使用T-SQL查询表数据-任务3连接查询.docx

    在数据库管理与开发中，T-SQL（Transact-SQL）是SQL Server中用于查询和操作数据的标准语言。本教程的项目四是关于使用T-SQL查询表数据，特别是任务3聚焦于连接查询，这是处理多表数据时非常关键的一个概念。 1. ...

    SQL语句-递归查询、分组、统计.pdf

    `ROLLUP`运算符用于创建汇总行，`CUBE`则生成所有可能的组合，包括空组。这些统计方法有助于理解数据的分布和趋势。 4. **GROUPING ID和GROUPING SETS**： `GROUPING`函数可以识别一个值是否在分组中，而`GROUPING...

    Microsoft_SQL_Server_2005技术内幕：T-SQL查询.pdf

    　步骤6：应用CUBE或ROLLUP选项 　步骤7：应用HAVING筛选器 　步骤8：处理SELECT列表 　步骤9：应用DISTINCT子句 　步骤10：应用ORDER　BY子句 　步骤11：应用TOP选项 　SQL Server 2005中新的逻辑处理阶段 　表...

    Microsoft+SQL+Server+2008技术内幕：T-SQL查询_源代码及附录 中文版

    主要内容包括SQL的基础理论、查询优化、查询算法及复杂度，以及在使用子查询、表表达式、排名函数、数据聚合和透视转换、TOP和APPLY、数据修改、分区表、特殊数据结构等实际应用时会遇到的各种高级查询问题和解决...

    SQLPLSQL.pdf

    - **CUBE功能**：介绍CUBE子句的用途及其与ROLLUP的区别。 ### 六、子查询 - **课程目标**：理解子查询的概念，掌握子查询的使用方法。 - **课程大纲**： - 子查询概述 - 子查询语句 - 子查询类型 - 多列子...

    sql and pl/sql

    ### SQL与PL/SQL知识点详解 #### 一、资料库基本概念 - **数据库管理系统（DBMS）**：Oracle数据库管理系统是一种关系型数据库管理系统（RDBMS），它使用SQL作为查询语言，支持多种数据类型、安全性特性以及事务...

    ORACLE和SQL Server的语法区别

    本文主要介绍Oracle与SQL Server在SQL语言层面的异同之处，重点在于Transact-SQL（T-SQL）与PL/SQL之间的区别，并提供了一些迁移策略。对于希望将现有的Oracle数据库系统迁移至SQL Server平台的用户来说，这些知识尤...

    T-SQL语句执行的顺序

    CUBE和ROLLUP是用于创建多维数据集的运算符，提供汇总的不同层次。在这个例子中未使用，但它们可以生成交叉总计或子总计。 10. **HAVING**： HAVING子句用于过滤GROUP BY后的结果集，它类似于WHERE，但作用于分组...

    Sql Server 分组统计并合计总数及WITH ROLLUP应用

    在SQL Server中，分组统计是一项基础且重要的功能，它允许我们按照特定列的值对数据进行聚合，以便分析和汇总信息。`GROUP BY`语句是实现这一目标的关键，而`WITH ROLLUP`是扩展这个功能的一种方法，特别适用于生成...

    C#.net_经典编程例子400个

    68<br>实例059　在ListBox控件间交换数据　68<br>实例060　将数据库数据添加到组合框中　70<br>实例061　借助绑定控件实现数据选择录入　71<br>实例062　ListBox拒绝添加重复信息　72<br>2.6　选择类控件应用　73<br...

    Oracle和SqlServer语法区别

    [GROUP BY [ALL] group_by_expression [,Un] [ WITH { CUBE | ROLLUp } ]] [HAVING search_condition] [ORDER BY order_expression [ASC | DESC] ] ``` 可以看到，Oracle的SELECT语句支持更多的子句，例如START ...

    sql server书籍技巧.rar

    本资料“sql server书籍技巧.rar”涵盖了多个SQL Server使用中的关键知识点，包括时间日期处理、字符串操作、排序与分页、树形和宝塔形数据结构、逐级汇总、二进制文件存取、定时任务实现、触发器应用等多个方面。...

Magicbox
Global site tag (gtag.js) - Google Analytics