oracle中的roll up,oracle group by 与roll up,cube,grouping sets,grouping_id联合使用

最新推荐文章于 2023-09-11 23:27:01 发布
最新推荐文章于 2023-09-11 23:27:01 发布
阅读量358 收藏 1
点赞数
文章标签： oracle中的roll up

转自:http://blog.sina.com.cn/s/blog_5f0fbdee0100saw3.html

Oracle的group by除了基本用法以外，还有3种扩展用法，分别是rollup、cube、grouping sets。

1 rollup

假设有一个表test，有A、B、C、D、E5列。

如果使用group by rollup(A,B,C)，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP

BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。roll up的意思是“卷起”，这也可以帮助我们理解group by

rollup就是对选择的列从右到左以一次少一列的方式进行grouping直到所有列都去掉后的grouping(也就是全表grouping)，对于n个参数的rollup，有n+1次的grouping。以下2个sql的结果集是一样的：

Select A,B,C,sum(E) from test group by rollup(A,B,C)

与

Select A,B,C,sum(E) from test group by A,B,C

union all

Select A,B,null,sum(E) from test group by A,B

union all

Select A,null,null,sum(E) from test group by A

union all

Select null,null,null,sum(E) from test

2 cube

cube的意思是立方，对cube的每个参数，都可以理解为取值为参与grouping和不参与grouping两个值的一个维度，然后所有维度取值组合的集合就是grouping的集合，对于n个参数的cube，有2^n次的grouping。如果使用group

by cube(A,B,C),，则首先会对(A、B、C)进行GROUP

BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP

BY操作，一共是2^3=8次grouping。同rollup一样，也可以用基本的group by加上结果集的union all写出一个与group by

cube结果集相同的sql：

Select A,B,C,sum(E) from test group by cube(A,B,C);

与

Select A,B,C,sum(E) from test group by A,B,C

union all

Select A,B,null,sum(E) from test group by A,B

union all

Select A,null,C,sum(E) from test group by A,C

union all

Select A,null,null,sum(E) from test group by A

union all

Select null,B,C,sum(E) from test group by B,C

union all

Select null,B,null,sum(E) from test group by B

union all

Select null,null,C,sum(E) from test group by C

union all

Select null,null,null,sum(E) from test;

3 grouping sets

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的参数允许重复

4 总结

rollup   (N+1个分组方案)

cube   (2^N个分组方案)

grouping sets (自定义罗列出分组方案)

5 注意点

5.1 机制不同

在rollup和cube的说明中分别给出了用基本group by加结果集union

all给出了结果集相同的sql，但这只是为了理解的方便而给出的sql，并不说明rollup和cube与基本group by加结果集union

all等价。实际上两者的内部机制是安全不一样的，前者除了写法简洁以外，运行时不需多次扫描表，效率远比后者高。

5.2 集合可运算

3种扩展用法的参数可以是源表中的某一个具体的列，也可以是若干列经过计算而形成的一个新列(比如说A+B，A||B)，也可以是这两种列的一个集合(例如(A+B，C))，对于grouping

set更是特殊，可以是空集合()，表示对全表进行group by。

5.3 group by 与 rollup, cube组合使用

3)Group by的基本用法以及这3种扩展用法可以组合使用，也就是说可以出现group by

A,rollup(A,B)这样的用法，oracle将对出现在group by中的每种用法的grouping列集合做笛卡尔积然后对其中的每一个元素做group

by。这话说起来挺绕口，举例说明吧，group by A,

rollup(A,B)，基本用法的grouping集合是(A),rollup(A,B)的grouping集合是((A,B),(A),()),两个集合的笛卡尔积集合是((A,A,B),(A,A),(A))，所以会首先对(A,A,B)做group

by，然后对(A,A)做group by，最后对(A)做group by。实际上对(A,A,B)做group by和对(A,B)做group

by两者是完全等价的(group by A,A,B结果和group by A,B完全一样)，同理对(A,A)做group by和对(A)做group

by也是等价的。简化后的结果就是首先对(A,B)做group by，然后对(A)做group by，最后再对(A)做group

by。下面给出两个等价的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;

6 grouping()、grouping_id()、group_id()

6.1 grouping()

参数只有一个，而且必须为group

by中出现的某一列，表示结果集的一行是否对该列做了grouping。对于对该列做了grouping的行而言，grouping()=0，反之为1；

6.2 grouping_id()

参数可以是多个，但必须为group

