对GROUP BY的增强

最新推荐文章于 2024-08-15 01:26:58 发布
最新推荐文章于 2024-08-15 01:26:58 发布
阅读量403 收藏
点赞数
分类专栏： DBA 文章标签： 数据库 oracle sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u011868279/article/details/127917230
版权
  • 使用ROLLUP操作产生求和值
  • 使用CUBE操作产生
  • 使用GROUPING函数确定该行值是由ROLLUP或者CUBE创建的
  • 使用GROUPING SETS生成一个简单的结果集

[oracle@oracle-db-19c ~]$ 
[oracle@oracle-db-19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Nov 18 10:19:05 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> 
SQL> alter session set container=PDB1;

Session altered.
SQL> show con_name

CON_NAME
------------------------------
PDB1
SQL> 
SQL> 
SQL> conn scott/tiger@PDB1
Connected.
SQL> 
SQL> show user
USER is "SCOTT"
SQL> 
SQL> 
SQL> set pagesize 200
SQL> set linesize 200
SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 24-JAN-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 02-APR-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL> select deptno,sum(sal) from emp group by deptno;

    DEPTNO   SUM(SAL)
---------- ----------
        30       9400
        10       8750
        20      10875

SQL> select deptno,sum(sal) from emp having sum(sal)>9000 group by deptno;

    DEPTNO   SUM(SAL)
---------- ----------
        30       9400
        20      10875

SQL> select deptno,job,sum(sal) from emp group by deptno,job;

    DEPTNO JOB         SUM(SAL)
---------- --------- ----------
        20 MANAGER         2975
        20 ANALYST         6000
        10 PRESIDENT       5000
        10 CLERK           1300
        30 SALESMAN        5600
        10 MANAGER         2450
        20 CLERK           1900
        30 MANAGER         2850
        30 CLERK            950

9 rows selected.

SQL> select deptno,job,sum(sal) from emp group by deptno,job order by 1,2;

    DEPTNO JOB         SUM(SAL)
---------- --------- ----------
        10 CLERK           1300
        10 MANAGER         2450
        10 PRESIDENT       5000
        20 ANALYST         6000
        20 CLERK           1900
        20 MANAGER         2975
        30 CLERK            950
        30 MANAGER         2850
        30 SALESMAN        5600

9 rows selected.

SQL>

带有ROLLUP和CUBE运算符的GROUP BY

  • 带有ROLLUP或者CUBE的GROUP BY 会通过交叉参考列产生一个超级总计行
  • ROLLUP分组生成一个结果集，包含有通常的分组行和求和值
  • CUBE分组生成一个结果集，包含从ROLLUP里面的行和交叉表行。
SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job) order by 1,2;

    DEPTNO JOB         SUM(SAL)
---------- --------- ----------
        10 CLERK           1300
        10 MANAGER         2450
        10 PRESIDENT       5000
        10                 8750
        20 ANALYST         6000
        20 CLERK           1900
        20 MANAGER         2975
        20                10875
        30 CLERK            950
        30 MANAGER         2850
        30 SALESMAN        5600
        30                 9400
                          29025

13 rows selected.

SQL>

SQL> select deptno,job,sum(sal) from emp group by deptno,job
  2  union all
  3  select deptno,null,sum(sal) from emp group by deptno
  4  union all
  5  select null,null,sum(sal) from emp;

    DEPTNO JOB         SUM(SAL)
---------- --------- ----------
        20 MANAGER         2975
        20 ANALYST         6000
        10 PRESIDENT       5000
        10 CLERK           1300
        30 SALESMAN        5600
        10 MANAGER         2450
        20 CLERK           1900
        30 MANAGER         2850
        30 CLERK            950
        30                 9400
        10                 8750
        20                10875
                          29025

13 rows selected.

SQL>

rollup(a,b,c)

group by a

union all

group by a,b

union all

group by a,b,c

union all

total


cube(a,b,c)

group by a

union all

group by b

union all

group by c

union all

group by a,b

union all

group by a,c

union all

group by b,c

union all

group by a,b,c

union all

total

SQL> 
SQL> select deptno,job,sum(sal) from emp group by deptno,job
  2  union all
  3  select deptno,null,sum(sal) from emp group by deptno
  4  union all
  5  select null,null,sum(sal) from emp;

Execution Plan
----------------------------------------------------------
Plan hash value: 1761893045

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    15 |   190 |    11  (19)| 00:00:01 |
|   1 |  UNION-ALL          |      |       |       |            |          |
|   2 |   HASH GROUP BY     |      |    11 |   165 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP  |    14 |   210 |     3   (0)| 00:00:01 |
|   4 |   HASH GROUP BY     |      |     3 |    21 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| EMP  |    14 |    98 |     3   (0)| 00:00:01 |
|   6 |   SORT AGGREGATE    |      |     1 |     4 |            |          |
|   7 |    TABLE ACCESS FULL| EMP  |    14 |    56 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job) order by 1,2;

Execution Plan
----------------------------------------------------------
Plan hash value: 52302870

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |    11 |   165 |     4  (25)| 00:00:01 |
|   1 |  SORT GROUP BY ROLLUP|      |    11 |   165 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | EMP  |    14 |   210 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

