더북(TheBook)

Knowhow | 복잡한 쿼리를 작성해야 할 때, 어떻게 해야 할까?

정답은 없다. 오히려 정답이 있는 것이 이상하다. 조회 대상 테이블과 컬럼, 원하는 결과가 때에 따라 모두 다르기 때문이다. 하지만 마음을 가다듬고 차근차근 작성한다면 불가능한 일은 없다. 예로부터 전해오는 복잡한 문제를 풀 때 사용하는 방법이 있는데, 바로 “분할해서 정복하라(Divide & Conquer)” 이다. 복잡한 쿼리를 작성할 때에도 작은 단위로 분할한 뒤, 다시 합치면 의외로 간단히 해결할 수 있다. 이런 맥락에서 필자 나름대로 정리한 쿼리 작성 절차는 다음과 같다.

(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;

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

입력

    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;

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

입력

    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

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

입력

    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

    4개의 행이 선택됨

예상 외로 쿼리가 복잡해졌지만, 지금까지 배운 내용을 기반으로 ‘분할 정복’ 개념을 적용하면 충분히 작성할 수 있다. 독자 여러분도 개발 현장에서 이런 방법을 사용해 보길 바란다. 그리고 다음 장에서 배우는 고급 SQL을 학습하면 훨씬 적은 코드로 작성할 수 있을 것이다.

신간 소식 구독하기
뉴스레터에 가입하시고 이메일로 신간 소식을 받아 보세요.