Oracle--数据分组查询

                                              数据分组

      在关系数据库中，使用数据分组可以取得表数据的汇总信息。数据分组是通过分组函数、GROUP BY以及HAVING等子句共同实现的。
 一、常用分组函数：
 1.MAX和MIN
 函数MAX用于取得列或表达式的最大值，函数MIN用于取得列或表达式的最小值，这俩个函数适用于任何数据类型。
 2.AVG和SUM
 函数AVG用于取得例或表达式的平均值，函数SUM用于取得列或表达式的总和，这俩个函数只适用于数字类型。
 3.COUNT
 该函数用于取得总计行数，另外，在COUNT函数中还可以引用表达式。因为分组函数会忽略NULL行，所以使用count(表达式)会显示非NULL的总计行数。
 4.VARIANCE和STDDEV
 函数VARIANCE用于取得列或表达式的方差，并且该函数只使用于数字类型。当只有一行数据时，其返回值为0;当存在多行数据时，方差按照如下公式取得：
 (SUM(expr²)-SUM(expr²)/COUNT(expr))/(COUNT(expr)-1)
 函数STDDEV用于取得列或表达式的标准偏差，并且该函数只适用于数字类型。当只有一行数据时，其返回值为0;当存在多行数据时，Oracle按照方差的平方根来计算标准偏差。
 例：SELECT variance(sal) 方差,stddev(sal) 标准偏差 FROM emp;
 二、使用group by 和having子句
 如果不使用group by子句，那么使用分组函数只能汇总表的所有行。为了对表行数据进行分组统计，需要使用group by子句，而为了限制分组显示结果，需要使用having子句。
 1.示例一，使用group by进行单列分组
 SELECT deptno 部门代码,avg(sal) 部门平均工资 FROM emp group by deptno;
 2.示例二，使用group by进行多列分组
 SELECT deptno,job,avg(sal),max(sal) FROM emp group by deptno,job;
 3.示例三，使用order by子句改变分组排序结果
 SELECT deptno,sum(sal) FROM emp group by deptno order by sums(sal) DESC;
 4.示例四，使用having子句限制分组结果
 SELECT deptno,avg(sal),max(sal) FROM emp group by deptno having avg(sal)<2500;
 三、使用ROLLUP和CUBE操作符
 为了取得更全面的数据统计，可以使用rollup和cube操作符。当使用rollup操作符时，在保留原有统计结果的基础上，还会生成横向小计和总计。
 1.示例一，使用rollup操作符执行数据统计(为了生成数据统计，横向小计和总计统计)
 例：SELECT deptno,job,avg(sal) FROM emp group by rollup(deptno,job);
 2.示例二，使用cube操作符执行数据统计(为了生成数据统计，横向小计，纵向小计以及总计统计)
 例：SELECT deptno,job,avg(sal) FROM emp group by cube(deptno,job);
 3.示例三，使用grouping函数
 当使用rollup或者cube操作符生成统计结果时，某个统计结果可能用到一列或者多列，也可能没有用到任何列。
 为了确定统计结果是否用了特定列，可以使用grouping函数。如果该函数返回0，则表示统计结果使用了该列；
 如果函数返回1，则表示统计结果没有使用该列。
 例：SELECT deptno,job,sum(sal),grouping(deptno),grouping(job) FROM emp group by rollup(deptno,job);
 4.示例四，在rollup操作符中使用复合列
 复合列是被看做一个逻辑单元的列组合，当引用复合列时，需要用括号括住相关列。
 例：SELECT deptno,job,sum(sal) FROM emp group by rollup((deptno,job));
 5.示例五，在CUBE操作符中使用复合列
 通过在CUBE操作符中使用复合列，可以略过CUBE操作符的某些统计结果。
 例：SELECT deptno,job,avg(sal) FROM emp group by cube((deptno,job));
 四、使用grouping sets操作符
 grouping sets操作符是group by子句的进一步扩展。通过使用grouping sets操作符，可以合并多个分组的统计结果，从而简化了多个分组的操作。
 下面以示例说明grouping sets操作符的作用及使用方法。
 1.示例一，显示部门平均工资
 当要显示每个部门的平均工资时，需要使用部门号(deptno)执行分组统计操作。
 SELECT deptno,avg(sal) FROM emp group by deptno;
 2.示例二，显示岗位平均工资
 当显示每个岗位的平均工资时，需要使用岗位(job)执行分组统计操作。
 SELECT job,avg(sal) FROM emp group by job;
 3.示例三，显示部门平均工资和岗位平均工资
 为了显示多个分组的统计结果，可以使用grouping sets操作符合并分组统计结果。
 SELECT deptno,job,avg(sal) FROM emp
 group by grouping sets(deptno,job);
 
 SQL> SELECT deptno,job,avg(sal) FROM emp
  2   group by grouping sets(deptno,job);

    DEPTNO JOB                  AVG(SAL)
---------- ------------------ ----------
           CLERK                  1037.5
           SALESMAN                 1400
           PRESIDENT                5000
           MANAGER            2758.33333
           ANALYST                  3000
        30                    1566.66667
        20                          2175
        10                    2916.66667

已选择8行。

五、连接分组
连接分组用于组合不同分组的统计结果。通过在group by子句中指定多个grouping sets操作符、cube操作符、rollup操作符，可以实现连接分组。
1.连接多个grouping sets
通过在group by子句中使用多个grouping sets操作符，可以连接多个grouping sets的分组结果。
SELECT deptno,job,mgr,sum(sal) FROM emp group by grouping sets(deptno),grouping sets(job,mgr);
2.连接rollup
 通过在group by子句中指定rollup操作符和其他分组，可以连接rollup的分组结果。
 SELECT deptno,job,mgr,sum(sal) FROM emp group by deptno,rollup(job,mgr);
3.连接cube
通过在group by子句中指定cube操作符和其他分组，可以连接cube的分组结界。
SELECT deptno,job,mgr,sum(sal) FROM emp group by deptno,cube(job,mgr);