[오라클] 간단한 쿼리인데 3개의 Row에 대해 계산식 적용하여 한행 추가

[MS-SQL] 로 작업했던 내용인데...

동일한 결과에 대한 오라클 쿼리에 대해 기록해본다.

이 쿼리는 원래 오라클이 먼저 생성된것임.


FKT  머 MS-SQL 쪽을 보면 된다.   


아래는 옆으로 늘려서 계산하게 되는 쿼리임. 


PS: 여기서 좀 골때렸던게...

 3, 5 년 차 쿼리를 만들었던게 있었는데... 
 WHERE 조건에 YYMM 컬럼에 대한 것이었다.

YYMM 은 YYYY-MM 데이타 포멧으로 년월이 저장된 컬럼인데 
아래는 교체했던 쿼리이고.

 YYMM 
BETWEEN 
       TO_CHAR(TO_DATE( P_ANAL_YEAR ||'0101' , 'YYYYMMDD' ) - (p_yearRange-1) * 365, 'YYYY-MM')
AND
       TO_CHAR( TO_DATE( P_ANAL_YEAR||'1231' , 'YYYYMMDD' ) , 'YYYY-MM')

잘못된 쿼리

YYMM
BETWEEN
       TO_CHAR(TO_DATE( :ANAL_YEAR , 'YYYY' ) - 3*365, 'YYYY')||'01'
AND
       :ANAL_YEAR||'12'


잘못되었다기보다... 어찌되었든 결과가 나왔었는데... 몇가지 찾아보니 참 거시기 하더라..

TO_DATE( '2010' , 'YYYY') 하니까  2010-01-01 이 아니고 10월 몇일로 뜨길래.. 당황스러웠다..

거기에 3년 을 빼고 년도에 01을 붙였으나 위에 언급했듯 기본 포멧이 '-' 이 들어간 데이타였는데...

데이타가 1(올해), 3년전, 5년전 데이타가 정상 쿼리 된걸 보면...

헛다리 짚었던 BETWEEN 때문이 아니었을까? 

이문제는 MS-SQL로 테스트를 해보아야 겠다...

아무튼 바뀐 쿼리 조건으로 테스트 해보니 쿼리를 2개를 만들려했던게 1개로 처리가 되어버리더라...

물론 하위 쿼리 <-- 때문에 시간이 2SEC 미만으로 나오긴 하는데 ... 느린건 뭐.. 어쩔수 없나보다

히스토리에서 데이타를 가져다가 테이블 저장없이 누적 계산과 더블어 데이타를 뽑아서 계산 수식을 적용해야 되다보니... ( 한방 쿼리로 아직 안되는건 머.....  )


-- 쿼리 소스!!

 SELECT

