농부지기 :: 농부지기

Oracle - 순차적으로 차감하기1

(DB) Oracle/SELECT-금액 2017. 1. 19. 23:20

-- 10G MODEL 절을 이용한 풀이 --
WITH tab AS
(
              SELECT 'AA' gubun, 'ABC' model, '20140101' yyyy, 3000 inv_qty, 1000 s_qty FROM dual
    UNION ALL SELECT 'AA'      , 'ABC'     , '20140102'      , 1000        , 2000       FROM dual
    UNION ALL SELECT 'AA'      , 'ABC'     , '20140103'      ,    0        , 2000       FROM dual
    UNION ALL SELECT 'AA'      , 'ABC'     , '20140105'      ,    0        , 1000       FROM dual
    UNION ALL SELECT 'AA'      , 'ABC'     , '20140106'      ,    0        , 1000       FROM dual
    UNION ALL SELECT 'AA'      , 'ABC'     , '20140109'      , 1000        , 1000       FROM dual
    UNION ALL SELECT 'AA'      , 'ABC'     , '20140110'      ,    0        , 1000       FROM dual
    UNION ALL SELECT 'BB'      , 'AAA'     , '20140106'      ,    0        , 1000       FROM dual
    UNION ALL SELECT 'BB'      , 'AAA'     , '20140109'      , 1000        , 1000       FROM dual
)
, t1 AS
(
SELECT gubun, model, yyyy, inv_qty, s_qty
     , ROW_NUMBER() OVER(PARTITION BY gubun, model ORDER BY yyyy) rn
  FROM tab
)
SELECT *
  FROM t1
 MODEL
 PARTITION BY (gubun, model)
 DIMENSION BY (rn)
 MEASURES (yyyy, inv_qty, s_qty, 0 net_qty)
 IGNORE NAV
 RULES AUTOMATIC ORDER ( net_qty[ANY] = s_qty[CV()] - inv_qty[CV()] + LEAST(net_qty[CV()-1], 0) )
 ORDER BY gubun, model, rn
;

-- 11G Recursive SQL 을 이용한 풀이 --
WITH tab AS
(
              SELECT 'AA' gubun, 'ABC' model, '20140101' yyyy, 3000 inv_qty, 1000 s_qty FROM dual
    UNION ALL SELECT 'AA'      , 'ABC'     , '20140102'      , 1000        , 2000       FROM dual
    UNION ALL SELECT 'AA'      , 'ABC'     , '20140103'      ,    0        , 2000       FROM dual
    UNION ALL SELECT 'AA'      , 'ABC'     , '20140105'      ,    0        , 1000       FROM dual
    UNION ALL SELECT 'AA'      , 'ABC'     , '20140106'      ,    0        , 1000       FROM dual
    UNION ALL SELECT 'AA'      , 'ABC'     , '20140109'      , 1000        , 1000       FROM dual
    UNION ALL SELECT 'AA'      , 'ABC'     , '20140110'      ,    0        , 1000       FROM dual
    UNION ALL SELECT 'BB'      , 'AAA'     , '20140106'      ,    0        , 1000       FROM dual
    UNION ALL SELECT 'BB'      , 'AAA'     , '20140109'      , 1000        , 1000       FROM dual
)
, t1 AS ( SELECT gubun, model, yyyy, inv_qty, s_qty
               , ROW_NUMBER() OVER(PARTITION BY gubun, model ORDER BY yyyy) rn
            FROM tab
        )
, t2(gubun, model, rn, yyyy, inv_qty, s_qty, net_qty) AS
    (
        SELECT gubun, model, rn, yyyy, inv_qty, s_qty
             , s_qty - inv_qty AS net_qty
          FROM t1
         WHERE rn = 1
         UNION ALL
        SELECT a.gubun, a.model, a.rn, a.yyyy, a.inv_qty, a.s_qty
             , a.s_qty - a.inv_qty + LEAST(b.net_qty, 0) AS net_qty
          FROM t1 a
             , t2 b
         WHERE a.gubun = b.gubun
           AND a.model = b.model
           AND a.rn = b.rn + 1
    )
    SELECT *
      FROM t2
     ORDER BY gubun, model, rn
