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

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

课程目标

识别可用的聚合函数
使用GROUP BY子句对数据进行分组
使用HAVING子句包含或排除分组的行

 

聚合函数

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

 

 聚合函数的类型

• AVG 平均值
• COUNT 记录个数
• MAX 最大值
• MIN 最小值
• SUM 求和
• LISTAGG 行转列函数
• STDDEV 求标准差
• VARIANCE 求协方差
• MEDIAN 求中位数

 

AVG,MAX, MIN, SUM

 

SELECT TRUNC(AVG(SALARY)), MAX(salary), MIN(salary), SUM(salary)
FROM employees;
/*
TRUNC(AVG(SALARY)) MAX(SALARY) MIN(SALARY) SUM(SALARY)
------------------ ----------- ----------- -----------
              6461       24000        2100      691416
*/

 

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

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

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

AVG(COMMISSION_PCT)
-------------------
         .222857143

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

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

 

 

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

 

-- commission_pct有NULL值，所以count的数量为35
SELECT COUNT(commission_pct) FROM employees;

-- 使用NVL替换空值以后，数量就变为107
SELECT COUNT(NVL(commission_pct, 0)) FROM employees;

 

DISTINCT关键字去除重复

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

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

 

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

-- 先按照department_id进行分组，然后再对每个分组按照job_id再分组
SELECT department_id ,job_id, MAX(salary), AVG(salary)
FROM employees
GROUP BY department_id, job_id
ORDER BY department_id
/*
10    AD_ASST     　　4400    4400
20    MK_MAN        　13000    13000
20    MK_REP        　6000    6000
30    PU_CLERK    　　3100    2780
30    PU_MAN        　11000    11000
*/

 

非法查询范例

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

 

HAVING子句

当使用HAVING子句时，只显示与HAVING子句匹配的分组。

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

SELECT department_id, COUNT(last_name)
FROM employees
GROUP BY department_id
HAVING AVG(salary) >= 8000
ORDER 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

 

聚合函数的嵌套

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