농부지기

[ Oracle - 금액배분 후 남은 잔여분 재일 큰 거래처에 가감  ]

--문제 : 거래처 구분별로 100,000만원을 각 거래처에 배분하여 인센티브를 지급하려고 한다.
--       단, 배분시 매출액대비 지급율을 계산하여 인센티브를 지급하는데
--       거래처 구분별로 100,000만원이 넘어가거나, 남을 경우  제일 많이 지급되는 거래처에서 가감한다.

WITH SALE_LIST AS ( SELECT '백화점' AS GUBN, '하늘점' AS CUST, '20104' AS YM, 31 AS AMT FROM DUAL UNION ALL SELECT '백화점' , '하늘점' , '20105' , 24 FROM DUAL UNION ALL SELECT '백화점' , '하늘점' , '20106' , 34 FROM DUAL UNION ALL SELECT '백화점' , '누리점' , '20101' , 25 FROM DUAL UNION ALL SELECT '백화점' , '누리점' , '20102' , 48 FROM DUAL UNION ALL SELECT '백화점' , '가리봉점' , '20105' , 34 FROM DUAL UNION ALL SELECT '온라인' , '11ST' , '20105' , 77 FROM DUAL UNION ALL SELECT '온라인' , '11ST' , '20106' , 85 FROM DUAL UNION ALL SELECT '온라인' , '11ST' , '20107' , 79 FROM DUAL UNION ALL SELECT '온라인' , 'GS마켓' , '20101' , 64 FROM DUAL UNION ALL SELECT '온라인' , 'GS마켓' , '20112' , 70 FROM DUAL ) SELECT A.* , ( CASE WHEN RANKING=1 AND 100000 != SUM(INCT_AMT) OVER(PARTITION BY GUBN) THEN INCT_AMT + (100000 - SUM(INCT_AMT) OVER(PARTITION BY GUBN)) ELSE INCT_AMT END ) AS REAL_INCT_AMT , INCT_AMT - ( CASE WHEN RANKING=1 AND 100000 != SUM(INCT_AMT) OVER(PARTITION BY GUBN) THEN INCT_AMT + (100000 - SUM(INCT_AMT) OVER(PARTITION BY GUBN)) ELSE INCT_AMT END ) AS DIFF_AMT FROM ( SELECT GUBN , CUST , SUM(AMT) AS CUST_SUM , SUM(SUM(AMT)) OVER(PARTITION BY GUBN) AS GUBN_SUM , TRUNC(SUM(AMT) / SUM(SUM(AMT)) OVER(PARTITION BY GUBN), 3) AS CUST_RAT , ROW_NUMBER() OVER(PARTITION BY GUBN ORDER BY SUM(AMT) DESC) AS RANKING , ROUND(TRUNC(SUM(AMT) / SUM(SUM(AMT)) OVER(PARTITION BY GUBN), 3) * 100000, 0) AS INCT_AMT FROM SALE_LIST GROUP BY GUBN, CUST ) A ORDER BY GUBN, RANKING ;

[ Oracle- 자료 복제 기본 ]

