使用 HAVING 篩選

改進 SQL Server 中的查詢效能

Dean Smith

Founder, Atamai Analytics

HAVING 的處理順序

1.  FROM

4.  WHERE

6.  HAVING

7.  SELECT

先用 WHERE 篩選再分組

SELECT Team, 
    SUM(TotalPoints) AS TotalSGTeamPoints
FROM PlayerStats
WHERE Position = 'SG'
GROUP BY Team

Team	TotalSGTeamPoints
ATL	2034
BOS	1606
BRK	2126
CHI	2905
CHO	2661
CLE	2489
...	...

用 HAVING 篩選列的錯誤示範

SELECT Team, 
    SUM(TotalPoints) AS TotalSGTeamPoints
FROM PlayerStats
WHERE Position = 'SG'
GROUP BY Team

SELECT Team, 
    SUM(TotalPoints) AS TotalSGTeamPoints
FROM PlayerStats
--WHERE Position = 'SG'
GROUP BY Team, Position
HAVING Position = 'SG'

不要用 HAVING 來篩選「個別」或「未分組」的列

Team	TotalSGTeamPoints
ATL	2034
BOS	1606
BRK	2126
CHI	2905
CHO	2661
CLE	2489
...	...

依群組彙總

SELECT 
    Team, 
    SUM(DRebound+ORebound) AS TotRebounds,
    SUM(DRebound) AS TotDef,
    SUM(ORebound) AS TotOff
FROM PlayerStats
GROUP BY Team;

Team	TotRebounds	TotDef	TotOff
ATL	3436	2693	743
BOS	3645	2878	767
BRK	3644	2852	792
CHI	3663	2873	790
CHO	3728	2901	827
CLE	3455	2761	694
...	...	...	...

用 WHERE 做群組前的篩選

SELECT 
    Team, 
    SUM(DRebound+ORebound) AS TotRebounds,
    SUM(DRebound) AS TotDef,
    SUM(ORebound) AS TotOff
FROM PlayerStats
WHERE ORebound >= 1000
GROUP BY Team;

用 WHERE 篩選個別列，用 HAVING 對「已分組」列做數值篩選

Team	TotRebounds	TotDef	TotOff

缺少聚合函式的情況

SELECT 
    Team, 
    SUM(DRebound+ORebound) AS TotRebounds,
    SUM(DRebound) AS TotDef,
    SUM(ORebound) AS TotOff
FROM PlayerStats
GROUP BY Team
HAVING ORebound >= 1000;

對數值欄位使用聚合函式後再套用 HAVING 篩選

------------------------------------------------
-- ERROR
Column 'PlayerStats.ORebound' is invalid in the 
HAVING clause because it is not contained in 
either an aggregate function or the GROUP BY 
clause.

搭配聚合函式的用法

SELECT 
    Team, 
    SUM(DRebound+ORebound) AS TotRebounds,
    SUM(DRebound) AS TotDef,
    SUM(ORebound) AS TotalOff
FROM PlayerStats
GROUP BY Team
    -- aggregate function SUM()
HAVING SUM(ORebound) >= 1000;

Team	TotRebounds	TotDef	TotOff
OKC	3695	2671	1024

重點整理

不要用 HAVING 來篩選個別或未分組的列
用 WHERE 篩選個別列，用 HAVING 對已分組列做數值篩選
HAVING 只能用在含聚合函式的數值欄位篩選

一起來練習吧！

改進 SQL Server 中的查詢效能