by中出现的列。Grouping_id()的返回值其实就是参数中的每列的grouping()值的二进制向量，例如如果grouping(A)=1，grouping(B)=0，则grouping_id(A,B)的返回值就是二进制的10，转成10进制就是2。

6.3 group_id()

无参数。见上面的说明3)，group

by对某些列的集合会进行重复的grouping，而实际上绝大多数情况下对结果集中的这些重复行是不需要的，那就必须有办法剔出这些重复grouping的行。当结果集中有n条重复grouping而形成的行时，每行的group_id()分别是0,1,…,n，这样我们在条件中加入一个group_id()<1就可以剔出这些重复grouping的行了。

7 示例

7.1 建表与数据

SQL> create table test(department_id number, a varchar2(20), b

varchar2(20));

Table created

SQL> insert into test values(10, 'A', 'B');

1 row inserted

SQL> commit;

Commit complete

7.2 查询语句

select department_id,

a,

b,

grouping(department_id),

grouping(a),

grouping(b)

from test

group by rollup(department_id, a, b)

order by 4, 5, 6;

select department_id,

a,

b,

grouping(department_id),

grouping(a),

grouping(b)

from test

group by cube(department_id, a, b)

order by 4, 5, 6;

Grouping函数:

可以接受一列，返回0或1。如果列值为空，则返回1，非空则返回0。它只能在rollup和或cube函数中使用，因为在统计中显示“全部”的那一项统计值时，那一项的标签通常是空的，这时grouping就非常有用，还可以在grouping的基础上进行decode,

case等进行美化。

SQL> select

grouping(index_type) g_ind, grouping(status) g_st, index_type, status,

count(*)

2 from t group by rollup(index_type, status) order by

1, 2;

G_IND G_ST

INDEX_TYPE

STATUS COUNT(*)

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

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

----------

0 0

LOB

N/A

1

0 0

LOB

VALID

572

0 0 FUNCTION-BASED

NORMAL

VALID

17

0 0 FUNCTION-BASED

DOMAIN

VALID

1

0 0 IOT -

TOP

VALID

115

0 0

CLUSTER

VALID

10

0 0

NORMAL

VALID

4557

0 0

NORMAL

N/A

56

0 0

DOMAIN

VALID

1

0 0

BITMAP

VALID

8

0 0

BITMAP

N/A

7

0 1 IOT -

TOP

115

0 1 FUNCTION-BASED

DOMAIN

1

0 1

DOMAIN

1

0 1

CLUSTER

10

0 1

BITMAP

15

0 1 FUNCTION-BASED

NORMAL

17

0 1

NORMAL

4613

0 1

LOB

573

1

1

5345

20 rows selected

Grouping_id函数：

比grouping还强点，可以接收多个列，这几个列都不为空时，返回0，只要有一个为空，则返回1，如果都为空，则返回3。

下面这条查询把grouping_id牛刀杀鸡，当做grouping用了，可以看到结果跟用grouping是完全一样。

SQL> select

grouping_id(index_type) g_ind, grouping_id(status) g_st, index_type, status,

count(*)

2 from t group by rollup(index_type, status) order by

1, 2;

G_IND G_ST

INDEX_TYPE

STATUS COUNT(*)

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

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

----------

0 0

LOB

N/A

1

0 0

LOB

VALID

572

0 0 FUNCTION-BASED

NORMAL

VALID

17

0 0 FUNCTION-BASED

DOMAIN

VALID

1

0 0 IOT -

TOP

VALID

115

0 0

CLUSTER

VALID

10

0 0

NORMAL

VALID

4557

0 0

NORMAL

N/A

56

0 0

DOMAIN

VALID

1

0 0

BITMAP

VALID

8

0 0

BITMAP

N/A

7

0 1 IOT -

TOP

115

0 1 FUNCTION-BASED

DOMAIN

1

0 1

DOMAIN

1

0 1

CLUSTER

10

0 1

BITMAP

15

0 1 FUNCTION-BASED

NORMAL

17

0 1

NORMAL

4613

0 1

LOB

573

1

1

5345

20 rows selected

下面这条查询才体现了grouping_id的作用。

SQL> select

grouping_id(index_type, status) g_st, index_type, status, count(*)

2 from t group by rollup(index_type, status) order by 1, 2;

G_ST

INDEX_TYPE

STATUS COUNT(*)

----------

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

----------

0

BITMAP

VALID

8

0

BITMAP

N/A

7

0

CLUSTER

VALID

10

0

DOMAIN

VALID

1

0 FUNCTION-BASED

