11.Oracle中五种聚合函数和(group by,having)分组查询

Oracle 聚合函数/分组

五种聚合函数

• min 求列中的最小值
• max 求列中的最大值
• avg 求列中的平均值
• sum 求列中的汇总值
• count 求列中值的个数

注意：

null不参与聚合统计

与聚合无关的列，不能出现在查询字段中

date, char类型不能进行sum和avg

select avg(comm), avg(nvl(comm, 0)), sum(comm)/count(1) from emp;-- null如何统计

-- 求表中总行数
select count(*) from emp;		-- 最差
select sum(1) from emp;			-- 最好
select count(1) from emp;
select count(empno) from emp;

---

group by分组

将查询结果，按照一个列（或者多个列）的值，进行分组，再在每个组的基础上，采用聚合函数进行统计（求这个组中的最大、最小、平均等统计值）

例如：

单个列：按照gender分组，按照job分组，按照deptno分组

-- 求每个部门中的雇员人数和平均薪水
select deptno, count(empno), avg(sal)
    from emp
        group by deptno;

多个列：先按照deptno分组，然后每个deptno再按照job分组

-- 求每个部门分别有哪些职位
-- 方法一：使用distinct
select distinct deptno, job
    from emp
        where job is not null
            order by deptno asc;
-- 方法二：分组
select deptno, job
    from emp
        where job is not null
            group by deptno, job
                order by deptno asc;

-- 每个部门内不同职位的人数
select deptno, job, count(empno)
    from emp
        where job is not null
            group by deptno, job
                order by deptno asc;

having

在分组统计的结果上再对统计的值进行筛选（不同于where，where是在group by之前就已经筛选过了）

-- 求每个部门的最低薪水和最高薪水差额
-- 1、低于1000的临时工不纳入此次统计
-- 2、求贫富差距超过2000的部门
select min(sal), max(sal), deptno
    from emp
        where sal >= 1000
            group by deptno
                having max(sal) - min(sal) > 2000;

思考：

1. 根据雇员的薪水等级分组，求每个等级下各有多少个人数？

select count(1), grade
    from emp join salgrade
        on sal between losal and hisal
            group by grade
                order by grade asc;

2. 根据程序员自定义的薪水范围作为等级条件进行分组，求每个等级下各有多少个人数？

自定义分组列（表达式）

select count(empno) emps,
    case
        when sal between 0 and 999 then 1
        when sal between 1000 and 1999 then 2
        when sal between 2000 and 2999 then 3
        when sal between 3000 and 3999 then 4
        else 5
    end grade
        from emp
            group by
            case
                when sal between 0 and 999 then 1
                when sal between 1000 and 1999 then 2
                when sal between 2000 and 2999 then 3
                when sal between 3000 and 3999 then 4
                else 5
            end
                order by grade asc;