专栏首页开发技术神奇的 SQL 之 HAVING → 容易被轻视的主角

神奇的 SQL 之 HAVING → 容易被轻视的主角

开心一刻

  一天,楼主和隔壁小男孩一起坐电梯,中途进来一位高挑的美女,她牵着一条雪白的贵宾犬

  小男孩看着这条雪白的贵宾犬,甚是喜欢,说道:阿姨,我能摸下这个狗狗吗?

  美女:叫姐姐

  小男孩低头看了下贵宾犬,虽说有点不乐意,但还是说道:阿姨,我能摸下这个姐姐吗?

  楼主想忍住,但实在是忍不住了,哈哈哈...

初识 HAVING

  关于 SQL 中的 HAVING,相信大家都不陌生,它往往与 GROUP BY 配合使用,为聚合操作指定条件

  说到指定条件,我们最先想到的往往是 WHERE 子句,但 WHERE 子句只能指定行的条件,而不能指定组的条件(这里面有个“阶”的概念,可以查阅:神奇的 SQL 之层级 → 为什么 GROUP BY 之后不能直接引用原表中的列),因此就有了 HAVING 子句,它用来指定组的条件。我们来看个具体示例就清楚了。

  我们有 学生班级表(tbl_student_class) 以及 数据如下 :

DROP TABLE IF EXISTS tbl_student_class;
CREATE TABLE tbl_student_class (
  id int(8) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  sno varchar(12) NOT NULL COMMENT '学号',
  cno varchar(5) NOT NULL COMMENT '班级号',
  cname varchar(50) NOT NULL COMMENT '班级名',
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生班级表';

-- ----------------------------
-- Records of tbl_student_class
-- ----------------------------
INSERT INTO tbl_student_class(sno, cno, cname) VALUES ('20190607001', '0607', '影视7班');
INSERT INTO tbl_student_class(sno, cno, cname) VALUES ('20190607002', '0607', '影视7班');
INSERT INTO tbl_student_class(sno, cno, cname) VALUES ('20190608003', '0608', '影视8班');
INSERT INTO tbl_student_class(sno, cno, cname) VALUES ('20190608004', '0608', '影视8班');
INSERT INTO tbl_student_class(sno, cno, cname) VALUES ('20190609005', '0609', '影视9班');
INSERT INTO tbl_student_class(sno, cno, cname) VALUES ('20190609006', '0609', '影视9班');
INSERT INTO tbl_student_class(sno, cno, cname) VALUES ('20190609007', '0609', '影视9班');

  我们要查询 学生人数为 3 的班级 ,这就需要用到 HAVING 了,相信大家都会写

SELECT cno, COUNT(*) nums FROM tbl_student_class GROUP BY cno HAVING COUNT(*) = 3;

  如果我们不使用 HAVING,会是什么样呢

  可以看到,除了数量等于 3 的班级之前,其他的班级也被查出来了

  我们可以简单总结下:WHERE 先过滤出行,然后 GROUP BY 对行进行分组,HAVING 再对组进行过滤,筛选出我们需要的组

  HAVING 子句的构成要素

    既然 HAVING 操作的对象是组,那么其使用的要素是有一定限制的,能够使用的要素有 3 种: 常数 、 聚合函数 和 聚合键 ,聚合键也就是 GROUP BY 子句中指定的列名

    示例中的 HAVING COUNT(*) = 3 , COUNT(*) 是聚合函数,3 是常数,都在 3 要素之中;如果有 3 要素之外的条件,会是怎么样呢

SELECT cno, COUNT(*) nums FROM tbl_student_class GROUP BY cno HAVING cname = '影视9班';

    执行如上 SQL 会失败,并提示:

[Err] 1054 - Unknown column 'cname' in 'having clause'

    在使用 HAVING 子句时,把 GROUP BY 聚合后的结果作为 HAVING 子句的起点,会更容易理解;示例中通过 cno 进行聚合后的结果如下:

    聚合后的这个结果并没有 cname 这个列,那么通过这个列来进行条件处理,当然就报错了啦

    细心的小伙伴应该已经发现,HAVING 子句的构成要素和包含 GROUP BY 子句时的 SELECT 子句的构成要素是一样的,都是只能包含 常数 、 聚合函数 和 聚合键

HAVING 的魅力

  HAVING 子句是 SQL 里一个非常重要的功能,是理解 SQL 面向集合这一本质的关键。下面结合具体的案例,来感受下 HAVING 的魅力

  是否存在缺失的编号

    tbl_student_class 表中记录的 id 是连续的(id 的起始值不一定是 1),我们去掉其中 3 条

DELETE FROM tbl_student_class WHERE id IN(2,5,6);
SELECT * FROM tbl_student_class;

    如何判断是否有编号缺失?

    数据量少,我们一眼就能看出来,但是如果数据量上百万行了,用眼就看不出来了吧

    不绕圈子了,我就直接写了,相信大家都能看懂(记得和自己想的对比一下)

SELECT '存在缺失的编号' AS gap
FROM tbl_student_class
HAVING COUNT(*) <> MAX(id) - MIN(id) + 1;

    上面的 SQL 语句里没有 GROUP BY 子句,此时整张表会被聚合为一组,这种情况下 HAVING 子句也是可以使用的(HAVING 不是一定要和 GROUP BY 一起使用)

    写的更严谨点,如下(没有 HAVING,不是主角,看一眼就好)

-- 无论如何都有结果返回
SELECT CASE WHEN COUNT(*) = 0 THEN '表为空'
    WHEN COUNT(*) <> MAX(id) - MIN(id) + 1 THEN '存在缺失的编号'
    ELSE '连续' END AS gap
FROM tbl_student_class;

那如何找出缺失的编号了,欢迎评论区留言

  求众数

    假设我们有一张表: tbl_student_salary ,记录着毕业生首份工作的年薪

DROP TABLE IF EXISTS tbl_student_salary;
CREATE TABLE tbl_student_salary (
  id int(8) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  name varchar(5) NOT NULL COMMENT '姓名',
  salary DECIMAL(15,2) NOT NULL COMMENT '年薪, 单位元',
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='毕业生年薪标';

insert into tbl_student_salary values (1,'李小龙', 1000000);
insert into tbl_student_salary values (2,'李四', 50000);
insert into tbl_student_salary values (3,'王五', 50000);
insert into tbl_student_salary values (4,'赵六', 50000);
insert into tbl_student_salary values (5,'张三', 70000);
insert into tbl_student_salary values (6,'张一三', 70000);
insert into tbl_student_salary values (7,'张二三', 70000);
insert into tbl_student_salary values (8,'张三三', 60000);
insert into tbl_student_salary values (9,'张三四', 40000);
insert into tbl_student_salary values (10,'张三丰', 30000);

    平均工资达到了 149000 元,乍一看好像毕业生大多都能拿到很高的工资。然而这个数字背后却有一些玄机,因为功夫大师李小龙在这一届毕业生中,由于他出众的薪资,将大家的平均薪资拉升了一大截

    简单地求平均值有一个缺点,那就是很容易受到离群值(outlier)的影响。这种时候就必须使用更能准确反映出群体趋势的指标——众数(mode)就是其中之一

    那么如何用 SQL 语句来求众数了,我们往下看

-- 使用谓词 ALL 求众数
SELECT salary, COUNT(*) AS cnt
FROM tbl_student_salary
GROUP BY salary
HAVING COUNT(*) >= ALL (
    SELECT COUNT(*)
    FROM tbl_student_salary
    GROUP BY salary);

    结果如下

    ALL 谓词用于 NULL 或空集时会出现问题,我们可以用极值函数来代替;这里要求的是元素数最多的集合,因此可以用 MAX 函数

-- 使用极值函数求众数
SELECT salary, COUNT(*) AS cnt
FROM tbl_student_salary
GROUP BY salary
HAVING COUNT(*) >= (
    SELECT MAX(cnt)
    FROM (
        SELECT COUNT(*) AS cnt
        FROM tbl_student_salary
        GROUP BY salary
        ) TMP
    ) ;

  求中位数

    当平均值不可信时,与众数一样经常被用到的另一个指标是中位数(median)。它指的是将集合中的元素按升序排列后恰好位于正中间的元素。如果集合的元素个数为偶数,则取中间两个元素的平均值作为中位数

    表 tbl_student_salary 有 10 条记录,那么 张三三, 60000 和 李四, 50000 的平均值 55000 就是中位数

    那么用 SQL,该如何求中位数呢?做法是,将集合里的元素按照大小分为上半部分和下半部分两个子集,同时让这 2 个子集共同拥有集合正中间的元素。这样,共同部分的元素的平均值就是中位数,思路如下图所示

    像这样需要根据大小关系生成子集时,就轮到非等值自连接出场了

-- 求中位数的SQL 语句:在HAVING 子句中使用非等值自连接
SELECT AVG(DISTINCT salary)
FROM (
    SELECT T1.salary
    FROM tbl_student_salary T1, tbl_student_salary T2
    GROUP BY T1.salary
    -- S1 的条件
    HAVING SUM(CASE WHEN T2.salary >= T1.salary THEN 1 ELSE 0 END) >= COUNT(*) / 2
    -- S2 的条件
    AND SUM(CASE WHEN T2.salary <= T1.salary THEN 1 ELSE 0 END) >= COUNT(*) / 2
) TMP;

    这条 SQL 语句的要点在于比较条件 >= COUNT(*)/2 里的等号,加上等号并不是为了清晰地分开子集 S1 和 S2,而是为了让这 2 个子集拥有共同部分

    如果去掉等号,将条件改成 > COUNT(*)/2 ,那么当元素个数为偶数时,S1 和 S2 就没有共同的元素了,也就无法求出中位数了;加上等号是为了写出通用性更高的 SQL

  查询不包含 NULL 的集合

    假设我们有一张学生报告提交记录表:tbl_student_submit_log

DROP TABLE IF EXISTS tbl_student_submit_log;
CREATE TABLE tbl_student_submit_log (
  id int(8) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  sno varchar(12) NOT NULL COMMENT '学号',
  dept varchar(50) NOT NULL COMMENT '学院',
  submit_date DATE COMMENT '提交日期',
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生报告提交记录表';

insert into tbl_student_submit_log values
(1,'20200607001', '理学院', '2020-12-12'),
(2,'20200607002', '理学院', '2020-12-13'),
(3,'20200608001', '文学院', null),
(4,'20200608002', '文学院', '2020-12-22'),
(5,'20200608003', '文学院', '2020-12-22'),
(6,'20200612001', '工学院', null),
(7,'20200617001', '经济学院', '2020-12-23');

    学生提交报告后, submit_date 列会被写入日期,而提交之前是 NULL

    现在我们需要从这张表里找出哪些学院的学生全部都提交了报告,这个 SQL 该怎么写?

    如果只是用 WHERE submit_date IS NOT NULL 条件进行查询,那文学院也会被包含进来,结果就不正确了

    正确的做法应该先以 dept 进行分组(GROUP BY),然后对组进行条件的过滤,SQL 如下

SELECT dept
FROM tbl_student_submit_log
GROUP BY dept
HAVING COUNT(*) = COUNT(submit_date);

    这里其实用到了 COUNT 函数,COUNT(*) 可以用于 NULL ,而 COUNT(列名) 与其他聚合函数一样,要先排除掉 NULL 的行再进行统计

    当然,使用 CASE 表达式也可以实现同样的功能,而且更加通用

SELECT dept
FROM tbl_student_submit_log
GROUP BY dept
HAVING COUNT(*) = SUM(
    CASE WHEN submit_date IS NOT NULL THEN 1
        ELSE 0 END
    );

  其他

    不仅仅只是如上的那些场景适用于 HAVING,还有很多其他的场景也是需要用到 HAVING 的,有兴趣的可以去翻阅《SQL进阶教程》

聚合键条件的归属

  我们来看个有趣的东西,还是用表:tbl_student_class

  我们发现,聚合键所对应的条件既可以写在 HAVING 子句当中,也可以写在 WHERE 子句当中

  虽然条件分别写在 HAVING 子句和 WHERE 子句当中,但是条件的内容,以及返回的结果都完全相同,因此,很多小伙伴就会觉得两种书写方式都没问题

  单从结果来看,确实没问题,但其中有一种属于偏离了 SQL 规范的非正规用法,推荐做法是: 聚合键所对应的条件应该书写在 WHERE 子句中 ,理由有二

语义更清晰

    WHERE 子句和 HAVING 子句的作用是不同的;前面已经说过,HAVING 子句是用来指定“组”的条件的,而“行”所对应的条件应该写在 WHERE 子句中,这样一来,写出来的 SQL 语句不但可以分清两者各自的功能,而且理解起来也更容易

执行速度更快

    使用 COUNT 等函数对表中数据进行聚合操作时,DBMS 内部进行排序处理,而排序处理会大大增加机器的负担,从而降低处理速度;因此,尽可能减少排序的行数,可以提高处理速度

    通过 WHERE 子句指定条件时,由于排序之前就对数据进行了过滤,那么就减少了聚合操作时的需要排序的记录数量;而 HAVING 子句是在排序之后才对数据进行分组的,与在 WHERE 子句中指定条件比起来,需要排序的数量就会多得多

    另外,索引是 WHERE 根据速度优势的另一个有利支持,在 WHERE 子句指定条件所对应的列上创建索引,可以大大提高 WHERE 子句的处理速度

总结

  1、集合论

    集合论是 SQL 语言的根基,只有从集合的角度来思考,才能明白 SQL 的强大威力

    学习 HAVING 子句的用法是帮助我们顺利地忘掉面向过程语言的思考方式并理解 SQL 面向集合特性的最为有效的方法

  2、HAVING 子句的要素

    3 个要素:常亮、聚合函数 和 聚合键

    HAVING 大多数情况下和结合 GROUP BY 来使用,但不是一定要结合 GROUP BY 来使用

  3、SQL 的执行顺序

    WHERE 子句是指定行所对应的条件,而 HAVING 子句是指定组所对应的条件

参考

  《SQL基础教程》

  《SQL进阶教程》

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 神奇的 SQL 之性能优化 → 让 SQL 飞起来

    在像 Web 服务这样需要快速响应的应用场景中,SQL 的性能直接决定了系统是否可以使用;特别在一些中小型应用中,SQL 性能更是决定服务能否快速响应的唯一标准

    青石路
  • MyBatis官方文档-SQL 语句构建器类

    Java程序员面对的最痛苦的事情之一就是在Java代码中嵌入SQL语句。这么来做通常是由于SQL语句需要动态来生成-否则可以将它们放到外部文件或者存储过程中。正...

    Java架构师必看
  • SQL 进阶技巧(上)

    由于工作需要,最近做了很多 BI 取数的工作,需要用到一些比较高级的 SQL 技巧,总结了一下工作中用到的一些比较骚的进阶技巧,特此记录一下,以方便自己查阅,主...

    kunge
  • 《MySQL必知必会》读书笔记

    第一次完整的技术书籍的读书笔记,这本书200多页,看起来轻松又简单,当然因为内容本身非常基础的缘故,这本书我也只是翻了一遍,等接触到具体内容的时候可以拿起来再...

    阿东
  • 第4篇:SQL

    前言 确实,关于SQL的学习资料,各类文档在网上到处都是。但它们绝大多数的出发点都局限在旧有关系数据库里,内容近乎千篇一律。而在当今大数据的浪潮下,SQL早就被...

    Spark学习技巧
  • 神奇的 SQL 之层级 → 为什么 GROUP BY 之后不能直接引用原表中的列

      标准 SQL 规定,在对表进行聚合查询的时候,只能在 SELECT 子句中写下面 3 种内容:通过 GROUP BY 子句指定的聚合键、聚合函数(SUM 、...

    青石路
  • 零基础 SQL 数据库小白,从入门到精通的学习路线与书单

    我观察了 865 个 SQL 入门者,发现大家在学习 SQL 的时候,最大的问题不是 SQL 语法,而是对 SQL 原理的不熟悉。

    Lenis
  • 干货 | 如何为您的应用程序有效地选择正确的数据库

    为您的应用程序找到合适的数据库解决方案并不容易。在全球最大的在线视频网站之一爱奇艺,我们在多个领域的数据库选择方面经验丰富:在线事务处理(OLTP),在线分析处...

    CloudBest
  • 你真的会玩SQL吗?之逻辑查询处理阶段

    你真的会玩SQL吗?系列目录 你真的会玩SQL吗?之逻辑查询处理阶段 你真的会玩SQL吗?和平大使 内连接、外连接 你真的会玩SQL吗?三范式、数据完整性 你真...

    欢醉
  • 数据库优化:SQL高性能优化指南,助你成就大神之路!

    如果 IN 的参数是(1,2,3)这样的值列表时,没啥问题,但如果参数是子查询时,就需要注意了。比如,现在有如下两个表:

    IT技术分享社区
  • 把Safari当chrome用(插件篇)

    主页:http://markdown-here.com 点评:在网页的编辑器中用markdown的语法输入内容,然后右击选择Markdown Toggle或者...

    尾尾部落
  • .NET面试题解析(11)-SQL语言基础及数据库基本原理

    本文内容涉及到基本SQL语法,数据的基本存储原理,数据库一些概念、数据优化等。抱砖引玉,权当一个综合复习!

    莫问今朝
  • 转行、入行必看!都2021年了,数据分析行业还值得进吗?

    私以为,数据分析行业是可以长期发展下去的,但是对于数据分析师的专业技能的要求会越来越严格。

    大数据分析不是事儿
  • Leetcode-sql-two

    本文主要是介绍LeetCode中关于SQL的练习题,从易到难,循序渐进。文中会介绍题目和尽可能多的解答方案

    皮大大
  • 复盘2020年:数字经济时代下的营销案例集锦

    数字营销是一种以及时、相关、定制化和节省成本的沟通方式连接消费者的高层次营销手段,对于开拓市场和挖掘消费者具有重要意义。

    盈鱼MA
  • 数据库基础与SQL基础知识整理

    1. DBMS(DataBaseManagement System,数据库管理系统)和数据库数据库Schema有两种含义,一种是概念上的Schema,指的是一组...

    洋仔聊编程
  • MySQL问题集锦

    (1)SELECT子句是必选的,其它子句如WHERE子句、GROUP BY子句等是可选的。

    Dabelv
  • SQL基础查询方法

    查询是对存储在 SQL Server 中的数据的一种请求。可以使用下列几种形式发出查询:

    fireWang
  • 记一次安全培训中对Yii框架数据库操作层若干接口安全性分析的总结

    给开发的同学们进行不定期的安全培训是安全建设中不可缺少的一环,也是非常重要的一环。以我的经验来看,安全培训或者说安全科普不能教条化,不能书面化,必须得动手实践,...

    FB客服

扫码关注云+社区

领取腾讯云代金券