Oracle字符串聚合函数LISTAGG

devtools/2024/12/22 9:12:53/

在Oracle 19c中,LISTAGG函数是一个非常有用的字符串聚合函数,它可以将来自多个行的值连接成一个单独的字符串。这个函数特别适用于将分组内的多个值合并为一个逗号分隔(或其他分隔符)的字符串。

LISTAGG函数的基本语法如下:

LISTAGG(column, [delimiter]) WITHIN GROUP (ORDER BY order_by_clause) [OVER(PARTITION BY paration_by_clause) ]
  • column 是你想要聚合的列。
  • [delimiter] 是可选的,用于指定值之间的分隔符,默认为NULL。如果不指定分隔符,则所有值将直接连接在一起,没有分隔。
  • WITHIN GROUP (ORDER BY order_by_clause) 是必须的,用于指定聚合时值的排序方式。
  • OVER(PARTITION BY XXX) 在不使用GROUP BY语句时候,也可以使用LISTAGG函数

示例1

假设我们有一个名为employees的表,其中包含department_idemployee_name两个字段,我们想要为每个部门列出所有员工的名字,名字之间用逗号分隔。

SELECTdepartment_id,LISTAGG(employee_name, ', ') WITHIN GROUP (ORDER BY employee_name) AS employees
FROMemployees
GROUP BYdepartment_id;

这个查询将返回每个部门的ID和该部门所有员工名字的列表,名字之间用逗号加空格分隔。

示例2

SYS@orcl> create table t_listagg(id number,nation varchar2(32),city varchar2(128),constraint pk_t_listagg_id primary key(id));Table created.INSERT INTO t_listagg
select 1 ID,'China' nation ,'广州' city from dual union all 
select 2 ID,'China' nation ,'深圳' city from dual union all  
select 3 ID,'China' nation ,'上海' city from dual union all  
select 4 ID,'China' nation ,'北京' city from dual union all  
select 5 ID,'USA' nation ,'New York' city from dual union all  
select 6 ID,'USA' nation ,'Boston' city from dual union all  
select 7 ID,'Japan' nation ,'Tokyo' city from dual  
COMMIT;SYS@orcl> col city format a30
SYS@orcl> select * from t_listagg;ID NATION                           CITY
---------- -------------------------------- ------------------------------1 China                            广州2 China                            深圳3 China                            上海4 China                            北京5 USA                              New York6 USA                              Boston7 Japan                            TokyoSYS@orcl> col LISTAGG_CITY format a60
SYS@orcl> set linesize 200
-- 用于指定聚合时值的,以city升序方式排序
SYS@orcl> select nation,listagg(city,',') within GROUP (order by city) listagg_city  FROM t_listagg  GROUP by nation;NATION                           LISTAGG_CITY
-------------------------------- ------------------------------------------------------------
China                            上海,北京,广州,深圳
Japan                            Tokyo
USA                              Boston,New York
-- 用于指定聚合时值的,以ID倒序方式排序
SYS@orcl> select nation,listagg(city,',') within GROUP (order by ID desc) listagg_city  FROM t_listagg  GROUP by nation;NATION                           LISTAGG_CITY
-------------------------------- ------------------------------------------------------------
China                            北京,上海,深圳,广州
Japan                            Tokyo
USA                              Boston,New York-- 用于指定聚合时值的,以ID倒序方式排序,以nation分组
SYS@orcl> select id,nation,city,listagg(city,',') within GROUP (order by id desc)  over (partition by nation) rank  FROM t_listagg;ID NATION                           CITY                           RANK
---------- -------------------------------- ------------------------------ ------------------------------------------------------------4 China                            北京                           北京,上海,深圳,广州3 China                            上海                           北京,上海,深圳,广州2 China                            深圳                           北京,上海,深圳,广州1 China                            广州                           北京,上海,深圳,广州7 Japan                            Tokyo                          Tokyo6 USA                              Boston                         Boston,New York5 USA                              New York                       Boston,New York7 rows selected.

