database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Q&A 41440 게시물 읽기
No. 41440
구간 순번 및 중복 확인 (내용 수정)
작성자
최상민(roice81)
작성일
2017-07-04 10:19ⓒ
2017-07-05 11:06ⓜ
조회수
6,279

몇번 작성하다 실패해서 여쭤 봅니다.

 

WITH t AS

(

SELECT 'R1' AS RR,1 AS SEQ,'4171745' AS BID FROM DUAL

UNION ALL SELECT 'R1',2,'' FROM DUAL

UNION ALL SELECT 'R1',3,'4171846' FROM DUAL

UNION ALL SELECT 'R1',4,'4171846' FROM DUAL

UNION ALL SELECT 'R1',5,'' FROM DUAL

UNION ALL SELECT 'R1',6,'' FROM DUAL

UNION ALL SELECT 'R1',7,'' FROM DUAL

UNION ALL SELECT 'R1',8,'8000340' FROM DUAL

UNION ALL SELECT 'R1',9,'8000340' FROM DUAL

UNION ALL SELECT 'R1',10,'' FROM DUAL

UNION ALL SELECT 'R1',11,'' FROM DUAL

UNION ALL SELECT 'R1',12,'4120554' FROM DUAL

UNION ALL SELECT 'R1',13,'4120554' FROM DUAL

UNION ALL SELECT 'R1',14,'' FROM DUAL

UNION ALL SELECT 'R1',15,'4171979' FROM DUAL

UNION ALL SELECT 'R1',16,'4171979' FROM DUAL

UNION ALL SELECT 'R1',17,'' FROM DUAL

UNION ALL SELECT 'R1',18,'' FROM DUAL

UNION ALL SELECT 'R1',19,'4120551' FROM DUAL

UNION ALL SELECT 'R1',20,'4120551' FROM DUAL

UNION ALL SELECT 'R1',21,'' FROM DUAL

UNION ALL SELECT 'R1',22,'' FROM DUAL

UNION ALL SELECT 'R1',23,'' FROM DUAL

UNION ALL SELECT 'R1',24,'' FROM DUAL

UNION ALL SELECT 'R1',25,'4198741' FROM DUAL

UNION ALL SELECT 'R2',1, '8000340' FROM DUAL

UNION ALL SELECT 'R2',2,'' FROM DUAL

UNION ALL SELECT 'R2',3,'' FROM DUAL

UNION ALL SELECT 'R2',4,'4120554' FROM DUAL

UNION ALL SELECT 'R2',5,'4120554' FROM DUAL

UNION ALL SELECT 'R2',6,'' FROM DUAL

UNION ALL SELECT 'R2',7,'4171979' FROM DUAL

UNION ALL SELECT 'R2',8,'4171979' FROM DUAL

UNION ALL SELECT 'R2',9,'' FROM DUAL

UNION ALL SELECT 'R2',10,'8005340' FROM DUAL

)

SELECT * FROM T

 

RR에 대한 구간 정보를 구하려고합니다.

SEQ는 순번입니다 (예제를 잘못 만들어서 문자가 되었네요, 숫자로 변경했습니다)

순번대로 bid 처음과 끝을 구려고 하는데

bid는 시작 다음 나오는 bid는 종료 입니다.

종료된 bid와 시작 bid는 같은 id입니다.

중간 null값으로 시작 종료를 구하지 못하고 있어요~

 

WITH FIN AS

