安科网

SQL语句——15、高级分组

ALiDan

ALiDan

2020-01-04

关注 关注

rollup 和 cube 

group by 生成数据结果时,只会生成相关列的数据统计信息,而不是生成小计和总计

group by统计结果如下（不是select直接返回格式）:

SQL> select deptno,job,avg(sal) from emp group by deptno,job;
--------------------------------------------------------------
|deptno\job| clerk | analyst | manager | president | salesman |--------------------------------------------------------------
| 10      | 1300 |          | 2450    | 5000      |         |
--------------------------------------------------------------
| 20      | 950  |   3000   | 2975    |           |         |
--------------------------------------------------------------
| 30      | 950  |          | 2850    |           | 1400    |
--------------------------------------------------------------

rollup操作在保留group by统计结果的基础上,还会生成横向小计(部门平均工资)和总计(所有员工的平均工资)

select deptno,job,avg(sal) from emp group by rollup(deptno,job);
-----------------------------------------------------------------------------
|deptno\job | clerk | analyst | manager | president | salesman | 小计        |-----------------------------------------------------------------------------
| 10        | 1300  |         | 2450    | 5000      |          | 2916.66667 |
-----------------------------------------------------------------------------
| 20        | 950   | 3000    | 2975    |           |          | 2175       |
-----------------------------------------------------------------------------
| 30        | 950   |         | 2850    |           | 1400     | 1566.66667 |
-----------------------------------------------------------------------------
| 合计       |       |         |         |           |          | 2073.21429 |
-----------------------------------------------------------------------------

cube 是在group by rollup 的基础上,再做出纵向小计:

SQL> select deptno,job,avg(sal) from emp group by cube(deptno,job);
-----------------------------------------------------------------------------
|deptno\job | clark | analyst | manager | president | salesman | 小计        |-----------------------------------------------------------------------------
| 10        | 1300  || 2450    | 5000      |          | 2916.66667 |
-----------------------------------------------------------------------------
| 20        | 950   | 3000    | 2975    |           |          | 2175       |
-----------------------------------------------------------------------------
| 30        | 950   |         | 2850    |           | 1400     | 1566.66667 |
-----------------------------------------------------------------------------
| 职位小计   |1037.5 | 3000     |2758.333 | 5000     | 1400      |            |
-----------------------------------------------------------------------------
| 合计      |       |          |         |           |          | 2073.21429 |
-----------------------------------------------------------------------------

ROLLUP sql写法:

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

DEPTNO        JOB     AVG(SAL)
---------- --------- ----------
10          CLERK      1300
10          MANAGER    2450
10          PRESIDENT  5000
10                     2916.66667
20          CLERK      950
20          ANALYST    3000
20          MANAGER    2975
20                     2175
30          CLERK      950
30          MANAGER    2850
30          SALESMAN   1400
30                     1566.66667
                       2073.21429

13 rows selected.

CUBE sql写法：

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

DEPTNO         JOB     AVG(SAL)
---------- --------- ----------
10           CLERK      1300
10           MANAGER    2450
10           PRESIDENT  5000
10                      2916.66667
20           ANALYST    3000
20           CLERK      950
20           MANAGER    2975
20                      2175
30           CLERK      950
30           MANAGER    2850
30           SALESMAN   1400
30                      1566.66667
             ANALYST    3000
             CLERK      1037.5
             MANAGER    2758.33333
             PRESIDENT  5000
             SALESMAN   1400
                        2073.21429

18 rows selected.

解析rollup语句

rollup(a,b,c)

等同于

group by a,b,c
union all
group by a,b
union all
group by a
union all
group by null

为维持输出列的数量一直 select子句中用null来代替

举例：

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

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

rows selected.

-----分解为:
SQL> select deptno,job,sum(sal) from emp group by deptno,job
union all
select deptno,null,sum(sal) from emp group by deptno
union all
select null,null,sum(sal) from emp
order by 1;

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

rows selected.

解析cube语句

cube(a,b,c) 分解成2的N次方个union all组合

等同于:

group by a,b,c
union all
group by a,b
union all
group by a
union all
group by a,c
union all
group by b,c
union all
group by b
union all
group by c
union all
group by null

举例：

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

--------解析为:
SQL> select deptno,job,sum(sal) from emp group by deptno,job
union all
select deptno,null,sum(sal) from emp group by deptno
union all
select null,job,sum(sal) from emp group by job
union all
select null,null,sum(sal) from emp
order by 1