;
--URL : http://www.gurubee.net/article/62179
--제가(마농) 예전에 냈던 퀴즈와 유사한 형태의 문제네요.
--http://www.gurubee.net/article/26172
--http://www.gurubee.net/lecture/2203
--SQL 은 구조적 질의어로 절차적 언어의 한계를 극복한 언어이죠.
--절차적인 처리 대신 구조적인 질의만으로 결과를 얻을 수 있죠.
--대부분의 절차적인 처리는 구조적 질의로 변경 가능합니다만...
--그러나 이 문제의 경우엔 구조적 질의로 변경 불가능하며, 절차적인 처리만 가능한 문제입니다.
--분석함수는 절차적인 함수가 아닙니다. 집합적인 함수입니다.
--분석함수로는 이 문제를 풀 수 없습니다.
--집합적 SQL 의 이런 한계를 극복할 수 있는것이 10G 의 Model 절입니다.
--11G 에서는 Recursive SQL 로 절차적 처리가 가능합니다.
--저도 Model 절을 접하기 전엔, 분석함수로 이 문제를 풀어보려 애썼던 적이 있었네요.
--결국 포기했었다가, MODEL절을 알고 난뒤 이 문제를 다시 풀게 되었습니다.

저작자표시

'(DB) Oracle > SELECT-금액' 카테고리의 다른 글

Oracle - 순차적으로 누적하기2 (0)	2017.01.19
Oracle - 순차적으로 누적하기0 (0)	2017.01.19
Oracle - 순차적으로 차감하기3 (0)	2017.01.19
Oracle - 순차적으로 차감하기2 (0)	2017.01.19
Oracle - 순차적으로 차감하기0 (0)	2017.01.19

Posted by 농부지기

,

Oracle - 순차적으로 차감하기0

(DB) Oracle/SELECT-금액 2017. 1. 19. 23:17

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

WITH t AS ( SELECT 3 seq, 10000 금액 FROM dual UNION ALL SELECT 2 , 30000 FROM dual UNION ALL SELECT 1 , 50000 FROM dual ) SELECT seq , 금액 , LEAST(금액, 요청액, 요청액 - 누적금액 + 금액) 차감액 , 금액 - LEAST(금액, 요청액, 요청액 - 누적금액 + 금액) 잔액 , 누적금액 FROM (SELECT seq , 금액 , SUM(금액) OVER(ORDER BY seq DESC) 누적금액 , 25200 요청액 FROM t ) --WHERE 누적금액 - 금액 < 요청액 ORDER BY SEQ ; -- 2% 부족.

[www.oracleclub.com 에서 마농님이 작성해 놓은 내용]

저작자표시

'(DB) Oracle > SELECT-금액' 카테고리의 다른 글

Oracle - 순차적으로 누적하기2 (0)	2017.01.19
Oracle - 순차적으로 누적하기0 (0)	2017.01.19
Oracle - 순차적으로 차감하기3 (0)	2017.01.19
Oracle - 순차적으로 차감하기2 (0)	2017.01.19
Oracle - 순차적으로 차감하기1 (0)	2017.01.19

Posted by 농부지기

,

Oracle - 월간 달력 생성. 주차.년차

(DB) Oracle/SELECT-일자 2017. 1. 19. 23:13

[ Oracle - 월간 달력 생성. 주차.년차 ]

SELECT YY_WEEK --년간주차
     , MM_WEEK --월간주차
     , SUM(DECODE(YOIL, '1', DD)) AS 일
     , SUM(DECODE(YOIL, '2', DD)) AS 월
     , SUM(DECODE(YOIL, '3', DD)) AS 화
     , SUM(DECODE(YOIL, '4', DD)) AS 수
     , SUM(DECODE(YOIL, '5', DD)) AS 목
     , SUM(DECODE(YOIL, '6', DD)) AS 금
     , SUM(DECODE(YOIL, '7', DD)) AS 토
  FROM (--2. 요일 및 주간 얻기
        SELECT TO_NUMBER(A.DD) AS DD
             , A.YM || A.DD    AS YMD
             , TO_CHAR(TO_DATE(A.YM || A.DD, 'YYYYMMDD'), 'D') AS YOIL
             , CEIL((ROWNUM + 7 - TO_NUMBER(TO_CHAR(TO_DATE(A.YM || A.DD, 'YYYYMMDD'), 'D'))) / 7) AS MM_WEEK
             , CEIL((TO_CHAR(TO_DATE(A.YM || A.DD,'YYYYMMDD'),'DDD') +
                     TO_CHAR(TO_DATE(SUBSTR(A.YM || A.DD,1,4)||'0101','YYYYMMDD'),'D') - 1) / 7) AS YY_WEEK
          FROM (--1. 해당년월에 대해 1~31일까지 생성
                SELECT '201403'             AS YM
                     , LPAD(ROWNUM, 2, '0') AS DD
                  FROM DUAL
                CONNECT BY LEVEL <= 31
                ) A
         WHERE A.DD <= TO_CHAR(LAST_DAY(TO_DATE(A.YM || '01', 'YYYYMMDD')), 'DD')
       )
GROUP BY YY_WEEK, MM_WEEK
ORDER BY YY_WEEK, MM_WEEK;