SQL _ HAVING

1.데이터의 빠진 부분 찾기

SeqTbl

seq	name
1	마이크
2	제인
3	데이브
5	조안
6	메리
8	안

위와 같은 일련번호를 가진 테이블이 있다고 하자. 그런데 일련번호가 있다고 해도, 번호가 연속적이지 못하다. 4와 7이 빠져있다. 이 테이블에서 데이터가 빠져있는지를 확인해 보는 SQL을 만들어보자. 만약 이 테이블이 파일이고 절차적언어를 이용한다면 다음과 같은 순서로 해결할 것이다.

1.일련번호를 정렬시킨다.

2.루프해서 한행씩 다음행과 번호를 비교한다.

이런 간단한 순서 안에서도 절차적언어와 파일 시스템의 특징을 알 수 있다. 파일의 레코드는 순서를 가지고 있고, 언어는 이를 정렬시킨다는 것이다. 하지만 테이블의 행은 순서를 가지고 있지 않고 SQL도 정렬 연산자를 가지고 있지 않다는 것이다.(ORDER BY는 SQL의 연산자가 아니다)대신 SQL은 복수행을 하나의 집합으로 취급한다. 테이블 전체를 하나의 집합으로 본다면 다음과 같은 SQL문을 만들 수 있을 것이다.

SELECT'결함있음'ASgap FROMSeqTbl HAVINGCOUNT(*) <> MAX(seq); gap ------------- '결함있음'

COUNT(*)의 행수와 번호의 최대값이 같다면 처음부터 끝까지 빠짐없이 카운트되었다는 의미이다.

그런데 이 SQL문에는 GROUP BY구가 없다. 이런 경우, 테이블 전체가 한행으로 집약되어진다. 이럴 때도 HAVING구는 문제없이 사용가능하다. 현재의 표준 SQL에서는 HAVING구를 단독으로 쓸 수 있다. 단, SELECT구에 원테이블의 열을 참조할 수 없기 때문에 위의 문제처럼 정수를 지정하든지, 「SELECT COUNT(*)」처럼 집약함수를 쓸 필요가 있다.

2. HAVING구로 서브쿼리:최빈치를 구하기

아래와 같은 연봉을 나타내는 졸업생 테이블이 있다. 이 학교의 평균연봉을 계산하면 55000달러이지만 이는 샘슨의 연봉이 다른 졸업생에 비해 10배나 높기 때문이다. 단순평균은 이런 값(샘슨의 연봉같은)에 영향을 많이 받는 단점이 있는데 이런 경우, 집단의 경향을 가장 정확하게 나타내는 지표 중에 하나인 최빈치를 이용해서 문제를 해결해보자.

name	income
샘슨	400000
마이클	30000
화이트	20000
아놀드	20000
스미스	20000
로렌스	15000
허드슨	15000
켄트	10000
베카	10000
스코트	10000

수입이 같은 졸업생을 하나로 하는 집단을 만들어 그 집단들로부터 요소의 수가 가장 많은 집함을 찾으면 된다. 아래와 같은 SQL문을 만들 수 있을 것이다.

SELECT income, COUNT(income) cnt FROM graduates GROUP BY income HAVING COUNT(*) >= ALL(SELECT COUNT(*) FROM graduates GROUP BY by income) INCOME CNT --------- ---------- 10000 3 20000 3

ALL 대신 Null과 공집합일 경우에 신경을 쓴다면 MAX,MIN도 사용가능하다. 지금은 "가장 많은" 경우이므로 MAX함수를 사용한다.

SELECT income FROM Graduates GROUP BY income HAVING COUNT(*) >= ( SELECTMAX(cnt) FROM ( SELECT COUNT(*) AS cnt FROM Graduates GROUP BY income) TMP) ;

3.Null을 포함하지 않는 집합을 찾기

COUNT함수의 사용법에는 COUNT(*)과 COUNT(열명)의 2가지 방법이 있다. 이 둘의 차이가 2가지 있다. 첫번째는 퍼포먼스의 차이이고 두번째는 COUNT(*)는 Null을 세지만 COUNT(열명)은 Null을 제외한다는 점이다.

Students

student_id	dpt	sbmt_date
100	이학부	2005-10-10
101	이학부	2005-09-22
102	문학부
103	문학부	2005-09-10
200	문학부	2005-09-22
201	공학부
202	경제학부	2005-09-25