DOMAIN

VALID

1

0 FUNCTION-BASED

NORMAL

VALID

17

0 IOT -

TOP

VALID

115

0

LOB

N/A

1

0

LOB

VALID

572

0

NORMAL

N/A

56

0

NORMAL

VALID

4557

1

BITMAP

15

1

CLUSTER

10

1

DOMAIN

1

1 FUNCTION-BASED

DOMAIN

1

1 FUNCTION-BASED

NORMAL

17

1 IOT -

TOP

115

1

LOB

573

1

NORMAL

4613

3 %

脸不圆是错觉
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
分组函数之rollup、cube函数、grouping sets函数
Samooyou的博客
06-27 3689
rollup 通常和group by语句一起使用，是根据维度在分组的结果集中进行聚合操作（通常为汇总，这取决于SELECT后的聚合函数）。最常用的场景是：为每个分组返回一个小计，同时为所有分组返回总计。1、不带rollup的goup by ： Group by A ,B产生的分组种数：1种；返回结果集：也就是这一种分组的结果集。2、带rollup但group by与rollup之间没有任何内容：1）Group by rollup(A ,B) 产生的分组种数：3种；返回结果集：为以上三种分组统计结果集的并集且
SQL 中 group By rollup 与 group By cube,grouping函数使用说明
嗅嗅匆匆的博客
05-05 2927
group By 语句不仅适用于Oracle ，其他数据库也同样适用，本文以SQLserver 数据库为例。 group By 语句除了最基本的语法外，还支持ROLLUP和CUBE语句。CUBE ROLLUP 是用于统计数据的。例如求数据的合计与小计。 grouping 函数使用 1. rollup 假设使用group by rollup(a,b)，首先会对(a,b)进行group by ...
高级聚合功能 group by rollup 以及 grouping grouping_id 函数的运用
Marko_Liu的博客
04-10 1301
首先，聚合函数是什么，他是按照分组字段把多行数据处理成一行，常用的6大聚合函数有 max 最大值 min 最小值 sum 累计值 avg 平均值 count 个数统计值 （如果 count(distinct …) 则统计内容不同的数据个数 这里我们主要讲下高级聚合功能 group by rollup 以及 grouping grouping_id 函数 示例，展示每年度下，每个月度的消费总额，单个...
Oracle rollup函数
最新发布
tttzzzqqq2018的博客
09-11 1524
Oracle rollup函数
Oracle中rollup函数详解
实泽有之，无泽虚之
12-13 7899
Oracle中rollup函数主要用于分组汇总，如果rollup中有n个字段，则会分别按【字段1】、【字段1,字段2】，【字段1,字段2,字段3】，...，【字段1,字段2,字段3,...字段n】进行分组统计，同时还会去掉所有分组字段进行一次统计，因此会有n+1次分组，也就是说，统计的分组维度是从左到右依次增加一列，从0列一直到n列。
oracle中使用group by优化distinct
09-10
2. **使用`GROUP BY ROLLUP/CUBE/GROUPING SETS`**：这些高级`GROUP BY`技术可以组合不同的分组，以实现类似于`DISTINCT`的效果。例如，如果所有字段都需去重，可以使用`GROUP BY`加上所有字段，然后利用`ROLLUP`或`...
Oracle中用GROUPING SETS分组自定义汇总
03-03
与`GROUP BY ROLLUP`和`GROUP BY CUBE`相比，`GROUPING SETS`更具有选择性，可以精确地控制生成的汇总组。`ROLLUP`生成所有可能的子集，而`CUBE`则生成所有可能的组合，这两者可能会产生大量的结果，尤其是在处理多...
Oracle中分组查询group by用法规则详解
12-14
- `GROUP BY GROUPING SETS`是`ROLLUP`和`CUBE`的组合，可自定义分组。 了解这些基本规则和用法后，你可以根据业务需求灵活运用`GROUP BY`来分析和汇总数据。记得在实际操作中，确保遵守上述规则，以避免SQL语法...
grouping or grouping_id in ORACLE
04-03
grouping or grouping_id
SQLServer中汇总功能的使用GROUPING,ROLLUP和CUBE
09-11
然而，GROUP BY还可以与GROUPING、ROLLUP和CUBE运算符结合使用，以实现更复杂的多维度汇总功能。这些高级的汇总方法在数据分析和报表生成中非常有用，尤其是在处理大型数据集时。 GROUPING运算符主要用于识别汇总行...
GROUP BY子句（rollup,cube,grouping sets）实例说明
08-22
GROUP BY 子句有多种形式，本文将通过实例来说明 GROUP BY 子句的三种样式：GROUP BY、GROUP BY ROLLUP、GROUP BY CUBE、GROUP BY GROUPING SETS。 1. GROUP BY 语句 GROUP BY 语句是最基本的分组语句，它可以根据...
oracle group by语句实例测试
09-11
- **使用CUBE、ROLLUP或GROUPING SETS**：这些高级的`GROUP BY`选项可以帮助生成更复杂的汇总数据，例如交叉总计或立方体。 总的来说，`GROUP BY`语句是数据分析和报告的重要工具，理解其工作原理和优化方法对于...
sql-Group-by.rar_oracle
09-21
通过阅读“sql分组函数使用.txt”，你将能够深入了解如何在Oracle中有效地使用GROUP BY，以及它如何与其他SQL元素结合使用以提供深入的数据洞察。理解并熟练运用这些概念对于进行复杂的数据分析和报告至关重要。
Java8 stream 中利用 groupingBy 进行多字段分组求和案例
09-07
在这个案例中，我们将探讨如何使用`groupingBy`进行多字段分组以及如何实现分组求和。 首先，`groupingBy`的基本用法是根据一个单一的属性将集合元素分组。例如，给定一个包含水果名称的列表，我们可以很容易地按照...
oracle中聚合函数的扩展使用(ROLLUP CUBE、GROUPING()函数与ROLLUP、CUBE的结合使用等）
07-05
GROUPING_ID()函数更进一步，它返回一个整数值，该值是GROUP BY列表中所有列的二进制表示，每个位对应于列在分组中的状态。0表示详细数据，1表示汇总。例如： ```sql SELECT DIVISION, JOB_ID, SUM(SALARY), ...
oracle中的roll up,oracle  group by 与roll up,cube,grouping sets,grouping_id联合使用
weixin_34479122的博客
04-05 461
Oracle的group by除了基本用法以外，还有3种扩展用法，分别是rollup、cube、groupingsets。1 rollup假设有一个表test，有A、B、C、D、E5列。如果使用group by rollup(A,B,C)，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUPBY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。rollu...
[2019.7.18]Oracle的rollup语句详解
weixin_44683593的博客
07-18 302
关于 rollup 语句的解析(借鉴了前辈们的经验） 在多行查询中，使用rollup语句能够大大提高我们的操作效率，在开始接触rollup语句 的时候，总是不能理解其中的顺序，查了一些资料和前辈们的博客，实践操作了几次，在此总结一下，首先rollup是卷起的意思，大体能够形容他的作用。 group by rollup（a，b） 这条语句相当于分组三次 第一次，a，b两个约束条件，即a相同，b也相同...
Group by后使用rollup
Baron_ND的博客
08-26 4796
一、如何理解group by后带rollup子句所产生的效果 group by后带rollup子句的功能可以理解为：先按一定的规则产生多种分组，然后按各种分组统计数据（至于统计出的数据是求和还是最大值还是平均值等这就取决于SELECT后的聚合函数）。因此要搞懂group by后带rollup子句的用法主要是搞懂它是如何按一定的规则产生多种分组的。另group by后带rollup子句所返回的结果集，可以理解为各个分组所产生的结果集的并集且没有去掉重复数据。下面举例说明： 1、对比没有带rollup的go
oracle 分组统计效率,Oracle 分组求和函数（rollup、cube、grouping sets）
weixin_31173113的博客
04-03 2977
文章目录1 场景1.1 概念1.2 思维导图1.3 数据准备2 知识点小结2.1 group by2.2 grouping sets：单独分组2.3 rollup：累计累加2.4 cube(交叉列表)2.6 grouping2.7 grouping_id1 场景内容修改中，请稍等… 1.1 概念rollup，cube，grouping sets 函数可以理解为 group by 分组函数封装后的...
oracle语法group by grouping sets转mysql语法使用 如何用
05-14
MySQL 也支持 GROUPING SETS 语法，但是需要使用括号来指定不同的维度。 例如，如果有一个表 sales，包含字段 date、product 和 amount，要按日期和产品统计销售额，可以使用以下语法： ``` SELECT date, product, SUM(amount) AS total_sales FROM sales GROUP BY date, product, (date), (product), (date, product) ``` 这里括号内分别指定了单独按日期、单独按产品、按日期和产品一起分组的情况。如果只想按日期或产品分组，可以省略相应的括号。

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值