복잡한 쿼리를 작성해보자

복잡한 쿼리를 작성해야 할 때.

1. 최종적으로 조회되는 결과 항목을 정의한다.
2. 필요한 테이블과 컬럼을 파악한다.
3. 작은 단위로 분할해서 쿼리를 작성한다.
4. 분할한 단위의 쿼리를 하나로 합쳐 최종 결과를 산출한다.
5. 결과를 검증한다.

예시로 알아보자.

연도별로 이탈리아 매출 데이터를 살펴 매출실적이 가장 많은 사원의 목록과 매출액을 구하는 쿼리를 작성해보자.

1. 출력 항목
   • 연도, 최대매출사원명, 최대매출액
2. 필요한 테이블
   • 이탈리아 찾기 : countries
   • 이탈리아 고객 찾기 : customers
   • 매출 : sales
   • 사원정보 : employees
3. 단위 분할
   ⓵ 연도, 사원별 이탈리아 매출액 구하기
   • 이탈리아 고객 찾기 : customers, countries를 country_id로 조인, country_name이 ‘Italy’인 것 찾기
   • 이탈리아 매출 찾기 : 위 결과와 sales 테이블을 cust_id로 조인
   • 최대 매출액을 구하려면 max 함수를 쓰고, 연도별로 group by 필요

입력

SELECT SUBSTR(a.sales_month, 1, 4) as years,
  a.employee_id, 
  SUM(a.amount_sold) AS amount_sold
from sales a,
  customers b,
  countries c
where a.cust_id = b.CUST_ID
  and b.country_id = c.COUNTRY_ID
  and c.country_name = 'Italy'
GROUP BY SUBSTR(a.sales_month, 1, 4), a.employee_id;

⓶ 1에서 구한 결과에서 연도별 최대, 최소 매출액 구하기

입력

SELECT years,
  MAX(amount_sold) AS max_sold
FROM (SELECT SUBSTR(a.sales_month, 1, 4) as years,
            a.employee_id,
            SUM(a.amount_sold) AS amount_sold
      FROM sales a,
          customers b,
          countries c
      WHERE a.cust_id = b.CUST_ID
        AND b.country_id = c.COUNTRY_ID
        and c.country_name = 'Italy'
      GROUP BY SUBSTR(a.sales_month, 1, 4), a.employee_id
      ) K
  GROUP BY years
  ORDER BY years;

⓷ 1의 결과와 2의 결과를 조인해서 최대매출, 최소매출액을 일으킨 사원을 찾아야 하므로, 1과 2 결과를 인라인 뷰로 만든다.

입력

SELECT emp.years,
        emp.employee_id,
        emp.amount_sold
FROM (SELECT SUBSTR(a.sales_month, 1, 4) as years,
              a. employee_id,
              SUM(a.amount_sold) AS amount_sold
      FROM sales a,
            customers b,
            countries c
      WHERE a.cust_id = b.CUST_ID
        AND b.country_id = c.COUNTRY_ID
        AND c.country_name = 'Italy'
      GROUP BY SUBSTR(a.sales_month, 1, 4), a.employee_id
      ) emp, 
      (SELECT years,
              MAX(amount_sold) AS max_sold
       FROM (SELECT SUBSTR(a.sales_month, 1, 4) as years,
                    a.employee_id,
                    SUM(a.amount_sold) AS amount_sold
             FROM sales a,
                  customers b,
                  countries c
             WHERE a.cust_id = b.CUST_ID
              AND b.country_id = c.COUNTRY_ID
              AND c.country_name = 'Italy'
             GROUP BY SUBSTR(a.sales_month, 1, 4), a.employee_id
             ) K
      GROUP BY years
      ) sale
  WHERE emp.years = sale.years
    AND emp.amount_sold = sale.max_sold
  ORDER BY years;

⓸ 마지막으로 3 결과와 사원 테이블을 조인해서 사원 이름을 가져온다.

입력

SELECT emp.years,
      emp.employee_id,
      emp2.emp_name,
      emp.amount_sold
FROM (SELECT SUBSTR(a.sales_month, 1, 4) as years,
              a. employee_id,
              SUM(a.amount_sold) AS amount_sold
      FROM sales a,
            customers b,
            countries c
      WHERE a.cust_id = b.CUST_ID
        AND b.country_id = c.COUNTRY_ID
        AND c.country_name = 'Italy'
      GROUP BY SUBSTR(a.sales_month, 1, 4), a.employee_id
      ) emp, 
      (SELECT years,
              MAX(amount_sold) AS max_sold
       FROM (SELECT SUBSTR(a.sales_month, 1, 4) as years,
                    a.employee_id,
                    SUM(a.amount_sold) AS amount_sold
             FROM sales a,
                  customers b,
                  countries c
             WHERE a.cust_id = b.CUST_ID
              AND b.country_id = c.COUNTRY_ID
              AND c.country_name = 'Italy'
             GROUP BY SUBSTR(a.sales_month, 1, 4), a.employee_id
             ) K
      GROUP BY years
      ) sale,
      employees emp2
 WHERE emp.years = sale.years
  AND emp.amount_sold = sale.max_sold
  AND emp.employee_id = emp2.employee_id
 ORDER BY years;

결과

YEARS	EMPLOYEE_ID	EMP_NAME	AMOUNT_SOLD
1998	145	John Russell	311761.02
1999	147	Alberto Errazuriz	193319.44
2000	153	Christopher Olsen	142987.82
2001	173	Sundita Kumar	426018.7

예상 외로 쿼리가 복잡해졌지만, 지금까지 배운 내용을 기반으로 ‘분할 정복’ 개념을 적용하면 충분히 작성할 수 있다.