• 8. 聚合函数


    8.1 聚合函数介绍

    聚合函数作用域一组数据,并且对一组数据返回一个值。

    函数 用法
    AVG() 平均值
    SUM()
    MAX() 最大值
    MIN() 最小值
    COUNT() 计数
    • 聚合函数传参需要是一个表中的字段名

    • 聚合函数无法嵌套使用

      # 例如这样是不行的
      AVG(SUM(字段名))
      # SUM(字段名)是一个数值,但是AVG需要传入字段名,所以无法嵌套使用
      

    基本使用

    # 只有数值型数据可以使用AVG()和SUM()
    # 查看工作带有REP的员工的平均工资、总工资、最大工资、最小工资
    SELECT AVG(salary),SUM(salary),MIN(salary),MAX(salary)
    FROM employees 
    WHERE job_id LIKE "%REP%";
    
    # 任何数据都可以用MIN()和MAX(),例如数值,字符串和日期
    SELECT MIN(hire_date), MAX(hire_date)
    FROM employees;
    
    # COUNT()计数函数,作用于任何类型
    SELECT COUNT(*)
    FROM employees
    WHERE department_id = 50;
    

    8.2 GROUP BY

    单列分组

    GROUP BY的作用就是分组,可以将表中的数据分为若干组

    # 先来看看分组后的表格是什么样的
    SELECT *
    FROM employees
    GROUP BY department_id;
    

    image-20220628010154687

    最终的结果却是只有单一的department_id,但是其实对应的数据是表中出现的对应department_id对应的第一条数据。

    # 实际使用
    SELECT department_id, AVG(salary)
    FROM employees
    GROUP BY department_id;
    /*
    +---------------+--------------+
    | department_id | AVG(salary)  |
    +---------------+--------------+
    | NULL          |  7000.000000 |
    |            10 |  4400.000000 |
    |            20 |  9500.000000 |
    |            30 |  4150.000000 |
    |            40 |  6500.000000 |
    |            50 |  3475.555556 |
    |            60 |  5760.000000 |
    |            70 | 10000.000000 |
    |            80 |  8955.882353 |
    |            90 | 19333.333333 |
    |           100 |  8600.000000 |
    |           110 | 10150.000000 |
    +---------------+--------------+
    12 rows in set (0.04 sec)
    */
    

    分类的字段不用包含在SELECT中

    多列分组

    # GROUP BY可以对多个列进行分组操作
    # 多个列分组操作实质上就是排列组合
    # 假设字段一有A、B,字段二有a,b,这两个分组后会有四个组Aa,Ab,Ba,Bb
    # 根据部门和工作分组查询工资
    SELECT department_id, job_id, SUM(salary)
    FROM employees
    GROUP BY department_id, job_id;
    /*
    +---------------+------------+-------------+
    | department_id | job_id     | SUM(salary) |
    +---------------+------------+-------------+
    |            90 | AD_PRES    |    24000.00 |
    |            90 | AD_VP      |    34000.00 |
    |            60 | IT_PROG    |    28800.00 |
    |           100 | FI_MGR     |    12000.00 |
    |           100 | FI_ACCOUNT |    39600.00 |
    |            30 | PU_MAN     |    11000.00 |
    |            30 | PU_CLERK   |    13900.00 |
    |            50 | ST_MAN     |    36400.00 |
    |            50 | ST_CLERK   |    55700.00 |
    |            80 | SA_MAN     |    61000.00 |
    |            80 | SA_REP     |   243500.00 |
    | NULL          | SA_REP     |     7000.00 |
    |            50 | SH_CLERK   |    64300.00 |
    |            10 | AD_ASST    |     4400.00 |
    |            20 | MK_MAN     |    13000.00 |
    |            20 | MK_REP     |     6000.00 |
    |            40 | HR_REP     |     6500.00 |
    |            70 | PR_REP     |    10000.00 |
    |           110 | AC_MGR     |    12000.00 |
    |           110 | AC_ACCOUNT |     8300.00 |
    +---------------+------------+-------------+
    20 rows in set (0.06 sec)
    */
    

    WITH ROLLUP

    使用WITH ROLLUP之后,查询的分组记录之后会增加一条记录,该记录会对聚合函数求所有记录的聚合函数,其余字段为NULL。

    SELECT department_id,AVG(salary),COUNT(department_id)
    FROM employees
    WHERE department_id > 80
    GROUP BY department_id WITH ROLLUP;
    /*
    +---------------+--------------+----------------------+
    | department_id | AVG(salary)  | COUNT(department_id) |
    +---------------+--------------+----------------------+
    |            90 | 19333.333333 |                    3 |
    |           100 |  8600.000000 |                    6 |
    |           110 | 10150.000000 |                    2 |
    | NULL          | 11809.090909 |                   11 |
    +---------------+--------------+----------------------+
    4 rows in set (0.06 sec)
    */
    

    注:使用ROLLUP的时候,不能用ORDER BY字句进行排序,两者互斥

    8.3 HAVING

    HAVING的使用

    HAVING需要和GROUP BY配合使用,相当于GROUP BY中的WHERE条件,但是WHERE不能使用聚合函数,因此需要用HAVING。

    HAVING使用

    • 行已经被分组
    • 使用了聚合函数
    • 满足HAVING的条件的分组会被显示
    • HAVING不能单独使用,需要配合GROUP BY
    # 查询最大工资大于10000的部门和其最大工资
    SELECT department_id,MAX(salary)
    FROM employees
    GROUP BY department_id
    HAVING MAX(salary) > 10000;
    /*
    +---------------+-------------+
    | department_id | MAX(salary) |
    +---------------+-------------+
    |            20 |    13000.00 |
    |            30 |    11000.00 |
    |            80 |    14000.00 |
    |            90 |    24000.00 |
    |           100 |    12000.00 |
    |           110 |    12000.00 |
    +---------------+-------------+
    6 rows in set (0.04 sec)
    */
    

    HAVING和WHERE的比较

    • WHERE可以直接用字段作为条件,不能用分组的聚合函数作为条件;

      HAVING必须要和GROUP配合使用,可以把分组计算的函数和分组字段作为条件

    • WHERE是先筛选后连接

      HAVING是先连接后筛选

      这决定了WHERE比HAVING效率更高,因为连接表后,数据成倍数上升,再筛选效率低

    开发中使用,分组相关用HAVING,普通条件用WHERE可以提高效率

    8.4 SELECT执行顺序

    SQL查询语句顺序

    SELECT ...
    FROM ...
    WHERE ...
    GROUP BY ...
    HAVING ...
    ORDER BY ...
    LIMIT ...
    

    SQL的执行顺序

    FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT
    

    在这七个过程中,每一个过程都会产生一个虚拟表,然后将这个虚拟表传递到下一个步骤。

    在FROM结果如果涉及到多表查询

    • 直接查询多表会在FORM阶段获取到一个笛卡尔积表
    • 然后通过ON来筛选获取筛选后的表
    • 如果添加外部行(左连接、右连接),这里会在上面表的基础上增加外部行。

    8.5 练习

    【题目】
    #1.where子句可否使用分组组函数进行过滤?
    不能
    
    #2.查询公司员工工资的最大值,最小值,平均值,总和
    SELECT MAX(salary), MIN(salary), AVG(salary), SUM(salary)
    FROM employees;
    
    #3.查询各job_id的员工工资的最大值,最小值,平均值,总和
    SELECT employees.job_id, MAX(salary), MIN(salary), AVG(salary), SUM(salary)
    FROM employees 
    GROUP BY employees.job_id;
    
    #4.选择具有各个job_id的员工人数
    SELECT employees.job_id, COUNT(employees.job_id)
    FROM employees 
    GROUP BY employees.job_id;
    
    #5.查询员工最高工资和最低工资的差距(DIFFERENCE)
    SELECT MAX(salary) - MIN(salary)
    FROM employees;
    
    # 6.查询各个管理者手下员工的最低工资,其中最低工资不能低于6000,没有管理者的员工不计算在内
    SELECT e.manager_id, MIN(e.salary)
    FROM employees e
    WHERE e.manager_id IS NOT NULL AND e.salary > 6000
    GROUP BY e.manager_id;
    
    # 7.查询所有部门的名字,location_id,员工数量和平均工资,并按平均工资降序
    SELECT d.department_name, d.location_id, COUNT(e.employee_id),AVG(e.salary)
    FROM employees e RIGHT JOIN departments d
    ON e.department_id = d.department_id
    GROUP BY d.department_id;
    
    # 8.查询每个工种、每个部门的部门名、工种名和最低工资
    SELECT e.job_id, d.department_name, j.job_title, MIN(e.salary)
    FROM employees e RIGHT JOIN departments d
    ON e.department_id = d.department_id
    LEFT JOIN jobs j
    ON e.job_id = j.job_id
    GROUP BY d.department_id, e.job_id ;
    
  • 相关阅读:
    PHP迭代与递归实现无限级分类
    初衷
    基于laravel5.4 vue 和vue-element搭建的单页面后台CMS
    lenovo E431 win8系统 安装win7
    Word Break II
    LeetCode:Linked List Cycle II
    PG里如何查看表,索引,表空间,数据库大小
    美国软件工程师面试注意事项
    打印从1到1000的整数,无循环,不判断
    fatal: incorrect checksum in control file
  • 原文地址:https://www.cnblogs.com/jiangblog/p/16418126.html
Copyright © 2020-2023  润新知