농부지기

[ 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 ;

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

WITH t(code,dt,money) AS ( select 'A', '201201', '100' from dual union all select 'A', '201202', '300' from dual union all select 'A', '201203', '10' from dual union all select 'B', '201203', '45' from dual union all select 'B', '201201', '150' from dual union all select 'B', '201202', '350' from dual union all select 'C', '201201', '200' from dual union all select 'C', '201202', '450' from dual union all select 'C', '201203', '55' from dual union all select 'D', '201201', '250' from dual union all select 'D', '201202', '550' from dual union all select 'D', '201203', '60' from dual ) SELECT code , dt , SUM(money) OVER(PARTITION BY code ORDER BY code, dt) AS money FROM t ORDER BY code, dt

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

WITH t AS ( SELECT '20120101' DT, 1 seq, 100 amt, 10 stdAmt FROM dual UNION ALL SELECT '20120102', 1, 200, 0 FROM dual UNION ALL SELECT '20120103', 1, 300, 0 FROM dual UNION ALL SELECT '20120104', 1,400, 0 FROM dual UNION ALL SELECT '20120105', 1,500, 0 FROM dual UNION ALL SELECT '20120106', 1,600, 0 FROM dual UNION ALL SELECT '20120107', 1,700, 0 FROM dual UNION ALL SELECT '20120101', 2, 100, 10 FROM dual UNION ALL SELECT '20120102', 2, 200, 0 FROM dual UNION ALL SELECT '20120103', 2, 300, 0 FROM dual UNION ALL SELECT '20120104', 2, 400, 0 FROM dual UNION ALL SELECT '20120105', 2, 500, 0 FROM dual UNION ALL SELECT '20120106', 2, 600, 0 FROM dual UNION ALL SELECT '20120107', 2, 700, 0 FROM dual ) SELECT DT , SEQ , amt , stdAmt , SUM(amt+stdAmt) OVER (PARTITION BY SEQ ORDER BY DT) sum_amt FROM t

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

WITH 재고테이블 AS ( SELECT 'PRO' product, 150 qty FROM dual ) , 출하테이블 AS ( SELECT '2010-06-20' dt,'PRO' product, 100 qty FROM dual UNION ALL SELECT '2010-06-24', 'PRO', 80 FROM dual UNION ALL SELECT '2010-06-28', 'PRO', 70 FROM dual ) SELECT b.dt, b.product, b.qty , a.qty - b.s_qty 재고 FROM 재고테이블 a, (SELECT dt, product, qty , SUM(qty) OVER(PARTITION BY product ORDER BY dt) s_qty FROM 출하테이블 ) b WHERE a.product(+) = b.product AND a.qty(+) > b.s_qty - b.qty ;

[ Oracle - 순차적으로 차감하기2 ]

--stock_onhand : 현 재고 --item_Demand : 요청 수량 WITH stock_onhand AS ( SELECT '사과' AS item, 60 qty FROM dual UNION ALL SELECT '배' , 800 FROM dual UNION ALL SELECT '귤' , 800 FROM dual ) , item_Demand AS ( SELECT '사과' item, '20131001_1' demand_seq, 20 qty FROM dual UNION ALL SELECT '사과' , '20131003_1' , 50 FROM dual UNION ALL SELECT '배' , '20131003_2' , 80 FROM dual UNION ALL SELECT '배' , '20131001_1' , 200 FROM dual UNION ALL SELECT '배' , '20131007_1' , 300 FROM dual UNION ALL SELECT '포도' , '20131007_1' , 300 FROM dua ) SELECT NVL(A.item, B.item) AS item , A.qty 재고수량, B.요청일, B.요청수량, B.요청수량합계 , NVL(A.qty, 0) - NVL(B.요청수량합계, 0) AS 잔량1 , GREATEST(0, A.qty - B.요청수량합계) AS 잔량2 , LEAST(A.qty, B.요청수량합계) - (B.요청수량합계 - B.요청수량) AS 잔량3 FROM stock_onhand A FULL OUTER JOIN ( SELECT item, demand_seq 요청일, qty 요청수량 , SUM(qty) OVER(PARTITION BY item ORDER BY demand_seq) 요청수량합계 FROM item_Demand) B ON A.item = B.item ORDER BY A.item, B.요청일;