5.分组函数——Oracle数据库学习日记

分组函数

• AVG,SUM,MIN,MAX,COUN函数了解及使用
• 使用GROUP BY 子句对数据进行分组
• 使用HAVING子句过滤分组结果集

1、分组函数

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

• AVG平均值
• COUNT统计值
• MAX最大值
• MIN最小值
• SUM求和
• STDDEV标准差
• VARIANCE方差

特点：组函数忽略空值
组函数使用格式：

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

1.1 AVG函数

示例

SQL> select avg(sal)
  2  from emp;

  AVG(SAL)
----------
2073.21429

查询sal列的平均值。
若列中有空值，会忽略。意思是总和中不包括空值，求平均数时也不会计算在内。

1.2 SUM函数

示例

SQL> select sum(sal)
  2  from emp;

  SUM(SAL)
----------
     29025

计算sal列所有值的和。

1.3 MIN，MAX函数

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

SQL> select min(sal),max(ename),min(hiredate)
 2  from emp;

 MIN(SAL) MAX(ENAME) MIN(HIREDA
---------- ---------- ----------
      800 WARD       1980-12-17

查询最低工资，最大名字，最小日期。（数字比较大小容易理解；字符比较大小比较的是字符的ASCII码的大小，从第一位字符的ASCII码开始比较，相同则比较下一位字符，依次类推；日期比较大小是把日期看作数字进行大小的比较）

1.4 COUNT函数

count函数返回满足select语句条件的表中的行数，包括重复的行和任何列中包含空值的行。
如果select语句中包含where子句，count返回满足where子句条件的行数。
示例

SQL> select count(comm),count(ename),count(*),count(2),count('a')
 2  from emp;

COUNT(COMM) COUNT(ENAME)   COUNT(*)   COUNT(2) COUNT('A')
----------- ------------ ---------- ---------- ----------
         4           14         14         14         14

查询comm列行数、ename列行数及整张表行数。其中*，2，‘a’都可以查询整张表的行数，至于这三种方式哪种效率更高尚有争议，暂且不提。

1.5 DISTINCT关键字

DISTINCT关键字的作用是去重。与count搭配用法如下，用于去重后统计行数：

SELECT COUNT(DISTINCT deptno)
FROM emp;

示例

SQL> select count(distinct job),count(distinct mgr)
  2  from emp;

COUNT(DISTINCTJOB) COUNT(DISTINCTMGR)
------------------ ------------------
                 5                  6

统计job列去重后的行数以及mgr列去重后的行数。

2、分组行

2.1 GROUP BY子句

语法

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

• 可以使用GROUP BY子句将表中的数据分成若干组。
  示例

SQL> select deptno,avg(sal),max(sal)
  2  from emp
  3  group by deptno;

    DEPTNO   AVG(SAL)   MAX(SAL)
---------- ---------- ----------
        30 1566.66667       2850
        20       2175       3000
        10 2916.66667       5000

查询根据deptno(部门编号)分组后的各部门的工资平均值和工资最大值。

• 在select列表中所有未包含在组函数中的列都应该包含在group by子句
  示例

SQL> select deptno,job,count(*),avg(sal),avg(nvl(comm,0))
  2  from emp
  3  group by deptno,job
  4  order by deptno;

    DEPTNO JOB         COUNT(*)   AVG(SAL) AVG(NVL(COMM,0))
---------- --------- ---------- ---------- ----------------
        10 CLERK              1       1300                0
        10 MANAGER            1       2450                0
        10 PRESIDENT          1       5000                0
        20 ANALYST            2       3000                0
        20 CLERK              2        950                0
        20 MANAGER            1       2975                0
        30 CLERK              1        950                0
        30 MANAGER            1       2850                0
        30 SALESMAN           4       1400              550

9 rows selected.

查询每个部门每个岗位的人数和工资平均值、奖金平均值。

2.2 HAVING子句

用HAVING子句过滤分组：

• 行已经被分组
• 使用了分组函数
• 满足having子句中条件的分组将被显示
  不能使用where子句过滤组
  语法

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

示例

SQL> select deptno,avg(sal)
  2  from emp
  3  where job='MANAGER'
  4  group by deptno
  5  having avg(sal)>2500;

    DEPTNO   AVG(SAL)
---------- ----------
        30       2850
        20       2975

按部门分组后查询各部门管理者中平均工资大于2500的部门。

3、嵌套组函数

组函数也可以嵌套使用。
示例

SQL> select max(avg(sal))
  2  from emp
  3  group by deptno;

MAX(AVG(SAL))
-------------
   2916.66667

查询各部门平均工资中的最大值。
组函数嵌套最多嵌套两次，再多没有意义。因为组函数是多行数据输出一个值，第一个函数可以通过group by子句控制输出多行，再嵌套第二个函数后结果只有一行，这时再进行嵌套没有意义