`

oracle自动汇总函数rollup和club及grouping set的用法

阅读更多

Oracle的group by除了基本用法以外,还有3种扩展用法,分别是rollup、cube、grouping sets。
三种函数都能对分组列实现自动汇总。

  1. 如果是Group by ROLLUP(A,B,C)的话,roll up的意思是“卷起”,首先会对(A、B、C)进行GROUP BY,然后对(A、B)进行GROUP BY,然后是(A)进行GROUP BY,最后对全表进行GROUP BY操作。对于n个参数的rollup,有n+1次的grouping。
  2. 如果使用group by cube(A,B,C),cube的意思是立方,则首先会对(A、B、C)进行GROUP BY,然后依次是(A、B),(A、C),(A),(B、C),(B),(C),最后对全表进行GROUP BY操作,一共是2^3=8次grouping。
  3. 如果使用grouping sets就是对参数中的每个参数做grouping,也就是有几个参数做几次grouping,例如使用group by grouping sets(A,B,C),则对(A),(B),(C)进行group by,如果使用group by grouping sets((A,B),C),则对(A,B),(C)进行group by。甚至grouping by grouping set(A,A)都是语法允许的,也就是对(A)进行2次group by,grouping sets的参数允许重复


Oracle为了解决引入ROLLUP和CUBE后,汇总和NULL值无法区分的情况,提供了GROUPING、GROUPING_ID和GROUP_ID三个函数

  1. Grouping用法,必须要有参数
    Grouping是用来判断当前Column是否是一个合计列,1为是,0为否
    Select Decode(Grouping(area),1,'所有地区',area) area,
           Decode(Grouping(month),1,'所有月份',month), sum(money)
    From   SaleOrder
    Group by RollUp(area,month);
  2. grouping_id()用法,必须要有参数
    grouping_id是向量值,可以认为是二进值的数,例:grouping_id(a,b,c),如果列a为空就是0 非空为1,列b和列c也一样,
    结果会得到一个三位数,用二进制转换成十进制就是了。
    select   job,deptno,sum(sal)   total_sal   from   emp   group   by   rollup(job,deptno)
    having grouping_id(job,deptno)<=2
  3. group_id()用法,没有参数
    group by对某些列的集合会进行重复的grouping,而实际上绝大多数情况下对结果集中的这些重复行是不需要的,那就必须有办法剔出这些重复grouping的行。当结果集中有n条重复grouping而形成的行时,每行的group_id()分别是0,1,…,n,这样我们在条件中加入一个group_id()<1就可以剔出这些重复grouping的行了。

ROLLUP在数据统计和报表生成过程中带来极大的便利,而且效率比起来Group By + Union组合方法效率高得多。这也体现了Oracle在SQL统计分析上人性化、自动化、高效率的特点。

Select A,B,sum(E) from test1 group by A, rollup(A,B);

Select A,B,sum(E) from test1 group by A,B
Union all
Select A,null,sum(E) from test1 group by A
Union all
Select A,null,sum(E) from test1 group by A;

分享到:
评论

相关推荐

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

    本博客简单介绍一下oracle分组函数之rollup的用法,rollup函数常用于分组统计,也是属于oracle分析函数的一种 环境准备 create table dept as select * from scott.dept; create table emp as select * from ...

    Oracle中用GROUPING SETS分组自定义汇总

    在Oracle数据库中,进行数据分析和报表制作时,我们经常需要对数据进行分组汇总,以得到不同层次的统计结果。`GROUP BY`语句是实现这一功能的基础,但默认情况下,它只提供单级的汇总信息。为了获取多级或自定义的...

    oracle的分析函数汇总

    例如,可以使用 Rollup 函数对数据进行分组和汇总,使用 Rank 函数对数据进行排名,使用 Lag 和 Lead 函数对数据进行延迟和前导操作,使用 Sum 和 Avg 函数对数据进行汇总和平均计算,使用 Ratio_to_Report 函数对...

    Oracle ROLLUP和CUBE 用法

    Oracle数据库中的ROLLUP和CUBE是两种用于生成数据汇总的SQL操作,它们在数据分析和报告制作中非常有用。本文将详细介绍这两个概念以及如何在实际应用中使用它们。 首先,我们来理解`ROLLUP`。ROLLUP操作是GROUP BY...

    oracle中聚合函数的扩展使用(ROLLUP CUBE、GROUPING()函数与ROLLUP、CUBE的结合使用等)

    当我们需要更复杂的分组和汇总信息时,可以使用扩展的聚合功能,如ROLLUP、CUBE以及GROUPING()和GROUPING_ID()函数。这些功能允许我们生成多层次的总计和小计,为数据分析提供了极大的灵活性。 1. ROLLUP子句: ...

    使用ROLLUP函数生成报表的小计、合计

    本文主要探讨如何利用SQL中的`ROLLUP`函数来实现这一功能,特别关注于Oracle数据库环境下的实现方法。 #### 一、基本概念介绍 `ROLLUP`是一个用于GROUP BY子句的扩展选项,它能够帮助我们生成包含不同层级汇总信息...

    grouping or grouping_id in ORACLE

    在Oracle数据库中,`GROUPING` 和 `GROUPING_ID` 是两种非常有用的函数,它们能够帮助用户更好地理解和组织聚合查询的结果。这两种函数主要用于处理复杂的分组情况,尤其是在使用`ROLLUP` 或 `CUBE` 时更为显著。...

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

    - `CUBE`适用于当你希望看到所有可能的子集和汇总时,如在交叉分析或多维数据探索中。 - `ROLLUP`适用于需要层次结构汇总的情况,比如在创建带有子总和的报表时。 了解这些概念后,可以更好地处理多维度数据,...

    GROUP BY子句(rollup,cube,grouping sets)实例说明

    GROUP BY 子句(rollup, cube, grouping sets)实例说明 GROUP BY 子句是 SQL 语言中用于分组数据的关键字,它可以根据一个或多个列对数据进行分组,并对每个分组应用聚合函数,以便计算和输出所需的结果。GROUP BY...

    rollup,cube,grouping sets()的个人理解

    通过使用`ROLLUP`,系统会自动添加各个字段的汇总行,例如先按`cgicode`分组,再按`cgicode`和`stockyear`分组,以此类推直到所有指定字段都被包含为止。 **结果分析**: 查询结果包含了从最细粒度(所有字段都有的...

    Oracle的Rollup用法

    Oracle 的 Rollup 用法是指在 SQL 语句中使用 ROLLUP 子句来对数据进行聚合和分组。ROLLUP 子句可以将查询结果按照一个或多个字段进行分组,并且可以生成子总计和总计。 在 Oracle 中,ROLLUP 子句可以与 GROUP BY ...

    oracle中的grouping

    本文将详细解释`GROUPING`函数的使用方法及其应用场景,并通过一个示例来帮助理解其工作原理。 #### 一、`GROUPING`函数简介 `GROUPING`函数主要用于标识列是否被包含在`GROUP BY`子句中。当某列未被`GROUP BY`...

    Oracle10G函数大全(chm格式的,比较全面)

    Oracle 10G函数大全的文档通常会详细涵盖以上及更多功能,通过学习和熟练掌握这些函数,可以极大地提高你在Oracle数据库管理、查询和开发中的效率。对于数据库管理员和开发者来说,这份资料无疑是一份宝贵的资源。

    rollup cube grouping sets的用法

    在数据库查询语言 SQL 中,`ROLLUP`, `CUBE`, 和 `GROUPING SETS` 是三个重要的概念,它们主要用于处理多维度数据的汇总和分组问题,使得数据分析更加灵活高效。接下来,我们将详细介绍这三个概念的原理、语法以及...

    Oracle 分析函数.doc

    1. **自动汇总函数:ROLLUP 和 CUBE** - **ROLLUP** 函数用于生成层次化的汇总数据。它会根据提供的列进行逐级汇总,从最细粒度到最粗粒度。例如,如果有`BILL_MONTH`和`AREA_CODE`两列,ROLLUP会分别计算月度、...

    Oracle分析函数

    ### Oracle分析函数详解 #### 一、概述 Oracle分析函数是一种强大的工具,它允许用户对查询结果进行复杂的分析处理,而无需多次查询数据库或编写复杂的程序逻辑。这大大简化了开发人员的工作,尤其是在处理大数据...

    ORACLE分析函数.pdf

    Oracle分析函数是数据库管理系统Oracle中的一种高级SQL特性,它允许用户在单个查询中对一组行进行计算,而无需使用子查询或自连接。这些函数极大地增强了数据分析和报告的能力,提高了查询性能。以下是对Oracle分析...

    Hive开窗函数测试-cube,rollup

    这个文档应该包含了具体的示例和代码,展示了如何在Hive SQL查询中使用窗口函数、cube和rollup。通过实践这些示例,你可以更深入地了解这些功能,并能灵活地应用于你的数据处理任务中。 在进行大数据分析时,了解并...

Global site tag (gtag.js) - Google Analytics