학생이 레포트를 제출하면, 제출일에 날짜를 기입한다. 미제출일 때는 Null이다. 이 테이블에서 소속된 전학생이 제출완료된 학부를 찾아보자.

--COUNT함수 이용-- SELECT dpt FROM Students GROUP BY dpt HAVING COUNT(*) = COUNT(sbmt_date); --CASE식 이용-- SELECT dpt FROM Students GROUP BY dpt HAVING COUNT(*) = SUM(CASE WHEN sbmt_date IS NOT NULL THEN 1 ELSE 0 END);

이번에는 "전원이 9월중에 제출 완료된 학부"를 선택하는 SQL문을 만들어보자.

SELECT dpt FROM students GROUP BY dpt HAVING COUNT(*) = SUM(CASE WHEN sbmt_date is not null AND TO_DATE(sbmt_date) BETWEEN '20050901' AND '20050930' THEN 1 ELSE 0 END);

4. 모든조건을 만족하는 가게 찾기

Items ShopItems

item		shop	item
맥주		대구	맥주
종이기저귀		대구	종이기저귀
자전거		대구	자전거
		대구	커텐
		서울	맥주
		서울	종이기저귀
		서울	자전거
		부산	테레비
		부산	종이기저귀
		부산	자전거



「item」테이블의 모든 상품을 갖추고 있는 가게를 선택해보자.

SELECT DISTINCTshop FROM ShopItems WHEREitem IN(SELECT item FROM Items);

shop -------- 대구 서울 부산

「ShopItems」테이블처럼 한개의 실체(여기서는 가게)에 대한 정보가 복수행에 분산되어져 있는 경우에는 WHERE구에서 단순히 OR 나 IN으로 조건을 지정해도 정답이 얻기가 힘들다. WHERE구에 지정된 조건은 어디까지나 1행에 대해서 적용되기 때문이다.

여기서 IN의 조건은 결국 「Items」테이블의 상품중 어느 한가지라도 있으면 결과에 포함되어져 버리게 된다. 그렇다면 복수행에 걸쳐 있는 조건, 즉 집합에 대한 조건.을 설정하려면 어떻게 해야 될까? 바로 HAVING구를 이용하면 된다.

SELECT SI.shop FROM ShopItems SI, Items I WHERE SI.item = I.item GROUP BY SI.shop HAVING COUNT(SI.item) = (SELECT COUNT(item) FROM Items);

shop -------- 대구 서울

기대하던 결과가 나왔다.

그런데 여기서 주의할 점이 있다!

SELECT SI.shop FROM ShopItems SI, Items I WHERE SI.item = I.item GROUP BY SI.shop HAVING COUNT(SI.item) = COUNT(I.item);

shop -------- 대구 서울 부산

보다시피 이 조건으로는 결과로 모든 가게가 선택되어졌다. 이것은 결합의 영향을 받아서 COUNT(I.item)의 값이 원래의 「Items」테이블의 행수가 아니게 되어버렸기 때문이다.그 결과를 보자.

SELECT SI.shop, COUNT(SI.item), COUNT(I.item) FROM ShopItems SI, Items I WHERE SI.item = I.item GROUP BY SI.shop;

SHOP COUNT(SI.ITEM) COUNT(I.ITEM) ---------------- -------------- ------------- 대구 3 3 부산 2 2 서울 3 3

자, 이번에는 상품마스터에 없는 커텐을 갖고 있는 대구점을 제외한 서울점만 선택하는 SQL문을 만들어보자.

SELECT SI.shop FROM ShopItems SI, Items II WHERE SI.item=I.item(+) GROUP BY SI.shop HAVING COUNT(SI.item) = (SELECT COUNT(item) FROM Items) /* 조건1 */ AND COUNT(I.item) = (SELECT COUNT(item) FROM Items); /* 조건2 */

shop -------- 서울

「ShopItems」테이블을 마스터로 해서 외부결합을 하면 「item」테이블에 존재하지 않는 커텐과 테레비는 NULL로써 I.item열에 나타난다.

조건1에 의해서 COUNT(SI.item)=4인 대구점이 제외 되고, COUNT(I.item)=2인 부산점이 제외된다.

shop	SI.item	I.item
대구	맥주	맥주
대구	종이기저귀	종이기저귀
대구	자전거	자전거
대구	커텐	-NULL
서울	맥주	맥주
서울	종이기저귀	종이기저귀
서울	자전거	자전거
부산	테레비	-NULL
부산	종이기저귀	종이기저귀
부산	자전거	자전거

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