PL/SQL 知识量:16 - 57 - 244
在大多数情况下,使用聚合函数返回的是所有行数据的统计结果。但这通常不是我们所需要的,一般情况下需要按某一列数据的值进行分类,在分类的基础上再进行查询。
创建分组是通过GROUP BY子句实现的。与WHERE子句不同,GROUP BY子句用于归纳信息类型,以汇总相关数据。
以下示例按照性别分组查询年龄的有关信息:
SQL> select sex,max(age),min(age),sum(age),avg(age) from people group by sex; SEX MAX(AGE) MIN(AGE) SUM(AGE) AVG(AGE) ------ ---------- ---------- ---------- ---------- male 32 25 113 28.25 female 32 19 80 26.6666666
所有的聚合函数都是对查询出的每一行数据进行分类后再进行统计计算的。所以在结果集中,对所进行分类的列的每一种数据都有一行统计结果值与之对应。
注意:GROUP BY子句中不支持对列分配的别名,也不支持使用了统计函数的集合列。另外,对SELECT后面每一列数据,除了出现在统计函数中的列以外,都必须在GROUP BY子句中应用。
如果表中记录的分组依赖于多列,只要在查询的GROUP BY子句中列出定义分组所需的所有列即可。
在SELECT语句的GROUP BY子句中,列出的列的数目没有上限,对组合列的唯一限制是其必须是查询的FROM子句中列出的表中的列。
SQL> select sex,height,max(age),min(age),sum(age),avg(age) from people group by sex,height; SEX HEIGHT MAX(AGE) MIN(AGE) SUM(AGE) AVG(AGE) ------ ------ ---------- ---------- ---------- ---------- male 170 25 25 25 25 male 176 27 27 27 27 female 166 32 32 32 32 female 168 29 19 48 24 male 188 29 29 29 29 male 166 32 32 32 32 6 rows selected
聚合函数按照GROUP BY子句列出的这些列的唯一组合来进行统计计算。
当GROUP BY子句中用于分组的列中出现NULL值时,会将所有的空值分在同一组,即认为它们是“相等”的。
示例:
SQL> select * from books; ID NAME PRICE --- ---------- ------ 1 SQL 50 2 MySQL 32 3 CSS 50 4 Java 5 JavaScript 6 Python 32 6 rows selected SQL> select price,avg(price),count(*) from books group by price; PRICE AVG(PRICE) COUNT(*) ------ ---------- ---------- 50 50 2 32 32 2 2
在SELECT语句的GROUP BY子句中,为要分组的列使用CUBE和ROLLUP运算符,可以快捷、有效地对存储在数据库里的数据进行汇总分析。CUBE和ROLLUP是SQL的扩展命令,可以在Oracle 8i(及以上版本)中使用。
1、CUBE运算符
CUBE生成的结果集是多维数据集。多维数据集是事实数据(即记录个别事件的数据)的扩展。扩展是基于用户要分析的列建立的,这些列称为维度。多维数据集是结果集,其中包含各维度的所有可能组合的交叉表格。
SQL> select sex,height,count(*) from people group by sex,height; SEX HEIGHT COUNT(*) ------ ------ ---------- male 170 1 male 176 1 female 166 1 female 168 2 male 188 1 male 166 1 6 rows selected SQL> select sex,height,count(*) from people group by cube(sex,height); SEX HEIGHT COUNT(*) ------ ------ ---------- 7 166 2 168 2 170 1 176 1 188 1 male 4 male 166 1 male 170 1 male 176 1 male 188 1 female 3 female 166 1 female 168 2 14 rows selected
由以上代码可见,使用CUBE运算符比不使用多出许多数据,出现了很多额外的分组,包括空行,这些绝不会出现在标准的GROUP BY命令所返回的结果中,它们都是CUBE命令所添加的汇总项。
sex和height维度(列)都包含空值,这表示此行中汇总了这两个维度(列)的所有值。
2、ROLLUP运算符
在生成包含小计和合计的报表时,ROLLUP运算符很有用。ROLLUP运算符生成的结果集类似于CUBE运算符所生成的结果集。CUBE和ROLLUP之间的区别在于:CUBE生成的结果集显示了所选列中值的所有组合的聚合;ROLLUP生成的结果集显示了所选列中值的某一层次结构的聚合。
SQL> select sex,height,count(*) from people group by rollup(sex,height); SEX HEIGHT COUNT(*) ------ ------ ---------- male 170 1 male 176 1 female 166 1 female 168 2 male 188 1 male 166 1 male 4 female 3 7 9 rows selected
与CUBE运算符相比,结果中只包含了sex列的统计信息和所有记录的统计信息,而没有包含height列的统计信息。如果需要统计height列的信息,而不统计sex列的信息,则只需要在GROUP BY子句中将height列放在最前面即可。
3、区分不同的NULL值
使用CUBE和ROLLUP运算符操作生成空值将会带来一个问题:如何区分CUBE操作生成的NULL值和在实际数据中返回的NULL值?可以使用GROUPING函数解决此问题。如果列值来自实际数据,GROUPING函数将返回0;如果列值是由CUBE操作生成的NULL,则返回1。
在CUBE(ROLLUP)操作中,生成的NULL代表所有值。因此,可以编写SELECT语句,使用GROUPING函数判断NULL的来源,将生成的NULL替换为字符串“ALL”。而实际数据中的NULL表示数据值未知,因此也可以将其SELECT编码为返回字符串“UNKNOWN”,用于表示实际数据中的NULL。
SQL> select * from books; ID NAME PRICE --- ---------- ------ 1 SQL 50 2 MySQL 32 3 CSS 50 4 Java 5 JavaScript 6 Python 32 6 rows selected SQL> select name,price,count(*) from books group by rollup(name,price); NAME PRICE COUNT(*) ---------- ------ ---------- SQL 50 1 MySQL 32 1 CSS 50 1 Java 1 JavaScript 1 Python 32 1 SQL 1 MySQL 1 CSS 1 Java 1 JavaScript 1 Python 1 6 13 rows selected SQL> select case when(grouping(name)=1) 2 then 'all' 3 else nvl(to_char(name),'unknown') 4 end as name, 5 case when(grouping(price)=1) 6 then 'all' 7 else nvl(to_char(price),'unknown') 8 end as price, 9 count(*) 10 from books 11 group by rollup(name,price); NAME PRICE COUNT(*) ---------- ---------------------------------------- ---------- SQL 50 1 MySQL 32 1 CSS 50 1 Java unknown 1 JavaScript unknown 1 Python 32 1 SQL all 1 MySQL all 1 CSS all 1 Java all 1 JavaScript all 1 Python all 1 all all 6 13 rows selected
注意:替换的数据类型要与该字段的数据类型相匹配,即只有字符类型的字段才能将其替换为“ALL”或者“UNKNOWN”,因此,以上代码中使用了to_char函数。
利用GROUP BY子句分组,只是简单地依据所选列的数据进行分组,将该列具有相同值的行划为一组。而在实际应用中,往往还需要删除那些不能满足条件的行组,为了实现这个功能,SQL提供了HAVING子句指定组或聚合的搜索条件。
HAVING通常与GROUP BY子句一起使用。如果不使用GROUP BY子句,HAVING的行为与WHERE子句一样。
SQL> select * from people; ID NAME AGE SEX HEIGHT WEIGHT ---- ---------- ---- ------ ------ ------ 1 Bob 25 male 170 70 2 Jeff 27 male 176 82 3 Jan 32 female 166 52 4 Susan 19 female 168 55 5 Tom 29 male 188 95 6 Lucy 29 female 168 65 7 Tomas 32 male 166 60 7 rows selected SQL> select sex,count(*) from people group by sex having count(*)>3; SEX COUNT(*) ------ ---------- male 4
HAVING子句和WHERE子句的相似之处在于,它也定义搜索条件。有关WHERE子句的所有操作,如使用连接符、通配符、函数等,在HAVING子句中都可以使用。但与WHERE子句不同, HAVING子句与组有关,而不是与单个的记录行有关。
总结如下:
如果指定了GROUP BY子句,那么HAVING子句定义的搜索条件将作用于这个GROUP BY子句创建的那些组上。
如果指定WHERE子句,而没有指定GROUP BY子句,那么HAVING子句定义的搜索条件将作用于WHERE子句的输出上,并把这个输出看作一个组。
如果既没有指定GROUP BY子句也没有指定WHERE子句,那么HAVING子句定义的搜索条件将作用于FROM子句的输出上,并把这个输出看作一个组。
Copyright © 2017-Now pnotes.cn. All Rights Reserved.
编程学习笔记 保留所有权利
MARK:3.0.0.20240214.P35
From 2017.2.6