WITH t AS
(
SELECT '111111' cd, 'AAAAAAA' item, '2012-06-01' dt, 'N' gb FROM dual
UNION ALL SELECT '111111', 'AAAAAAA', '2012-06-02', 'N' FROM dual
UNION ALL SELECT '111111', 'AAAAAAA', '2012-06-04', 'N' FROM dual
UNION ALL SELECT '111111', 'AAAAAAA', '2012-06-05', 'N' FROM dual
UNION ALL SELECT '111111', 'AAAAAAA', '2012-06-07', 'N' FROM dual
UNION ALL SELECT '111111', 'AAAAAAA', '2012-06-11', 'N' FROM dual
UNION ALL SELECT '111111', 'AAAAAAA', '2012-06-15', 'N' FROM dual
UNION ALL SELECT '111111', 'AAAAAAA', '2012-06-18', 'Y' FROM dual
UNION ALL SELECT '111111', 'AAAAAAA', '2012-06-19', 'Y' FROM dual
UNION ALL SELECT '111111', 'AAAAAAA', '2012-06-21', 'N' FROM dual
UNION ALL SELECT '111111', 'AAAAAAA', '2012-06-23', 'N' FROM dual
UNION ALL SELECT '111111', 'AAAAAAA', '2012-06-28', 'Y' FROM dual
UNION ALL SELECT '222222', 'BBBBBBB', '2012-06-01', 'N' FROM dual
UNION ALL SELECT '222222', 'BBBBBBB', '2012-06-02', 'Y' FROM dual
UNION ALL SELECT '222222', 'BBBBBBB', '2012-06-04', 'N' FROM dual
UNION ALL SELECT '222222', 'BBBBBBB', '2012-06-05', 'Y' FROM dual
UNION ALL SELECT '222222', 'BBBBBBB', '2012-06-07', 'Y' FROM dual
UNION ALL SELECT '222222', 'BBBBBBB', '2012-06-11', 'N' FROM dual
UNION ALL SELECT '222222', 'BBBBBBB', '2012-06-15', 'N' FROM dual
UNION ALL SELECT '222222', 'BBBBBBB', '2012-06-18', 'Y' FROM dual
UNION ALL SELECT '222222', 'BBBBBBB', '2012-06-19', 'Y' FROM dual
UNION ALL SELECT '222222', 'BBBBBBB', '2012-06-21', 'Y' FROM dual
UNION ALL SELECT '222222', 'BBBBBBB', '2012-06-23', 'Y' FROM dual
UNION ALL SELECT '222222', 'BBBBBBB', '2012-06-28', 'Y' FROM dual
)
SELECT cd, item, dt, gb
     , ROW_NUMBER() OVER(PARTITION BY cd, item, rn1-rn2, gb ORDER BY dt) rn
  FROM (SELECT cd, item, dt, gb
             , ROW_NUMBER() OVER(PARTITION BY cd, item ORDER BY dt) rn1
             , ROW_NUMBER() OVER(PARTITION BY cd, item, gb ORDER BY dt) rn2
          FROM t
        )
;

마농님 감사합니다.

위 문제를 해결하기 위해 여러 글을 검색해봤는데 그때마다 마농님께서 다 해결을 해주셨더군요.

몇일간 머리 싸매고 고민하던걸 한방에 해결해주셔서 정말 다시한번 감사드립니다.