Oracle DB组函数
&#; 组函数
– 类型和语法
– 使用AVGSUMMINMAXCOUNT
– 在组函数中使用DISTINCT关键字
– 组函数中的NULL值
何谓组函数
组函数会对行集进行计算为每个组提供一个结果
与单行函数不同组函数用于对行集进行计算从而为每个组提供一个结果这些集合可以包含整个表也可以包含表分割成的组
组函数的类型
&#; AVG
&#; COUNT
&#; MAX
&#; MIN
&#; STDDEV
&#; SUM
&#; VARIANCE
每个函数都接受一个参数下表列出了在语法中可使用的选项
组函数语法
SELECT group_function(column)
FROM table
[WHERE condition]
[ORDER BY column];
组函数语法
组函数应放在SELECT关键字之后可以使用逗号分隔多个组函数
使用组函数的准则
&#; DISTINCT使函数仅考虑非重复值;ALL使函数考虑每个值(包括重复值)默认值为ALL因此无需指定
&#; 使用expr参数的函数的数据类型可以是CHARVARCHARNUMBER或DATE
&#; 所有组函数都忽略空值要用一个值替代空值使用NVLNVLCOALESCECASE或DECODE函数
使用AVG和SUM函数
可以对数字数据使用AVG和SUM函数
hr@TEST> SELECT AVG(salary) MAX(salary)MIN(salary) SUM(salary) FROM employees WHERE job_id LIKE %REP%;
AVG(SALARY) MAX(SALARY) MIN(SALARY) SUM(SALARY)
可以对能够存储数字数据的列使用AVGSUMMIN和MAX函数示例显示所有销售代表的月薪平均值最高值最低值与总和
使用MIN和MAX函数
可以对数字字符和日期数据类型使用MIN和MAX函数
hr@TEST> SELECT MIN(hire_date) MAX(hire_date) FROM employees;
MIN(HIRE_DATE) MAX(HIRE_DATE)
JAN APR
可以对数字字符和日期数据类型使用MAX和MIN函数示例显示任职时间最短和最长的雇员
下面的示例显示在包含所有雇员的列表中按字母顺序排列姓氏时位于首位及位于末位的雇员姓氏
hr@TEST> SELECT MIN(last_name) MAX(last_name) FROM employees;
MIN(LAST_NAME) MAX(LAST_NAME)
Abel Zlotkey
注AVGSUMVARIANCE和STDDEV函数仅可用于处理数字数据类型MAX和MIN函数不能用于处理LOB或LONG数据类型
使用COUNT函数
COUNT(*)将返回表中的行数
hr@TEST> SELECT COUNT(*) FROM employees WHERE department_id = ;
COUNT(*)
示例显示部门 中雇员的数量
COUNT(expr)将返回expr为非空值的行的数量
hr@TEST> SELECT COUNT(commission_pct) FROM employees WHERE department_id = ;
COUNT(COMMISSION_PCT)
示例显示部门 中可以获得佣金的雇员的数量
COUNT函数有以下三种格式
&#; COUNT(*)
&#; COUNT(expr)
&#; COUNT(DISTINCT expr)
COUNT(*)用于返回表中符合SELECT语句标准的行数包括重复行和在任何列中含有空值的行如果SELECT语句中包含WHERE子句则COUNT(*)会返回符合WHERE子句中条件的行数相反COUNT(expr)返回由expr标识的列中非空值的数量COUNT(DISTINCT expr)返回由expr标识的列中不同非空值的数量
使用DISTINCT关键字
&#; COUNT(DISTINCT expr)将返回expr的不同非空值的数量
&#; 要显示EMPLOYEES表中不同部门值的数量可使用
hr@TEST> SELECT COUNT(DISTINCT department_id) FROM employees;
COUNT(DISTINCTDEPARTMENT_ID)
使用DISTINCT关键字可以避免对某一列中的任何重复值进行计数
示例显示EMPLOYEES表中不同部门值的数量
组函数和空值
组函数将忽略列中的空值
hr@TEST> SELECT AVG(commission_pct) FROM employees;
AVG(COMMISSION_PCT)
仅根据在表的COMMISSION_PCT列中存储了有效值的行计算平均值平均值的计算方法是用付给所有雇员的佣金总和除以获得佣金的雇员数()
NVL函数会强制组函数包括空值
hr@TEST> SELECT AVG(NVL(commission_pct )) FROM employees;
AVG(NVL(COMMISSION_PCT))
根据表中的所有行计算平均值不考虑COMMISSION_PCT列中是否存储空值平均值的计算方法是用付给所有雇员的佣金总和除以公司中的雇员总数()
所有组函数都忽略列中的空值但是NVL函数会强制组函数包括空值
