转行函数listagg的增强史侵立删

最新推荐文章于 2023-06-25 14:17:10 发布

心神沫沫

最新推荐文章于 2023-06-25 14:17:10 发布

阅读量556

点赞数

分类专栏：数据库

原文链接：https://blog.csdn.net/Hehuyi_In/article/details/90521478

版权

数据库专栏收录该内容

32 篇文章 1 订阅

订阅专栏

12.1以前
Oracle 12c之前有一个内部函数wm_concat，用法类似

SELECT u.userid, u.usercode, u.username,wm_concat(to_char(r.rolename))
FROM S_U_R ur, S_U u, S_R r
WHERE ur.userid = u.userid
AND ur.roleid = r.roleid and u.usercode = 'rf.test'
group by u.userid, u.usercode, u.username

按u.userid, u.usercode, u.username字段进行分组，并把分组后原本应该按列显示的rolename转为按行显示

12.1
从12.1开始，Oracle取消了wm_concat内部函数。其实11gR2开始，oracle就引入了listagg函数，不过Listagg函数输出是有长度限制的(varchar2(4000))，如果超过该长度会收到报错ORA-01489: result of string concatenation is too long

本文中的示例依赖于以下测试表

--DROP TABLE emp PURGE;

CREATE TABLE emp (
  empno    NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY,
  ename    VARCHAR2(10),
  job      VARCHAR2(9),
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2)
);

INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
我们看一个简单的LISTAGG函数示例，为每个部门生成一个以逗号分隔的员工列表。

COLUMN employees FORMAT A40
SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
GROUP BY deptno
ORDER BY deptno;

    DEPTNO EMPLOYEES
---------- ----------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
如果串联导致字符串长于LISTAGG函数的返回数据类型，则会产生“ORA-01489”错误。在以下示例中，我们使用CROSS JOIN来强制进行大型聚合。

COLUMN employees FORMAT A40
SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
       CROSS JOIN (SELECT level FROM dual CONNECT BY level <= 1000)
WHERE  deptno = 30
GROUP BY deptno
ORDER BY deptno;

FROM   emp
       *
ERROR at line 2:
ORA-01489: result of string concatenation is too long
如果转换后的长度确实超过4000应该怎么办，网上找到了几种解决方法：

use any of these solutions:

1 - xmlagg function

2 - You can also use the stragg function in lieu of listagg.

3 - And, of course, you can build your own PL/SQL concatenation function that returns type CLOB.

找一个rolename最长的用户试试

SELECT u.userid, u.usercode,u.username,
rtrim(xmlagg(xmlelement(e,to_char(r.rolename),',').extract('//text()') order by u.userid, u.usercode, u.username).GetClobVal(),',')
FROM S_U_R ur, S_U u, S_R r
WHERE ur.userid = u.userid
AND ur.roleid = r.roleid
having u.usercode = 'longest'
group by u.userid, u.usercode, u.username

12.2如何处理溢出错误
在12.2中，我们可以添加ON OVERFLOW TRUNCATE子句来优雅地处理溢出错误。默认情况下，truncate文字是省略号（'...'），并包含溢出字符的计数。

