神奇的 SQL 之 HAVING → 容易被輕視的主角

青石路發表於2020-12-24
SQL

開心一刻

　　一天，樓主和隔壁小男孩一起坐電梯，中途進來一位高挑的美女，她牽著一條雪白的貴賓犬

　　小男孩看著這條雪白的貴賓犬，甚是喜歡，說道：阿姨，我能摸下這個狗狗嗎？

　　美女：叫姐姐

　　小男孩低頭看了下貴賓犬，雖說有點不樂意，但還是說道：阿姨，我能摸下這個姐姐嗎？

　　樓主想忍住，但實在是忍不住了，哈哈哈...

初識 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進階教程》

相關文章