SQL>

GROUPING函数

SQL> 
SQL> select deptno,job,sum(sal),grouping(deptno),grouping(job) from emp group by rollup(deptno,job);

    DEPTNO JOB         SUM(SAL) GROUPING(DEPTNO) GROUPING(JOB)
---------- --------- ---------- ---------------- -------------
        10 CLERK           1300                0             0
        10 MANAGER         2450                0             0
        10 PRESIDENT       5000                0             0
        10                 8750                0             1
        20 CLERK           1900                0             0
        20 ANALYST         6000                0             0
        20 MANAGER         2975                0             0
        20                10875                0             1
        30 CLERK            950                0             0
        30 MANAGER         2850                0             0
        30 SALESMAN        5600                0             0
        30                 9400                0             1
                          29025                1             1

13 rows selected.

SQL> 
SQL> select decode(grouping(deptno)||grouping(job),'01','Subtotal ' || deptno,'11','Total',deptno) deptno,job,sum(sal) from emp group by rollup(deptno,job);

DEPTNO                                            JOB         SUM(SAL)
------------------------------------------------- --------- ----------
10                                                CLERK           1300
10                                                MANAGER         2450
10                                                PRESIDENT       5000
Subtotal 10                                                       8750
20                                                CLERK           1900
20                                                ANALYST         6000
20                                                MANAGER         2975
Subtotal 20                                                      10875
30                                                CLERK            950
30                                                MANAGER         2850
30                                                SALESMAN        5600
Subtotal 30                                                       9400
Total                                                            29025

13 rows selected.

SQL> 

SQL> select decode(grouping(deptno)||grouping(job),'01','Subtotal ' || deptno,'11','Total',deptno) deptno,job,sum(sal),grouping(deptno),grouping(job) from emp group by rollup(deptno,job);

DEPTNO                                            JOB         SUM(SAL) GROUPING(DEPTNO) GROUPING(JOB)
------------------------------------------------- --------- ---------- ---------------- -------------
10                                                CLERK           1300                0             0
10                                                MANAGER         2450                0             0
10                                                PRESIDENT       5000                0             0
Subtotal 10                                                       8750                0             1
20                                                CLERK           1900                0             0
20                                                ANALYST         6000                0             0
20                                                MANAGER         2975                0             0
Subtotal 20                                                      10875                0             1
30                                                CLERK            950                0             0
30                                                MANAGER         2850                0             0
30                                                SALESMAN        5600                0             0
Subtotal 30                                                       9400                0             1
Total                                                            29025                1             1

13 rows selected.

SQL>

DB架构
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
group by使用
张亚京
04-03 7433
   通用数据库具有基于表的特定列对数据进行分析的能力。 可按照在 GROUP BY 子句中定义的组对行进行分组。以其最简单的形式，组由称为分组列的列组成。 SELECT 子句中的列名必须为分组列或列函数。列函数对于 GROUP BY 子句定义的每个组各返回一个结果。下列示例产生一个列出每个部门编号的最高薪水的结果： SELECT DEPT, MAX(SALARY) AS MAXIMU
