WITH 절 - 서브쿼리 중복 사용 방지

WITH b2 AS
	( SELECT PERIOD, REGION, sum(LOAN_JAN_AMT) AS jan_amt
	    FROM KOR_LOAN_STATUS
	   GROUP BY PERIOD, REGION
	),
	c AS
	(SELECT b.period, max(b.jan_amt) max_jan_amt
	   FROM ( SELECT PERIOD, REGION, sum(LOAN_JAN_AMT) AS jan_amt
	    		FROM KOR_LOAN_STATUS
	   		  GROUP BY PERIOD, REGION
	   		) b,
	   		( SELECT max(PERIOD) max_month
	   		    FROM KOR_LOAN_STATUS
	   		   GROUP BY SUBSTR(PERIOD, 1, 4)
	   		) a 
	   WHERE b.period = a.max_month
	   GROUP BY b.period
	)
SELECT b2.*
  FROM b2, c
WHERE b2.period = c.period
  AND b2.jan_amt = c.max_jan_amt
ORDER BY 1;

 

b 와 b2 는 같은 내용이다.

이는 아래와 같이 b2 하나로 개선될 수 있다.

 

WITH b2 AS
	( SELECT PERIOD, REGION, sum(LOAN_JAN_AMT) AS jan_amt
	    FROM KOR_LOAN_STATUS
	   GROUP BY PERIOD, REGION
	),
	c AS
	(SELECT b2.period, max(b2.jan_amt) max_jan_amt
	   FROM b2,
	   		( SELECT max(PERIOD) max_month
	   		    FROM KOR_LOAN_STATUS
	   		   GROUP BY SUBSTR(PERIOD, 1, 4)
	   		) a 
	   WHERE b2.period = a.max_month
	   GROUP BY b2.period
	)
SELECT b2.*
  FROM b2, c
WHERE b2.period = c.period
  AND b2.jan_amt = c.max_jan_amt
ORDER BY 1;