Penyaringan dengan HAVING

Meningkatkan Kinerja Kueri di SQL Server

Dean Smith

Founder, Atamai Analytics

Urutan pemrosesan HAVING

1.  FROM

4.  WHERE

6.  HAVING

7.  SELECT

Pengelompokan dengan filter baris 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
...	...

Penyaringan baris dengan 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'

Jangan gunakan HAVING untuk menyaring baris individual atau tidak dikelompokkan

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

Agregasi per grup

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
...	...	...	...

Penyaringan grup dengan WHERE

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

Gunakan WHERE untuk menyaring baris individual dan HAVING untuk filter numerik pada baris yang dikelompokkan

Team	TotRebounds	TotDef	TotOff

Tanpa fungsi agregat

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

Terapkan filter HAVING pada kolom numerik menggunakan fungsi agregat

------------------------------------------------
-- ERROR
Kolom 'PlayerStats.ORebound' tidak valid di 
klausa HAVING karena tidak termasuk dalam 
fungsi agregat atau klausa GROUP BY.

Dengan fungsi agregat

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

Ringkasan

Jangan gunakan HAVING untuk menyaring baris individual atau yang tidak dikelompokkan
Gunakan WHERE untuk menyaring baris individual dan HAVING untuk filter numerik pada baris yang dikelompokkan
HAVING hanya dapat diterapkan pada kolom numerik dalam filter fungsi agregat

Ayo berlatih!

Meningkatkan Kinerja Kueri di SQL Server