COLUMN employees FORMAT A70
SELECT deptno, LISTAGG(ename, ',' ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
       CROSS JOIN (SELECT level FROM dual CONNECT BY level <= 1000)
WHERE  deptno = 30
GROUP BY deptno
ORDER BY deptno;

    DEPTNO EMPLOYEES
---------- ----------------------------------------------------------------------
        30 ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLE
           N,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,AL
           LEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,
[removed]
           N,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,AL
           LEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,...(5339)
如果我们不想使用省略号，我们可以指定我们自己的truncate文字。在下面的示例中，我们使用了截断文字'~~~'。

COLUMN employees FORMAT A70
SELECT deptno, LISTAGG(ename, ',' ON OVERFLOW TRUNCATE '~~~') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
       CROSS JOIN (SELECT level FROM dual CONNECT BY level <= 1000)
WHERE  deptno = 30
GROUP BY deptno
ORDER BY deptno;

    DEPTNO EMPLOYEES
---------- ----------------------------------------------------------------------
        30 ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLE
           N,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,AL
           LEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,
[removed]
           N,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,AL
           LEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,~~~(5339)
我们也可以通过添加来省略计数WITHOUT COUNT。默认值相当于显式使用WITH COUNT。

COLUMN employees FORMAT A70
SELECT deptno, LISTAGG(ename, ',' ON OVERFLOW TRUNCATE '...' WITHOUT COUNT) WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
       CROSS JOIN (SELECT level FROM dual CONNECT BY level <= 1000)
WHERE  deptno = 30
GROUP BY deptno
ORDER BY deptno;

    DEPTNO EMPLOYEES
---------- ----------------------------------------------------------------------
        30 ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLE
           N,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,AL
           LEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,
[removed]
           N,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,AL
           LEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,...
https://oracle-base.com/articles/12c/listagg-function-enhancements-12cr2

19c中的LISTAGG DISTINCT
LISTAGG函数是在Oracle 11gR2中引入的，使字符串聚合更简单。在Oracle 12.2中，它扩展为包括溢出错误处理。Oracle 19c LISTAGG增加了通过包含Distinct关键字从结果中删除重复项的功能。

还是用上面那个示例表

我们在部门10中添加一些名为“MILLER”的额外人员，以便在聚合列表中为我们提供重复项。

INSERT INTO emp VALUES (9998,'MILLER','ANALYST',7782,to_date('23-1-1982','dd-mm-yyyy'),1600,NULL,10);
INSERT INTO emp VALUES (9999,'MILLER','MANADER',7782,to_date('23-1-1982','dd-mm-yyyy'),1500,NULL,10);
COMMIT;
正如预期的那样，我们现在在部门10中看到多个名为“MILLER”的条目

如果我们想删除重复项，我们该怎么办？

解决方案：19c之前
我们可以通过多种方式解决这个问题。在下面的示例中，我们使用ROW_NUMBER分析函数删除重复项，然后使用常规LISTAGG函数来聚合数据。

COLUMN employees FORMAT A40,
SELECT e2.deptno, LISTAGG(e2.ename, ',') WITHIN GROUP (ORDER BY e2.ename) AS employees
FROM (SELECT e.*,ROW_NUMBER() OVER (PARTITION BY e.ename ORDER BY e.empno) AS myrank FROM emp e) e2
WHERE  e2.myrank = 1
GROUP BY e2.deptno
ORDER BY e2.deptno;

    DEPTNO EMPLOYEES
---------- ----------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
或者，我们可以使用DISTINCT在内联视图中删除重复的行，然后使用传统的LISTAGG函数来聚合数据

COLUMN employees FORMAT A40
SELECT e2.deptno, LISTAGG(e2.ename, ',') WITHIN GROUP (ORDER BY e2.ename) AS employees
FROM   (SELECT DISTINCT e.deptno, e.ename  FROM   emp e) e2
GROUP BY e2.deptno
ORDER BY e2.deptno;

    DEPTNO EMPLOYEES
---------- ----------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
解决方案：19c及之后
Oracle 19c引入了一种更简单的解决方案。我们现在可以DISTINCT直接在LISTAGG函数调用中包含关键字

COLUMN employees FORMAT A40
SELECT deptno, LISTAGG(DISTINCT ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
GROUP BY deptno
ORDER BY deptno;

    DEPTNO EMPLOYEES
---------- ----------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
默认功能是包含所有结果，我们可以使用ALL关键字明确表达。

SELECT deptno, LISTAGG(ALL ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
GROUP BY deptno
ORDER BY deptno;

    DEPTNO EMPLOYEES
---------- ----------------------------------------
        10 CLARK,KING,MILLER,MILLER,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
https://oracle-base.com/articles/19c/listagg-distinct-19c

心神沫沫

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
转行函数listagg的增强史侵立删

12.1以前Oracle 12c之前有一个内部函数wm_concat，用法类似SELECT u.userid, u.usercode, u.username,wm_concat(to_char(r.rolename))FROM S_U_R ur, S_U u, S_R rWHERE ur.userid = u.useridAND ur.roleid = r.roleid and u.u...
复制链接

扫一扫