'(DB) Oracle/SELECT-금액' 카테고리의 글 목록 (2 Page)

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

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

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

저작자표시

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

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

Posted by 농부지기

,

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

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

[ 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.요청일;

저작자표시

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

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

Posted by 농부지기

,

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 농부지기

,

농부지기

'(DB) Oracle/SELECT-금액'에 해당되는 글 14건

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

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

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

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

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

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

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

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

카테고리

태그목록

최근에 올라온 글

최근에 달린 댓글

최근에 받은 트랙백

글 보관함

달력

링크

티스토리툴바