`

Oracle ROLLUP和CUBE 用法

阅读更多

rollup只有第一个参数起作用也是理解不正确的,先看看例子吧:

 

 SQL>   select   grade,id,num   from   a;  
   
  GRADE             ID                                 NUM  
  ----------   ----------   ----------  
  a                     1                                       1  
  a                     2                                       2  
  b                     3                                       4  
  b                     4                                       4  
   
  对grade字段进行rollup:  
   
  SQL>   select   grade,sum(num)   from   a   group   by   rollup(grade);  
   
  GRADE                 SUM(NUM)  
  ----------   ----------  
  a                                       3  
  b                                       8  
                                        11  
  同时对grade和id字段进行rollup  
  SQL>   SELECT   decode(grouping_id(grade,ID),2,'小计',3,'合计',grade)   grade,  
      2                   decode(grouping_id(grade,ID),1,'小计',3,'合计',ID)   ID,  
      3                   SUM(num)  
      4     FROM   a   GROUP   BY   ROLLUP(grade,ID)  
      5     /  
   
  GRADE             ID                       SUM(NUM)  
  ----------   ----------   ----------  
  a                     1                                       1  
  a                     2                                       2  
  a                     小计                                 3  
  b                     3                                       4  
  b                     4                                       4  
  b                     小计                                 8  
  合计               合计                               11  
   
  7   rows   selected  
   
  再看看先对grade分组,再对id进行rollup的情况:  
   
  SQL>   SELECT   grade,  
      2                   decode(GROUPING(ID),1,'合计',ID)   ID,  
      3                   SUM(num)  
      4     FROM   a   GROUP   BY   grade,rollup(ID)  
      5     /  
   
  GRADE             ID                       SUM(NUM)  
  ----------   ----------   ----------  
  a                     1                                       1  
  a                     2                                       2  
  a                     合计                                 3  
  b                     3                                       4  
  b                     4                                       4  
  b                     合计                                 8  
   
  6   rows   selected  
   
  这里GROUP   BY   grade,rollup(ID)跟你的理解应该很相近了,而且可以看出GROUP   BY   grade,rollup(ID)结果跟ROLLUP(grade,ID)很类似,只是少了最后1行总合计,但是也可以就看出rollup多个字段时并不是只有1个字段起作用的  
   
  可以认为你理解的是只对第一个字段的累计,跟GROUP   BY   grade,rollup(ID)的结果很接近,再看rollup3个字段的情况:  
  SQL>   select   part,grade,id,num   from   a;  
   
  PART   GRADE             ID                                 NUM  
  ----   ----------   ----------   ----------  
  p1       a                     1                                       1  
  p1       a                     2                                       2  
  p1       b                     3                                       3  
  p1       b                     4                                       4  
  p2       c                     5                                       5  
  p2       d                     6                                       6  
   
  6   rows   selected  
   
  SQL>    
  SQL>   SELECT   decode(grouping_id(part,grade,ID),7,'总计',part)   part,  
      2                   decode(grouping_id(part,grade,ID),3,'小计',7,'总计',grade)   grade,  
      3                   decode(grouping_id(part,grade,ID),1,'小计',3,'小计',7,'总计',ID)   ID,  
      4                   SUM(num)  
      5     FROM   a   GROUP   BY   ROLLUP(part,grade,ID)  
      6     /  
   
  PART   GRADE             ID                       SUM(NUM)  
  ----   ----------   ----------   ----------  
  p1       a                     1                                       1  
  p1       a                     2                                       2  
  p1       a                     小计                                 3  
  p1       b                     3                                       3  
  p1       b                     4                                       4  
  p1       b                     小计                                 7  
  p1       小计               小计                               10  
  p2       c                     5                                       5  
  p2       c                     小计                                 5  
  p2       d                     6                                       6  
  p2       d                     小计                                 6  
  p2       小计               小计                               11  
  总计   总计               总计                               21  
   
  13   rows   selected  
   
  这里不光只对第一个字段做了累计,先按(part,grade,ID)分组累计,然后按(part,grade)分组累计,再按(part)分组累计,最后累计全部  
  再看看rollup   和   cube的区别:  
  对于ROLLUP(part,grade,ID),grouping_id(part,grade,ID)的值范围在(0,1,3,7)间即  
  part,grade,ID(作为合计时计为1)  
  0,0,0  
  0,0,1  
  0,1,1  
  1,1,1  
  而对于cube(part,grade,ID),grouping_id(part,grade,ID)的值范围在0-7之间即  
  part,grade,ID(作为合计时计为1)  
  0,0,0  
  0,0,1  
  0,1,0  
  0,1,1  
  1,0,0  
  1,0,1  
  1,1,0  

分享到:
评论
1 楼 dolphin_ygj 2009-06-28  
CREATE   TEST   TABLE   AND   INSERT   TEST   DATA.  
                                  create   table   students  
                                  (id   number(15,0),  
                                  area   varchar2(10),  
                                  stu_type   varchar2(2),  
                                  score   number(20,2));  
                                   
                                  insert   into   students   values(1,   '111',   'g',   80   );  
                                  insert   into   students   values(1,   '111',   'j',   80   );  
                                  insert   into   students   values(1,   '222',   'g',   89   );  
                                  insert   into   students   values(1,   '222',   'g',   68   );  
                                  insert   into   students   values(2,   '111',   'g',   80   );  
                                  insert   into   students   values(2,   '111',   'j',   70   );  
                                  insert   into   students   values(2,   '222',   'g',   60   );  
                                  insert   into   students   values(2,   '222',   'j',   65   );  
                                  insert   into   students   values(3,   '111',   'g',   75   );  
                                  insert   into   students   values(3,   '111',   'j',   58   );  
                                  insert   into   students   values(3,   '222',   'g',   58   );  
                                  insert   into   students   values(3,   '222',   'j',   90   );  
                                  insert   into   students   values(4,   '111',   'g',   89   );  
                                  insert   into   students   values(4,   '111',   'j',   90   );  
                                  insert   into   students   values(4,   '222',   'g',   90   );  
                                  insert   into   students   values(4,   '222',   'j',   89   );  
                                  commit;  
                   
                                  col   score   format   999999999999.99  
  ROLLUP  
                   
                                  select   id,area,stu_type,sum(score)   score    
                                  from   students  
                                  group   by   rollup(id,area,stu_type)  
                                  order   by   id,area,stu_type;  
                                   
                                  /*--------理解rollup  
                                  select   a,   b,   c,   sum(   d   )  
                                  from   t  
                                  group   by   rollup(a,   b,   c);  
                                   
                                  等效于  
                                   
                                  select   *   from   (  
                                    select   a,   b,   c,   sum(   d   )   from   t   group   by   a,   b,   c    
                                    union   all  
                                    select   a,   b,   null,   sum(   d   )   from   t   group   by   a,   b  
                                    union   all  
                                    select   a,   null,   null,   sum(   d   )   from   t   group   by   a  
                                    union   all  
                                    select   null,   null,   null,   sum(   d   )   from   t  
                                  )  
                                  */  
                   
  CUBE  
                   
                                  select   id,area,stu_type,sum(score)   score    
                                  from   students  
                                  group   by   cube(id,area,stu_type)  
                                  order   by   id,area,stu_type;  
                                   
                                  /*--------理解cube  
                                  select   a,   b,   c,   sum(   d   )   from   t  
                                  group   by   cube(   a,   b,   c)  
                                   
                                  等效于  
                                   
                                  select   a,   b,   c,   sum(   d   )   from   t  
                                  group   by   grouping   sets(    
                                            (   a,   b,   c   ),    
                                            (   a,   b   ),   (   a   ),   (   b,   c   ),    
                                            (   b   ),   (   a,   c   ),   (   c   ),    
                                            ()   )  
                                  )  
                                  */  
   
    GROUPING  
                   
                                  从上面的结果中我们很容易发现,每个统计数据所对应的行都会出现null,  
                                  如何来区分到底是根据那个字段做的汇总呢,grouping函数判断是否合计列!  
                                   
                                  select   decode(grouping(id),1,'all   id',id)   id,  
                                                  decode(grouping(area),1,'all   area',to_char(area))   area,  
                                                  decode(grouping(stu_type),1,'all_stu_type',stu_type)   stu_type,  
                                                  sum(score)   score  
                                  from   students  
                                  group   by   cube(id,area,stu_type)  
                                  order   by   id,area,stu_type;

相关推荐

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

    而在GROUP BY的基础上,Oracle数据库提供了两种高级分组功能:ROLLUP和CUBE,它们允许我们更灵活地生成汇总数据。 1. ROLLUP(滚联回溯): ROLLUP是GROUP BY的一个扩展,它不仅返回每个单独的分组结果,还会生成...

    Oracle分组函数之ROLLUP的基本用法

    总结来说,Oracle的`ROLLUP`和`CUBE`函数是强大的数据分析工具,能够帮助用户快速生成多层次的汇总数据,适用于各种复杂的数据报告需求。在处理大量数据时,它们的简洁语法和高效性能使得数据聚合变得更为方便。理解...

    Oracle分组统计

    在 Oracle 中,除了 Grouping Sets 之外,还有其他两种分组统计方法:Rollup 和 Cube。Rollup 是一种统计方法,它可以对数据进行分组和聚合计算,但它只能对单个列进行分组。Cube 是一种特殊的分组统计方法,它可以...

    《Pro Oracle SQL》Chapter7 Advanced Grouping -- 7.5Putting CUBE To Work

    《Pro Oracle SQL》Chapter 7 Advanced Grouping 是一本关于Oracle...通过阅读《Pro Oracle SQL》的这一章节,以及实践7_11_os.sql和7_12_os.sql中的示例,读者可以深入了解并掌握CUBE的用法,提升自己的数据处理能力。

    oracle书籍

    9. 分组和分析函数:如GROUP BY、ROLLUP、CUBE和RANK等,用于数据的分组汇总和复杂分析,是大数据处理和OLAP操作的关键。 10. 连接函数:如CONNECT_BY_ROOT和START_WITH,用于构建复杂的树状查询,处理层次结构数据...

    ORACLE__SQL.rar_oracle

    3. **Oracle特有功能**:Oracle数据库提供了一些特有的SQL语法和功能,如游标(CURSOR)、PL/SQL(Oracle的存储过程语言)、分组函数(GROUP BY, ROLLUP, CUBE)以及窗口函数(OVER()子句)。 4. **数据类型**:...

    Oracle 10g培训经典_中文版

    - Les17_对 GROUP BY 子句的扩展.ppt可能深入讨论了GROUP BY的高级用法,如HAVING子句和ROLLUP/CUBE/GROUPING SETS。 通过这套培训资料,学习者可以从基础的SQL语法到复杂的数据库管理技巧逐步进阶,了解并掌握...

    Oracle常见用法

    4. **`GROUPING`函数**:`GROUPING`用于识别由`ROLLUP`、`CUBE`或`GROUPING SETS`返回的空值,区分标准空值和汇总情况。 **`HAVING`子句**: `HAVING`在`GROUP BY`后使用,用于筛选分组后的结果,可以包含聚合函数...

    自己整理的Oracle常用高级语句.rar

    Oracle数据库是全球广泛使用的大型关系型数据库管理系统,其强大的功能和灵活性深受IT专业人士的喜爱。这里提供的"Oracle常用高级语句.rar"压缩包包含了多个与Oracle数据库相关的文档,涵盖了PL/SQL编程、函数应用、...

    Oracle OLAP Expression Syntax Reference Release 11.2-208

    5. **聚合操作**: Oracle OLAP支持多种聚合操作,如ROLLUP和CUBE,它们用于创建上卷(Roll-up)、下钻(Drill-down)、切片(Slice)和切块(Dice)等分析操作。这些操作帮助用户从不同角度查看数据。 6. **安全性...

    Oracle中分组查询group by用法规则详解

    - `GROUP BY GROUPING SETS`是`ROLLUP`和`CUBE`的组合,可自定义分组。 了解这些基本规则和用法后,你可以根据业务需求灵活运用`GROUP BY`来分析和汇总数据。记得在实际操作中,确保遵守上述规则,以避免SQL语法...

    Oracle高级sql学习与练习

    10. 增强GROUP BY功能,使用GROUP BY扩展选项,如GROUPING SETS、CUBE和ROLLUP等,可以实现更复杂的聚合计算。 11. 分析函数(ANALYTICAL FUNCTIONS)是Oracle SQL的高级特性之一,允许在数据集上进行窗口计算,...

    Oracle_Database_11g完全参考手册.part2/3

    14.3 使用ROLLUP、GROUPING和CUBE 14.4 家族树和COlLrlectby 14.4.1 排除个体和分支 14.4.2 向根遍历 14.4.3 基本规则 第15章 更改数据:插入、更新、合并和删除 第16章 DECODE和CASE.SQL中的if-fhen-else 第17章 ...

    Oracle_Database_11g完全参考手册.part3/3

    14.3 使用ROLLUP、GROUPING和CUBE 14.4 家族树和COlLrlectby 14.4.1 排除个体和分支 14.4.2 向根遍历 14.4.3 基本规则 第15章 更改数据:插入、更新、合并和删除 第16章 DECODE和CASE.SQL中的if-fhen-else 第17章 ...

    Oracle专题培训.doc

    2. 使用方法和限制 使用`APPEND`提示可以启用直接路径插入。然而,这种方法不支持事务,可能导致无法回滚,且可能影响其他并发操作。 四、物化视图 物化视图是预先计算并存储的结果集,提供快速的数据访问。它们...

    不错的oracle教学课件

    10. **Les17分组查询扩展.ppt** - 分组查询的高级用法,可能包含GROUP BY ROLLUP、CUBE和GROUPING SETS,这些功能允许更灵活的数据汇总和分析。 通过这个教学课件,学习者不仅可以掌握Oracle SQL的基本语法,还能...

    Oracle 10g中的高级SQL函数

    `GROUP BY` 和 `HAVING` 子句是SQL中的基础工具,但在Oracle 10g中,你可以使用 `CUBE()`, `ROLLUP()`, `GROUPING SETS()` 进行多维度分析,创建更复杂的分组。`CONNECT BY` 用于构建层次结构,如组织结构或产品分类...

Global site tag (gtag.js) - Google Analytics