注意事项

  1. 字符串长度限制LISTAGG函数在Oracle中有字符串长度的限制。在Oracle 12c及之前的版本中,这个限制是4000字节。从Oracle 12c Release 2开始,可以通过设置ON OVERFLOW TRUNCATE子句来处理超出长度的情况,但Oracle 19c仍然默认有这个限制。如果聚合的字符串超过了这个长度,查询将失败。

  2. 处理超长字符串:如果你预期聚合的字符串可能会超过4000字节的限制,你可以考虑使用XMLAGGXMLELEMENT函数作为替代方案,因为XMLAGG不受此限制。但是,请注意,使用XMLAGG会使查询更加复杂,并且可能需要额外的处理来将XML类型的结果转换为字符串。

  3. 性能:对于大型数据集,LISTAGG函数可能会影响查询性能。在可能的情况下,考虑使用索引、优化查询逻辑或考虑数据聚合的替代方法。

  4. 版本兼容性:虽然LISTAGG在Oracle 11g Release 2及更高版本中可用,但某些特性(如ON OVERFLOW TRUNCATE)可能在较新的版本中才可用。始终参考你正在使用的Oracle版本的官方文档。


http://www.ppmy.cn/devtools/101922.html

相关文章

LeetCode 3133.数组最后一个元素的最小值:位运算+双指针

【LetMeFly】3133.数组最后一个元素的最小值&#xff1a;位运算双指针 力扣题目链接&#xff1a;https://leetcode.cn/problems/minimum-array-end/ 给你两个整数 n 和 x 。你需要构造一个长度为 n 的 正整数 数组 nums &#xff0c;对于所有 0 < i < n - 1 &#xff0…

网络游戏运营

游戏运营是将一款游戏平台推入市场&#xff0c;并通过一系列的策略和行动&#xff0c;使玩家从接触、认识到最终成为忠实玩家的过程。这一过程涵盖了多个方面&#xff0c;包括前期准备、上线运营、活动策划、数据分析、渠道合作以及用户维护等。以下是对游戏运营的详细解析&…

用ChatGPT精确营销:如何让AI深度理解并推广你的产品

在现代商业中,人工智能(AI)正迅速成为企业成功的关键因素之一。ChatGPT作为一种强大的语言模型,不仅能回答问题,还能通过深度理解和互动,帮助企业精准推广产品。然而,如何让ChatGPT真正了解并有效地推广你的产品,是许多使用者面临的挑战。在本文中,我们将探讨如何通过…

debian/ubuntu 通过串口连接WiFi

修改 /etc/wpa_supplicant.conf&#xff0c;如果没有这个文件就创建文件 vi /etc/wpa_supplicant.conf设置wifi信息 network{ssid"这里是你的wifi账号"psk"这里是你的wifi密码" }连接wifi killall wpa_supplicant wpa_supplicant -i wlan0 -c /etc/wpa_…

SQLite 插入数据并返回自增ID

要插入数据并返回自增ID&#xff0c;我们可以使用SQLite的last_insert_rowid()函数。这个函数返回了最后一次插入操作的自增ID。 下面我们通过一个示例来演示如何插入数据并返回自增ID。 首先&#xff0c;创建一个表来存储学生信息&#xff1a; CREATE TABLE students (id I…

【研究生论文】—— 综述怎么写

怎么写综述 “综述”指的是对某一特定主题或领域h的文献、研究、进展等进行系统性回顾和总结的一种文章类型。很多时候我们需要知道的不是综述是什么&#xff0c;而是综述不是什么&#xff0c;综述不是单纯的查询报告&#xff0c;综述需要在自己的查询结果上面提出自己的看法和…

5个常见问答 | 1+X证书《大数据应用开发(Python)》

1、 1X大数据应用开发&#xff08;Python&#xff09;哪些人群可以考&#xff1f; 全日制在读的中高职学校、应用型本科、本科层次职业教育试点学校院校的学生&#xff0c;有意向从事与证书相关岗位的社会人士都可考取该证书。 2、1X大数据应用开发&#xff08;Python&am…

etcd参数解释

etcd 版本 [rootaaaaaa ~]# /data/etcd/etcd-v3.5.15-linux-amd64/etcd --version etcd Version: 3.5.15 Git SHA: 9a5533382 Go Version: go1.21.12 Go OS/Arch: linux/amd64基础命令: etcd [flags]&#xff1a;启动一个 etcd 服务器。etcd --version&#xff1a;显示 etcd…