[데이터 모델과 SQL] 14. 관심 그룹(HAVING) - 2/2

 

● HAVING 구문을 사용할 때 주의할 점

검색식에서 HAVING 구문은 FROM, WHERE 구문의 처리 결과 셋을 입력으로 HAVING 구문에 지정된 조건을 적용하여 다시 관심 그룹을 구성한다. 따라서 FROM, WHERE 구문의 처리 결과가 검색 의도와 맞지 않는 것이라면 HAVING 구문 역시 제대로 된 관심 그룹을 만들어내지 못한다.

 

예) 세 명 미만의 정교수(Professor)가 근무하는 분야(category)를 알고 싶다.
위 내용을 출력하는 검색식을 먼저 서브쿼리를 이용해서 아래와 같이 구성할 수 있다.

교직원의 직함(title), 그리고 분야(category)를 알기 위해 faculty(교수 정보), categories(분야 정보), faculty_categories(교수별 분야 정보, link 테이블) 테이블들을 살펴보아야 한다.

① 각 분야의 이름(CategoryName)을 검색하는 메인 쿼리를 구성한다.

SELECT categories.CategoryDescription FROM cateogories

② 각 분야에 속한 정교수 수를 세는 서브쿼리를 구성한다.

SELECT COUNT(facultycategories.StaffID) 
FROM facultycategories INNER JOIN faculty ON facultycategories.StaffID=faculty.StaffID 
WHERE faculty.Title='Professor'

③ ①에서 ②의 값이 3 미만인 결과 만을 출력한다.

SELECT categories.CategoryDescription, 
(SELECT COUNT(facultycategories.StaffID) 
FROM facultycategories INNER JOIN faculty ON facultycategories.StaffID=faculty.StaffID 
WHERE faculty.Title='Professor' AND facultycategories.CategoryID=categories.CategoryID) AS numOfProfessors 
FROM categories 
WHERE (SELECT COUNT(facultycategories.StaffID) 
FROM facultycategories INNER JOIN faculty ON facultycategories.StaffID=faculty.StaffID 
WHERE faculty.Title='Professor' 
AND facultycategories.CategoryID=categories.CategoryID) < 3;

 

위 식을 HAVING 구문을 이용해서 다시 구성해 보면 아래와 같다.

① 분야별 정교수 수를 출력한다.

SELECT categories.CategoryDescription, 
(SELECT COUNT(fc2.StaffID) FROM facultycategories AS fc2 INNER JOIN faculty AS f2 ON fc2.StaffID=f2.StaffID 
WHERE f2.Title='Professor' AND fc2.CategoryID=facultycategories.CategoryID) AS numOfProfessors 
FROM (categories LEFT OUTER JOIN facultycategories ON categories.CategoryID=facultycategories.CategoryID) 
LEFT OUTER JOIN faculty ON facultycategories.StaffID=faculty.StaffID 
GROUP BY Categories.CategoryDescription

② ①의 결과를 대상으로(즉, 가상테이블을 대상으로) 정교수 수가 3명 미만인 분야를 출력한다.

SELECT categories.CategoryDescription, 
(SELECT COUNT(fc2.StaffID) 
FROM facultycategories AS fc2 INNER JOIN faculty AS f2 ON fc2.StaffID=f2.StaffID 
WHERE f2.Title='Professor' AND fc2.CategoryID=facultycategories.CategoryID) AS numOfProfessors 
FROM (categories LEFT OUTER JOIN facultycategories ON categories.CategoryID=facultycategories.CategoryID) 
LEFT OUTER JOIN faculty ON facultycategories.StaffID=faculty.StaffID 
GROUP BY Categories.CategoryDescription 
HAVING numOfProfessors < 3;

서브쿼리를 이용한 예와 HAVING 구문을 이용한 예의 검색식이 서로 다름을 알 수 있다. 서브쿼리를 이용하는 검색식의 경우 메인 쿼리에서 분야를 검색하고 서브쿼리에서 각 분야에서 특정 조건이 만족하는지 확인한다.

HAVING 구문을 이용한 검색식의 경우 참고 대상으로 이루어진 가상 테이블을 먼저 구하고 이 가상 테이블을 관심 그룹인 분야(categories.CategoryDescription)별로 구분한 후 각 분야 안에서 HAVING 조건을 만족하는 결과를 얻게 된다.

 

예를 하나 더 살펴보자.

예) 날짜(OrderDate) 별 주문 금액이 1,000달러가 넘는 고객들의 전체 이름(full name), 주문 금액과 주문 날짜를 알고 싶다.

위 검색식을 GROUP BY 구문과 HAVING 연산을 이용해서 아래와 같이 구성할 수 있다.

SELECT order_details.OrderNumber, CONCAT(customers.CustFirstName, ' ', customers.CustLastName) AS CustFullName, SUM(order_details.QuotedPrice*order_details.QuantityOrdered) AS OrderedPrice 
FROM (order_details LEFT OUTER JOIN orders ON order_details.OrderNumber=orders.OrderNumber) 
LEFT OUTER JOIN customers ON orders.CustomerID=customers.CustomerID 
GROUP BY orders.OrderDate, orders.CustomerID 
HAVING OrderedPrice>=1000;

 

SQL 요청식(검색식)의 처리 순서가 FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY 순서가 되지만 DBMS의 쿼리 분석기(옵티마이저)는 최적화를 위해 쿼리를 앞 뒤로 여러 번 읽기 때문에 SELECT 절에서 지정한 Alias인 OrderedPrice를 HAVING 절의 조건에서 사용할 수 있다.

만약 사용하는 DBMS에서 이와 같은 동작을 허용하지 않는다면 Alias로 재지정된 이름을 사용하지 말고 풀어서 SUM(order_details.QuantityOrdered*order_details.QuotedPrice)>=1000와 같이 그대로 써 주는 것도 좋다.

 

■