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でクエリ性能を改善する