농부지기

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

[ 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