Oracle DB 组函数

原文地址：http://blog.csdn.net/rlhua/article/details/12868639

• 组函数：

– 类型和语法

– 使用AVG、SUM、MIN、MAX、COUNT

– 在组函数中使用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参数的函数的数据类型可以是CHAR、VARCHAR2、NUMBER或DATE。

• 所有组函数都忽略空值。要用一个值替代空值，使用NVL、NVL2、COALESCE、CASE或DECODE函数。

• 使用AVG和SUM函数

可以对数字数据使用AVG和SUM函数。

hr@TEST0924> SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary) FROM employees WHERE job_id LIKE '%REP%';

AVG(SALARY) MAX(SALARY) MIN(SALARY) SUM(SALARY)

----------- ----------- ----------- -----------

 8272.72727       11500        6000      273000

可以对能够存储数字数据的列使用AVG、SUM、MIN和MAX函数。示例显示所有销售代表的月薪平均值、最高值、最低值与总和。

使用MIN和MAX函数

可以对数字、字符和日期数据类型使用MIN和MAX函数。

hr@TEST0924> SELECT MIN(hire_date), MAX(hire_date) FROM employees;

MIN(HIRE_DATE)     MAX(HIRE_DATE)

------------------ ------------------

13-JAN-01          21-APR-08

可以对数字、字符和日期数据类型使用MAX和MIN函数。示例显示任职时间最短和最长的雇员。

下面的示例显示在包含所有雇员的列表中，按字母顺序排列姓氏时位于首位及位于末位的雇员姓氏：

hr@TEST0924> SELECT MIN(last_name), MAX(last_name) FROM employees;

MIN(LAST_NAME)            MAX(LAST_NAME)

------------------------- -------------------------

Abel                      Zlotkey

注：AVG、SUM、VARIANCE和STDDEV函数仅可用于处理数字数据类型。MAX和MIN函数不能用于处理LOB或LONG数据类型。

• 使用COUNT函数

COUNT(*)将返回表中的行数：

hr@TEST0924> SELECT COUNT(*) FROM employees WHERE department_id = 50;

  COUNT(*)

----------

        45

示例显示部门50 中雇员的数量。

COUNT(expr)将返回expr为非空值的行的数量：

hr@TEST0924> SELECT COUNT(commission_pct) FROM employees WHERE department_id = 80;

COUNT(COMMISSION_PCT)

---------------------

                   34

示例显示部门80 中可以获得佣金的雇员的数量。

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@TEST0924> SELECT COUNT(DISTINCT department_id) FROM employees;

COUNT(DISTINCTDEPARTMENT_ID)

----------------------------

                          11

使用DISTINCT关键字可以避免对某一列中的任何重复值进行计数。

示例显示EMPLOYEES表中不同部门值的数量。

• 组函数和空值

组函数将忽略列中的空值：

hr@TEST0924> SELECT AVG(commission_pct) FROM employees;

AVG(COMMISSION_PCT)

-------------------

         .222857143

仅根据在表的COMMISSION_PCT列中存储了有效值的行计算平均值。平均值的计算方法是用付给所有雇员的佣金总和除以获得佣金的雇员数(4)。

NVL函数会强制组函数包括空值：

hr@TEST0924> SELECT AVG(NVL(commission_pct, 0)) FROM employees;

AVG(NVL(COMMISSION_PCT,0))

--------------------------

                .072897196

根据表中的所有行计算平均值，不考虑COMMISSION_PCT列中是否存储空值。平均值的计算方法是用付给所有雇员的佣金总和除以公司中的雇员总数(20)。

所有组函数都忽略列中的空值。但是，NVL函数会强制组函数包括空值。