从11g到19c,Oracle列转行函数listagg的增强史

2024-02-18 08:40

本文主要是介绍从11g到19c,Oracle列转行函数listagg的增强史,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

一、 构造测试表 

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

CREATE TABLE emp (empno    NUMBER(4) 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);commit;

二、12.1以前

Oracle 12c之前有一个内部函数wm_concat,用法超级简单

SELECT wm_concat(ename) FROM emp;

用途是把原本应该按列显示的ename,合并到一行显示并且用逗号分隔开

 也可以分组合并,例如

SELECT mgr, wm_concat(ename)
FROM emp
GROUP BY mgr;

三、 12.1版本

1. 简单用法

从12.1开始,Oracle取消了wm_concat内部函数。其实11gR2开始,oracle就引入了listagg函数,固定语法如下:

LISTAGG(col_name, ',') WITHIN GROUP (ORDER BY col_name) 

可以看到,它可以自己设置分隔符,还可以对分隔字段排序了。

我们也先来看一个最简单的用法

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

 再试试前面的按分组合并

COLUMN employees FORMAT A40SELECT mgr, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM emp
GROUP BY mgr;

2. 字符串超长

不过listagg函数输出是有长度限制的(varchar2(4000)),超过该长度会收到报错 ORA-01489: result of string concatenation is too long

在以下示例中,我们使用CROSS JOIN来强制进行大型聚合。

COLUMN employees FORMAT A40
SELECT LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM empCROSS JOIN (SELECT level FROM dual CONNECT BY level <= 100); -- emp 14行*100行=1400个ename合并

如果转换后的长度确实超过4000应该怎么办,网上找到了几种解决方法:

  • 使用 xmlagg 函数
  • 使用 stragg 函数
  • 自己创建 PL/SQL 连接函数,并 returns type CLOB

3. 测试 xmlagg 函数

用法是:

rtrim(xmlagg(xmlelement(e,to_char(col_name),',').extract('//text()')

SELECT rtrim(xmlagg(xmlelement(e,ename,',').extract('//text()')))
FROM emp
CROSS JOIN (SELECT level FROM dual CONNECT BY level <= 100);

这里遇到另一个问题,虽然合并后的字段可以超过4000长度了,但又遇到另一个报错,解决方法后面补充

三、 12.2版本

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

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

如果遇到缺少右括号的报错,说明数据库版本太低,识别不了这个语法

        如果不想使用省略号,也可以指定自己的truncate文字。在下面的示例中,我们使用了 ~~~。

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

 也可以通过添加WITHOUT COUNT来省略计数,默认值相当于显式使用WITH COUNT。

COLUMN employees FORMAT A40
SELECT 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 <= 100);


 

四、 19c版本

1. LISTAGG DISTINCT

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”的条目

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

 如果想删除重复项,我们该怎么办?

2. 解决方案:19c之前

需要我们手动去重,可以用分析函数或者distinct

法1:用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;

法2:用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;

 

3. 解决方案:19c及之后

19c引入了一种更简单的解决方案,可以直接在listagg函数中包含distinct关键字。

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

 

参考

ORACLE-BASE - LISTAGG Function Enhancements in Oracle Database 12c Release 2 (12.2)

ORACLE-BASE - LISTAGG DISTINCT in Oracle Database 19c

这篇关于从11g到19c,Oracle列转行函数listagg的增强史的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/720633

相关文章

MySQL 中的 CAST 函数详解及常见用法

《MySQL中的CAST函数详解及常见用法》CAST函数是MySQL中用于数据类型转换的重要函数,它允许你将一个值从一种数据类型转换为另一种数据类型,本文给大家介绍MySQL中的CAST... 目录mysql 中的 CAST 函数详解一、基本语法二、支持的数据类型三、常见用法示例1. 字符串转数字2. 数字

Python内置函数之classmethod函数使用详解

《Python内置函数之classmethod函数使用详解》:本文主要介绍Python内置函数之classmethod函数使用方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地... 目录1. 类方法定义与基本语法2. 类方法 vs 实例方法 vs 静态方法3. 核心特性与用法(1编程客

Python函数作用域示例详解

《Python函数作用域示例详解》本文介绍了Python中的LEGB作用域规则,详细解析了变量查找的四个层级,通过具体代码示例,展示了各层级的变量访问规则和特性,对python函数作用域相关知识感兴趣... 目录一、LEGB 规则二、作用域实例2.1 局部作用域(Local)2.2 闭包作用域(Enclos

MySQL count()聚合函数详解

《MySQLcount()聚合函数详解》MySQL中的COUNT()函数,它是SQL中最常用的聚合函数之一,用于计算表中符合特定条件的行数,本文给大家介绍MySQLcount()聚合函数,感兴趣的朋... 目录核心功能语法形式重要特性与行为如何选择使用哪种形式?总结深入剖析一下 mysql 中的 COUNT

MySQL 中 ROW_NUMBER() 函数最佳实践

《MySQL中ROW_NUMBER()函数最佳实践》MySQL中ROW_NUMBER()函数,作为窗口函数为每行分配唯一连续序号,区别于RANK()和DENSE_RANK(),特别适合分页、去重... 目录mysql 中 ROW_NUMBER() 函数详解一、基础语法二、核心特点三、典型应用场景1. 数据分

MySQL数据库的内嵌函数和联合查询实例代码

《MySQL数据库的内嵌函数和联合查询实例代码》联合查询是一种将多个查询结果组合在一起的方法,通常使用UNION、UNIONALL、INTERSECT和EXCEPT关键字,下面:本文主要介绍MyS... 目录一.数据库的内嵌函数1.1聚合函数COUNT([DISTINCT] expr)SUM([DISTIN

Python get()函数用法案例详解

《Pythonget()函数用法案例详解》在Python中,get()是字典(dict)类型的内置方法,用于安全地获取字典中指定键对应的值,它的核心作用是避免因访问不存在的键而引发KeyError错... 目录简介基本语法一、用法二、案例:安全访问未知键三、案例:配置参数默认值简介python是一种高级编

python 常见数学公式函数使用详解(最新推荐)

《python常见数学公式函数使用详解(最新推荐)》文章介绍了Python的数学计算工具,涵盖内置函数、math/cmath标准库及numpy/scipy/sympy第三方库,支持从基础算术到复杂数... 目录python 数学公式与函数大全1. 基本数学运算1.1 算术运算1.2 分数与小数2. 数学函数

Oracle 数据库数据操作如何精通 INSERT, UPDATE, DELETE

《Oracle数据库数据操作如何精通INSERT,UPDATE,DELETE》在Oracle数据库中,对表内数据进行增加、修改和删除操作是通过数据操作语言来完成的,下面给大家介绍Oracle数... 目录思维导图一、插入数据 (INSERT)1.1 插入单行数据,指定所有列的值语法:1.2 插入单行数据,指

Python中help()和dir()函数的使用

《Python中help()和dir()函数的使用》我们经常需要查看某个对象(如模块、类、函数等)的属性和方法,Python提供了两个内置函数help()和dir(),它们可以帮助我们快速了解代... 目录1. 引言2. help() 函数2.1 作用2.2 使用方法2.3 示例(1) 查看内置函数的帮助(