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

SQL 질문좀 드립니다.

by 당직하사 [2013.09.26 18:34:56]

SELECT A.REPT, A.INPT, A.INVT, ROW_NUMBER() OVER (ORDER BY REPT, INPT) RN FROM (

SELECT 84 AS REPT, 87 AS INPT, 'RM' AS INVT FROM DUAL

UNION

SELECT 84 AS REPT, 88 AS INPT, 'RP' AS INVT FROM DUAL

UNION

SELECT 84 AS REPT, 89 AS INPT, 'RP' AS INVT FROM DUAL

UNION

SELECT 84 AS REPT, 93 AS INPT, 'RM' AS INVT FROM DUAL

UNION

SELECT 84 AS REPT, 94 AS INPT, 'RM' AS INVT FROM DUAL

UNION

SELECT 84 AS REPT, 95 AS INPT, 'RP' AS INVT FROM DUAL

UNION

SELECT 84 AS REPT, 96 AS INPT, 'RP' AS INVT FROM DUAL

UNION

SELECT 84 AS REPT, 99 AS INPT, 'RM' AS INVT FROM DUAL

UNION

SELECT 85 AS REPT, 1 AS INPT, 'RP' AS INVT FROM DUAL

UNION

SELECT 85 AS REPT, 2 AS INPT, 'RP' AS INVT FROM DUAL

UNION

SELECT 85 AS REPT, 4 AS INPT, 'RM' AS INVT FROM DUAL

UNION

SELECT 85 AS REPT, 5 AS INPT, 'RM' AS INVT FROM DUAL

UNION

SELECT 85 AS REPT, 6 AS INPT, 'RM' AS INVT FROM DUAL

UNION

SELECT 85 AS REPT, 7 AS INPT, 'RP' AS INVT FROM DUAL) A

위 문장 실행해 보시면

REPT	INPT	INVT	RN
84	87	RM	1
84	88	RP	2
84	89	RP	3
84	93	RM	4
84	94	RM	5
84	95	RP	6
84	96	RP	7
84	99	RM	8
85	1	RP	9
85	2	RP	10
85	4	RM	11
85	5	RM	12
85	6	RM	13
85	7	RP	14

실행결과과 위와 같은 형태로 나오게 됩니다.
이때 INVT 값 RM, RP를 그룹으로 하고 연결되는 REPT, INPT 번호
84/87, 84/88, 84/89 항목의 RN값 MIN(1) MAX(3)을 구하고 싶습니다.
예외사항으로 84/99, 85/1, 85/2의 경우 RM,RP로 그룹이 되어지고
이때 RN의 MIN, MAX 값은 각가 8, 10을 조회할 수 있는 SQL이 필요합니다

도움 부탁드립니다.

최종 원하는 데이터는

RM,RP를 동일하게 그룹화 하여 RN값에 대한 MIN, MAX 값을 구하고 싶습니다.
MIN MAX
1 3
4 7
8 10
11 14

입니다.

by 우리집아찌 [2013.09.26 18:42:30]

-- 문제가 이해가 안가요.. 원하는 데이타도 써주세요..
WITH T AS (
SELECT A.REPT, A.INPT, A.INVT, ROW_NUMBER() OVER (ORDER BY REPT, INPT) RN 
FROM (
SELECT 84 AS REPT, 87 AS INPT, 'RM' AS INVT FROM DUAL
UNION
SELECT 84 AS REPT, 88 AS INPT, 'RP' AS INVT FROM DUAL
UNION 
SELECT 84 AS REPT, 89 AS INPT, 'RP' AS INVT FROM DUAL
UNION
SELECT 84 AS REPT, 93 AS INPT, 'RM' AS INVT FROM DUAL
UNION
SELECT 84 AS REPT, 94 AS INPT, 'RM' AS INVT FROM DUAL
UNION
SELECT 84 AS REPT, 95 AS INPT, 'RP' AS INVT FROM DUAL
UNION
SELECT 84 AS REPT, 96 AS INPT, 'RP' AS INVT FROM DUAL
UNION
SELECT 84 AS REPT, 99 AS INPT, 'RM' AS INVT FROM DUAL
UNION
SELECT 85 AS REPT, 1 AS INPT, 'RP' AS INVT FROM DUAL
UNION
SELECT 85 AS REPT, 2 AS INPT, 'RP' AS INVT FROM DUAL
UNION
SELECT 85 AS REPT, 4 AS INPT, 'RM' AS INVT FROM DUAL
UNION
SELECT 85 AS REPT, 5 AS INPT, 'RM' AS INVT FROM DUAL
UNION
SELECT 85 AS REPT, 6 AS INPT, 'RM' AS INVT FROM DUAL
UNION
SELECT 85 AS REPT, 7 AS INPT, 'RP' AS INVT FROM DUAL) A
)

SELECT REPT 
   ,INPT
   ,INVT 
   ,MIN(RN) OVER(PARTITION BY INPT - RN ) MIN_VAL
   ,MAX(RN) OVER(PARTITION BY INPT - RN ) MAX_VAL
      
FROM T
ORDER BY RN

by 당직하사 [2013.09.26 18:53:34]

원하는 데이터를 적어놓긴 했는데..
설명드리기 참 애매하네요.. ㅠㅠ

by 마농 [2013.09.26 18:52:41]

원본데이터만 올리셨네요
결과데이터도 원본데이터처럼 표형태로 올려주세요.
장황하고 난해한 설명 말구요.

by 마농 [2013.09.26 19:04:16]

WITH t AS
(
SELECT 84 rept, 87 inpt, 'RM' invt FROM dual
UNION ALL SELECT 84, 88, 'RP' FROM dual
UNION ALL SELECT 84, 89, 'RP' FROM dual
UNION ALL SELECT 84, 93, 'RM' FROM dual
UNION ALL SELECT 84, 94, 'RM' FROM dual
UNION ALL SELECT 84, 95, 'RP' FROM dual
UNION ALL SELECT 84, 96, 'RP' FROM dual
UNION ALL SELECT 84, 99, 'RM' FROM dual
UNION ALL SELECT 85,  1, 'RP' FROM dual
UNION ALL SELECT 85,  2, 'RP' FROM dual
UNION ALL SELECT 85,  4, 'RM' FROM dual
UNION ALL SELECT 85,  5, 'RM' FROM dual
UNION ALL SELECT 85,  6, 'RM' FROM dual
UNION ALL SELECT 85,  7, 'RP' FROM dual
)
SELECT MIN(rn_min) rn_min
     , MAX(rn_max) rn_max
  FROM (SELECT invt
             , MIN(rn) rn_min
             , MAX(rn) rn_max
             , SUM(DECODE(invt, 'RM', 1)) OVER(ORDER BY MIN(rn)) grp
          FROM (SELECT rept, inpt, invt
                     , ROW_NUMBER() OVER(ORDER BY rept, inpt) rn
                     , ROW_NUMBER() OVER(PARTITION BY invt ORDER BY rept, inpt) rn1
                  FROM t
                )
         GROUP BY invt, rn - rn1
        )
 GROUP BY grp
 ORDER BY grp
;

by 당직하사 [2013.09.26 19:07:02]

답변 감사드립니다 마농님..

댓글등록

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

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

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

데이터베이스 질문과 답변