group by分组函数之rollup与cube用法

最新推荐文章于 2024-06-27 12:00:17 发布
最新推荐文章于 2024-06-27 12:00:17 发布
阅读量1k 收藏
点赞数 1
分类专栏： Oracle PLSQL/SQL 文章标签： sql oracle 11g

ROLLUP，是GROUP BY子句的一种扩展，可以为每个分组返回小计记录以及为所有分组返回总计记录。

CUBE，也是GROUP BY子句的一种扩展，可以返回每一个列组合的小计记录，同时在末尾加上总计记录。
示例：
一、初始化表及数据代码如下：

点击(此处)折叠或打开

  1. SQL> create table group_test (group_id int, job varchar2(10), name varchar2(10), salary int);

  3. 表已创建。

  5. SQL> 
  6. SQL> insert into group_test values (10,'Coding', 'Bruce',1000);

  8. 已创建 1 行。

  10. SQL> insert into group_test values (10,'Programmer','Clair',1000);

  12. 已创建 1 行。

  14. SQL> insert into group_test values (10,'Architect', 'Gideon',1000);

  16. 已创建 1 行。

  18. SQL> insert into group_test values (10,'Director', 'Hill',1000);

  20. 已创建 1 行。

  22. SQL> 
  23. SQL> insert into group_test values (20,'Coding', 'Jason',2000);

  25. 已创建 1 行。

  27. SQL> insert into group_test values (20,'Programmer','Joey',2000);

  29. 已创建 1 行。

  31. SQL> insert into group_test values (20,'Architect', 'Martin',2000);

  33. 已创建 1 行。

  35. SQL> insert into group_test values (20,'Director', 'Michael',2000);

  37. 已创建 1 行。

  39. SQL> 
  40. SQL> insert into group_test values (30,'Coding', 'Rebecca',3000);

  42. 已创建 1 行。

  44. SQL> insert into group_test values (30,'Programmer','Rex',3000);

  46. 已创建 1 行。

  48. SQL> insert into group_test values (30,'Architect', 'Richard',3000);

  50. 已创建 1 行。

  52. SQL> insert into group_test values (30,'Director', 'Sabrina',3000);

  54. 已创建 1 行。

  56. SQL> 
  57. SQL> insert into group_test values (40,'Coding', 'Samuel',4000);

  59. 已创建 1 行。

  61. SQL> insert into group_test values (40,'Programmer','Susy',4000);

  63. 已创建 1 行。

  65. SQL> insert into group_test values (40,'Architect', 'Tina',4000);

  67. 已创建 1 行。

  69. SQL> insert into group_test values (40,'Director', 'Wendy',4000);

  71. 已创建 1 行。

  73. SQL> 
  74. SQL> commit;

  76. 提交完成。

  78. SQL> select * from group_test;

  80.    GROUP_ID JOB        NAME           SALARY
    ---------- ---------- ---------- ----------
            10 Coding     Bruce            1000
            10 Programmer Clair            1000
            10 Architect  Gideon           1000
            10 Director   Hill             1000
            20 Coding     Jason            2000
            20 Programmer Joey             2000
            20 Architect  Martin           2000
            20 Director   Michael          2000
            30 Coding     Rebecca          3000
            30 Programmer Rex              3000
            30 Architect  Richard          3000


      GROUP_ID JOB        NAME           SALARY
    ---------- ---------- ---------- ----------
            30 Director   Sabrina          3000
            40 Coding     Samuel           4000
            40 Programmer Susy             4000
            40 Architect  Tina             4000
            40 Director   Wendy            4000


  83. 已选择16行。

二、group by一般分组与rollup语句区别
先看一般的group by语句

点击(此处)折叠或打开

  1. SQL> select group_id,sum(salary) from group_test
  2.   2 group by group_id;

  4.   GROUP_ID SUM(SALARY)
  5. ---------- -----------
  6.         30 12000
  7.         20 8000
  8.         40 16000
  9.         10 4000
使用rollup分组后如下

点击(此处)折叠或打开

  1. SQL> edit
  2. 已写入 file afiedt.buf

  4.   1 select group_id,sum(salary) from group_test
  5.   2* group by rollup(group_id)
  6. SQL> /

  8.   GROUP_ID SUM(SALARY)
  9. ---------- -----------
  10.         10 4000
  11.         20 8000
  12.         30 12000
  13.         40 16000
  14.            40000
以上sql与上一sql执行结果相比，通过group_id分组查询，使用rollup查询结果会增加一个汇总合计列，可通过union all改写如下：

SQL> edit
已写入 file afiedt.buf

  1  select group_id,sum(salary) from group_test
  2  group by group_id
  3  union all
  4  select null,sum(salary) from group_test
  5* order by 1