group by的使用
xuanwugang的博客
03-26 1530
1、概述“Group By”从字面意义上理解就是根据“By”指定的规则对数据进行分组，所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理。一般跟聚合函数一起使用，如sum，max，min2、原始表3、简单Group By示例1select 类别, sum(数量) as 数量之和 from A group by 类别返回结果如下表，实际上就是分类汇总。4、Gr...
group by的增强
weixin_43814593的博客
04-23 274
分组函数的嵌套 目的：求部门平均工资的最大值 select max(avg(sal)) from emp group by deptno; GROUP BY 语句的增强 select deptno,job,sum(sal) from emp group by deptno,job order by deptno; select sum(sal) from emp group by deptno;...
Oracle group by 语句增强
晓晓鱼的博客
10-23 1242
SELECT dept, job, SUM(salary) FROM emp GROUP BY dept, job; + SELECT dept,  SUM(salary) FROM emp GROUP BY dept; + SELECT SUM(salary) FROM emp; 可以写成： SELECT dept, job, SUM(salary) FROM emp G
增强group by
cml2016的博客
03-23 144
总结： 1.提示当使用group by时，未在group by部分用到的表列在select部分出现时必须使用分组函数。 2.having子句允许用户指定对一个记录组的搜索条件。而通常的where查询条件只针对单记录，不...
关于group by的深入理解(扩展到rollup,增强groupby,主要用于小计)
男人,就应该对自己狠一点!
03-06 528
当我们选不只一个栏位，且其中至少一个栏位有包含函数的运用时，我们就要用到group by这个指令。在这个情况下。 我们需要确定我们有group by所有其他的栏位。换句话说。除了包括函数的栏位外。我们都要将其放在group by子名。 msdn中这样解释： 指定用来放置输出行的组。如果 SELECT 子句 <select list> 中包含聚合函数，则 GROUP BY 将计算每组的汇...
高级sql学习--增强group by！！！
事后诸葛亮的程序人生（微信：zq9017197）
10-15 1995
<br />总结：<br />1.提示当使用group by时，未在group by部分用到的表列在select部分出现时必须使用分组函数。<br />2.having子句允许用户指定对一个记录组的搜索条件。而通常的where查询条件只针对单记录，不针对记录组。<br />3.在Group By子句中，必须采用表达式的全称，而不能够采用其别名.<br />4.采用Group语句会自动对纪录进行排序。在使用Order By排序子句对统计结果进行重新排序的时候，要慎重。<br />5.如果想把某个字段当作第一顺
解决MySQL 5.7.9版本sql_mode=only_full_group_by问题
09-09
在MySQL 5.7.9版本中，引入了一个新的默认SQL模式`ONLY_FULL_GROUP_BY`，这个模式旨在增强SQL查询的严谨性和一致性，遵循SQL标准。然而，它也可能导致一些原本在旧版本中能正常运行的查询出现错误。本文将详细讲解`...
Python在groupby分组后提取指定位置记录方法
12-24
Python的pandas库中的groupby()函数是进行数据分组的核心工具，它允许我们将数据按照某一或某些列进行聚合，以实现对特定组内的统计分析。本文将深入探讨如何在groupby分组后提取指定位置的记录，这对于精细化的数据...
sqlserver+group by分组查询分页存储过程
07-16
`GROUP BY`子句在SQL查询语言中用于将数据表中的行归类为一组，通常与聚合函数如`COUNT()`, `SUM()`, `AVG()`, `MAX()`, 和 `MIN()` 结合使用，以便对每一组的数据进行统计分析。例如，如果我们有一个销售记录表，并...
Mysql5.7及以上版本 ONLY_FULL_GROUP_BY报错的解决方法
09-08
MySQL 5.7及以上版本引入了一个新的SQL标准模式`ONLY_FULL_GROUP_BY`，该模式旨在增强SQL查询的严格性，确保结果集的合法性。在早期的MySQL版本中，某些GROUP BY查询可以返回不确定的行数据，这在严格模式下是不允许...
慕课网笔记（增强型 group by ）
qq_37538698的博客
10-31 150
分组函数的嵌套 目的：求部门平均工资的最大值 select max(avg(sal)) from emp group by deptno; GROUP BY 语句的增强 select deptno,job,sum(sal) from emp group by deptno,job order by deptno; select sum(sal) from emp group by dep...
提高group by语句的执行效率
我的秘密花园
03-30 3146
distinct A,B,C过滤掉3个字段都相同的记录
hive中group by的增强特性：grouping__id,grouping,groupin sets,cube,rollup
fuyun6363的博客
03-03 5311
目录GROUPING SETS按grouping sets查询和group by查询再union的等价关系Grouping__ID（两个下划线_）Groupingcube and rolluprollupcubehive.new.job.grouping.set.cardinality设置每条数据复制的份数 本博客来聊聊SELECT语句的GROUP BY子句的增强聚合特性。 模拟数据（分隔符为’...
Oracle Group by增强功能(Rollup，Cube，Grouping sets及Grouping函数)学习笔记+实例
CircleLY的博客
07-17 1951
Rollup，Cube，Grouping sets都是用于在Group by的基础上进行小组小计，合计等增强操作，多用于如公司-部门-小组等多级运算。 在本次测试中用到数据表如下(建表语句及测试数据在文章末尾)： 包含员工ID，员工姓名，部门ID，职位ID，薪资 1.Rollup 1.1 使用实例 SELECT department_id, job_id,employee_id,...
webjs 数据库 离线读取本地数据库源码-SAAS 本地化及未来之窗行业应用跨平台架构
最新发布
cybersnow精通 28 门计算机语言，凭借其超凡的技术能力，成功开发过上万个应用，广泛涉及政府、商业、个人等众多领域，甚至在检察院、环保局、公安局等专业场景中也大放异彩。不仅熟练掌握单片机和物联网开发，在软件架构设计方面更是独树一帜，自创了跨平台软件
08-15 176
【代码】webjs 数据库 离线读取本地数据库源码-SAAS 本地化及未来之窗行业应用跨平台架构。
[Qt][Qt窗口][下]详细讲解
SnowK博客
08-13 3683
[Qt][Qt窗口][下]详细讲解
mysql介绍
weixin_57763462的博客
08-14 301
丰富的存储引擎：MySQL 提供了多种存储引擎，如 InnoDB、MyISAM、Memory 等，每种存储引擎都有其特定的应用场景和优势。多语言支持：MySQL 支持多种编程语言，包括 C、C++、Python、Java、Perl、PHP 等，并提供了相应的 API。安全性：MySQL 提供了灵活的权限和密码系统，支持基于主机的验证，并且所有的密码传输都采用加密形式，确保了数据的安全性。管理工具：MySQL 提供了丰富的管理工具，如命令行客户端、图形界面管理工具等，方便用户进行数据库的管理和维护。
pandas里面的groupby
05-13
在pandas中，groupby是一种非常强大的数据分组技术，可以对数据进行分组、聚合、转换等操作，通常用于数据分析和处理。...另外，groupby还支持apply函数，可以对每个分组进行自定义操作，大大增强了其灵活性和适用性。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值