Oracle 的行列转换listagg,wm_concat和regexp_substr

博客首页 » Oracle 的行列转换listagg,wm_concat和regexp_substr

发布于 09 Sep 2013 07:11
标签 blog
关于Oracle 的行列转换，网上有许多讨论。到Oracle 11g以后官方的listagg出现了。它带有灵活的group和over语法，使得行列转换变得容易。除了listagg以外，传统的wm_concat也可以胜任行列转换，而regexp_substr在加上connect以后可以成为这两个函数的反函数。

LISTAGG

在oracle 11可以使用LISTAGG 函数
Oracle 11g Release 2引入了LISTAGG 函数，使得聚集连接字符串变得很容易。并且允许使用我们指定连接串中的字段顺序。使用LISTAGG如下：

COLUMN employees FORMAT A50
SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
GROUP BY deptno;
    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

先看示例代码：

with temp as(  
  select 'China' nation ,'Guangzhou' city from dual union all  
  select 'China' nation ,'Shanghai' city from dual union all  
  select 'China' nation ,'Beijing' city from dual union all  
  select 'USA' nation ,'New York' city from dual union all  
  select 'USA' nation ,'Bostom' city from dual union all  
  select 'Japan' nation ,'Tokyo' city from dual   
)  
select nation,listagg(city,',') within GROUP (order by city)  
from temp  
group by nation

这是最基础的用法：
LISTAGG(XXX,XXX) WITHIN GROUP( ORDER BY XXX)

用法就像聚合函数一样，通过Group by语句，把每个Group的一个字段，拼接起来。
非常方便。

同样是聚合函数，还有一个高级用法：
就是over(partition by XXX)
也就是说，在你不实用Group by语句时候，也可以使用LISTAGG函数：

with temp as(  
  select 500 population, 'China' nation ,'Guangzhou' city from dual union all  
  select 1500 population, 'China' nation ,'Shanghai' city from dual union all  
  select 500 population, 'China' nation ,'Beijing' city from dual union all  
  select 1000 population, 'USA' nation ,'New York' city from dual union all  
  select 500 population, 'USA' nation ,'Bostom' city from dual union all  
  select 500 population, 'Japan' nation ,'Tokyo' city from dual   
)  
select population,  
       nation,  
       city,  
       listagg(city,',') within GROUP (order by city) over (partition by nation) rank  
from temp

总结：LISTAGG()把它当作SUM()函数来使用就可以了。

WM_CONCAT

如果用的是10.2.0.4及以前的版本，可以直接使用WM_CONCAT，而10.2.0.5及以后的版本需要多加一个TO_CHAR

Oracle对表的数据拆分和合并

在日常数据库操作中，我们经常对数据进行拆分和合并，下面让我们来看看如何实现。

假设有表TEMP_TABLE(ID, NAME, CODE)，数据如下：

select t.* from temp_table t;  
ID NAME CODE  www.2cto.com  
1 A 10
2 B 20
3 C 30
4 A 40
5 A 50
6 A|B|C 60

我们将NAME=A的数据合并，使用Oracle函数WM_CONCAT()，SQL及结果如下：

select t.name,to_char(wm_concat(t.code))   
 from temp_table t   
where name='A'   
group by t.name;  
NAME CODE
A 10,40,50

因为10.2.0.5及以后版本，WM_CONCAT返回结果不再是VARCHAR2而是CLOB类型，我们可以用TO_CHAR函数转换成字符串，也可以用REPLACE函数将结果中的‘,’转变成我们需要的其他分隔符。
再来看看如何将一条记录通过指定的分隔符拆分成多条记录，用上图中的ID=6作为例子，NAME=A|B|C，我们将拆分成三条记录，SQL和结果如下：

SELECT id, REGEXP_SUBSTR(name, '[^|]+', 1, rn) b  
  FROM temp_table, (SELECT ROWNUM rn FROM DUAL CONNECT BY ROWNUM <= 50)  
 WHERE REGEXP_SUBSTR(name, '[^|]+', 1, rn) IS NOT NULL;  
 
ID NAME 
1 A
2 B
3 C
4 A
5 A
6 A
6 B
6 C

其中，REGEXP_SUBSTR就是我们要用到的拆分函数，以‘|’为分隔符拆分字段NAME，我们看到最后三行，将原本NAME=A|B|C拆分成了三条记录。

需要注意的是，在FROM关键字后面(SELECT ROWNUM rn FROM DUAL CONNECT BY ROWNUM <= 50)，其中50是我们预估的最大拆分数据的条数，在此例中我们使用3就够了。

对于WM_CONCAT，因为是Workspace Manager的内部函数，所以eygle推荐用以下SQL代替。

SQL> create or replace TYPE en_concat_im
  2  AUTHID CURRENT_USER AS OBJECT
  3  (
  4    CURR_STR VARCHAR2(32767),
  5    STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT en_concat_im) RETURN NUMBER,
  6    MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT en_concat_im,
  7              P1 IN VARCHAR2) RETURN NUMBER,
  8    MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN en_concat_im,
  9                        RETURNVALUE OUT VARCHAR2,
 10                        FLAGS IN NUMBER)
 11                RETURN NUMBER,
 12    MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT en_concat_im,
 13               SCTX2 IN  en_concat_im) RETURN NUMBER
 14  );
 15  /
 
Type created.
 
SQL> 
SQL> create or replace TYPE BODY en_concat_im
  2  IS
  3    STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT en_concat_im)
  4    RETURN NUMBER
  5    IS
  6    BEGIN
  7       SCTX := en_concat_im(NULL) ;
  8       RETURN ODCICONST.SUCCESS;
  9    END;
 10    MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT en_concat_im,
 11             P1 IN VARCHAR2)
 12    RETURN NUMBER
 13    IS
 14    BEGIN
 15       IF(CURR_STR IS NOT NULL) THEN
 16         CURR_STR := CURR_STR || ';' || P1;
 17       ELSE
 18         CURR_STR := P1;
 19       END IF;
 20       RETURN ODCICONST.SUCCESS;
 21    END;
 22    MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN en_concat_im,
 23                        RETURNVALUE OUT VARCHAR2,
 24                        FLAGS IN NUMBER)
 25       RETURN NUMBER
 26    IS
 27    BEGIN
 28       RETURNVALUE := CURR_STR ;
 29       RETURN ODCICONST.SUCCESS;
 30    END;
 31    MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT en_concat_im,
 32                      SCTX2 IN en_concat_im)
 33    RETURN NUMBER
 34    IS
 35    BEGIN
 36       IF(SCTX2.CURR_STR IS NOT NULL) THEN
 37         SELF.CURR_STR := SELF.CURR_STR || ';' || SCTX2.CURR_STR ;
 38       END IF;
 39       RETURN ODCICONST.SUCCESS;
 40    END;
 41  END;
 42  /
 
Type body created.