--0. 행복제 응용 문제 1 SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 10; --1. 행복제 기본 유형과 조인 -- ==> 쓸데 없이 많은 수(99) 만큼 복제하여 조인하는 비효율 발생 -- ==> MAX(cnt) 만큼만 복제 하면 어떨까? WITH t AS ( SELECT 1 pk, 2 cnt FROM dual UNION ALL SELECT 2 , 4 FROM dual UNION ALL SELECT 3 , 6 FROM dual ) SELECT pk, cnt, lv FROM t , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 99) WHERE lv <= cnt ORDER BY pk, lv ; --2. MAX(cnt) 만큼만 복제 하는 방법 WITH t AS ( SELECT 1 pk, 2 cnt FROM dual UNION ALL SELECT 2 , 4 FROM dual UNION ALL SELECT 3 , 6 FROM dual ) SELECT pk, cnt, lv FROM t , (--a. 여기에서 가장 큰 cnt를 구한다. (이 수만큼 복제 된다.) SELECT LEVEL lv FROM (SELECT MAX(cnt) cnt FROM t ) CONNECT BY LEVEL <= cnt ) WHERE lv <= cnt ORDER BY pk, lv ; -- ==> 테이블을 두번 읽는 비효율 발생 --==> 테이블에 직접 Connect By Level 조건을 걸면 한번만 읽고 가능하긴 한데... --==> 위에 언급한 유의 사항 때문에 안되겠군... 어떻게 안될까? --3. Connect_By_Root 조건 삽입. SELECT pk, cnt, LEVEL lv FROM t CONNECT BY LEVEL <= cnt AND CONNECT_BY_ROOT pk = pk ; --==> 어라? 잘 되네? 이게 왜 되는거야? 뭔가 불안한데... --==> 11G 에서는 안됩니다. 고로 사용 불가... --==> ORA-30007: CONNECT BY ROOT 연산자는 START WITH 또는 CONNECT BY 조건에서 지원되지 않습니다. --4. dbms_random.value 조건 삽입. SELECT pk, cnt, LEVEL lv FROM t CONNECT BY LEVEL <= cnt AND PRIOR pk = pk AND PRIOR dbms_random.value IS NOT NULL ; --==> 어라? 잘 되네? 이건 또 왜 되는거야? 불안한데... --==> 이것 역시 11G 에서는 안됩니다. 고로 사용 불가...라고 쓸려고 했는데. --==> 으미.. 잘 되네..참고 싸이트에서는 안된다고 쓰여 있던데... --==> 참고로 내 11g 버전은 11.2.0.1.0 --==> 이걸 써야 되는 거야? 쓰지 말아야 하는거야? 고민되네... --5. Model 절 활용 SELECT pk, cnt, lv FROM t MODEL PARTITION BY (pk) DIMENSION BY (1 lv) MEASURES(cnt) RULES(cnt[FOR lv FROM 1 TO cnt[1] INCREMENT 1] = cnt[1]) ; --6. Multiset 을 이용한 방법 SELECT pk, cnt , TO_NUMBER(column_value) lv FROM t , TABLE(CAST(MULTISET(SELECT LEVEL FROM dual CONNECT BY LEVEL <= cnt) AS sys.dbms_debug_vc2coll)) ; --7. 11G Recursive SQL WITH t AS ( SELECT 1 pk, 2 cnt FROM dual UNION ALL SELECT 2, 4 FROM dual UNION ALL SELECT 3, 6 FROM dual ) , t1(pk, cnt, lv) AS ( SELECT pk, cnt , 1 lv FROM t UNION ALL SELECT pk, cnt , lv + 1 lv FROM t1 WHERE lv + 1 <= cnt ) SELECT * FROM t1 ORDER BY pk, lv ; --○ 행복제 응용 문제 2 --한 필드에 여러 값이 구분자로 저장되어 있을 때 구분자로 구별하여 행 나누기 WITH t AS ( SELECT 1 pk, '1,3,5' v FROM dual UNION ALL SELECT 2, '2,4,6,8' FROM dual UNION ALL SELECT 3, '9,10,11' FROM dual ) SELECT pk, lv , REGEXP_SUBSTR(v, '[^,]+', 1, lv) v FROM t , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 99) WHERE lv <= LENGTH(v) - LENGTH(REPLACE(v, ',')) + 1 ORDER BY pk, lv ;

--http://www.gurubee.net/article/55635#comment_116470

--http://rwijk.blogspot.kr/2007/11/interval-based-row-generation.html

[ Oracle - 순차적으로 누적하기9 ]

일별 월누적매출 구하기

실 데이터는 <표1-실데이터>처럼 존재하며, <표2-결과>와 같은 결과 데이터를 조회

<표1-실데이터>

<표2-결과>

WITH t AS ( SELECT '20090101' dt, 'A' cd, 100 amt FROM dual UNION ALL SELECT '20090101' , 'B' , 200 FROM dual UNION ALL SELECT '20090102' , 'A' , 150 FROM dual UNION ALL SELECT '20090102' , 'B' , 200 FROM dual UNION ALL SELECT '20090102' , 'C' , 100 FROM dual UNION ALL SELECT '20090103' , 'A' , 300 FROM dual UNION ALL SELECT '20090103' , 'B' , 100 FROM dual ) SELECT dt, cd, amt, sum_amt FROM ( SELECT a.dt , b.cd , c.amt , SUM(c.amt) OVER(PARTITION BY b.cd ORDER BY a.dt) sum_amt FROM ( SELECT DISTINCT dt FROM t ) a CROSS JOIN (SELECT DISTINCT cd FROM t) b LEFT OUTER JOIN t c ON a.dt = c.dt AND b.cd = c.cd ) WHERE sum_amt IS NOT NULL ORDER BY dt, cd

[ Oracle - 순차적으로 누적하기8 ]

