九、聚合函数

九、聚合函数

*聚合函数⼜叫组函数，通常是对表中的数据进⾏统计和计算，⼀般结合分组(group by)来使⽤，⽤于统计和计算分组数据

※※※注意：聚合函数不对空值进⾏统计！！！※※※

*常⽤的聚合函数:

1. max(col): 表⽰求指定列的最⼤值
2. min(col): 表⽰求指定列的最⼩值
3. sum(col): 表⽰求指定列的和
4. avg(col): 表⽰求指定列的平均值
5. count(col): 表⽰求指定列的总⾏数

---

1、max(列名)

SELECT MAX(sal) FROM emp;

---

2、min(列名)

SELECT MIN(sal) FROM emp;

---

3、sum(列名)

-- 会自动忽略null，不忽略0
SELECT SUM(comm) FROM emp;

---

4、avg(列名)

-- 默认会忽略null，没有奖金的人数会忽略不算
SELECT AVG(comm) FROM emp;
-- 使用nvl可以避免这个问题
SELECT AVG(NVL(comm, 0)) FROM emp;

---

5、count(列名)

-- 包含所有列，相当于行数，统计时不忽略null
SELECT COUNT(*) FROM emp;
-- 只包含comm那一列，统计时忽略null
SELECT COUNT(comm) FROM emp;
-- 用1代表代码行，统计时忽略一整行字段值都是null的行
SELECT COUNT(1) FROM emp;

效率上：

1. 列名是主键(empno)：count(列) > count(1) 【主键有索引】
   反之：count(1) > count(列)
2. 如果表多个列，没有主键：count(1) > count()
   反之：count(主键列) 效率最高
3. 如果表只有一个字段(列)：count(*)最快

---

6、*Group By

-- 格式
group by ... having

-- 详细格式
select 列 
from 表 
where 条件 
group by 分组 
having 分组过滤 
order by 列

-- 栗子1
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno;
-- 栗子2：having：只出现在group by后面，顶替where的功能
SELECT deptno,AVG(sal) 平均工资 FROM emp
GROUP BY deptno HAVING AVG(sal)>2000 
ORDER BY deptno ASC;
-- 栗子3：多列分组查询
SELECT deptno,job,count(*) 
FROM emp 
GROUP BY deptno,job;

*多列分组规则：

1. 多列分组时，分组列出现顺序对于最终查询结果没有任何影响的
   1. Group By deptno, job 与Group By job, deptno给出最终结果是一致的
2. 多列分组时, Group By 一次只能根据一个分组列进行分组
   1. Group By deptno,job 此时Group By 需要执行两次
3. 多列分组时,从第二个分组列开始,操作的是上一个分组列生成的临时表
   1. Group By deptno,job，当执行 Group By job时，操作临时表由Group By deptno来生成

※※※注意：※※※

1、where后面不允许过滤分组函数，对分组条件过滤用 having！

2、select的列必须是group by列中的值！！！

---

7、*Order By

1. desc： 降序
2. asc：升序

※※※注意：Order By使用的排序字段只能来源于select生成的临时表列※※※

-- 栗子
SELECT * FROM emp ORDER BY sal DESC；
-- 表示根据select生成的临时表的第3列进行排序（排序从1开始，此处为sal）
select empno,ename,sal from emp order by 3 asc;
-- 多列（用‘,’分割）
SELECT * FROM emp WHERE deptno=30 ORDER BY sal DESC,hiredate ASC;

-- 栗子
select dept.dname, count(*) -- 此时count(*)是聚合函数
from dept join emp
on dept.deptno = emp.deptno
group by dept.dname
having count(*) >= 5 -- 此时count(*)是聚合函数
order by count(*) desc; -- 此时count(*)是select生成的新临时表的列名

---

8、*limit

*从临时表指定位置的数据行开始，向下截取指定数量的数据行并将其保存到一个新的临时表中。

-- limit两位参数时，第一个是开始位置（第一个返回记录行的偏移量），第二个是截取行数（返回记录行的最大数目）
select * from emp order by sal desc limit 0,2
-- limit 2 相当于 limit0,2
select * from emp order by sal desc limit 2