grouping

在使用rollup和cube时统计结果可能用到一列也可能用到多列,为了确定统计结果是否使用了特定列才使用grouping

返回为0 代表使用了该列

返回为1 代表没使用该列

SQL> select deptno,job,avg(sal),grouping(deptno),grouping(job) from emp
group by cube (deptno,job) order by 1;

DEPTNO        JOB       AVG(SAL) GROUPING(DEPTNO) GROUPING(JOB)
---------- --------- ---------- ---------------- -------------
10           CLERK       1300         0                0
10           MANAGER     2450         0                0
10           PRESIDENT   5000         0                0
10                       2916.66667   0                1
20           ANALYST     3000         0                0
20           CLERK       950          0                0
20           MANAGER     2975         0                0
20                       2175         0                1
30           CLERK       950          0                0
30           MANAGER     2850         0                0
30           SALESMAN    1400         0                0
30                       1566.66667   0                1
             ANALYST     3000         1                0
             CLERK       1037.5       1                0
             MANAGER     2758.33333   1                0
             PRESIDENT   5000         1                0
             SALESMAN    1400         1                0
                         2073.21429   1                1

18 rows selected.

grouping sets

GROUPING SETS是一个GROUP BY的增强语句，能让你随心所欲的指定多个分组列小计或合计，ORACLE会用UNION ALL将GROUPING SETS中制定的每组合计组合在一起。

GROUPING SETS相对与多个GROUP BY的优势：

  1. 只要一次扫描基表，能得到更好的性能
  2. 不需要写复杂的UNION语句
select deptno,job,avg(sal) from emp group by grouping sets(deptno,job);
--------实际就是两个group by的union all--------
select deptno,null,avg(sal) from emp group by deptno
union all
select null,job,avg(sal) from emp group by job;

sql分组

ALiDan

ALiDan

0 关注 0 粉丝 0 动态

关注 关注

相关推荐

对两个字段分组

在平时的开发任务中我们经常会用到MYSQL的GROUP BY分组， 用来获取数据表中以分组字段为依据的统计数据。比如有一个学生选课表，表结构如下：。GROUP BY X意思是将所有具有相同X字段值的记录放到一个分组里。从表中的记录我们可以看出这个分组结果是

godfather 2020-06-26

报表怎么做“其他”分组

SQL 中分组是很常见的运算，是将一个集合按照某种规则拆分成若干个子集，常常伴随着对子集的进一步汇总计算。报表中我们通常称这种分组为“等值分组”。实现这种等值分组也很容易，只需要按照指定字段做 group(), 再针对分组做聚合运算即可。有时除了上述等值分

ItBJLan 2020-06-17

分组汇总 | 分组汇总去重_了解几种汇总的用法

select fCompany,fFeeStatus,sum as [某家公司的总数 ] --如果fFeeStatus状态只有一种时可以这样做，如果有多种就不可这样做了。from tbGroupSum0605

dreamhua 2020-06-05

sql连接查询和分组查询

FROM <表名> WHERE ……SELECT COUNT(*) AS 人数, SGrade AS 年级 FROM Students GROUP BY SGrade. SELECT COUNT(*)........FROM表名 COUNT B

明月清风精进不止 2020-04-21

SQL分组查询GroupBy加having

“Group By”从字面意义上理解就是根据“By”指定的规则对数据进行分组，所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理。where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分

明月清风精进不止 2020-02-17

MySQL之指定过滤条件（HAVING）

在 MySQL SELECT 语句中，除了能使用 GROUP BY 子句分组数据外，还可以使用 HAVING 子句过滤分组，在结果集中规定了包含哪些分组和排除哪些分组。其中，<条件>指的是指定的过滤条件。HAVING 子句和 WHERE 子句非

ALiDan 2020-01-12

MySQL之分组查询（GROUP BY）

在 MySQL SELECT 语句中，允许使用 GROUP BY 子句，将结果集中的数据行根据选择列的值进行逻辑分组，以便能汇总表内容的子集，实现对每个组而不是对整个结果集进行整合。可以指定多个列，彼此间用逗号分隔。GROUP BY 子句可以包含任意数目的

ztyzly00 2020-01-12

SQL语句——08、聚集,分组,行转列

count 为列值时空不在统计之内，为*时包含空行和重复行。上面执行的聚集函数都是对所有记录统计，如果想分组统计需要使用group by，为了限制分组统计的结果需要使用having过滤。idle> select deptno,avg from emp

