꿈꾸는 개발자, DBA 커뮤니티 구루비

꽤나 어려운 쿼리 문제 도움 요청드립니다.

by 신이만든짝퉁 오라클 11G 너무 어려움 머리가 복잡함 [2016.10.28 16:17:32]

-- 원본 데이터
CREATE TABLE TMP_MAS
(
SEQ NUMBER
, PRODUCT NUMBER
, GUBUN VARCHAR2(10)
, START_DT VARCHAR2(8)
, END_DT VARCHAR2(8)
, VALUE NUMBER
);
 
INSERT INTO TMP_MAS
(SEQ, PRODUCT, GUBUN, START_DT, END_DT, VALUE)
VALUES (10, 10000, 'A', '20160516', '20160531', 40);
INSERT INTO TMP_MAS
(SEQ, PRODUCT, GUBUN, START_DT, END_DT, VALUE)
VALUES (9, 10000, 'A', '20160501', '20160510', 30);
INSERT INTO TMP_MAS
(SEQ, PRODUCT, GUBUN, START_DT, END_DT, VALUE)
VALUES (8, 10000, 'A', '20160501', '20160531', 20);
INSERT INTO TMP_MAS
(SEQ, PRODUCT, GUBUN, START_DT, END_DT, VALUE)
VALUES (6, 10000, 'A', '20160501', '20160515', 30);
INSERT INTO TMP_MAS
(SEQ, PRODUCT, GUBUN, START_DT, END_DT, VALUE)
VALUES (4, 10000, 'B', '20160501', '20160531', 45);
INSERT INTO TMP_MAS
(SEQ, PRODUCT, GUBUN, START_DT, END_DT, VALUE)
VALUES (3, 10000, 'B', '20160510', '20160531', 70);
INSERT INTO TMP_MAS
(SEQ, PRODUCT, GUBUN, START_DT, END_DT, VALUE)
VALUES (2, 10000, 'B', '20160501', '20160531', 71);
COMMIT ;
 
SELECT * FROM TMP_MAS;
 
 
-- 결과 데이터
WITH RESULT AS (
SELECT 10 SEQ, 10000 PRODUCT, 'A' GUBUN, '20160516' START_DT, '20160531' END_DT, 40 VALUE, '20160516' RESULT_ST_DT, '20160531' RESULT_ED_DT FROM DUAL UNION ALL
SELECT 9 SEQ, 10000 PRODUCT, 'A' GUBUN, '20160501' START_DT, '20160510' END_DT, 30 VALUE, '20160501' RESULT_ST_DT, '20160510' RESULT_ED_DT FROM DUAL UNION ALL
SELECT 8 SEQ, 10000 PRODUCT, 'A' GUBUN, '20160501' START_DT, '20160531' END_DT, 20 VALUE, '20160511' RESULT_ST_DT, '20160515' RESULT_ED_DT FROM DUAL UNION ALL
SELECT 4 SEQ, 10000 PRODUCT, 'B' GUBUN, '20160501' START_DT, '20160531' END_DT, 45 VALUE, '20160501' RESULT_ST_DT, '20160531' RESULT_ED_DT FROM DUAL
)
SELECT * FROM RESULT
ORDER BY PRODUCT ASC, GUBUN ASC, RESULT_ED_DT DESC
;

안녕하세요.
위 원본데이터에서 아래 결과 데이터를 출력하는 것이 목적입니다.
상당히 어려운 문제여서 고수님들의 도움을 기다립니다.

조건 1) 매월 1일부터 31일(매월 마지막)까지의 각각의 VALUE 값을 구하는 문제
조건 2) SEQ는 유니크하며 숫자가 클 수록 늦게 입력된 데이터
조건 3) SEQ가 큰 수부터 작은 수까지 중복되지 않은기간 사이의 VALUE를 구함
조건 4) 데이터가 중복인 경우 SEQ가 큰 ROW의 VALUE를 구함
조건 5) 1일 부터 31일까지 모든 기간의 데이터를 구하면 하위행(SEQ 가 작은 값)의 값은 버림

예) PRODUCT = 10000, GUBUN = A 인 경우
SEQ의 범위는 10 ~ 6까지이며 10부터 6까지 역순으로 값을 구해나감
SEQ = 10, 20160516 ~ 20160531 : 추출 O, 16일부터 31까지 VALUE = 40임
SEQ = 9, 20160501 ~ 20160510 : 추출 O, 01부터 10일까지 VALUE = 30임
SEQ = 8, 20160511 ~ 20160515 : 추출 O, 단, 포함관계인 01일~ 10일, 16일 ~ 31일은 SEQ=10, SEQ = 9에서 이미 구했으므로, 10일부터 15일까지의 VALUE = 20을 추출
SEQ = 6, : 추출 X, 01부터 31일까지의 모든 VALUE를 이미 추출하였음

