데이터베이스 질문과 답변
안녕하세요.
프로시저로 데이터를 비율 컬럼의 합계를 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 ;
--문제는 업데이트를 loop 를 돌면서 한껀씩 해서 그런거 같은데 이걸 어떤식으로 해야 좋은지 문의드립니다.
동일일자, 동일키에 대한 건수가 어느정도 되나요?
건수는 많고, 비율 부족분이 작아야만 가능할 것 같은데요?
예를 들어 건수가 5개이고 비율 부족분이 0.0004 라고 하면 5개중 4개의 자료로에 0.0001 을 더하면 될텐데.
만약, 건수가 3개 뿐이고 비율 부족분이 0.0004 라고 하면 어떻게 처리해야 하나요?
안녕하세요.
키값으로 비율이 1이 안되는 대상의 비율의 합이 보통 0.4123(혹은 1.235) 이런식이고 1을 만들어주려면
비율의 증가 0.0001(혹은 -0.0001)씩 5877(혹은 235)번을 실행해서 처리를 해서 비율이 합이 1이 되도록 해주려고 합니다.
그런데 테이블에서 키값의 대상이 1건이 아니고 200~300건의 대상이 있다보니 컬럼 순서대로 0.0001 증가(혹은감소)해서 해줘야 되구요. 이걸 반복해서 1이 되기 전까지 처리하려고 합니다.
문의해주신대로 키값의 건수가 3개고 부족분이 0.0004면 순서대로 0.0001을 더해주고 컬럼 order by 첫번째 데이터에 한번더 0.0001이 처리해주려고 합니다.
-- 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
;
와 ~~ 너무 감사합니다.
제가 하고싶은 의도대로 깔끔하게 너무 잘 짜주셔서 감사드립니다.
진짜 대단하십니다. 짧은 시간안에 이렇게 깔끔하게 만들어 주시고 테스트 했는데 그냥 잘 되네요. ㅋㅋㅋㅋㅋ
이거 소스 좀 제 블로그로 퍼가도 되나요?? 출처는 밝혀두겠습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.