(

SELECT 'R1' AS RR,1 AS SEQ, '4171745' AS BID, 'A' AS SID, 'AA' AS SSID, '4171745' AS STID, '4171846' AS EDID,1 AS DR_SEQ FROM DUAL

UNION ALL SELECT 'R1',2,'','A','AA','4171745','4171846',1 FROM DUAL

UNION ALL SELECT 'R1',3,'4171846','A','AA','4171745','4171846',1 FROM DUAL

UNION ALL SELECT 'R1',4,'4171846','B','BB','4171846','8000340',1 FROM DUAL

UNION ALL SELECT 'R1',5,'','B','BB','4171846','8000340',1 FROM DUAL

UNION ALL SELECT 'R1',6,'','B','BB','4171846','8000340',1 FROM DUAL

UNION ALL SELECT 'R1',7,'','B','BB','4171846','8000340',1 FROM DUAL

UNION ALL SELECT 'R1',8,'8000340','B','BB','4171846','8000340',1 FROM DUAL

UNION ALL SELECT 'R1',9,'8000340','C','CC','8000340','4120554',2 FROM DUAL

UNION ALL SELECT 'R1',10,'','C','CC','8000340','4120554',2 FROM DUAL

UNION ALL SELECT 'R1',11,'','C','CC','8000340','4120554',2 FROM DUAL

UNION ALL SELECT 'R1',12,'4120554','C','CC','8000340','4120554',2 FROM DUAL

UNION ALL SELECT 'R1',13,'4120554','D','DD','4120554','4171979',2 FROM DUAL

UNION ALL SELECT 'R1',14,'','D','DD','4120554','4171979',2 FROM DUAL

UNION ALL SELECT 'R1',15,'4171979','D','DD','4120554','4171979',2 FROM DUAL

UNION ALL SELECT 'R1',16,'4171979','E','EE','4171979','4120551',3 FROM DUAL

UNION ALL SELECT 'R1',17,'','E','EE','4171979','4120551',3 FROM DUAL

UNION ALL SELECT 'R1',18,'','E','EE','4171979','4120551',3 FROM DUAL

UNION ALL SELECT 'R1',19,'4120551','E','EE','4171979','4120551',3 FROM DUAL

UNION ALL SELECT 'R1',20,'4120551','F','FF','4120551','8000340',3 FROM DUAL

UNION ALL SELECT 'R1',21,'','F','FF','4120551','8000340',3 FROM DUAL

UNION ALL SELECT 'R1',22,'','F','FF','4120551','8000340',3 FROM DUAL

UNION ALL SELECT 'R1',23,'','F','FF','4120551','8000340',3 FROM DUAL

UNION ALL SELECT 'R1',24,'','F','FF','4120551','8000340',3 FROM DUAL

UNION ALL SELECT 'R1',25,'4198741','F','FF','4120551','8000340',3 FROM DUAL

UNION ALL SELECT 'R2',1,'8000340','G','CC','8000340','4120554',2 FROM DUAL

UNION ALL SELECT 'R2',2,'','G','CC','8000340','4120554',2 FROM DUAL

UNION ALL SELECT 'R2',3,'','G','CC','8000340','4120554',2 FROM DUAL

UNION ALL SELECT 'R2',4,'4120554','G','CC','8000340','4120554',2 FROM DUAL

UNION ALL SELECT 'R2',5,'4120554','H','DD','4120554','4171979',2 FROM DUAL

UNION ALL SELECT 'R2',6,'','H','DD','4120554','4171979',2 FROM DUAL

UNION ALL SELECT 'R2',7,'4171979','H','DD','4120554','4171979',2 FROM DUAL

UNION ALL SELECT 'R2',8,'4171979','I','GG','4171979','8005340',4 FROM DUAL

UNION ALL SELECT 'R2',9,'','I','GG','4171979','8005340',4 FROM DUAL

UNION ALL SELECT 'R2',10,'8005340','I','GG','4171979','8005340',4 FROM DUAL

)

SELECT * FROM FIN

 

위와 같이 5가지 항목을 구하고 싶은데

한번에 구하기는 어려울까요??

중간에 null이 있어서 어렵네요

 

sid는 구간에 대한 순번을 부여한것인데 r1에서 r2로 바뀌면 문제가 있네요 ㅠㅠ

ssid는 각 구간에 대해 유니크한 ID를 부여한것입니다. 중복되는 구간은 동일한 ID를 사용합니다.

dr_seq는 ssid가 유니크하게 부여된 순번입니다.

 

 

SID SSID는 숫자여도 상관없습니다

 

 

쿼리로는 힘들까요??

프로C로 해야하는지...

 

막막해서 올려봅니다.

 

부탁드려요~

 

 

 

 

 

이 글에 대한 댓글이 총 7건 있습니다.

설명이 부족하네요.
각 항목들이 어떻게 나오는지? 이유를 절차적으로 설명해 주세요.
다른 항목들은 유추가 가능한데...dr_seq 항목은 뭔지 모르겠네요?

