오라클 버전으로 WITHIN GROUP 대신 XMLAGG 사용하게 된 케이스

XMLAGG

XMLAGG 함수는 Oracle에서 사용되는 XML을 생성하기 위한 함수 중 하나입니다.

이 함수는 여러 행의 데이터를 그룹화하여 XML 형식으로 변환합니다. GROUP BY 절과 함께 사용되어 여러 행의 데이터를 그룹화하고, 그룹화된 데이터를 XML 형식으로 결합합니다.

 

주로 다음과 같은 형태로 사용됩니다:

SELECT group_column
    , XMLAGG(XMLElement("element_name", column_to_convert) ORDER BY ordering_column).getClobVal() 
      AS xml_result 
FROM your_table 
GROUP BY group_column;

• group_column: 데이터를 그룹화할 기준 열입니다.
• XMLElement("element_name", column_to_convert): XML 엘리먼트를 생성하는 함수입니다. "element_name"은 생성될 XML 엘리먼트의 이름이고, column_to_convert은 XML 엘리먼트의 값으로 사용될 열입니다.
• ORDER BY ordering_column: XML 엘리먼트를 정렬하기 위한 옵션입니다.
• xml_result: XMLAGG 함수의 결과물로, 그룹화된 데이터를 XML 형식으로 변환한 결과를 나타냅니다.

예를 들어, 다음과 같은 테이블이 있다고 가정해보겠습니다.

CREATE TABLE my_table ( category VARCHAR2(50), item VARCHAR2(50) );

그리고 이 테이블에 다음과 같은 데이터가 들어 있다고 가정해봅시다.

category | item ---------|----- Fruit | Apple Fruit | Orange Fruit | Banana Vegetable| Tomato Vegetable| Potato

728x90

 

 

XMLAGG 함수를 사용하여 category를 기준으로 데이터를 그룹화하고, 각 카테고리별로 item을 XML 형식으로 변환한다면:

SELECT category, XMLAGG(XMLElement("item", item) ORDER BY item).getClobVal() AS xml_result 
FROM my_table GROUP BY category;

 

 

이 쿼리의 결과로는 다음과 같은 XML이 생성될 것입니다:

<Fruit> 
    <item>Apple</item> 
    <item>Banana</item> 
    <item>Orange</item> 
</Fruit> 
<Vegetable> 
    <item>Potato</item> 
    <item>Tomato</item>
</Vegetable>

 

이렇게 생성된 XML은 각각의 카테고리에 해당하는 아이템을 그룹화하여 XML 형식으로 출력됩니다.

 

---

다양한 예시

LISTAGG 함수가 도입되기 전에 동일 기능을 구현하기 위해 다양한 기법들이 사용되었다. 정리해보자.

아래와 같이 데이터를 생성하자.

CREATE TABLE t1 (c1 NUMBER(1), c2 VARCHAR2(2));  
INSERT INTO t1 VALUES (1, '01'); 
INSERT INTO t1 VALUES (2, '02'); 
INSERT INTO t1 VALUES (2, '03'); 
INSERT INTO t1 VALUES (3, '04'); 
INSERT INTO t1 VALUES (3, '04'); 
INSERT INTO t1 VALUES (3, '05'); 
INSERT INTO t1 VALUES (3, '06');

 

LISTAGG, 11g

① 11g를 사용한다면 LISTAGG 함수를 사용하면 된다. 집계함수(1번)와 분석함수(2번) 형태로 사용이 가능하다.

-- 1 
SELECT   a.c1
      ,  LISTAGG (a.c2, ',') WITHIN GROUP (ORDER BY a.c2) AS c2     
FROM t1 a GROUP BY a.c1;   

C1 C2           
--- ------------   
1 01             
2 02,03          
3 04,04,05,06   

3 rows selected.  


-- 2 
SELECT a.c1
     ,        LISTAGG (a.c2, ',') WITHIN GROUP (ORDER BY a.c2) OVER (PARTITION BY A.c1) AS c2   
FROM t1 a;   

C1 C2           
--- ------------   
1 01             
2 02,03          
2 02,03          
3 04,04,05,06    
3 04,04,05,06    
3 04,04,05,06    
3 04,04,05,06   

7 rows selected.

 

WM_CONCAT