추가설명 : 기간이 겹치는 경우, 하위행에서는 겹치는 날짜를 제거한 기간에 한하여 VALUE를 구합니다. SEQ = 8 의 기간은 SEQ= 10, SEQ=9에 의해서 01 ~ 10 / 16 ~ 31까지의 기간이 서로 겹칩니다. 이 경우 상위행에서 데이터가 추출되었으므로, 11 ~ 15기간동안만 VALUE = 20 으로 추출합니다.

예) PRODUCT = 10000, GUBUN = B 인 경우
SEQ의 범위는 4 ~ 2까지이며, 4부터 2까지 역순으로 값을 구해나감
SEQ = 4, 20160501 ~ 20160531 : 추출 O, 1일부터 31까지 VALUE = 45임
SEQ = 3 : 추출 X, 1일부터 31일까지 모든 VALUE를 이미 추출하였음
SEQ = 2 : 추출 X, 1일부터 31일까지 모든 VALUE를 이미 추출하였음

by 신이만든짝퉁 [2016.10.28 16:58:49]

겹치는 날짜에 대해서 추가설명을 더 달았습니다.

복잡한 경우의 수가 많이 나와서 설명이 미흡했습니다.

더 필요한 정보가 있다면 말씀해주세요.

by Kyle [2016.10.28 17:46:30]

이게 최선인지는 장담하기 힘들지만 구해 지기는 하네요...ㅎ

WITH TMP_MAS(SEQ, PRODUCT, GUBUN, START_DT, END_DT, VALUE) AS (
    SELECT 10, 10000, 'A', '20160516', '20160531', 40 FROM DUAL UNION ALL
    SELECT 9, 10000, 'A', '20160501', '20160510', 30  FROM DUAL UNION ALL
    SELECT 8, 10000, 'A', '20160501', '20160531', 20  FROM DUAL UNION ALL
    SELECT 6, 10000, 'A', '20160501', '20160515', 30  FROM DUAL UNION ALL
    SELECT 4, 10000, 'B', '20160501', '20160531', 45  FROM DUAL UNION ALL
    SELECT 3, 10000, 'B', '20160510', '20160531', 70  FROM DUAL UNION ALL
    SELECT 2, 10000, 'B', '20160501', '20160531', 71  FROM DUAL
)
SELECT PRODUCT, GUBUN, MAX(SEQ) SEQ, MIN(TMP_DT) START_DT, MAX(TMP_DT) END_DT, VALUE
FROM (
    SELECT
        BB.PRODUCT,
        BB.GUBUN,
        AA.TMP_DT,
        BB.VALUE,
        BB.SEQ,
        ROW_NUMBER() OVER(PARTITION BY BB.PRODUCT, BB.GUBUN, AA.TMP_DT ORDER BY BB.SEQ DESC) RN
    FROM (
        SELECT A.MON || LPAD(DAY, 2, '0') TMP_DT
        FROM (
            SELECT SUBSTR(START_DT, 1, 6) MON
            FROM TMP_MAS
            GROUP BY SUBSTR(START_DT, 1, 6)
        ) A CROSS JOIN (
            SELECT LEVEL DAY FROM DUAL
            CONNECT BY LEVEL <= 31
        ) B
    ) AA INNER JOIN TMP_MAS BB
        ON ( AA.TMP_DT BETWEEN BB.START_DT AND BB.END_DT )
)
WHERE RN = 1
GROUP BY PRODUCT, GUBUN, VALUE

by 신이만든짝퉁 [2016.10.31 10:30:36]

같이 고민해주셔서 감사합니다. ^^
아쉽게도 SEQ=8 을 2016-05-01 ~ 2016-0511으로 변경할 경우 결과가 아래와 같이 나와야하는데 다르게 나오고 있습니다.

seq=10, 2016-05-16 ~ 2016-05-31, 40
seq=9, 2016-05-01 ~ 2016-05-10, 30
seq=8, 2016-05-11 ~ 2016-05-11, 20
seq=6, 2016-05-12 ~ 2016-05-15, 30
seq=4, 2016-05-01 ~ 2016-05-31, 45

by Kyle [2016.10.31 10:59:42]

제가 value 가 같을 수도 있다는 부분을 간과 했네요 group by 부분에 seq 만 추가 하면 될듯 합니다