SQL> /

  GROUP_ID SUM(SALARY)
---------- -----------
        10        4000
        20        8000
        30       12000
        40       16000
                   40000

下面查看通过group_id,job二个字段一般分组与rollup语句的区别

SQL> select group_id,job,sum(salary) from group_test
  2  group by rollup(group_id,job);

  GROUP_ID JOB        SUM(SALARY)
---------- ---------- -----------
        10 Coding            1000
        10 Director          1000
        10 Architect         1000
        10 Programmer        1000
        10                   4000
        20 Coding            2000
        20 Director          2000
        20 Architect         2000
        20 Programmer        2000
        20                   8000
        30 Coding            3000

  GROUP_ID JOB        SUM(SALARY)
---------- ---------- -----------
        30 Director          3000
        30 Architect         3000
        30 Programmer        3000
        30                  12000
        40 Coding            4000
        40 Director          4000
        40 Architect         4000
        40 Programmer        4000
        40                  16000
                            40000

已选择21行。
用union all方式对上面代码改写如下：

SQL> select group_id,job,sum(salary) from group_test
  2  group by group_id,job
  3  union all
  4  select group_id,null,sum(salary) from group_test
  5  group by group_id
  6  union all
  7  select null,null,sum(salary) from group_test
  8  order by 1,2;

  GROUP_ID JOB        SUM(SALARY)
---------- ---------- -----------
        10 Architect         1000
        10 Coding            1000
        10 Director          1000
        10 Programmer        1000
        10                   4000
        20 Architect         2000
        20 Coding            2000
        20 Director          2000
        20 Programmer        2000
        20                   8000
        30 Architect         3000

  GROUP_ID JOB        SUM(SALARY)
---------- ---------- -----------
        30 Coding            3000
        30 Director          3000
        30 Programmer        3000
        30                  12000
        40 Architect         4000
        40 Coding            4000
        40 Director          4000
        40 Programmer        4000
        40                  16000
                            40000

已选择21行。

SQL> select group_id,job,grouping(group_id),grouping(job),sum(salary)
  2  from group_test
  3  group by rollup(group_id,job);

  GROUP_ID JOB        GROUPING(GROUP_ID) GROUPING(JOB) SUM(SALARY)
---------- ---------- ------------------ ------------- -----------
        10 Coding                      0             0        1000
        10 Director                    0             0        1000
        10 Architect                   0             0        1000
        10 Programmer                  0             0        1000
        10                             0             1        4000
        20 Coding                      0             0        2000
        20 Director                    0             0        2000
        20 Architect                   0             0        2000
        20 Programmer                  0             0        2000
        20                             0             1        8000
        30 Coding                      0             0        3000

  GROUP_ID JOB        GROUPING(GROUP_ID) GROUPING(JOB) SUM(SALARY)
---------- ---------- ------------------ ------------- -----------
        30 Director                    0             0        3000
        30 Architect                   0             0        3000
        30 Programmer                  0             0        3000
        30                             0             1       12000
        40 Coding                      0             0        4000
        40 Director                    0             0        4000
        40 Architect                   0             0        4000
        40 Programmer                  0             0        4000
        40                             0             1       16000
                                       1             1       40000

已选择21行。

分析上面sql执行结果，可知grouping用法，当对某例进行了汇总合计时，grouping（此列）的值为1，否则为0。
三、group by一般分组与cube语句区别

SQL> edit
已写入 file afiedt.buf

  1  select group_id,job,sum(salary) from group_test
  2  group by cube(group_id,job)
  3* order by 1,2
SQL> /

  GROUP_ID JOB        SUM(SALARY)
---------- ---------- -----------
        10 Architect         1000
        10 Coding            1000
        10 Director          1000
        10 Programmer        1000
        10                   4000
        20 Architect         2000
        20 Coding            2000
        20 Director          2000
        20 Programmer        2000
        20                   8000
        30 Architect         3000

  GROUP_ID JOB        SUM(SALARY)
---------- ---------- -----------
        30 Coding            3000
        30 Director          3000
        30 Programmer        3000
        30                  12000
        40 Architect         4000
        40 Coding            4000
        40 Director          4000
        40 Programmer        4000
        40                  16000
           Architect        10000
           Coding           10000

  GROUP_ID JOB        SUM(SALARY)
---------- ---------- -----------
           Director         10000
           Programmer       10000
                            40000

已选择25行。

你会发现，cube与rollup用法不同，cube会对所有例进行分组汇总合计。
通过以上例子，可知：
如果是Group by  ROLLUP(A, B, C)的话，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。
如果是GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作。 grouping_id()可以美化效果。除了使用GROUPING函数，还可以使用GROUPING_ID来标识GROUP BY的结果。