WITH t AS
(
SELECT 'R1' rr, 1 seq, '4171745' bid FROM dual
UNION ALL SELECT 'R1',  2, ''        FROM dual
UNION ALL SELECT 'R1',  3, '4171846' FROM dual
UNION ALL SELECT 'R1',  4, '4171846' FROM dual
UNION ALL SELECT 'R1',  5, ''        FROM dual
UNION ALL SELECT 'R1',  6, ''        FROM dual
UNION ALL SELECT 'R1',  7, ''        FROM dual
UNION ALL SELECT 'R1',  8, '8000340' FROM dual
UNION ALL SELECT 'R1',  9, '8000340' FROM dual
UNION ALL SELECT 'R1', 10, ''        FROM dual
UNION ALL SELECT 'R1', 11, ''        FROM dual
UNION ALL SELECT 'R1', 12, '4120554' FROM dual
UNION ALL SELECT 'R1', 13, '4120554' FROM dual
UNION ALL SELECT 'R1', 14, ''        FROM dual
UNION ALL SELECT 'R1', 15, '4171979' FROM dual
UNION ALL SELECT 'R1', 16, '4171979' FROM dual
UNION ALL SELECT 'R1', 17, ''        FROM dual
UNION ALL SELECT 'R1', 18, ''        FROM dual
UNION ALL SELECT 'R1', 19, '4120551' FROM dual
UNION ALL SELECT 'R1', 20, '4120551' FROM dual
UNION ALL SELECT 'R1', 21, ''        FROM dual
UNION ALL SELECT 'R1', 22, ''        FROM dual
UNION ALL SELECT 'R1', 23, ''        FROM dual
UNION ALL SELECT 'R1', 24, ''        FROM dual
UNION ALL SELECT 'R1', 25, '4198741' FROM dual
UNION ALL SELECT 'R2',  1, '8000340' FROM dual
UNION ALL SELECT 'R2',  2, ''        FROM dual
UNION ALL SELECT 'R2',  3, ''        FROM dual
UNION ALL SELECT 'R2',  4, '4120554' FROM dual
UNION ALL SELECT 'R2',  5, '4120554' FROM dual
UNION ALL SELECT 'R2',  6, ''        FROM dual
UNION ALL SELECT 'R2',  7, '4171979' FROM dual
UNION ALL SELECT 'R2',  8, '4171979' FROM dual
UNION ALL SELECT 'R2',  9, ''        FROM dual
UNION ALL SELECT 'R2', 10, '8005340' FROM dual
)
SELECT rr, seq, bid
     , CHR(64 + DENSE_RANK() OVER(ORDER BY rr, rn1)) sid
     , CHR(64 + DENSE_RANK() OVER(ORDER BY rn2)) ||
       CHR(64 + DENSE_RANK() OVER(ORDER BY rn2)) ssid
     , stid, edid
     , '해석불가?' dr_seq
  FROM (SELECT rr, seq, bid
             , MIN(rn) OVER(PARTITION BY rr, stid) rn1
             , MIN(rn) OVER(PARTITION BY     stid) rn2
             , stid, edid
          FROM (SELECT rr, seq, bid
                     , ROW_NUMBER() OVER(ORDER BY rr, seq) rn
                     , LAG (bid, 1, bid) IGNORE NULLS
                       OVER(PARTITION BY rr ORDER BY seq) stid
                     , LEAD(bid, 1, bid) IGNORE NULLS
                       OVER(PARTITION BY rr ORDER BY seq) edid
                  FROM t
                )
        )
 ORDER BY rr, seq
;

마농(manon94)님이 2017-07-04 15:01에 작성한 댓글입니다.

 마농닙 답변 감사합니다 

Dr seq는 ssid 생성된순번입니다

각 구간별 순번이 중복된 순번이 나오면 같은 순번을 사용하는 것입니다

다그리고 문제를 풀다 난관에 부디친게

오라클 버전이 9i에요

 

Ignore null을 사용할수다 없더라고요 ㅠㅠ

최상민(roice81)님이 2017-07-05 00:54에 작성한 댓글입니다.

dr_seq 에 대한 설명은 도무지 이해가 안가네요.  
글로 설명하지 마시고 데이터로 설명해 주세요.
절차적으로 왜 그 값이 나오는지 흐름을 보여주세요.
 

-- 9i 용 ignore nulls 구문 대체 --
SELECT rr, seq, bid
     , CHR(64 + DENSE_RANK() OVER(ORDER BY rr, rn1)) sid
     , CHR(64 + DENSE_RANK() OVER(ORDER BY rn2)) ||
       CHR(64 + DENSE_RANK() OVER(ORDER BY rn2)) ssid
     , stid, edid
     , '해석불가?' dr_seq
  FROM (SELECT rr, seq, bid
             , MIN(rn) OVER(PARTITION BY rr, stid, edid) rn1
             , MIN(rn) OVER(PARTITION BY     stid, edid) rn2
             , stid, edid
          FROM (SELECT rr, seq, bid
                     , ROW_NUMBER() OVER(ORDER BY rr, seq) rn
                     , FIRST_VALUE(bid) OVER(
                       PARTITION BY rr, gb ORDER BY seq) stid
                     , FIRST_VALUE(bid) OVER(
                       PARTITION BY rr, gb ORDER BY seq DESC) edid
                  FROM (SELECT rr, seq, bid
                             , CEIL(
                               COUNT(bid) OVER(PARTITION BY rr ORDER BY seq)
                               / 2) gb
                          FROM t
                        )
                )
        )
 ORDER BY rr, seq
;

마농(manon94)님이 2017-07-05 09:38에 작성한 댓글입니다.
이 댓글은 2017-07-05 11:02에 마지막으로 수정되었습니다.

SSID는

SID가 A,B,C,D 순차적으로 부여가 되었다면

SSID는 STID, EDID가 중복되는 구간에 대해 동일한 ID를 사용합니다.

 

