05-03 02:12 阅读 172

Oracle 19C学习 - 06. 聚合函数与GROUP BY(HAVING)分组

（本博文采用的数据库是Oracle自带的hr用户范本数据库)

聚合函数

聚合函数对行进行操作，以使每个组产生一个结果。

AVG(字段)与SUM(字段)函数

查找最大和最小入职日期

复制代码

SELECT MAX(hire_date), MIN(hire_date)
FROM employees
WHERE job_id LIKE '%PROG%';

MAX(HIRE_DATE)      MIN(HIRE_DATE)------------------- -------------------2007-05-21 00:00:00 2005-06-25 00:00:00

复制代码

AVG函数遇到NULL的字段会忽略，比如十条记录求平均值，有两条记录是NULL，所以得到的结果是总数/8的结果。

要解决这个问题，需要使用NVL函数先将NULL值替换，然后嵌套在AVG函数内部求平均数。

复制代码

SELECT AVG(commission_pct) FROM employees;   --只有非NULL记录参加了计算

AVG(COMMISSION_PCT)-------------------
         .222857143SQL> SELECT AVG(NVL(commission_pct,0)) FROM employees;   --将NULL记录替换为0后参加计算。

AVG(NVL(COMMISSION_PCT,0))--------------------------
                .072897196

复制代码

COUNT(字段)求个数，这个函数不统计空值。

找出工资大于10000的员工数量

复制代码

 SELECT COUNT(commission_pct) FROM employees;

COUNT(COMMISSION_PCT)
---------------------
                   35

SELECT COUNT(*) FROM employees;

  COUNT(*)
----------
       107

复制代码

DISTINCT关键字去除重复

根据员工表统计出部门的数量

SELECT COUNT(DISTINCT(department_id)) FROM employees;

COUNT(DISTINCT(DEPARTMENT_ID))------------------------------                            11

GROUP BY子句

将数据表中的行分成较小的组。

GROUP BY子句语法

按照department_id进行分组，然后求每个部门的平均工资。

复制代码

SELECT department_id, AVG(salary) FROM employees   --先分组，再聚合
GROUP BY department_id
ORDER BY department_id

DEPARTMENT_ID AVG(SALARY)
------------- -----------           10        4400           20        9500           30        4150           40        6500           50        3475.55556           60        5760           70       10000           80       8955.88235           90       19333.3333          100       8601.33333          110       10154                    7000

复制代码

按照多列分组： GROUP BY 字段1，字段2

先按照部门进行分组，然后按照部门中员工的职位再次分组求平均工资。

复制代码

SELECT department_id, job_id, AVG(salary)
FROM employees
WHERE department_id <=50GROUP BY department_id, job_idORDER BY department_id, job_id;

DEPARTMENT_ID JOB_ID     AVG(SALARY)
------------- ---------- -----------           10 AD_ASST           4400           20 MK_MAN            13000           20 MK_REP             6000           30 PU_CLERK          2780           30 PU_MAN            11000           40 HR_REP             6500           50 SH_CLERK          3215           50 ST_CLERK          2785           50 ST_MAN            7280

复制代码

非法查询范例

在SELECT语句中，除了聚合函数包含的列，其他的列必须出现在GROUP BY中。如果不在，则会出现聚合函数一个行与其他字段多行对应不上的情况，成为非法查询。

HAVING子句

当使用HAVING子句时，Oracle服务器对组的限制如下:

行被分组。
应用分组功能。
显示与HAVING子句匹配的组。

按照部门分组，查找出平均大于8000工资的部门，查看每个部门的人数是多少。

复制代码

SELECT department_id, COUNT(last_name)
FROM employees
GROUP BY department_id
HAVING AVG(salary) >= 8000ORDER BY department_id;

DEPARTMENT_ID COUNT(LAST_NAME)
------------- ----------------           20                2           70                1           80               34           90                3          100                6          110                2

复制代码

Having可以与WHERE同时出现。

WHERE先限制第一步筛选出来的记录，然后在进行分组，分组后在使用HAVING设定的条件提取出来符合条件的分组。

复制代码

SELECT department_id, COUNT(last_name), job_id, AVG(salary)
FROM employees
WHERE job_id  LIKE '%PROG'GROUP BY department_id, job_id
HAVING AVG(salary) < 8000;

DEPARTMENT_ID COUNT(LAST_NAME) JOB_ID     AVG(SALARY)
------------- ---------------- ---------- -----------           60                5 IT_PROG           5760

复制代码

聚合函数的嵌套

计算出平均工资最高的那个部门。