데이터베이스 퀴즈 및 튜닝 문제

누적 수량을 40개 단위로 자르기 0 3 5,894

by 김신 [2016.06.22 17:27:13]

안녕하세요.

다름이 아니오라 누적 수량을 40개 단위로 자르고 싶습니다.

WITH TT AS (
    SELECT '15' OP FROM DUAL
    UNION ALL
    SELECT '15' OP FROM DUAL
    UNION ALL
    SELECT '15' OP FROM DUAL
    UNION ALL
    SELECT '10' OP FROM DUAL
    UNION ALL
    SELECT '10' OP FROM DUAL
    UNION ALL
    SELECT '7' OP FROM DUAL
    UNION ALL
    SELECT '32' OP FROM DUAL
    UNION ALL
    SELECT '7' OP FROM DUAL
    UNION ALL
    SELECT '4' OP FROM DUAL
)
SELECT  ROW_NUMBER() OVER(ORDER BY TO_NUMBER(OP) DESC) RN, OP
FROM    TT;
 

RN OP
1 32
2 15
3 15
4 15
5 10
6 10
7 7
8 7
9 4

위의 데이타와 같이 40개 누적으로 자른다면

BOX1 : 1

BOX2 : 2,3

BOX3 : 4,5,6

BOX4 : 7,8,9

이렇게 총 4박스에 들어가게 되는데

실제 마지막 BOX4 의 수량은 BOX1, BOX2, BOX3 으로 나누어 들어 갈수 있습니다.

이걸 쿼리로 어떻게 구현해야 할지 감이 잘 안오네요.

전문가님들의 의견 부탁 드립니다.

 

by 김신 [2016.06.22 17:30:54]

제가 개인적으로은 여기까진 해 봤는데..

마지막 문제가 해결이 안되네요.

WITH T AS (
    SELECT  SEQ, OP
    FROM    (
                SELECT
                        ROW_NUMBER() OVER(ORDER BY TO_NUMBER(OP) DESC) SEQ
                      , OP
                FROM    (          
                            SELECT '15' OP FROM DUAL
                            UNION ALL
                            SELECT '15' OP FROM DUAL
                            UNION ALL
                            SELECT '15' OP FROM DUAL
                            UNION ALL
                            SELECT '10' OP FROM DUAL
                            UNION ALL
                            SELECT '10' OP FROM DUAL
                            UNION ALL
                            SELECT '7' OP FROM DUAL
                            UNION ALL
                            SELECT '32' OP FROM DUAL
                            UNION ALL
                            SELECT '7' OP FROM DUAL
                            UNION ALL
                            SELECT '4' OP FROM DUAL
                        )
            )            
)
    
SELECT  SEQ, OP, RS2
FROM    T
MODEL
DIMENSION BY (SEQ)                  -- 로우를 식별할 기준이 되는 컬럼 명시
MEASURES(SEQ ASEQ, 0 RSEQ, OP, 0 RS, 0 RS2)      -- 새로 계산이 되는 컬럼 명시
IGNORE NAV
RULES AUTOMATIC ORDER
(
    RS[SEQ]     = CASE WHEN RS[CV()-1]+OP[CV()] > 40 THEN OP[CV()] ELSE TO_CHAR(RS[CV()-1]+OP[CV()]) END
  , RSEQ[SEQ]   = CASE WHEN RS[CV()-1]+OP[CV()] > 40 THEN ASEQ[CV()] ELSE RSEQ[CV()-1] END
  , RS2[SEQ]    = CASE WHEN RS[CV()-1]+OP[CV()] > 40 THEN RSEQ[CV()] ELSE RSEQ[CV()-1] END
 

)
ORDER BY SEQ
;

SEQ OP RS2
1 32 0
2 15 2
3 15 2
4 15 4
5 10 4
6 10 4
7 7 7
8 7 7
9 4 7

 

by 마농 [2016.06.23 08:34:40] 
WITH tt AS
(
SELECT 15 op FROM dual
UNION ALL SELECT 15 FROM dual
UNION ALL SELECT 15 FROM dual
UNION ALL SELECT 10 FROM dual
UNION ALL SELECT 10 FROM dual
UNION ALL SELECT  7 FROM dual
UNION ALL SELECT 32 FROM dual
UNION ALL SELECT  7 FROM dual
UNION ALL SELECT  4 FROM dual
)
, t0 AS
(
SELECT ROW_NUMBER() OVER(ORDER BY op DESC) rn
     , op
  FROM tt
)
, t1(lv, rn, op, p, r, cnt, box) AS
(
SELECT 1 lv
     , rn
     , op
     , '-' || rn || '-' p
     , '[' || op r
     , op cnt
     , 1 box
  FROM t0
 WHERE rn = 1
 UNION ALL
SELECT p.lv + 1 lv
     , c.rn
     , c.op
     , p.p || c.rn || '-'  p
     , p.r || CASE WHEN p.cnt + c.op > 40 THEN '],[' ELSE ',' END || c.op r
     , CASE WHEN p.cnt + c.op > 40 THEN c.op ELSE p.cnt + c.op END cnt
     , CASE WHEN p.cnt + c.op > 40 THEN p.box + 1 ELSE p.box END box
  FROM t1 p
     , t0 c
 WHERE INSTR(p.p, '-'||c.rn||'-') = 0
   AND ( p.cnt + c.op > 40 OR p.rn < c.rn )
)
SELECT DISTINCT r
  FROM (SELECT r||']' r
             , RANK() OVER(ORDER BY box) rk
          FROM t1
         WHERE lv = 9
        )
 WHERE rk = 1
;

 

by 김용한 [2017.09.14 10:03:22]

이건진짜모르겠네요

하루종일 고민해봐도모르겠어서

마농님 답변봤는데 너무어려워요 설명좀부탁드려요 ~~

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입