SQL 分组函数介绍

**1、 **分组函数的概念
分组函数作用于一组数据，并对一组数据返回一个值。

2、 分组函数的类型：
avg 平均值
count 统计值
max 最大值
min 最小值
sum 合计
stddev 标准差
variance 方差

a . AVG 和 SUM 函数
可以对数值型数据使用 AVG 和 SUM 函数

#查询公司中平均奖金是多少

SQL> select avg(sal)
  2  from emp;

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

b . MIN 和 MAX 函数
可以对数值型、字符型和日期型数据使用 MIN 和 MAX 函数

#列出最早入职和最晚入职的时间，并计算出他们的天数差和月份差 增加列别名 days months

SQL> select min(hiredate),max(hiredate),max(hiredate)-min(hiredate) days,months_between(max(hiredate),min(hiredate)) months
  2  from emp;

MIN(HIREDA MAX(HIREDA       DAYS     MONTHS
---------- ---------- ---------- ----------
1980-12-17 1987-05-23       2348 77.1935484

#查询各部门的最高薪水、最低薪水、平均薪水

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

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

c. count 函数
COUNT() 返回表中的行数：
COUNT()返回满足SELECT语句条件的表中的行数，包括重复的行和任何列中包含空值的行。如果SELECT语句中包含WHERE子句，COUNT(*)返回满足WHERE子句中条件的行数。

# 返回表的行数
SQL> select count() from emp;

  COUNT(*)
----------
        14

#查询公司中有奖金的人数

SQL> select count(*)
  2  from emp
  3  where comm!=0 or comm is not null;

  COUNT(*)
----------
         4

count（expr） 返回非控制的expr 的行数
返回由expr标识的列中非空值的数量。

#查询公司所有员工的个数

SQL> select count(ename)
  2  from emp;  

COUNT(ENAME)
------------
          14

d . DISTINCT 关键字

count （distinct expr） 返回 expr 非空且不重复的记录数。

SQL> select count(distinct deptno) 
  2  from emp;

COUNT(DISTINCTDEPTNO)
---------------------
                    3

e . 分组函数和空值

SQL> select comm from emp;

      COMM
----------

       300
       500

      1400




         0





14 rows selected.

分组函数忽略空值：

SQL> select avg(comm) from emp;

 AVG(COMM)
----------
       550

NVL 函数使分组函数无法忽略空值：

SQL> select avg(nvl(comm,0)) from emp;   

AVG(NVL(COMM,0))
----------------
      157.142857

3. 分组数据
   有时需要将信息表分成更小的组。这可以通过使用GROUP BY子句来实现。
   可以使用GROUP BY 子句将表中的数据分成若干组。

#查看公司各职位的的员工个数，按照人数最多到最少排序

SQL> select job,count(*)     
  2  from emp
  3  group by job
  4  order by count(*) desc;

JOB         COUNT(*)
--------- ----------
CLERK              4
SALESMAN           4
MANAGER            3
ANALYST            2
PRESIDENT          1

4、过滤分组
不能使用where 子句过滤组， 可以使用having 子句过滤组
HAVING 子句
使用HAVING子句过滤分组：

1. 行已经被分组
2. 使用了分组函数
3. 满足having子句中条件的分组将被显示

#查询各部门的平均薪水及部门编号，要求只有员工姓名中包含‘A’才参与统计，只列出平均薪水>1500的，按照平均薪水升序排列

SQL> select deptno,avg(sal)
  2  from emp 
  3  where ename like '%A%'
  4  group by deptno
  5  having avg(sal)>1500
  6  order by avg(sal);

    DEPTNO   AVG(SAL)
---------- ----------
        30       1580
        10       2450

#按照部门分组统计，求最高薪水，平均薪水，最低薪水，只有薪水是1200以上的员工才参与统计，并且分组结果中只包含平均薪水在1500以上的部门，并且按照平均薪水倒序排列

SQL> select deptno,max(sal),min(sal),avg(sal)
  2  from emp
  3  where sal > 1200
  4  group by deptno
  5  having avg(sal)>1500
  6  order by avg(sal) desc;

    DEPTNO   MAX(SAL)   MIN(SAL)   AVG(SAL)
---------- ---------- ---------- ----------
        20       3000       2975 2991.66667
        10       5000       1300 2916.66667
        30       2850       1250       1690

#查询最高工资大于4000的部门编号

SQL> select deptno
  2  from emp
  3  group by deptno 
  4  having max(sal)>4000;

    DEPTNO
----------
        10