② WM_CONCAT 함수는 WMSYS 유저가 내부적으로 사용한다. (SQL Reference에 없다...--;) LISTAGG보다 성능은 떨어지지만 추가 기능(DISTINCT 구문, 분석함수 누적, KEEP 절)을 지원한다. 4번 방식을 이용하면 정렬도 가능하다.

-- 1 
SELECT   a.c1
    ,          wmsys.wm_concat (a.c2) AS c2     
FROM t1 a 
GROUP BY a.c1;   


C1 C2           
--- ------------   
1 01             
2 02,03          
3 04,06,05,04   

3 rows selected.  


-- 2 
SELECT   a.c1
    ,    wmsys.wm_concat (DISTINCT a.c2) AS c2     
FROM t1 a 
GROUP BY a.c1;   

C1 C2           
--- ------------   
1 01             
2 02,03          
3 04,05,06      

3 rows selected.  


-- 3 
SELECT a.c1
    ,  wmsys.wm_concat (a.c2) OVER (ORDER BY a.c2) AS c2   
FROM t1 a;   

C1 C2                     
--- ----------------------   
1 01                       
2 01,02                    
2 01,02,03                 
3 01,02,03,04,04           
3 01,02,03,04,04           
3 01,02,03,04,04,05        
3 01,02,03,04,04,05,06    

7 rows selected.  


-- 4 
SELECT   a.c1
    ,    MAX (CAST (a.c2 AS VARCHAR2 (4000))) as c2     
FROM (
        SELECT a.c1                  
            , wmsys.wm_concat (a.c2) OVER (PARTITION BY a.c1 ORDER BY a.c2) AS c2             
        FROM t1 a
     ) a 
GROUP BY a.c1;   

C1 C2           
--- ------------   
1 01             
2 02,03          
3 04,04,05,06   

3 rows selected.  
            

-- 5 
SELECT   a.c1
      ,  wmsys.wm_concat (a.c2) KEEP (DENSE_RANK FIRST ORDER BY a.c2 ) AS c2     
FROM t1 a 
GROUP BY a.c1;   

C1 C2           
--- ------------   
1 01             
2 02             
3 04,04         
          
3 rows selected.

 

XMLAGG

③ 10g에서는 XMLAGG 함수를 사용해도 된다. 

SELECT   a.c1
    ,    SUBSTR (XMLAGG (XMLELEMENT (a, ',', a.c2) ORDER BY a.c2).EXTRACT ('//text()'), 2) AS c2     
    FROM t1 a 
    GROUP BY a.c1;   
    
    
    
    C1 C2           
    --- ------------   
    1 01             
    2 02,03          
    3 04,04,05,06   
    
    3 rows selected.

 

MODEL

④ 후임자를 괴롭히고 싶다면 MODEL 절을 사용해도 된다...--;

SELECT   a.c1
    ,    RTRIM (a.c2, ',') as c2     
FROM (
        SELECT c1
            ,  c2
            ,  rn             
        FROM t1 a            
       MODEL PARTITION BY (a.c1)                  
       DIMENSION BY (ROW_NUMBER() OVER (PARTITION BY a.c1 ORDER BY a.c2) AS rn )                  
       MEASURES (CAST (a.c2 AS VARCHAR2(4000)) AS c2)                  
       RULES (c2[ANY] ORDER BY rn DESC = c2[CV()] || ',' || c2[CV()+1])
    ) a    
WHERE a.rn = 1 ORDER BY a.c1;   


C1 C2           
--- ------------   
1 01             
2 02,03          

3 04,04,05,06   

3 rows selected.

 

ROW_NUMBER & SYS_CONNECT_BY_PATH

⑤ 9i에서는 전통적 방식인 ROW_NUMBER와 SYS_CONNECT_BY_PATH 조합을 사용하면 된다.

SELECT     a.c1,            
    SUBSTR (MAX (SYS_CONNECT_BY_PATH (a.c2, ',')), 2) AS c2       
FROM (
        SELECT a.c1
           , a.c2
           , ROW_NUMBER () OVER (PARTITION BY a.c1 ORDER BY a.c2) AS rn               
        FROM t1 a
      ) a 
START WITH a.rn = 1 
CONNECT BY a.c1 = PRIOR a.c1        
    AND a.rn - 1 = PRIOR a.rn   
GROUP BY a.c1   ORDER BY a.c1;   

C1 C2           
--- ------------   
1 01             
2 02,03          
3 04,04,05,06   

3 rows selected.