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

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

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

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

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

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

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

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 누락에 대한 처리를 했습니다^^; 역시 쿼리 짤 때는 처음에 방향이 중요하네요. 재귀해보자 생각한 게 잘못이네요ㅋ

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
;