음수의 연속된 곱을 한방에 해결할수 있는 방법이 있을까요? 1. 절대값만 누적 곱한 후 음수값만 카운트하여 건수가 홀수이면 -1을 곱해주면 끝. WITH test AS ( SELECT 1 id, 1 col1 FROM dual UNION ALL SELECT 2, -2 FROM dual UNION ALL SELECT 3, 3 FROM dual UNION ALL SELECT 4, -4 FROM dual UNION ALL SELECT 6, 6 FROM dual UNION ALL SELECT 7, -7 FROM dual UNION ALL SELECT 9, 9 FROM dual ) SELECT id, col1 , ROUND( EXP(SUM(LN(ABS(col1))) OVER(ORDER BY id)) * DECODE(MOD(COUNT(DECODE(SIGN(col1),-1,1)) OVER(ORDER BY id),2),1,-1,1) , 10) col2 FROM test ; 다만 이때 주의할것은 LN 함수 때문에 정확한 값이 아닌 근사값이 나온다는 것입니다. 그래서 위의 쿼리에서 round 함수를 이용해 값을 보정해 주었습니다. 2. Model 절을 이용하면 정확한 값을 계산 할 수 있습니다. ; WITH test AS ( SELECT 1 id, 1 col1 FROM dual UNION ALL SELECT 2, -2 FROM dual UNION ALL SELECT 3, 3 FROM dual UNION ALL SELECT 4, -4 FROM dual UNION ALL SELECT 6, 6 FROM dual UNION ALL SELECT 7, -7 FROM dual UNION ALL SELECT 9, 9 FROM dual ) SELECT id, col1, col2 FROM test MODEL DIMENSION BY (ROW_NUMBER() OVER(ORDER BY id) rn) MEASURES (id, col1, 0 col2) RULES (col2[ANY] = NVL(col2[CV()-1], 1) * col1[CV()]) ;

[ Oracle - 순차적으로 누적하기7 ]

위 테이블과 같은 데이터 구조에서
시작시간의 빈값에 순차적으로 시작시간 + 걸린시간값이 그룹코드별로 들어가 있는 sql

WITH t AS ( SELECT 'ECOMP01' grp, '6230394' lot, 0.4 dd, TO_DATE('2010-09-29 15:43', 'yyyy-mm-dd hh24:mi') tm FROM dual UNION ALL SELECT 'ECOMP01', '6229155', 0.14, null FROM dual UNION ALL SELECT 'ECOMP01', '6229177', 0.02, null FROM dual UNION ALL SELECT 'ECOMP01', '6230479', 0.41, null FROM dual UNION ALL SELECT 'ECOMP01', '6230694', 0.02, null FROM dual UNION ALL SELECT 'ECOMP01', '6230487', 0.16, null FROM dual UNION ALL SELECT 'ECOMP01', '6230501', 0.03, null FROM dual UNION ALL SELECT 'ECOMP01', '6231286', 0.07, null FROM dual UNION ALL SELECT 'ECOMP01', '6230552', 0.81, null FROM dual UNION ALL SELECT 'ECOMP02', '6228735', 0.17, TO_DATE('2010-10-01 19:18', 'yyyy-mm-dd hh24:mi') FROM dual UNION ALL SELECT 'ECOMP02', '6228948', 1.33, null FROM dual UNION ALL SELECT 'ECOMP02', '6228949', 1.33, null FROM dual UNION ALL SELECT 'ECOMP02', '6230272', 0.67, null FROM dual UNION ALL SELECT 'ECOMP02', '6230274', 0.67, null FROM dual UNION ALL SELECT 'ECOMP02', '6230275', 0.67, null FROM dual UNION ALL SELECT 'ECOMP02', '6230273', 2.67, null FROM dual UNION ALL SELECT 'ECOMP02', '6230276', 1.33, null FROM dual UNION ALL SELECT 'ECOMP02', '6230277', 3.33, null FROM dual UNION ALL SELECT 'ECOMP02', '6230817', 0.67, null FROM dual UNION ALL SELECT 'ECOMP02', '6230819', 0.67, null FROM dual ) SELECT grp, lot, dd, tm , LAST_VALUE(tm - dd IGNORE NULLS) OVER(PARTITION BY grp ORDER BY rownum) + SUM(dd) OVER(PARTITION BY grp ORDER BY rownum) tm2 FROM t ;

[ Oracle - 순차적으로 누적하기6 ]

WITH cod AS ( SELECT '' pcd, 'A' cd FROM dual UNION ALL SELECT 'A', 'B' FROM dual UNION ALL SELECT 'B', 'C' FROM dual UNION ALL SELECT 'B', 'D' FROM dual UNION ALL SELECT 'A', 'E' FROM dual ) , mst AS ( SELECT 'C' cd, 1000 v FROM dual UNION ALL SELECT 'D', 2000 FROM dual UNION ALL SELECT 'E', 5000 FROM dual ) SELECT LEVEL lv , cd , ( SELECT SUM(v) FROM mst WHERE cd IN (SELECT cd FROM cod START WITH cd = a.cd CONNECT BY PRIOR cd = pcd) ) sum_v FROM cod a START WITH pcd IS NULL CONNECT BY PRIOR cd = pcd ;