WITH TMP_MAS(SEQ, PRODUCT, GUBUN, START_DT, END_DT, VALUE) AS (
    SELECT 10, 10000, 'A', '20160516', '20160531', 40 FROM DUAL UNION ALL
    SELECT 9, 10000, 'A', '20160501', '20160510', 30  FROM DUAL UNION ALL
    SELECT 8, 10000, 'A', '20160501', '20160511', 20  FROM DUAL UNION ALL
    SELECT 6, 10000, 'A', '20160501', '20160515', 30  FROM DUAL UNION ALL
    SELECT 4, 10000, 'B', '20160501', '20160531', 45  FROM DUAL UNION ALL
    SELECT 3, 10000, 'B', '20160510', '20160531', 70  FROM DUAL UNION ALL
    SELECT 2, 10000, 'B', '20160501', '20160531', 71  FROM DUAL
)
SELECT PRODUCT, GUBUN, SEQ, MIN(TMP_DT) START_DT, MAX(TMP_DT) END_DT, VALUE
FROM (
    SELECT
        BB.PRODUCT,
        BB.GUBUN,
        AA.TMP_DT,
        BB.VALUE,
        BB.SEQ,
        ROW_NUMBER() OVER(PARTITION BY BB.PRODUCT, BB.GUBUN, AA.TMP_DT ORDER BY BB.SEQ DESC, BB.VALUE) RN
    FROM (
        SELECT A.MON || LPAD(DAY, 2, '0') TMP_DT
        FROM (
            SELECT SUBSTR(START_DT, 1, 6) MON
            FROM TMP_MAS
            GROUP BY SUBSTR(START_DT, 1, 6)
        ) A CROSS JOIN (
            SELECT LEVEL DAY FROM DUAL
            CONNECT BY LEVEL <= 31
        ) B
    ) AA INNER JOIN TMP_MAS BB
        ON ( AA.TMP_DT BETWEEN BB.START_DT AND BB.END_DT )
)
WHERE RN = 1
GROUP BY PRODUCT, GUBUN, SEQ, VALUE
ORDER BY PRODUCT, GUBUN, SEQ DESC

by 신이만든짝퉁 [2016.10.31 12:16:23]

우와!! 역시 세상에는 고수가 많습니다.

정말 많이 배웁니다.

Kyle 님께서 주신 쿼리도 분석해 봐야겠네요.^^

by jkson [2016.10.28 18:00:04]

퇴근시간이라 막 만들다보니 과정이 엉망이고 비효율이 엄청나지만 답은 나옵니다만..

조금 수정하셔서 사용하시면 될 것 같기도 합니다;; 주말 잘보내세요~~

WITH T(SEQ,PRODUCT,GUBUN,START_DT,END_DT,VALUE,DT, RN, SEQNO) AS
(
SELECT SEQ, PRODUCT, GUBUN, START_DT, END_DT, VALUE, DT, RN, 1 SEQNO
  FROM
    (
    SELECT SEQ,PRODUCT,GUBUN,START_DT,END_DT,VALUE, TRUNC(TO_DATE(START_DT,'yyyymmdd'),'MM') + LV - 1 DT, RN
      FROM (SELECT SEQ,PRODUCT,GUBUN,START_DT,END_DT,VALUE
                 , ROW_NUMBER() OVER(PARTITION BY PRODUCT, GUBUN ORDER BY SEQ DESC) RN
              FROM TMP_MAS)
         , (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 31)
     WHERE RN = 1
    )
 WHERE DT BETWEEN TO_DATE(START_DT,'yyyymmdd') AND TO_DATE(END_DT,'yyyymmdd')
UNION ALL
SELECT B.SEQ, B.PRODUCT, B.GUBUN, B.START_DT, B.END_DT, B.VALUE, TRUNC(TO_DATE(B.START_DT,'yyyymmdd'),'MM') + LV - 1
     , B.RN, ROW_NUMBER() OVER(PARTITION BY B.PRODUCT, B.GUBUN ORDER BY B.SEQ) SEQNO
  FROM T A
    , (SELECT SEQ,PRODUCT,GUBUN,START_DT,END_DT,VALUE
            , ROW_NUMBER() OVER(PARTITION BY PRODUCT, GUBUN ORDER BY SEQ DESC) RN
         FROM TMP_MAS)  B
    , (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 31)
  WHERE A.PRODUCT = B.PRODUCT
    AND A.GUBUN = B.GUBUN
    AND A.RN + 1 = B.RN
    AND A.SEQNO = 1
    AND TRUNC(TO_DATE(B.START_DT,'yyyymmdd'),'MM') + LV - 1 BETWEEN B.START_DT AND B.END_DT
    AND NOT EXISTS (SELECT 1
                      FROM TMP_MAS C
                     WHERE PRODUCT = A.PRODUCT
                       AND GUBUN = A.GUBUN
                       AND SEQ > B.SEQ
                       AND TRUNC(TO_DATE(B.START_DT,'yyyymmdd'),'MM') + LV - 1 BETWEEN C.START_DT AND C.END_DT)
)
SELECT MAX(SEQ) SEQ, PRODUCT, GUBUN, MAX(START_DT) START_DT, MAX(END_DT) END_DT, MAX(VALUE) VALUE
    , TO_CHAR(MIN(DT),'YYYYMMDD') RESULT_ST_DT
    , TO_CHAR(MAX(DT),'YYYYMMDD') RESULT_ED_DT
  FROM T
 GROUP BY PRODUCT, GUBUN, RN
 ORDER BY PRODUCT ASC, GUBUN ASC,  TO_NUMBER(SEQ) DESC

