농부지기

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