하위 테이블에서 걸러낸 결과로 전체 컬럼 가져오기(group by, having count)

지난번에 포스팅 했던

[DB(MS, PG, MY..etc)] - MS-SQL 튜닝에 대한 짧은 생각

과 관련해서 튜닝이라고하기 까지는 좀 뭐하지만 간단한 팁(?)정도로 하나 소개할까 한다.

-----------------------------------------------------------------

프로그램 개발을 하거나 데이터 추출을 할 때

많이 고민하게 되는 것 중에 하나가 귀차니즘이다.(고민하느냐... 노가다를 하느냐...ㅎ)

무슨뜻인가 하면

예를 들어 어떤 결과물을 내려고 하는데

그 과정에서 첫 번째 쿼리를 실행한 결과를 바탕으로

두 번째 쿼리를 실행하거나 또는 그 이상의 쿼리를 사용해야 하는 경우이다.

(아무튼... 요약하자면 어떤 결과를 내기위해서 쿼리를 여러 번 실행해야 하는데 그게 귀찮다는 말이다.)

글로 설명하기가 좀 애매하니...

쿼리로 설명하자면

지점정보 테이블(branch)과 고객정보 테이블(cust_info)이 있다고 했을 때

원하는 결과물은 고객정보 테이블에서 지점번호로 group by했을 때 둘 이상의 결과가 나오는 고객들을 대상으로

해당 지점의 정보를 보여주고 싶은 것이라고 하자. (즉, 두명 이상 고객이 속해있는 지점의 정보를 추출)

1. 먼저 고객정보 테이블에서 지점번호로 1차 결과

select 지점번호, count(*)

from cust_info

group by 지점번호

having count(*) > 1

branch_code 

----------- -----------

101         2

102         2

(2개 행이 영향을 받음)

2. 위 쿼리의 결과를 지점정보 테이블의 where절에 맞춰 최종 결과

select *

from branch

where branch_code in (101, 102)

branch_code branch_name          branch_pnumber branch_address_1                                   branch_address_2

----------- -------------------- -------------- -------------------------------------------------- --------------------------------------------------

101         강남                   11111          강남구 역삼동                                            테헤란로

102         명동                   22222          중구 명동                                              아무아무길

(2개 행이 영향을 받음)

대충 이런식일텐데... 행이 얼마 안될때야 이렇게 하겠지만 행이 많아지면 효율성이 너무 떨어지는 방식이다.

그래서 하위쿼리라는 것이 있는데

별로 어려울건 없고 기본적으로 join과 비슷하다고 보면 된다.

위에서 실행하는 두 쿼리를 한 번 실행해서 동일한 결과를 낼 수가 있다.

select a.*-- 지점정보를 모두 가져온다.

from branch a -- 동일한 이름의 컬럼이 있기 때문에 가급적 테이블 별칭을 사용하는 습관을 들이는 게 좋다.

where a.branch_code in

(

select b.branch_code, count(*)

from cust_info b

group by b.branch_code

having count(*) > 1

)

order by a.branch_code

branch_code branch_name          branch_pnumber branch_address_1                                   branch_address_2

----------- -------------------- -------------- -------------------------------------------------- --------------------------------------------------

101         강남                   11111          강남구 역삼동                                            테헤란로

102         명동                   22222          중구 명동                                              아무아무길

(2개 행이 영향을 받음)

이런식으로 하면 두 번의 쿼리 실행을 한 번으로 줄일 수가 있으며

첫 번째 쿼리의 결과를 두 번째 쿼리의 where절에 입력해야 하는 수고를 덜 수 있다.

이건 성능의 문제를 떠나서 DB관리자 혹은 개발자의 삶의 질이 달라질 수 있는 간단하지만 아주 중요한 팁이다. ㅎㅎ

그럼 만약에 위에서 한발 더 나가서

위 지점에 속해있는 고객의 정보까지 모두 보고싶다면?

select a.*, c.*

from branch a 

left outer join cust_info c

on a.branch_code = c.branch_code

where a.branch_code in

(

select b.branch_code

from cust_info b

group by b.branch_code

having count(*) > 1

)

order by a.branch_code

branch_code branch_name          branch_pnumber branch_address_1                                   branch_address_2                                   branch_code cust_no     contract_num1    cust_name                      cust_pnumber cust_address_1                                     cust_address_2

----------- -------------------- -------------- -------------------------------------------------- -------------------------------------------------- ----------- ----------- ---------------- ------------------------------ ------------ -------------------------------------------------- --------------------------------------------------

101         강남                   11111          강남구 역삼동                                            테헤란로                                               101         1           1011010000000001 홍길동                            11111        강남                                                 헬로

101         강남                   11111          강남구 역삼동                                            테헤란로                                               101         2           1011010000000002 임꺽정                            11111        강남                                                 헬로2

102         명동                   22222          중구 명동                                              아무아무길                                              102         1           1021020000000001 명동주민                           22222        명동                                                 백병

102         명동                   22222          중구 명동                                              아무아무길                                              102         2           1021020000000002 이아무개                           22222        명동                                                 명동성당

(4개 행이 영향을 받음)

컬럼이 길어져서 좀 보기가 안좋긴 하지만...

이런식으로 간단하게 고객정보 테이블을 한번 더 join해서 정보를 가져오면 된다.

간단한 내용이긴 하지만 아무 실무에서는 많이 써먹을 수 있을 것이라고 생각한다.