Oracle学习——多行函数(组函数)和创建数据组(GROUP BY)
本章将介绍多行函数(组函数)的使用。
1、组函数介绍
组函数操作行集,给出每组的结果。组函数不象单行函数一样,组函数对行的集合进行操作,每组只返回一个结果。这些集合可能是整个表或者表分成的组。
1.1、组函数与单行函数区别
单行函数对查询到的每个结果集做处理,而组函数只对分组数据做处理。
单行函数对每个结果集返回一个结果,而组函数对每个分组只返回一个结果。
1.2、组函数类型
- AVG 平均数
- COUNT 计数
- MAX 最大值
- MIN 最小值
- SUM 合计
1.3、组函数语法
语法结构:
1 2 3 4 5 | SELECT [ column ,] group_function( column ), .... FROM table [ WHERE condition] [ GROUP BY column ] [ ORDER BY column ] |
如果没有GROUP BY子句,将会把整个数据集看作一个组,从而进行组函数的计算。
1.4、组函数使用原则
- 用于函数的参数的数据类型可以是CHAR、VARCHAR2、NUMBER或者DATE
- 所有组函数忽略空值,为了用一个值代替空值,用NVL、NVL2或COALESCE函数进行对空值的处理
2、组函数的使用
2.1、AVG和SUM函数
AVG(arg):对分组的数据做平均值运算。(如果该行是空值,则不会作为行计算)
arg:参数类型只能是数字类型
SUM(arg):对分组数据求和
arg:参数类型只能是数字类型
2.2、MIN和MAX函数
MIN(arg):求得分组中最小数据
arg:参数类型可以是字符(排序最前)、数字(最小)、日期(最早)。
MAX(arg):求得分组中最大数据
arg:参数类型可以是字符(排序最后)、数字(最大)、日期(最迟)。
2.3、COUNT函数
返回一个表中的行数。
COUNT函数有三种格式:COUNT(*),COUNT(expr),COUNT(DISTINCT expr)
2.3.1、COUNT(*)
返回表中满足SELECT语句标准的行数,包括重复行,包括有空值列的行。如果有WHERE语句,则COUNT(*)会返回满足WHERE子句条件的行数。
2.3.2、COUNT(expr)
返回在列中的,由expr指定的非空值的数。(比如指定某一行,则只有该行有数的行才会被计数)
2.3.3、COUNT(DISTINCT expr)
返回列中,由expr指定的非空的且不重复的行数。
2.4、组函数与空值Null
所有组函数忽略了列中的空值。在组函数使用中可以使用NVL函数来处理空值。
1 2 | SELECT AVG (NAVL(commission_pct, 0)) FROM employee |
上述语句主要说的是计算所有员工的commisson_pct字段下的平均值。如果该字段下的数据有空值则使用0来替代计算平均值。(我们不能直接使用AVG因为会自动忽略空值的行作为数据集中的一员)
3、创建数据组(GROUP BY)
根据需要将查询到的结果集根据某个信息划分为较小的组,采用GROUP BY子句实现。
语法结构:
1 2 3 4 5 | SELECT [ column ,] group_function( column ), .... FROM table [ WHERE condition] GROUP BY column [ ORDER BY column ] |
GROUP BY需要跟在WHERE子句的后面(如果有WHERE子句)。
3.1、使用分组原则
- 如果在SELECT子句中包含了组函数,就不能选择单独的结果,除非单独的列出现在GROUPY子句中。如果未能在GROUP BY子句中包含一个字段列表,你会收到一个错误信息。
- 使用WHERE子句,你可以在划分行成组以前进行过滤行
- 在GROUP BY子句中必须包含列,且不能使用列的别名
- 默认情况下,行的排列会以包含在GROUP BY列表中的所有字段的升序进行排列。可以使用ODER BY子句来个性化定义。
3.2、约束分组结果(HAVING子句)
我们前面提到了WHERE子句需要在GROUP BY子句之前来进行筛选,先对大集合进行筛选后才会进行分组,再进行分组计算。那我们对于分组计算后的结果集还要进行筛选该怎么办呢?这时就需要使用HAVING子句。
HAVING子句适用于对于分组处理之后的结果进行条件的过滤。一般会和GROUP BY一起使用。
语法结构:
1 2 3 4 | SELECT column1, group_fun(column2) FROM table GROUP BY cloumn1 HAVING 过滤条件(例如: MAX (column2) > 100) |
这里注意HAVING之后跟随的条件不一定是SELECT中出现的聚合函数的条件。(你可以使用MIN(column3) < 1000)都是可以的。
3.3、组函数的嵌套
对于组函数我们是可以进行嵌套的,举一个常见的例子,我们要求每个部门的平均薪水中的最大值。(最大平均薪水)
1 2 3 | SELECT MAX ( AVG (salary)) FROM employee e GROUP BY e.departmentid |
采用GROUP BY之后我们使用AVG将会得到多个组的值,对这些多个组再进行MAX就可以再得到这些组的最大值了。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性