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

最新推荐文章于 2024-07-22 09:40:00 发布
最新推荐文章于 2024-07-22 09:40:00 发布
阅读量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 821
在此使用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 7994
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...
数据库小计和总计之 Rollup函数 简单介绍
qq_14959289的博客
09-01 7681
分组函数之ROLLUP用法简单介绍
【转】group by后使用rollup子句总结
weixin_34168700的博客
05-29 931
今天接触到了group by后带rollup子句的用法（真是丑死人了，接触ORACLE一两年了今天才知道还有这样一个用法），group by后带rollup子句这个子句所产生的功能实在是太高级了，见了就高兴。但完后感觉也没什么的，很简单的。 下面按我的理解简单总结一下： 一、如何理解group by后带rollup子句所产生的效果 group by后带rollup子句的功能可以理解为：先按一...
Group by后使用rollup
Baron_ND的博客
08-26 4923
一、如何理解group by后带rollup子句所产生的效果 group by后带rollup子句的功能可以理解为：先按一定的规则产生多种分组，然后按各种分组统计数据（至于统计出的数据是求和还是最大值还是平均值等这就取决于SELECT后的聚合函数）。因此要搞懂group by后带rollup子句的用法主要是搞懂它是如何按一定的规则产生多种分组的。另group by后带rollup子句所返回的结果集，可以理解为各个分组所产生的结果集的并集且没有去掉重复数据。下面举例说明： 1、对比没有带rollup的go
group by cube 用法
tangaoyu520hf的博客
08-25 3602
/*GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)， 最后对全表进行GROUP BY操作。 */ --构造环境 drop table dept purge; drop table emp purge; create table dept as select * from scott.
group by分组函数之rollup与cube用法1
08-08
而在GROUP BY的基础上，Oracle数据库提供了两种高级分组功能：ROLLUP和CUBE，它们允许我们更灵活地生成汇总数据。 1. ROLLUP（滚联回溯）： ROLLUP是GROUP BY的一个扩展，它不仅返回每个单独的分组结果，还会生成...
Oracle分组函数之ROLLUP的基本用法
09-09
Oracle数据库中的分组函数是数据分析和报表生成的重要工具，它们允许我们对数据进行汇总和聚合。其中，`ROLLUP`函数是一种特殊的分组操作，它提供了多级分组的能力，可以生成汇总数据的不同层次，从最细粒度的分组到...
Oracle ROLLUP和CUBE 用法
03-16
ROLLUP操作是GROUP BY的一个扩展，它允许你生成一个包含不同层次的分组结果，从最细粒度的分组到最粗粒度的分组，类似于数据透视表的逐级展开。例如，如果你有一个按地区、城市和部门分组的数据集，使用ROLLUP可以...
Oracle中分组查询group by用法规则详解
12-14
- `GROUP BY`子句可以结合`DISTINCT`关键字用于消除重复行，但`DISTINCT`通常不与`GROUP BY`一起使用，因为`GROUP BY`本身就是用来分组去重的。 7. **扩展应用**： - `GROUP BY ROLLUP`用于创建分组的层次结构，...
GROUP BY子句（rollup,cube,grouping sets）实例说明
08-22
GROUP BY 子句（rollup, ...GROUP BY 语句用于基本的分组，GROUP BY ROLLUP 语句用于生成所有可能的分组结果，GROUP BY CUBE 语句用于生成所有可能的组合结果，GROUP BY GROUPING SETS 语句用于生成指定的分组结果。
group by、rollup、cube的用法以及区别
weixin_30446197的博客
07-27 385
在此使用oracle，oracle 中的用法为 group by [rollup|cube]( colomn),sql server中的用法为group by colomn with [rollup|cube] 首先要弄明白rollup 和cube，就要知道group by的用法，group by 为对列进行分组，只展现分组统计的值，而rollup 为分层次展现，cube为展现列中所有层次...
Group by后加rollup、cube、grouping sets的用法和区别
xiaobai_laobai的博客
09-03 2447
Group by后加rollup、cube、grouping sets的用法和区别 一、相关分析 通常当聚合率和数据量没有大于一定程度时，对于不涉及Rollup、Cube、Grouping_Sets这三种操作的聚合很少出现GC问题。对于Rollup、Cube、Grouping_Sets操作可采用如下优化方法避免GC。 Rollup / Cube / Grouping_Sets时，某些场景下，如果多维度的字段比较多，内存或者GC会造成性能问题。特别的， 在实现这三种操作 时， 记录数会出现倍数的膨胀， 调优的
Oracle SQL中的汇总Group by, Rollup 与 Grouping等函数联用示例
TechRunner
10-03 2801
Oracle 中的分组汇总函数 Group by，通过Rollup实现自动小结式的汇总……
mysql group by cube_group by cube和rollup学习笔记
weixin_26872803的博客
02-01 880
建表如下：create table TEST_GROUP(NAME VARCHAR2(20),CLASS VARCHAR2(10),KEMU VARCHAR2(4),CHENGJI VARCHAR2(3));insert into TEST_GROUP (NAME, CLASS, KEMU, CHENGJI)values ('张三', '1班', '数学', '90');insert into T...
group by rollup
brrby
06-28 707
group by rollup
oracle中group by rollup函数的的用法
宇宙的博客
08-12 3521
一、采用union all的方式拼接 采用union all 方式，union all 左侧查询相关明细记录 ，右侧拼接聚合函数组成的汇总行 简单例子： select column1,column2,column3,column4 from tableA union all select '合计'，sum(column2),sum(column3),sum(column4) from table...
SQL Server内置的HTAP技术
最新发布
2401_85789772的博客
07-22 561
假设用户建了一个行存索引和列存索引组合的表，事务插入数据时，会同时插入行存和Delta Store，Delta Store达到100W行阈值后冻结，tuple-mover后台线程会将其中较冷的数据迁移到Main Store，无论是过去的传统数仓，还是现在的大数据技术栈，都存在这个时效性问题。根据数仓场景的特点，SQL Server列存的开销其实可以接受，然后使用类Delta-Main架构也是比较主流的做法，但是到了HTAP的场景，整个数据库需要支撑高并发的查询和更新，列存的开销就会被放大。
group by ROLLUP
09-04
总而言之，使用GROUP BY ROLLUP子句可以实现按照指定列进行多层次的分组，并计算聚合函数的值。这样可以得到不同层次的分组总计。<span class="em">1</span><span class="em">2</span><span class="em">3 #### 引用...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值