R1의 SID가 C, D, E의 경우 STID, EDID가

R2와 중복되는 구간으로 SSID는 R1에서의 CC, DD, EE를 그대로 사용한것입니다.

 

DR_SEQ는 R1과 R2가 중복되는 구간과 아닌 구간을 순번으로 표현한것입니다.

R1의 SSID가 AA부터 BB까지는 중복되지 않는 구간에 첫순번을 부여하고

CC 부터 DD까지는 R1과 R2가 중복되어 두번째 순번을 부여하고

R1이 다시 중복되지 않은 구간이 나와 세번째 순번을 부여합니다.

R2의 SSID가 R1과 중복되어 두번째 순번을 부여한것이고

R2의 SSID가 중복되지 않아 네번째 순번을 부여한 것입니다.

 

제가 처음부터 질물과 예시를 잘못 적었네요

최종 수정하여 다시 올렸습니다

 

매번 감사합니다~

 

최상민(roice81)님이 2017-07-05 10:48에 작성한 댓글입니다.

SELECT rr, seq, bid
     , sid, ssid
     , stid, edid
     , SUM(v) OVER(ORDER BY ssid, rr, seq) dr_seq
  FROM (SELECT rr, seq, bid
             , CHR(64 + DENSE_RANK() OVER(ORDER BY rr, rn1)) sid
             , CHR(64 + DENSE_RANK() OVER(ORDER BY rn2)) ||
               CHR(64 + DENSE_RANK() OVER(ORDER BY rn2)) ssid
             , stid, edid
             , DECODE( LAG(cnt) OVER(ORDER BY rr, seq)
                     , cnt, 0, DECODE(rn, rn2, 1) ) v
          FROM (SELECT rr, seq, bid
                     , rn
                     , MIN(rn) OVER(PARTITION BY rr, stid, edid) rn1
                     , MIN(rn) OVER(PARTITION BY     stid, edid) rn2
                     , COUNT(DISTINCT rr) OVER(PARTITION BY stid, edid) cnt
                     , stid, edid
                  FROM (SELECT rr, seq, bid
                             , ROW_NUMBER() OVER(ORDER BY rr, seq) rn
                             , FIRST_VALUE(bid) OVER(
                               PARTITION BY rr, gb ORDER BY seq) stid
                             , FIRST_VALUE(bid) OVER(
                               PARTITION BY rr, gb ORDER BY seq DESC) edid
                          FROM (SELECT rr, seq, bid
                                     , CEIL( COUNT(bid) OVER(
                                             PARTITION BY rr ORDER BY seq)
                                           / 2) gb
                                  FROM t
                                )
                        )
                )
        )
 ORDER BY rr, seq
;

마농(manon94)님이 2017-07-05 13:30에 작성한 댓글입니다.

마농님 감사에하다는 말을 이제서야 하네요

출장중이라 컴터 접속을 못했네요

 

매번 감사합니다

 

그런데 CEIL이 이해가 가지 않는군요

그냥 반올림 한다는 걸로만 알고있는데.

어떻게 순번이 그렇게 변하는건지...

사실 저도 카운트를 몇번시도하다 111222 가 나와야하는데

112233이렇게 나오더라고요

 

시간되시면 CEIL 원리좀 부탁드립니다

 

사실 아직도 이해가 안가요~ ㅎㅎ

최상민(roice81)님이 2017-07-10 09:30에 작성한 댓글입니다.

어려운 쿼리입니다. 바로 이해하는게 더 이상하죠.
단계별로 쪼개어 실행해 보시고 결과 확인해 보세요.
1단계 Count(bid) Over 를 실행해 보시면 bid 가 있는 것만 누적 카운트 됩니다.
이 값을 2로 나누어 올림처리하면 2개씩 한 묶음이 됩니다.

마농(manon94)님이 2017-07-10 11:01에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
41443특정 컬럼의 데이터를 안보이게 할 수 있을까요? [2]
궁물노답
2017-07-10
5702
41442하나의 FK가 두 테이블을 참조하는 경우도 있나요?
조동건
2017-07-06
5631
41441그룹잡아서 소분류를 결과로 내고 싶습니다. [2]
봉달이
2017-07-05
5761
41440구간 순번 및 중복 확인 (내용 수정) [7]
최상민
2017-07-04
6279
41439세로로 된 데이터를 가로로 보여주기 [2]
webma
2017-07-03
6021
41436XML Type 컬럼에 조건 select 문의 [2]
양정표
2017-07-03
5382
41435Insert 작업 속도개선 요청 [2]
마카
2017-06-29
5500
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2022 DSN, All rights reserved.
작업시간: 0.064초, 이곳 서비스는
	PostgreSQL v14.2로 자료를 관리합니다