데이터베이스 질문과 답변
아래 input 을 이용하여 숫자대역이 중복되는 row를 추출하는 쿼리질문입니다. 인풋의 컬럼은 숫자이며, end1 은 항상 start1 보다 크거나 같습니다. 추출조건은 a,b 두개의 테이블로 self조인으로 한다고 가정할때, a.start1은 b.start1 과 b.end1 사이의 범위에 있거나 또는 a.end1는 b.start1 과 b.end1 사이의 범위에 있어야 합니다. 결국은 숫자대역대가 중복되는 모든 row를 distinct 하지않고, 추출하여, 나중에 경계선이 중복되지않게 수정하려는 의도입니다. 쉽게 설명드리면 중복되는 ip대역대를 모두 검출하는 의도입니다. 쿼리문을 어떻게 작성해야 될까요? 고수님들의 답변기다리겟습니다. ^^ -- input SELECT 100 AS START1, 250 AS END1 FROM DUAL UNION ALL SELECT 100 AS START1, 100 AS END1 FROM DUAL UNION ALL SELECT 100 AS START1, 200 AS END1 FROM DUAL UNION ALL SELECT 250 AS START1, 250 AS END1 FROM DUAL UNION ALL SELECT 250 AS START1, 255 AS END1 FROM DUAL UNION ALL SELECT 90 AS START1, 120 AS END1 FROM DUAL UNION ALL SELECT 150 AS START1, 255 AS END1 FROM DUAL UNION ALL SELECT 50 AS START1, 50 AS END1 FROM DUAL UNION ALL SELECT 50 AS START1, 50 AS END1 FROM DUAL UNION ALL SELECT 1 AS START1, 49 AS END1 FROM DUAL UNION ALL SELECT 59 AS START1, 59 AS END1 FROM DUAL UNION ALL SELECT 60 AS START1, 60 AS END1 FROM DUAL UNION ALL SELECT 61 AS START1, 70 AS END1 FROM DUAL UNION ALL SELECT 256 AS START1, 300 AS END1 FROM DUAL ; -- output START1 END1 100 250 100 100 100 200 250 250 250 255 90 120 150 255 50 50 50 50
by 마농
[2017.05.19 08:14:24]
생각하시는 조건은 오류가 있습니다.
a 의 범위가 b 의 범위를 포함하는 경우엔 누락됩니다.
범위 검색은 시작과 종료를 서료 교차하여 비교하면 간단합니다.
WITH t AS
(
SELECT 1 idx, 100 start1, 250 end1 FROM dual
UNION ALL SELECT 2, 100, 100 FROM dual
UNION ALL SELECT 3, 100, 200 FROM dual
UNION ALL SELECT 4, 250, 250 FROM dual
UNION ALL SELECT 5, 250, 255 FROM dual
UNION ALL SELECT 6, 90, 120 FROM dual
UNION ALL SELECT 7, 150, 255 FROM dual
UNION ALL SELECT 8, 50, 50 FROM dual
UNION ALL SELECT 9, 50, 50 FROM dual
UNION ALL SELECT 10, 1, 49 FROM dual
UNION ALL SELECT 11, 59, 59 FROM dual
UNION ALL SELECT 12, 60, 60 FROM dual
UNION ALL SELECT 13, 61, 70 FROM dual
UNION ALL SELECT 14, 256, 300 FROM dual
)
SELECT *
FROM t a
WHERE EXISTS (SELECT 1
FROM t b
WHERE b.idx != a.idx
AND b.start1 <= a.end1
AND b.end1 >= a.start1
)
ORDER BY idx
;
by 마농
[2017.05.19 08:26:01]
참고로 중복된 IP 대역을 하나로 통합하는 구문 올립니다.
(+ 1) 부분은 중복은 아니지만 인접한 대역도 하나로 합치기 위함입니다.
SELECT grp
, MIN(start1) start1
, MAX(end1) end1
FROM (SELECT idx, start1, end1
, SUM(flag) OVER(ORDER BY start1, end1, idx) grp
FROM (SELECT idx, start1, end1
, CASE WHEN MAX(end1) OVER(ORDER BY start1, end1, idx
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
) + 1 >= start1
THEN 0 ELSE 1 END flag
FROM t
)
)
GROUP BY grp
ORDER BY grp
;
by 궁금이
[2017.05.19 11:39:10]
역시 천재.^^
) 버튼을 클릭하여 작성 하시면 됩니다.