也可以 Group by Rollup(A,(B,C)) ，Group by A Rollup(B,C)，…… 这样任意按自己想要的形式结合统计数据，非常方便。


转载自http://blog.itpub.net/21251711/viewspace-1073184/，感谢原作者！

blockheading
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
mysql中的cube和rollup_group by、rollup、cube的用法以及区别
weixin_35642839的博客
02-08 812
在此使用oracle，oracle 中的用法为 group by [rollup|cube]( colomn),sql server中的用法为group by colomn with [rollup|cube]首先要弄明白rollup 和cube，就要知道group by的用法，group by 为对列进行分组，只展现分组统计的值，而rollup 为分层次展现，cube为展现列中所有层次，听我娓娓...
Oracle 聚合实现小计、合计 (GROUP BY ROLLUP)
xy596356456的博客
08-30 7821
1.先创建一个测试表 -- Create table 水果月销售记录表 create table C_FRUITS_SALES_RECORD_T ( quarter VARCHAR2(20),--季度 month VARCHAR2(20),--月份 apple NUMBER,--苹果销量 banana NUMBER,--香蕉销量 orange NUMBER--橘子销量 ) 导入测试数据 QUARTER MONTH APPLE BANAN...
group by、rollup、cube的用法以及区别
weixin_30446197的博客
07-27 375
在此使用oracle，oracle 中的用法为 group by [rollup|cube]( colomn),sql server中的用法为group by colomn with [rollup|cube] 首先要弄明白rollup 和cube，就要知道group by的用法，group by 为对列进行分组，只展现分组统计的值，而rollup 为分层次展现，cube为展现列中所有层次...
ROLLUP,CUBE,GROUP BY
qq_42835776的博客
09-23 310
ROLLUP 运算符生成的结果集类似于 CUBE 运算符生成的结果集。 下面是 CUBE 和 ROLLUP 之间的具体区别： CUBE 生成的结果集显示了所选列中值的所有组合的聚合。 ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合。 ROLLUP 优点： （１）ROLLUP 返回单个结果集，而 COMPUTE BY 返回多个结果集，而多个结果集会增加应用程序代码的复杂性。 （２）ROLLUP 可以在服务器游标中使用，而 COMPUTE BY 则不可以。 （３）有时，查询优
GROUP BY ROLLUP和GROUP BY CUBE 的区别
胡振涛
05-12 307
在进行数据库视频的过程中，学到的东西很多，尤其对于SQL SERVER的增删改查，收货更是慢慢的，那么今天谈一次在数据库视频过程中遇到的： “ ROLLUP和GROUP BY CUBE ”的区别 首先这两个赐予的出现都是在分组里出现的，但是两者有着小小的区别。 首先是当查询条件为一个时：（如下） 那么此时两者的结果并没有什么区别，最后所得的查询结果一样：（...
group by rollup函数的用法
java框架技术
07-28 1960
[code="java"] 有个表a有字段c1,c2有记录 2015-03-01 2 2015-03-01 3 2015-03-02 5 想要得到得到如下结果 2015-03-01 4 2015-03-02 5 合计 9 可以通过 select decode(grouping(c1),1,'合计',c1),sum(c2) from ...
group by分组函数之rollup与cube用法1
08-08
而在GROUP BY的基础上，Oracle数据库提供了两种高级分组功能：ROLLUP和CUBE，它们允许我们更灵活地生成汇总数据。 1. ROLLUP（滚联回溯）： ROLLUP是GROUP BY的一个扩展，它不仅返回每个单独的分组结果，还会生成...
GROUP BY子句（rollup,cube,grouping sets）实例说明
08-22
GROUP BY 子句（rollup, ...GROUP BY 语句用于基本的分组，GROUP BY ROLLUP 语句用于生成所有可能的分组结果，GROUP BY CUBE 语句用于生成所有可能的组合结果，GROUP BY GROUPING SETS 语句用于生成指定的分组结果。
SQLServer中汇总功能的使用GROUPING,ROLLUP和CUBE
09-11
然而，GROUP BY还可以与GROUPING、ROLLUP和CUBE运算符结合使用，以实现更复杂的多维度汇总功能。这些高级的汇总方法在数据分析和报表生成中非常有用，尤其是在处理大型数据集时。 GROUPING运算符主要用于识别汇总行...
oracle 分组函数
01-08
本篇文章将详细阐述Oracle分组函数的概念、功能以及常用分组函数的使用方法。 一、分组函数概述 分组函数主要作用于一组数据上，而不是单个记录，它们用于对数据进行汇总，如计算总和、平均值、最大值、最小值等。...
ORACLE学习笔记系列（15）使用扩展的 GROUP BY 子句
08-03
在"ORACLE学习笔记系列（15）使用扩展的 GROUP BY 子句"这篇博文中，作者深入探讨了GROUP BY子句的高级用法，特别是如何利用扩展功能来处理更复杂的查询需求。 在标准的GROUP BY语句中，我们通常会指定一列或多列，...
【SQL】group by rollup的使用
热门推荐
西瓜那么甜
08-12 2万+
这周看代码，看到了一个sql语法，使用的是group by rollup，一开始看文档没有明白，后来写了个例子又问了问别人才明白。不多说直接上例子，因为是例子所以就不要纠结字段类型了。 首先创建一张表  CREATE TABLE "user" ( "id" int(11) NOT NULL AUTO_INCREMENT, "name" varchar(20) DEFAULT NULL...
Group by后使用rollup
Baron_ND的博客
08-26 4663
一、如何理解group by后带rollup子句所产生的效果 group by后带rollup子句的功能可以理解为：先按一定的规则产生多种分组，然后按各种分组统计数据（至于统计出的数据是求和还是最大值还是平均值等这就取决于SELECT后的聚合函数）。因此要搞懂group by后带rollup子句的用法主要是搞懂它是如何按一定的规则产生多种分组的。另group by后带rollup子句所返回的结果集，可以理解为各个分组所产生的结果集的并集且没有去掉重复数据。下面举例说明： 1、对比没有带rollup的go
group by rollup()和group by grouping sets()的使用
xingandxin的博客
07-13 452
【SQL】group by rollup()和group by grouping sets()的使用 1.group by rollup() 逐次对字段进行group by 操作（分组计数） 注意：有顺序，必须是有层级关系的字段组合（例如：地市、区县、乡镇） 2.group by grouping sets() 分别对每组或每个字段进行group by操作。 对各个层级进行汇总，将各层级的汇总值union all 在一起，组合成一个结果集。 null代表count（*） 例如： #此处用A、B、C、D代表地
group by后面加rollup、cube、grouping sets分别代表什么含义
IT_心如止水的博客
05-17 870
众所周知，group by 语法是对给定的字段进行分组统计，最后得到一个分组统计数据。 满足一下几个要求： 1、select语句中的字段必须为分组字段或者聚合函数（
Group by后加rollup、cube、grouping sets的用法和区别
xiaobai_laobai的博客
09-03 2395
Group by后加rollup、cube、grouping sets的用法和区别 一、相关分析 通常当聚合率和数据量没有大于一定程度时，对于不涉及Rollup、Cube、Grouping_Sets这三种操作的聚合很少出现GC问题。对于Rollup、Cube、Grouping_Sets操作可采用如下优化方法避免GC。 Rollup / Cube / Grouping_Sets时，某些场景下，如果多维度的字段比较多，内存或者GC会造成性能问题。特别的， 在实现这三种操作 时， 记录数会出现倍数的膨胀， 调优的
数据库小计和总计之 Rollup函数 简单介绍
qq_14959289的博客
09-01 7536
分组函数之ROLLUP用法简单介绍
SQL面试题练习 —— 截止目前登陆用户数及登陆用户列表
最新发布
hu_wei123的博客
06-27 210
题目来源：滴滴。
GROUP BY ; PARTITION BY ;ROLLUP和CUBE的区别
05-04
GROUP BY、PARTITION BY、ROLLUP 和 CUBE 都是 SQL 中用于数据分组的关键字。 - GROUP BY：是 SQL 中最基本的分组操作关键字，用于将数据按照指定的列进行分组，并对每组数据进行聚合操作。 - PARTITION BY：是在 OVER 子句中用于分区窗口函数的关键字，用于将查询结果集分成多个分区，然后对每个分区进行聚合操作。 - ROLLUP：是 GROUP BY 子句中的一种扩展，用于生成分组集合的多级汇总，从而生成更细粒度的聚合结果。 - CUBE：也是 GROUP BY 子句中的一种扩展，用于生成交叉分组集合的多级汇总，从而生成更全面的聚合结果。 它们之间的区别如下： - GROUP BY 只能一次性对数据进行分组，而 ROLLUP 和 CUBE 可以生成多级分组，并且可以在同一个查询中同时使用。 - PARTITION BY 可以在一个查询中使用多次，而 GROUP BY、ROLLUP 和 CUBE 只能使用一次。 - ROLLUP 和 CUBE 会生成更多的聚合数据和分组集合，因此会比 GROUP BY 和 PARTITION BY 生成更大的数据集。 - ROLLUP 和 CUBE 生成的数据集可能会包含 NULL 值，而 GROUP BY 和 PARTITION BY 不会生成 NULL 值。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值