월욜 출근해서 보니 다시 봐도 엄청 비효율적이네요ㅋ 재귀 구문에서 group by가 안 되는군요.

group by 해서 min, max 만들면서 가면 좀 효율적일 것 같았는데.. Kyle님 답변달아주신 게 훨씬 효율적이네요^^

추가)비효율적이고 한눈에 들어오지도 않지만 비효율을 조금 줄이고 SEQ 누락에 대한 처리를 했습니다^^; 역시 쿼리 짤 때는 처음에 방향이 중요하네요. 재귀해보자 생각한 게 잘못이네요ㅋ

by 신이만든짝퉁 [2016.10.31 10:35:39]

위 답변을 달아주신 Kyle님과 마찬가지로,
SEQ=8 을 2016-05-01 ~ 2016-0511으로 변경할 경우 결과가 아래와 같이 나와야 하는데 다르게 나옵니다.

쉬운 쿼리가 아니라서 저도 아직 해결을 못했습니다.
프로시저로 해봐야 하는 건지 고민이 되는 군요

seq=10, 2016-05-16 ~ 2016-05-31, 40
seq=9, 2016-05-01 ~ 2016-05-10, 30
seq=8, 2016-05-11 ~ 2016-05-11, 20
seq=6, 2016-05-12 ~ 2016-05-15, 30
seq=4, 2016-05-01 ~ 2016-05-31, 45

by jkson [2016.10.31 11:33:56]

seq가 순차적으로 줄어드는 게 아니군요? 재귀쿼리에서 8->6으로 건너 뛸 때 7이 없어서 그렇네요^^; 여튼 아주 비효율적인 쿼리라 그냥 참고용으로만 봐주세요^^;;

by 신이만든짝퉁 [2016.10.31 11:38:29]

아닙니다.

쿼리 작성에 많은 힌트가 되었습니다. 감사합니다. ^^

by jkson [2016.10.31 12:17:42]

쿼리 수정했습니다. 결론적으로는 재귀를 쓸 필요가 없었는데 방향을 잘못잡았네요ㅋ

by 마농 [2016.10.31 11:14:50]

WITH tmp_mas(seq, product, gubun, start_dt, end_dt, value) AS
(
          SELECT 10, 10000, 'A', '20160516', '20160531', 40 FROM dual
UNION ALL SELECT  9, 10000, 'A', '20160501', '20160510', 30 FROM dual
UNION ALL SELECT  8, 10000, 'A', '20160501', '20160511', 20 FROM dual
UNION ALL SELECT  6, 10000, 'A', '20160501', '20160515', 30 FROM dual
UNION ALL SELECT  4, 10000, 'B', '20160501', '20160531', 45 FROM dual
UNION ALL SELECT  3, 10000, 'B', '20160510', '20160531', 70 FROM dual
UNION ALL SELECT  2, 10000, 'B', '20160501', '20160531', 71 FROM dual
)
, tmp_cal AS
(
SELECT TO_CHAR(dt + LEVEL - 1, 'yyyymmdd') dt
  FROM (SELECT TO_DATE('201605', 'yyyymm') dt FROM dual)
 CONNECT BY LEVEL <= LAST_DAY(dt) - dt + 1
)
SELECT seq, product, gubun, v
     , MIN(dt) s
     , MAX(dt) e
  FROM (SELECT product, gubun, dt
             , MAX(seq) seq
             , MAX(value) KEEP(DENSE_RANK LAST ORDER BY seq) v
             , ROW_NUMBER() OVER(PARTITION BY product, gubun ORDER BY dt) rn1
             , ROW_NUMBER() OVER(PARTITION BY product, gubun, MAX(seq) ORDER BY dt) rn2
          FROM tmp_mas a
             , tmp_cal b
         WHERE b.dt BETWEEN a.start_dt AND a.end_dt
         GROUP BY product, gubun, dt
        )
 GROUP BY product, gubun, seq, v, rn1-rn2
 ORDER BY product, gubun, seq DESC
;

by 신이만든짝퉁 [2016.10.31 11:37:48]

역쉬~~ 대단하십니다. ^^

쿼리를 분석해 봐야겠네요. 감사합니다!!

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변