tanrong 2019-12-28

SQL语句的补充(一)

select 分组列,聚合函数 from 表名 group by 分组列。--如果分组的列有多个，则要多个列的条件完全一致才会被化为一组。-- having 后面的条件需要注意条件的参数。-- 前三种是常用的。select 列名, 别名 from 表名。如

dreamhua 2019-12-26

分享一则性能优化案例--分组汇总的妙用

概述今天主要分享一个mysql数据库性能优化的案例，这里通过一个需求实现来体验下两个方案的优劣。需求在公司门户系统中实现每个用户查看各自发布的文章列表的时候，能够在文章名称后面显示该文章的留言数量。这里大家可以想一下自己会怎么去实现..总结从整体资源消耗来

81911536 2019-12-14

mysql多种条件分组排序求某类最新一条

主要是对group by 理解的不够深入。才出现这样的情况，后来网上学习了一下，并记录下来分享给大家。select * from test group by category_id order by `date`. select a.* from test

xiaobaif 2019-12-13

sql中筛选第一条记录【分组排序】

我们现在有一张表titles，共有4个字段，分别是emp_no，title(职位)，from_date，to_date，记录的是员工在某个时间段内职位名称，因为会存在升职，转岗之类的，里面emp_no可能会对应多个职位，我们现在要取到所有员工最近的职位信息

一对儿程序猿 2019-12-08

Mysql常用查询

IN/NOT IN操作符：查询满足（不满足）指定范围内的条件的记录。MYSQL> SELECT s_id, s_name FROM fruits WHERE s_id IN ;BETWEEN AND/NOT BETWEEN AND查询： 查询指定范围

azhou 2019-10-22

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

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

oraclemch 2019-07-05

MySQL实现按天分组统计，提供完整日期列表，无数据自动补0

业务需求最近要在系统中加个统计功能，要求是按指定日期范围里按天分组统计数据量，并且要能够查看该时间段内每天的数据量。解决思路直接按数据表日期字段group by统计，发现如果某天没数据，该日期是不出现的，这不太符合业务需求。参考Oracle的“select

sqlitexin 2019-07-25

分组查询GROUP BY的使用与SQL执行顺序的讲解

在SQL中使用GROUP BY来对SELECT的结果进行数据分组，在具体使用GROUP BY之前需要知道一些重要的规定。也就是说可以在组里再分组，为数据分组提供更细致的控制。

汉武帝刘彻 2019-03-19

SQL 难点解决：直观分组

示例 2：按顺序分别列出使用 Chinese、English、French 及其它语言作为官方语言的国家数量。with t as (select 'Chinese',1 union all select 'English',2. union all sel

YFCEMBEDD 2019-06-30

hive row_number分组排序top

自从hive 0.11.0 开始，加入了类似orcle的分析函数，很强大，可以查询到分组排序top值。一：语法(用法)： rank() over dense_rank() over row_number() over 其中[partition by col

zjyzz 2014-12-08

ORA-00937: 不是单组分组函数

今天在java中拼接sql时犯了一个很低级的错误，sql语句拼接好以后，查询总是报ORA-00937: 不是单组分组函数，检查了好久都没没有找到错误的原因，在控制台打印的sql语句放到pl/SQL中执行也没错，但死活在junit测试的时候报ORA-0093

dropkai 2011-06-10

写一个“特殊”的查询构造器 - (五、聚合函数、分组、排序、分页)

where 相关的子句构造完成后，我们继续构造其它子句。这一篇我们进行聚合函数、分组、排序等子句的构造。聚合函数在 SQL 中，有一些用来统计、汇总的函数，被称作聚合函数，如 SUM、COUNT、AVG 等。使用 select() 方法时，我们可以用 se

Depth 2019-06-27
ALiDan

ALiDan

W3CSchool教程
HTML 教程
CSS 教程
Bootstrap 教程
Javascript 教程
jQuery 教程
后端教程
C 教程
Java 教程
PHP 教程
Python 教程
Go 教程
移动开发
Android 教程
Swift 教程
Kotlin 教程
jQuery Mobile 教程
ionic 教程
关于我们
新闻动态
联系方式
招聘英才
安科实验室
帮助与反馈

安科网(Ancii)，中国第一极客网

Copyright © 2013 - 2019 Ancii.com

京ICP备18063983号 京公网安备11010802014868号