[Oracle数据库学习]五、分组函数

D4

分组函数

分组函数作用于一组数据，并对一组数据返回一个值。

组函数类型

函数	说明
AVG	平均值
COUNT	计数
MAX	最大值
MIN	最小值
STDDEV	标准差
SUM	求和

 

组函数语法

SELECT [column,] group_function(column), ...
FROM table
[WHERE condition]
[GROUP BYcolumn]
[ORDER BYcolumn];

 

AVG和SUM

可以对数值型数据使用。

SELECT AVG(salary), SUM(salary), MAX(salary), MIN(salary)
FROM employees
WHERE job_id like '%DEV%'

AVG(SALARY)	SUM(SALARY)	MAX(SALARY)	MIN(SALARY)
6500	13000	8000	5000

 

MAX和MIN

可以对任意类型数据使用。

SELECT MAX(hire_date), MIN(hire_date)
FROM employees;

MAX(HIRE_DATE)	MIN(HIRE_DATE)
2019-01-05T00:00:00Z	2007-11-13T00:00:00Z

 

COUNT

COUNT(*)

返回记录总数

SELECT COUNT(*)
FROM employees
WHERE department_id = 1;

COUNT(*)

2

 

COUNT(expr)

返回expr不为空的记录总数。

SELECT COUNT(commission_pct)
FROM employees;

COUNT(COMMISSION_PCT)

4

 

COUNT(DISTINCT expr)

返回expr非空且不重复的记录总数。

SELECT COUNT(DISTINCT department_id)
FROM employees;

COUNT(DISTINCTDEPARTMENT_ID)

2

 

组函数忽略空值

SELECT AVG(commission_pct)
FROM employees;

AVG(COMMISSION_PCT)

0.16

NVL函数使分组函数不忽略空值。

SELECT AVG( NVL(commission_pct, 0))
FROM employees;

AVG(NVL(COMMISSION_PCT,0))

0.128

注：此处使用NVL函数当commission_pct为空值时取0。

 

分组数据

GROUP BY子句

SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];

将表中的数据分成若干组。

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;

DEPARTMENT_ID	AVG(SALARY)
1	7500
2	6000

SELECT AVG(salary)
FROM employees
GROUP BY department_id;

AVG(SALARY)

7500

6000

注：不需要在SELECT语句中指定GROUP BY的列。

 

使用多个列分组

SELECT department_id, job_id, AVG(salary)
FROM employees
GROUP BY department_id, job_id;

DEPARTMENT_ID	JOB_ID	AVG(SALARY)
2	DEV	8000
1	PM	10000
2	QA	6000
2	AM	4000
1	DEV	5000

 

 非法使用组函数

1）所有包含于SELECT中而未包含于组函数中的列，都必须包含于GROUPBY子句中。

错误示例：

SELECT department_id, AVG(salary)
FROM employees;

报错：ORA-00937: not a single-group group function

 

2）不能在WHERE子句中使用组函数。

错误示例：

SELECT department_id, AVG(salary)
FROM employees
WHERE AVG(salary) > 8000;

报错：ORA-00934: group function is not allowed here

 

使用HAVING子句过滤分组

SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];

1）行已经被GROUP BY分组；

2）使用了分组函数group_function；

3）显示满足HAVING子句中group_condition的分组。

SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 8000;

DEPARTMENT_ID	MAX(SALARY)
1	10000

SELECT job_id, SUM(salary) PAYROLL
FROM employees
WHERE job_id NOT LIKE '%AM%'
GROUP BY job_id
HAVING SUM(salary) > 8000
ORDER BY SUM(salary);

JOB_ID	PAYROLL
PM	10000
DEV	13000

 

嵌套组函数

SELECT MAX(AVG(salary))
FROM employees
GROUP BY job_id;

MAX(AVG(SALARY))

10000

 

总结：

本节介绍组函数、分组数据：

1）分组函数：AVG、SUM、MAX、MIN、COUNT（COUNT(*)、COUNT(expr)、COUNT(DISTINCT expr)）等；

2）分组数据：GROUP BY子句（分组）、HAVING子句（过滤，可使用组函数）；

3）错误用法：SELECT中的列不在组函数中的，必须在GROUP BY子句中；

　　　　　　WHERE子句中不能使用组函数。