SQL _ HAVING_2

아래 테이블은 소방대원의 상태를 나타내는 표이다. 팀 전원이 대기 상태일 때만 출동이 가능하다.

출동명령을 내릴 수 있는 팀을 찾아보자.

Teams

member (대원)	team_id (팀ID)	status (상태)
죠	1	대기
켄	1	출동중
미크	1	대기
카렌	2	출동중
키스	2	휴가
죤	3	대기
하트	3	대기
딕	3	대기
베스	4	대기
알렌	5	출동중
로버트	5	휴가
마크	5	대기

「모든 멤버의 상태가 "대기중"이다」라는 조건은 전칭양화이므로 NOT EXISTS를 사용하면 된다.

SELECT team_id, member FROM teams t1 WHERE NOT EXISTS (SELECT * FROM teams t2 WHERE t1.team_id = t2.team_id AND t2.status <> '대기') --결과-- TEAM_ID MEMBER -------- ------------ 3 딕 3 하트 3 죤 4 베스

이것은 전칭양화와 존재양화간의 동치변환을 이용한 것이다.

「모든 멤버의 상태가 대기중이다」

= 「대기중이지 않은 멤버가 한명도 존재하지 않는다」

이 쿼리는 퍼포먼스면에서 뛰어나고 구체적인 팀멤버도 표시가능하다는 장점이 있다. 하지만 이중부정을 사용하기 때문에 직감적으로 알기 어려운 쿼리이기도 하다.

HAVING구를 사용하면 다음과 같이 간단하게 표현할 수 있다.

--전칭문을 집합으로 표현 ① SELECT team_id FROM teams GROUP BY team_id HAVING count(*) = sum(CASE WHEN status = '대기' THEN 1 ELSE 0 END); ----전칭문을 집합으로 표현 ② SELECT team_id FROM teams GROUP BY team_id HAVING max(status) = '대기' AND min(status) = '대기'; ---최대값과 최소값이 같다는 것은 그 집합은 한종류의 값밖에 없다는 것을 나타내는 것임 --결과-- team_id ------- 3 4

이번에는 출동가능멤버를 일람하는 쿼리를 만들어보자

SELECT team_id, CASE WHEN max(status) = '대기' AND min(status) = '대기' THEN '출동!' ELSE '멤버부족' END AS status FROM teams GROUP BY team_id --결과-- TEAM_ID STATUS -------- --- 1 멤버부족 2 멤버부족 4 출동! 5 멤버부족 3 출동!

Materials

center (거점)	receive_date (반입일)	material (자원)
서울	2007-4-01	주석
서울	2007-4-12	아연
서울	2007-5-17	알루미늄
서울	2007-5-20	아연
부산	2007-4-20	동
부산	2007-4-22	니켈
부산	2007-4-29	납
대구	2007-3-15	티탄
대구	2007-4-01	탄소강
대구	2007-4-24	탄소강
대구	2007-5-02	마그네슘
대구	2007-5-10	티탄
인천	200-5-10	아연
인천	2007-5-28	주석

위의 테이블에서 자원이 중복되어 있는 거점을 찾는 쿼리를 만들어보자.

--자원이 중복된 거점 선택 SELECT center FROM materials GROUP BY center HAVING count(center) <> count(distinct material); --결과-- CENTER ------------ 서울 대구 --거점마다 중복여부 일람표시 SELECT center, CASE WHEN count(center) <> count(distinct material) THEN '중복있음' ELSE '중복없음' END status FROM materials GROUP BY center; --결과-- CENTER STATUS ------------ --------- 부산 중복없음 서울 중복있음 인천 중복없음 대구 중복있음 --중복이 있는 집합: EXISTS이용 SELECT center, material FROM materials m1 WHERE EXISTS (SELECT * FROM materials m2 WHERE m1.center = m2.center AND m1.material = m2.material AND m1.receive_date <> m2.receive_date); --결과-- CENTER MATERIAL ------------ ------------ 서울 아연 서울 아연 대구 티탄 대구 탄소강 대구 탄소강 대구 티탄

위테이블에서 원산국을 추가시킨 테이블을 이용해 (자원,원산국)의 2열로 봤을 때 중복되어 있는 거점을 선택하는 쿼리를 만들어보자.

Materials2

center (거점)	receive_date (반입일)	material (자원)	orgland (원산국)
서울	2007-04-01	주석	칠레
서울	2007-04-12	아연	타이
서울	2007-05-17	알루미늄	브라질
서울	2007-05-20	아연	타이
부산	2007-04-20	동	호주
부산	2007-04-22	니켈	남아프리카
부산	2007-04-29	납	인도
대구	2007-03-15	티탄	볼리비아
대구	2007-04-01	탄소강	칠레ㅔ
대구	2007-04-24	탄소강	아르헨티나
대구	2007-05-02	마그네슘	칠레
대구	2007-05-10	티탄	타이
인천	200-05-10	아연	미국
인천	2007-05-28	주석	러시아

