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은 집계 함수의 수치 열에만 적용됩니다

Ayo berlatih!

SQL Server에서 쿼리 성능 향상하기