XMLAgg 함수 사용시 값 중복제거하기, 연결된 값의 개수 구하기

LISTAGG에 비해서 XMLAgg는 덜 사용되어서 그런가, XMLAgg에 대한 중복제거 관련한 내용을 쉽게 찾을 수 없었다.

중복제거할 수 있는 아주 심플하고 명확한 포스팅을 바랬는데 아쉽게도 LISTAGG만큼 많지 않았다.

 

그래서 작성한 쇼츠 블로그.

XMLAgg 중복제거

REGEXP_REPLACE(LTRIM(XMLAgg(XMLElement(x,'구분자','컬럼명').EXTRACT('//text()').GETCLOBVAL(),'구분자'), '([^;]+)(;\1)*(;|$)','\1\3')

 

추가적으로, 데이터 연결했을 때 분석용으로 몇개의 데이터가 들어갔는지 확인할 때 썼던 쿼리이다.

XMLAgg로 연결한 값의 개수 구하기

((LENGTH(REGEXP_REPLACE(LTRIM(XMLAgg(XMLElement(x,'구분자','컬럼명').EXTRACT('//text()').GETCLOBVAL(),'구분자'), '([^;]+)(;\1)*(;|$)','\1\3'))+'구분자 바이트 크기')/'연결하는 코드값 크기 + 구분자 크기')

XMLAgg로 연결한 값의 크기를 LENGTH 함수로 구한 다음, 연결하는 값의 코드 크기로 나누는.. 노가다성 쿼리긴 하다.

* 데이터가 특정코드로서 바이트 크기가 일정했을 때만 유효한 방법

 

예를 들어, 단말코드가 4자리로 고정되어 있다고 가정했을 때 사용법이다.

DEV_CD	DESCRIPTION
A100	모델1
A101	모델2
B100	모델3

((LENGTH(REGEXP_REPLACE(LTRIM(XMLAgg(XMLElement(x,'/',DEV_CD).EXTRACT('//text()').GETCLOBVAL(),'/'), '([^;]+)(;\1)*(;|$)','\1\3'))+'1')/5)

구분자 : '/'

1. 마지막 데이터를 연결할 때는 구분자를 넣지 않으므로 총 LENGTH 구한 것에 +1을 해준다.

2. 그 다음 단말코드 길이 + 구분자 길이인 '5'로 나눠준다.

 

그럼, 몇개의 데이터가 연결됐는지 확인할 수 있다.

XMLAgg를 이용해서 연결한 데이터	개수
A100/A101/B100	3

 

데이터 개수제한 문제가 있을때 분석용으로만 데이터 추출용도로 작성한 쿼리라... 솔직히 성능적으로 매우 문제가 많을거지만..