'C',
ROUND( SQRT( (2*SF1*ST1)/SK1  ), ROUNDCNT),
ROUND( SQRT( (2*SF2*ST2)/SK2  ), ROUNDCNT),
ROUND( SQRT( (2*SF3*ST3)/SK3  ), ROUNDCNT)

        FROM
        (
                SELECT  SUM( DECODE( HEADER , 'F' , AVG_DISTINCTION , 0 )) AS SF1,
                        SUM( DECODE( HEADER , 'K' , AVG_DISTINCTION , 0 )) AS SK1,
                        SUM( DECODE( HEADER , 'T' , AVG_DISTINCTION , 0 )) AS ST1,
                       
                        SUM( DECODE( HEADER , 'F' , INI_DISTINCTION , 0 )) AS SF2,
                        SUM( DECODE( HEADER , 'K' , INI_DISTINCTION , 0 )) AS SK2,
                        SUM( DECODE( HEADER , 'T' , INI_DISTINCTION , 0 )) AS ST2,
                       
                        SUM( DECODE( HEADER , 'F' , MAX_DISTINCTION , 0 )) AS SF3,
                        SUM( DECODE( HEADER , 'K' , MAX_DISTINCTION , 0 )) AS SK3,
                        SUM( DECODE( HEADER , 'T' , MAX_DISTINCTION , 0 )) AS ST3
                FROM
                (
                            SELECT  'CC' AS KEY,  'F' HEADER , AVG_DISTINCTION, INI_DISTINCTION , MAX_DISTINCTION
                            FROM
                            (
                            -- 중복 --
                                SELECT
AVG( round( OUTPUT_SUM - INPUT_SUM  ,roundCnt) ) AS AVG_DISTINCTION,
SUM( DECODE( SUBSTR( YYMM , 6 ), '01',OUTPUT_SUM - INPUT_SUM, 0 )   ) AS INI_DISTINCTION,
MAX( round(  OUTPUT_SUM - INPUT_SUM ,roundCnt) ) AS MAX_DISTINCTION
                                      
                                FROM
                                (
                                    SELECT YYMM, ( SELECT SUM( INPUT_SUM_AMT ) FROM PLAN_RESULTS
                                                WHERE GIGUM_CODE = P_GIGUM_CODE
                                                AND   ANAL_YEAR = P_ANAL_YEAR AND   YYMM LIKE P_ANAL_YEAR || + '%'
                                                AND   YYMM <= A.YYMM ) INPUT_SUM,  -- 수입 누계 누적

                                                ( SELECT SUM( OUTPUT_SUM_AMT ) FROM PLAN_RESULTS
                                                WHERE GIGUM_CODE = P_GIGUM_CODE
                                                AND   ANAL_YEAR = P_ANAL_YEAR AND   YYMM LIKE P_ANAL_YEAR || + '%'
                                                AND   YYMM <= A.YYMM ) OUTPUT_SUM  -- 지출 누계 누적

                                    FROM PLAN_RESULTS A
                                    WHERE GIGUM_CODE = P_GIGUM_CODE
                                    AND   ANAL_YEAR = P_ANAL_YEAR
                                    AND   YYMM LIKE P_ANAL_YEAR || + '%'
                                   
                               
                                )
                            -- 중복 --
                            )year0
                            union
                            SELECT  'CC' AS KEY, 'K' , ROUND( P_OP/12/100, ROUNDCNT ) , ROUND( P_OP/12/100, ROUNDCNT ) ,ROUND( P_OP/12/100, ROUNDCNT ) FROM DUAL  
                            -- K쿼리
                            UNION
                              SELECT  --year0.yymm,
                                   'CC' AS KEY,
                                   'T' HEADER ,
                                   AVG_DISTINCTION * ROUND( P_OP2 / 12/ 100, ROUNDCNT ),
                                   INI_DISTINCTION * ROUND( P_OP2 / 12/ 100, ROUNDCNT ),
                                   MAX_DISTINCTION * ROUND( P_OP2 / 12/ 100, ROUNDCNT )
                            FROM
                            (
                            -- 중복 --
                                SELECT AVG( round( OUTPUT_SUM - INPUT_SUM  ,roundCnt) ) AS AVG_DISTINCTION,
                                       SUM( DECODE( SUBSTR( YYMM , 6 ), '01',OUTPUT_SUM - INPUT_SUM, 0 )   ) AS INI_DISTINCTION,
                                       MAX( round(  OUTPUT_SUM - INPUT_SUM ,roundCnt) ) AS MAX_DISTINCTION
                                      
                                FROM
                                (
                                    SELECT YYMM, ( SELECT SUM( INPUT_SUM_AMT ) FROM PLAN_RESULTS
                                                WHERE GIGUM_CODE = P_GIGUM_CODE
                                                AND   ANAL_YEAR = P_ANAL_YEAR AND   YYMM LIKE P_ANAL_YEAR || + '%'
                                                AND   YYMM <= A.YYMM ) INPUT_SUM,  -- 수입 누계 누적

                                                ( SELECT SUM( OUTPUT_SUM_AMT ) FROM PLAN_RESULTS
                                                WHERE GIGUM_CODE = P_GIGUM_CODE
                                                AND   ANAL_YEAR = P_ANAL_YEAR AND   YYMM LIKE P_ANAL_YEAR || + '%'
                                                AND   YYMM <= A.YYMM ) OUTPUT_SUM  -- 지출 누계 누적

                                    FROM PLAN_RESULTS A
                                    WHERE GIGUM_CODE = P_GIGUM_CODE
                                    AND   ANAL_YEAR = P_ANAL_YEAR
                                    AND   YYMM LIKE P_ANAL_YEAR || + '%'
                                   
                               
                                )
                            -- 중복 --
                            )
                 ) GROUP BY KEY
        )