복잡한 쿼리 효율적으로 작성하기

1. 최종적으로 조회되는 결과 항목을 정의한다.

2. 필요한 테이블과 컬럼을 파악한다.

3. 작은 단위로 분할해서 쿼리를 작성한다.

4. 분할한 단위의 쿼리를 하나로 합쳐 최종 결과를 산출한다.

5. 결과를 검증한다.

 

1) 출력항목

연도    최대매출사원명    최대매출액

 

2) 필요한 테이블

- 이탈리아 찾기 : countries

- 이탈리아 고객 찾기 : consumers

- 매출 : sales

- 사원정보 : employees

 

3) 단위 분할

a) 연도, 사원별 이탈리아 매출액 구하기

 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

 

b) a에서 구한 결과에서 연도별 최대, 최소 매출액 구하기

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

 

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

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

 

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

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