[Tibero] AGGR_CONCAT, REPLACE, CHR 함수를 이용한 쿼리 만들기[펌]

[Tibero] AGGR_CONCAT, REPLACE, CHR 함수를 이용한 쿼리 만들기[펌]

 

AGGR_CONCAT, REPLACE, CHR 함수를 이용한 쿼리 만들기

AGGR_CONCAT, REPLACE, CHR 함수로 테이블 내의 데이터를 원하는대로 나열할 수 있다.

 

AGGR_CONCAT을 이용하면 여러 행의 데이터를 하나의 컬럼으로 모아서 보여줄 수 있다.

 

SELECT AGGR\_CONCAT(LEVEL,',')  
FROM DUAL  
CONNECT BY LEVEL < 10  

위 쿼리를 수행하면 다음과 같이 한 행에 모든 행의 값을 ,로 구분해서 반환해준다.

 

AGGR_CONCAT(LEVEL,',')

1,2,3,4,5,6,7,8,9

 

1 row selected.
USER_TAB_COLUMNS와 같은 뷰에 활용하면 효과적인데, 특정 DATA_TYPE만 선택해서 쿼리를 생성하거나 DATA_TYPE에 따라 별도의 처리를 해줄 수 있다.

 

DBA_DATA_FILES 뷰에서 DATA_TYPE이 숫자인 컬럼만 골라서 조회하는 쿼리를 AGGR_CONCAT 함수를 이용해서 만들어낼 수 있다. 다음과 같이 조회하면

SELECT 'SELECT ' ||  
AGGR\_CONCAT(COLUMN\_NAME,',') ||  
' FROM DBA\_DATA\_FILES'  
FROM  
DBA\_TAB\_COLUMNS  
WHERE TABLE\_NAME='DBA\_DATA\_FILES'  
AND DATA\_TYPE='NUMBER'  

아래와 같은 결과를 받을 수 있다.

'SELECT'||AGGR_CONCAT(COLUMN_NAME,',')||'FROMDBA_DATA_FILES'

SELECT FILE\_ID,BYTES,BLOCKS,RELATIVE\_FNO,MAXBYTES,MAXBLOCKS,INCREMENT\_BY FROM DB  
A\_DATA\_FILES

1 row selected.
linesize가 충분하지 않다면, 주어진 linesize 만큼 개행이 되어 추가적인 수작업이 필요할 수 있는데 이때 REPLACE와 CHR 함수를 이용하면 적당히 처리하기 쉽게 개행을 시킬 수 있다.

다음과 같이 ','를 ','||CHR(10)||CHR(13) 으로 변경시켜주면 ,를 ,\n으로 변경시킴으로써, 의도적으로 개행을 시켜 linesize로 인해 문자열이 망가지는 문제를 해결할 수 있다.

SELECT 'SELECT ' ||
        REPLACE(
                AGGR_CONCAT(COLUMN_NAME,',')
                ,',',','||CHR(10)||CHR(13)
        )||
        ' FROM DBA_DATA_FILES'
FROM
        DBA_TAB_COLUMNS
WHERE TABLE_NAME='DBA_DATA_FILES'
        AND DATA_TYPE='NUMBER'

위 쿼리를 수행하면 다음과 같은 결과를 반환받는다.

'SELECT'||REPLACE(AGGR_CONCAT(COLUMN_NAME,','),',',','||CHR(10)||CHR(13))||'FROM
--------------------------------------------------------------------------------
SELECT FILE_ID,
BYTES,
BLOCKS,
RELATIVE_FNO,
MAXBYTES,
MAXBLOCKS,
INCREMENT_BY FROM DBA_DATA_FILES

1 row selected.

자주 사용하는 CHR

• CHR(9)
  • \t(탭)
• CHR(10)
  • \n(줄바꿈)
• CHR(13)
  • \r(행의 시작)
• CHR(38)
  • &(앰퍼샌드)
• CHR(39)
  • '(따옴표)
• CHR(44)
  • ,(쉼표)

Oracle에서는LISTAGG,Impala에서는GROUP_CONCAT함수를 이용해서AGGR_CONCAT과 같은 기능을 할 수 있다.

 

 

출처 : https://oboki.net/workspace/database/tibero/tibero-aggr_concat-replace-chr-%ED%95%A8%EC%88%98%EB%A5%BC-%EC%9D%B4%EC%9A%A9%ED%95%9C-%EC%BF%BC%EB%A6%AC-%EB%A7%8C%EB%93%A4%EA%B8%B0/