[ Oracle - 순차적으로 누적하기5 ]

WITH temp AS ( SELECT '199901' reg_dd, 100 a FROM dual UNION ALL SELECT '199902', 200 FROM dual UNION ALL SELECT '200001', 500 FROM dual UNION ALL SELECT '200002', 250 FROM dual UNION ALL SELECT '200002', 200 FROM dual UNION ALL SELECT '200003', 200 FROM dual UNION ALL SELECT '200301', 500 FROM dual ) SELECT gb , DECODE(gb, 3, 'TOTAL', (gb*200) ||'~'|| (gb*200+200)) gb_nm , DECODE(lv, 1, '합계', 2, '건수', 3, '비율') gb_nm2 , MIN(DECODE(yy, 2002, DECODE(lv, 1, s, 2, c, 3, r))) v_2002 , MIN(DECODE(yy, 2003, DECODE(lv, 1, s, 2, c, 3, r))) v_2003 , MIN(DECODE(yy, 2004, DECODE(lv, 1, s, 2, c, 3, r))) v_2004 , MIN(DECODE(yy, 2005, DECODE(lv, 1, s, 2, c, 3, r))) v_2005 , MIN(DECODE(yy, 2006, DECODE(lv, 1, s, 2, c, 3, r))) v_2006 , MIN(DECODE(yy, 2007, DECODE(lv, 1, s, 2, c, 3, r))) v_2007 , MIN(DECODE(yy, 2008, DECODE(lv, 1, s, 2, c, 3, r))) v_2008 , MIN(DECODE(yy, 2009, DECODE(lv, 1, s, 2, c, 3, r))) v_2009 , MIN(DECODE(yy, 2010, DECODE(lv, 1, s, 2, c, 3, r))) v_2010 , MIN(DECODE(yy, 2011, DECODE(lv, 1, s, 2, c, 3, r))) v_2011 FROM (SELECT a.gb , a.yy , SUM(s) s , SUM(c) c , TRUNC(RATIO_TO_REPORT(SUM(s)) OVER(PARTITION BY a.yy) * 200, 2) r FROM (SELECT MOD(LEVEL, 4) gb , 2001 + CEIL(LEVEL/4) yy FROM dual CONNECT BY LEVEL <= 10 * 4 ) a , (SELECT NVL(TRUNC(a / 200), 3) gb , SUBSTR(reg_dd, 1, 4) yy , SUM(a) s , COUNT(*) c FROM temp GROUP BY SUBSTR(reg_dd, 1, 4) , ROLLUP(TRUNC(a / 200)) ) b WHERE a.gb = b.gb(+) AND a.yy >= b.yy(+) GROUP BY a.gb, a.yy ) , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 3) GROUP BY gb, lv ORDER BY gb, lv

[ Oracle - 순차적으로 누적하기4 ]

with t as(
select  '20120502' dat, '10001' lotno, 21120 inp_qty, 0 god_qty from dual union all
select  '20120505' dat, '10001' lotno, 21120 inp_qty, 7000 god_qty from dual union all
select  '20120507' dat, '10001' lotno, 0 inp_qty, 13000 god_qty from dual
)
select  * from t

DAT LOTNO    INP_QTY    GOD_QTY
-------------------------------------------------------------
20120502   10001   21120     0
20120505   10001   21120     7000 --> inp_qty - god_qty = 14120
20120507   10001   14120   13000    

WITH t1 AS ( SELECT '20120502' dat, '10001' lotno, 21120 inp_qty FROM dual ), t2 AS ( SELECT '20120505' dat, '10001' lotno, 7000 god_qty FROM dual UNION ALL SELECT '20120507' , '10001' , 13000 FROM dual ), t3 AS ( SELECT dat, lotno, inp_qty, 0 god_qty FROM t1 UNION ALL SELECT dat, lotno, 0 inp_qty, god_qty FROM t2 ) SELECT dat , lotno , (SELECT NVL(SUM(inp_qty - god_qty), 0) FROM t3 WHERE lotno = a.lotno AND dat < a.dat ) + inp_qty inp_qty_sum , god_qty FROM t3 a ORDER BY lotno, dat ;

[ Oracle - 순차적으로 누적하기3 ]