COUNT함수가 인수로 쓸 수 있는 열은 한개밖에 없다. COUNT(material, orgland)라고는 쓸 수 없을 것이다. 어떻게 하면 될까? 한개밖에 인수로 쓸 수 없다면 강제로 인수를 한개로 만들면 될 것이다.

SELECT center FROM materials2 GROUP BY center HAVING count(material||orgland) <> count(distinct material||orgland); --결과-- CENTER ------------ 서울 --EXISTS를 사용하기

select distinct center from materials2 m1 where exists (select * from materials2 m2 where m1.center = m2.center and m1.material = m2.material and m1.orgland = m2.orgland and m1.receive_date <> m2.receive_date)

TestResults테이블을 이용해 아래 문제의 쿼리를 작성해보자.

TestResults

student_id (학생ID)	class (클래스)	sex (성별)	score (점수)
001	A	남	100
002	A	여	100
003	A	여	49
004	A	남	30
005	B	여	100
006	B	남	92
007	B	남	80
008	B	남	80
009	B	여	10
010	C	남	92
011	C	남	80
012	C	여	21
013	D	여	100
014	D	여	0
015	D	여	0

Q1.클래스의 75%이상의 학생이 80점이상인 클래스를 선택하시오

SELECT class FROM testresults GROUP BY class HAVING count(*) * 0.75 <= sum(CASE WHEN score >= 80 THEN 1 ELSE 0 END); --결과-- CLASS ------- B

Q2.50점 이상을 받은 학생 중, 남자수가 여자수보다 많은 클래스를 선택하시오

SELECT class FROM testresults GROUP BY class HAVING sum(CASE WHEN sex = '남' AND score >= 50 THEN 1 ELSE 0 END) > sum(CASE WHEN sex = '여' AND score >= 50 THEN 1 ELSE 0 END); --결과-- CLASS -------- B C

Q3. 여자의 평균점이 남자의 평균점보다 높은 클래스를 선택하시오.

SELECT class FROM testresults GROUP BY class HAVING avg(CASE WHEN sex = '남' THEN score ELSE null END) < avg(CASE WHEN sex = '여' THEN score ELSE null END) --결과-- CLASS ------- A

아래 테이블을 이용해서 수학이 80점 이상이고 국어가 50점 이상인 학생을 찾는 쿼리를 작성해보자.

TestScores

student_id (학생ID)	subject (과목)	score (점수)
100	수학	100
100	국어	80
100	과학	80
200	수학	80
200	국어	95
300	수학	40
300	국어	90
300	사회	55
400	수학	80

SELECT student_id FROM testscores WHERE subject IN('수학','국어') GROUP BY student_id HAVING SUM(CASE WHEN subject = '수학' AND score >= 80 THEN 1 WHEN subject = '국어' AND score >= 50 THEN 1 ELSE 0 END) = 2 --결과-- STUDENT_ID ---------- 100 200

HAVING구를 사용함에 있어 포인트를 한마디로 얘기하라면 "무엇으로 집합이라고 가정할 것인지에 착목하라"는 것이다. SQL에 있어서 무엇을 집합으로 가정할 것인지의 기준은 집합을 이루는 것이 현실세계에서 어떤 레벨인지는 상관없고, 단지 테이블에서 어떻게 표현되는지만이 중요하다. 어떤 실체가 때에 따라서는 요소가 되기도 하고 집합이 되기도 하는 것이다. 실체 한개당 한행만이 할당된다면 이 실체는 집합의 요소로써 취급되고 있는 것이다. 이때는 조건을 지정할 때 WHERE구를 사용하면 된다. 한편 한개당 복수행이 할당된다면 집합으로 취급되고있다는 증거이다. 그렇다면 HAVING구를 사용하면 된다.

집합의 성질을 조사하기 위한 대표적인 조건을 정리해두자. 이 조건은 HAVING구 혹은 SELECT구의 CASE식에서 사용할 수 있다.

집합의 성질을 찾기 위한 조건의 사용법 일람

	조건식	용도
1	COUNT(DISTINCT col) = COUNT(col)	col값이 유일하다
2	COUNT(*) = COUNT(col)	col에 NULL이 존재하지 않는다
3	COUNT(*) = MAX(col)	col는 결번이 없는 연번(개시값은 1)
4	COUNT(*) = MAX(col) - MIN(col) + 1	col는 결번이 없는 연번(개시값은 임의의 정수)
5	MIN(col) = MAX(col)	col가 한개만의 값을 가지던가 아니면 NULL이다
6	MIN(col) * MAX(col) > 0	모든 col_x의 기호가 동일하다
7	MIN(col) * MAX(col) < 0	최대값은 양수고 최소값은 음수이다
8	MIN(ABS(col)) = 0	col은 적어도 한개의 0를 포함한다
9	MIN(col - 정수 ) = -MAX(col - 정수)	col의 최대값과 최소값은 지정한 정수에서 값은 간격으로 떨어져있다

[출처] SQL _ HAVING_2|작성자 미우