아래 테이블에 각각 id, bm의 fr, to값입니다. id, bm을 이용하여 구간이 중복되는값을 알로 싶습니다.
구간값은 숫자, 한글(가나다), 영문(ABC)입니다.
id | fr | to | bm |
서울 | 101 | 103 | 보람 |
서울 | 101 | 102 | 보람 |
서울 | 101 | 101 | 보람 |
경기 | 가 | 다 | 보람 |
경기 | 가 | 다 | 보람 |
강원 | A | B | 보람 |
강원 | A | C | 보람 |
부산 | 가 | 라 | 보람 |
부산 | 가 | 다 | 보람 |
부산 | 나 | 나 | 보람 |
부산 | 가 | 가 | 보람 |
결과 테이블
id | fr | to | bm | du_fr | du_to |
서울 | 101 | 103 | 보람 | 101 | 102 |
서울 | 101 | 102 | 보람 | 101 | 102 |
서울 | 101 | 101 | 보람 | 101 | 101 |
경기 | 가 | 다 | 보람 | 가 | 다 |
경기 | 가 | 다 | 보람 | 가 | 다 |
강원 | A | B | 보람 | A | B |
강원 | A | C | 보람 | A | B |
부산 | 가 | 라 | 보람 | 가 | 다 |
부산 | 가 | 다 | 보람 | 가 | 다 |
부산 | 나 | 나 | 보람 | 나 | 나 |
부산 | 가 | 가 | 보람 | 가 | 가 |
1. 중복 구간이 두개 이상 나누어 지는 경우엔 여러줄로 표시하나요?
- (a ~ z), (a ~ b), (x ~ y) 인 경우 어떻게 표시해야 하는지?
- (a ~ z), (a ~ b), (c ~ d), (x ~ y) 인 경우 어떻게 표시해야 하는지?
2. 레코드 식별자가 보이지 않네요?
- (가, 다) 의 중복자료 식별이 안되네요.
- 레코드 식별자가 필요해 보입니다.
3. 구간이 숫자와 문자가 공존하네요?
- 문자는 1자리 고정인가요? 2자리 이상은 없겟죠?
- 숫자는 3자리 고정인가요? 2자리나 4자리는 없는지?
- 혹시 90 ~ 100 이런식의 자리수가 바뀌는 자료는 없는지?
1. 중복 구간이 두개 이상 나누어 지는 경우엔 여러줄로 표시하나요?
- (a ~ z), (a ~ b), (x ~ y) 인 경우 어떻게 표시해야 하는지?
- (a ~ z), (a ~ b), (c ~ d), (x ~ y) 인 경우 어떻게 표시해야 하는지?
답변 : 중복 구간이 2개 이상인 경우는 없습니다.
2. 레코드 식별자가 보이지 않네요?
- (가, 다) 의 중복자료 식별이 안되네요.
- 레코드 식별자가 필요해 보입니다.
답변 : 가~다 의 형식이기 때문에 가~다 모두 중복입니다.
3. 구간이 숫자와 문자가 공존하네요?
- 문자는 1자리 고정인가요? 2자리 이상은 없겟죠?
- 숫자는 3자리 고정인가요? 2자리나 4자리는 없는지?
- 혹시 90 ~ 100 이런식의 자리수가 바뀌는 자료는 없는지?
답변 : 문자는 1자리 고정이고요 숫자는 1~999까지 자리수가 바뀌는 자료도 있습니다.
비교시 자기자신을 제외시키려면 식별자가 있어야 합니다.(pk 가 없다면? rownum 이나 rowid 이용)
구간 중복 체크는 시작과 종료를 서로 교차하여 비교해야 합니다.
문자로 저장된 가변자리수의 숫자 비교를 위해 LPAD 사용후 LTRIM 했습니다.
WITH t AS ( SELECT 1 pk, '서울' id, '101' fr_v, '103' to_v, '보람' bm FROM dual UNION ALL SELECT 2, '서울', '101', '102', '보람' FROM dual UNION ALL SELECT 3, '서울', '101', '101', '보람' FROM dual UNION ALL SELECT 4, '경기', '가' , '다' , '보람' FROM dual UNION ALL SELECT 5, '경기', '가' , '다' , '보람' FROM dual UNION ALL SELECT 6, '강원', 'A' , 'B' , '보람' FROM dual UNION ALL SELECT 7, '강원', 'A' , 'C' , '보람' FROM dual UNION ALL SELECT 8, '부산', '가' , '라' , '보람' FROM dual UNION ALL SELECT 9, '부산', '가' , '다' , '보람' FROM dual UNION ALL SELECT 10, '부산', '나' , '나' , '보람' FROM dual UNION ALL SELECT 11, '부산', '가' , '가' , '보람' FROM dual UNION ALL SELECT 12, '울산', '10' , '110', '보람' FROM dual UNION ALL SELECT 13, '울산', '95' , '105', '보람' FROM dual UNION ALL SELECT 14, '일산', '10' , '50' , '보람' FROM dual UNION ALL SELECT 15, '일산', '5' , '9' , '보람' FROM dual ) SELECT a.pk, a.id, a.bm, a.fr_v, a.to_v , LTRIM(GREATEST(LPAD(a.fr_v, 3, '0'), MIN(LPAD(b.fr_v, 3, '0'))), '0') du_fr_v , LTRIM( LEAST(LPAD(a.to_v, 3, '0'), MAX(LPAD(b.to_v, 3, '0'))), '0') du_to_v , LISTAGG(b.pk, ',') WITHIN GROUP(ORDER BY b.pk) du_list FROM t a , t b WHERE a.pk != b.pk(+) -- 자기자신 제외 AND a.id = b.id(+) AND a.bm = b.bm(+) AND LPAD(a.fr_v, 3, '0') <= LPAD(b.to_v(+), 3, '0') AND LPAD(a.to_v, 3, '0') >= LPAD(b.fr_v(+), 3, '0') GROUP BY a.pk, a.id, a.bm, a.fr_v, a.to_v ORDER BY a.pk ;