Date   Code Amt
20120701   1111 1000
20120701   1112  2000
20120702    data 없음
20120703    1111 3000
20120704    1112 5000
20120704    3333 1000
20120705 data 없음


결과: 20120705 기준 조회

Date   Code Amt
20120701    1111 1000
20120701    1112  2000

20120702    1111 1000
20120702    1112 2000

20120703    1111  4000
20120703    1112 2000

20120704    1111   4000
20120704    1112   7000
20120704    3333   1000

20120705    1111    4000
20120705    1112    7000
20120705    3333    1000
.
.
.

위와 같은 결과를 얻고 싶습니다. (1일 부터 해당일자 까지 누적 합계,  1, 1~2, 1~3, 1~4, 1~5일 이런식으로 한달치 누적합계)
특정일자에 data가 없으면 전날 까지의 누적 합계가  그 날짜의 합계가됨.

with tab as ( select '20120701' as date1, '1111' as code, 1000 as amt from dual union all select '20120701' as date1, '1112' as code, 2000 as amt from dual union all select '20120702' as date1, '' as code, 0 as amt from dual union all select '20120703' as date1, '1111' as code, 3000 as amt from dual union all select '20120704' as date1, '1112' as code, 5000 as amt from dual union all select '20120704' as date1, '3333' as code, 1000 as amt from dual union all select '20120705' as date1, '' as code, 0 as amt from dual ) select t2.date1 "Date" , t1.code "Code" , sum(distinct t1.amt) "Amt" from tab t1, tab t2 where t1.date1 <= t2.date1 and t1.code is not null group by t2.date1, t1.code order by t2.date1, t1.code;

[ Oracle - 순차적으로 누적하기1 ]

<RAW 데이터>

일자별로 사용량이 기록된 RAW 데이터가 있습니다.
월별 재고, 월별 사용량, 해당월 포함 직전 2개월 사용량을 구하는 것 입니다.

BOH - 기초재고
OUT - 사용량
EOH - 기말재고
SUM - 직전 2개월 사용량(해당월 포함)

6월 결과 데이터  SUM 값은 4월, 5월, 6월 사용량입니다.
결과 데이터는 6월부터 보여 주지만 SUM 값은 RAW 데이터 중 4월, 5월 데이터를 잡아와야 합니다.

WITH t_mst AS ( SELECT 'A' cd, '20120420' dt, 200 boh, 10 out, 190 eoh FROM dual UNION ALL SELECT 'A', '20120625', 190, 40, 150 FROM dual UNION ALL SELECT 'A', '20120810', 150, 20, 130 FROM dual UNION ALL SELECT 'A', '20120825', 130, 50, 80 FROM dual UNION ALL SELECT 'A', '20120914', 80, 5, 75 FROM dual UNION ALL SELECT 'A', '20121006', 75, 20, 55 FROM dual UNION ALL SELECT 'A', '20121016', 55, 10, 45 FROM dual UNION ALL SELECT 'B', '20120420', 200, 10, 190 FROM dual UNION ALL SELECT 'B', '20120625', 190, 40, 150 FROM dual UNION ALL SELECT 'B', '20120810', 150, 20, 130 FROM dual UNION ALL SELECT 'B', '20120825', 130, 50, 80 FROM dual UNION ALL SELECT 'B', '20120914', 80, 5, 75 FROM dual UNION ALL SELECT 'B', '20121006', 75, 20, 55 FROM dual UNION ALL SELECT 'B', '20121016', 55, 10, 45 FROM dual ) SELECT * FROM (SELECT cd , TO_CHAR(ym, 'yyyymm') ym , LAST_VALUE(MAX(eoh) KEEP(DENSE_RANK LAST ORDER BY dt) IGNORE NULLS) OVER(PARTITION BY cd ORDER BY ym) stockqty , NVL(SUM(out), 0) outqty , SUM(SUM(out)) OVER(PARTITION BY cd ORDER BY ym RANGE INTERVAL '2' MONTH PRECEDING) sumqty FROM (SELECT ADD_MONTHS(TO_DATE(:sYm, 'yyyymm'), LEVEL-3) ym FROM dual CONNECT BY LEVEL <= MONTHS_BETWEEN( TO_DATE(:eYm, 'yyyymm') -- 201210 , TO_DATE(:sYm, 'yyyymm') -- 201206 ) + 3 ) c LEFT OUTER JOIN t_mst a PARTITION BY (a.cd) ON a.dt LIKE TO_CHAR(c.ym, 'yyyymm')||'%' GROUP BY cd, ym ) WHERE ym >= :sYm ORDER BY cd, ym ;