< SUM(컬럼) OVER(PARTITION BY 컬럼 ORDER BY 컬럼) >
SUM과 달리 SUM() OVER() 함수는 GROUP BY 가 필요없다. 다시말해 성능 향상을 꾀할 수 있다.
다른 OVER()를 사용하는 함수와 마찬가지로 OVER() 절이 의미하는 바는 동일하다.
PARTITION BY 컬럼으로 구분하여 합계를 구하고
ORDER BY 컬럼 순서로 합계를 구한다.
간단하지 않은 샘플)
위와 같이 매 날짜별로 생성되어있는 데이터를
DC_YN 열의 값이 동일한 경우 GROUP BY 하고 (파란색 부분의 연속된 값을 각각 하나의 ROW로...)
다를 경우 표시하여 아래와 같이 시작~종료일로 표시하는 SQL은 아래와 같다.
간단하면서 알고보면 쉽지만, 아무나 생각해낼 수는 없는 풀이방법을 제공해주신 이*철 책임님께 경의를 표합니다. :D
<좋은 SQL>
SELECT PT_NO 환자번호
, ORD_DTE 처방일
, ORD_SEQ 처방순번
,MIN(DC_YN) DC_YN
,MAX(DC_YN) DC_YN
,GROUP_NUM
, MIN(RSV_DTIM) AS 시작일
, MAX(RSV_DTIM) AS 종료일
FROM (
SELECT RN
, PT_NO
, ORD_DTE
, ORD_SEQ
, DC_YN
, SUM(CASE WHEN DC_YN = 'D' THEN 1
WHEN DC_YN = 'C' AND 앞수 = 'D' THEN 1
ELSE 0 END) OVER(ORDER BY ORD_SEQ,RN) AS GROUP_NUM
, RSV_DTIM
FROM (
SELECT ROWNUM RN
, LAG(DC_YN) OVER(ORDER BY ROWNUM) AS 앞수
, A.*
FROM 테이블 A
WHERE PT_NO = '12345678'
AND ORD_DTE = '2004-10-01'
AND ORD_SEQ = 4
) A
) A
GROUP BY PT_NO
, ORD_DTE
, ORD_SEQ, GROUP_NUM
ORDER BY PT_NO,ORD_DTE,ORD_SEQ,시작일
PLAN은 아래와 같다. (컬럼 60여개, 약 2600만건)
안좋은 SQL
WITH AA AS (
SELECT * FROM ( SELECT ROWNUM RNUM, PT_NO, ORD_DTE, ORD_SEQ, RMCD, RSV_DTIM, DC_YN
, LAG(DC_YN) OVER (PARTITION BY PT_NO, ORD_DTE, ORD_SEQ, RMCD ORDER BY RSV_DTIM) LAG_DC_YN
, LEAD(DC_YN) OVER (PARTITION BY PT_NO, ORD_DTE, ORD_SEQ, RMCD ORDER BY RSV_DTIM) LEAD_DC_YN
FROM (SELECT *
FROM 테이블
) A
ORDER BY PT_NO, ORD_DTE, ORD_SEQ, RMCD, RSV_DTIM, DC_YN)
), BB AS (
SELECT ROWNUM RNUM2, AA.* FROM AA WHERE NVL(DC_YN,'NULL') <> NVL(LAG_DC_YN,'NULL') ORDER BY RNUM
), CC AS (
SELECT ROWNUM RNUM2, AA.* FROM AA WHERE NVL(DC_YN,'NULL') <> NVL(LEAD_DC_YN,'NULL') ORDER BY RNUM
)
SELECT BB.*, CC.RSV_DTIM, CC.RNUM - BB.RNUM + 1 ROW_CNT
FROM BB INNER JOIN CC
ON BB.RNUM2 = CC.RNUM2
AND BB.DC_YN = CC.DC_YN;
아래와 같이 PLAN 이 정말 어마무시하게 나온다.
