데이터를 비율 컬럼의 합계를 1로 업데이트하는 쿼리

** 데이터를 비율 컬럼의 합계를 1로 업데이트를 하는 걸 만들고 있습니다.

테이블 명은  Test , 컬럼 a,b,c,d ,날짜, 키값, 비율 , 등록일, 수정일 , 등록자, 수정자 로 구성되어 있습니다.

날짜와 키값을 기준으로 ab,c,d 컬럼의 order by 순서 대로 프로시져로 비율의 값을 0.0001 이나 0.0001 로 빼줘서 비율의 합이 1이 되도록 하려고 합니다.

 

-- 대상이 되는 키값, 현재까지 비율의 합 , 처리해야할 갯수를 조회하고 , +인지 - 인지 확인 하고 loop 실행 

    FOR c0 IN (
              SELECT 키값
                , SUM(비율) SUM_PRC_RATIO              /* 비율의 합  */
                , ABS((1.0 - SUM(비율)) / 0.0001) AS GAP_LIMIT  /* 처리해야할 갯수 */
                , CASE WHEN (1.0 - SUM(비율)) > 0.0 THEN '+'
                       ELSE '-'
                       END AS GAP_OPER
              FROM test
              날짜= '201806' and 키값 = '키' 
              GROUP BY 키값
              HAVING SUM(PRC_RATIO) != 1 
      ) LOOP

loop

for c1 in (selelct  a,b,c,d ,날짜, 키값, 비율 from Test where 날짜= '201806' and 키값 = '키' --조회 ) loop 

update test set 비율 = 비율 + (0.0001) ;--(0.0001)

selelct  sum(비율) into sums from Test where 날짜= '201806' and 키값 = '키' ;--조회

exit when sums :=1 ; 

end loop ;

end loop ; 

===================================================

샘플 예제 

===================================================

-- 1. 업데이트 대상 확인 --

WITH test AS

(

SELECT 1 id, '201806' dt, 1 a, 1 b, 1 c, 1 d, 0.5000 prc_ratio FROM dual

UNION ALL SELECT 1, '201806', 1, 1, 1, 2, 0.4990 FROM dual

UNION ALL SELECT 1, '201806', 1, 1, 1, 3, 0.0002 FROM dual

UNION ALL SELECT 1, '201806', 1, 1, 1, 4, 0.0002 FROM dual

UNION ALL SELECT 1, '201806', 1, 1, 1, 5, 0.0002 FROM dual

UNION ALL SELECT 2, '201806', 1, 1, 1, 1, 0.5000 FROM dual

UNION ALL SELECT 2, '201806', 1, 1, 1, 2, 0.4990 FROM dual

UNION ALL SELECT 2, '201806', 1, 1, 1, 3, 0.0001 FROM dual

UNION ALL SELECT 2, '201806', 1, 1, 1, 4, 0.0001 FROM dual

UNION ALL SELECT 2, '201806', 1, 1, 1, 5, 0.0001 FROM dual

UNION ALL SELECT 3, '201806', 1, 1, 1, 1, 0.5000 FROM dual

UNION ALL SELECT 3, '201806', 1, 1, 1, 2, 0.4990 FROM dual

UNION ALL SELECT 3, '201806', 1, 1, 1, 3, 0.0004 FROM dual

UNION ALL SELECT 3, '201806', 1, 1, 1, 4, 0.0004 FROM dual

UNION ALL SELECT 3, '201806', 1, 1, 1, 5, 0.0004 FROM dual

)

SELECT id, dt

     , a, b, c, d

     , prc_ratio

     , rn

     , (FLOOR(cnt2 / cnt1) + CASE WHEN rn <= MOD(cnt2, cnt1) THEN 1 ELSE 0 END) * s x

  FROM (SELECT id, dt

             , a, b, c, d

             , prc_ratio

             , ROW_NUMBER() OVER(PARTITION BY id, dt ORDER BY a, b, c, d) rn

             , COUNT(*) OVER(PARTITION BY id, dt) cnt1

             , ABS (1 - SUM(prc_ratio) OVER(PARTITION BY id, dt)) / 0.0001 cnt2

             , SIGN(1 - SUM(prc_ratio) OVER(PARTITION BY id, dt)) * 0.0001 s

          FROM test

--         WHERE id = 1

--           AND dt = '201806'

        )

;

 

-- 2. Update --

MERGE INTO test a

USING

(

SELECT rid

     , (FLOOR(cnt2 / cnt1) + CASE WHEN rn <= MOD(cnt2, cnt1) THEN 1 ELSE 0 END) * s x

  FROM (SELECT ROWID rid

             , ROW_NUMBER() OVER(PARTITION BY id, dt ORDER BY a, b, c, d) rn

             , COUNT(*) OVER(PARTITION BY id, dt) cnt1

             , ABS (1 - SUM(prc_ratio) OVER(PARTITION BY id, dt)) / 0.0001 cnt2

             , SIGN(1 - SUM(prc_ratio) OVER(PARTITION BY id, dt)) * 0.0001 s

          FROM test

--         WHERE id = 1

--           AND dt = '201806'

        )

 WHERE (FLOOR(cnt2 / cnt1) + CASE WHEN rn <= MOD(cnt2, cnt1) THEN 1 ELSE 0 END) > 0

) b

ON (a.ROWID = b.rid)

WHEN MATCHED THEN

UPDATE SET a.prc_ratio = a.prc_ratio + b.x

;

출처 : http://www.gurubee.net/article/79760 

by 마농 [2018.07.04 10:26:26]