`

Listagg() - Oracle11gR2进行字符串汇总的新函数

阅读更多

wait4friend 的一篇老文《Oracle进行字符串汇总》里面,提到了三种常用的方式。其中比较好用的是10g之后提供的wm_concat()函数。在日常使用中,我们发现了wm_concat函数的一些缺点。首先是这个函数本身不支持排序,要通过嵌套视图的方式进行排序。另一个问题是,当排序过程中,可能会遭遇ora-01467 sort key too long错误。

 

在11gR2版本中,有一个新的函数listagg(),可以提供更强的功能,并且效率更高。listagg()可以作为聚合函数使用,也可以作为分析函数使用。语法如下,

LISTAGG(measure_expr [, 'delimiter']) WITHIN GROUP (order_by_clause) [OVER query_partition_clause]

下面的例子演示了作为聚合函数使用,并且和wm_concat()进行比较。把10000行数据按照flag分为50个分组,并且对字段进行排序的情况下,在测试环境中,wm_concat和listagg有50倍的性能差异。

 

-- prepare demo data
drop table ttt purge;
create table ttt(flag int, x varchar2(10) , y varchar2(10), z varchar2(10), m varchar2(10), n varchar2(10));

insert into ttt select mod(rownum,50), rownum,rownum,rownum,rownum,rownum from dual connect by rownum<=10000;
commit;

select * from ttt;

-- wm_concat without ordering
select flag,
to_char(wm_concat(x)) as rx,
to_char(wm_concat(y)) as ry,
to_char(wm_concat(z)) as rz
from ttt
group by flag;

-- wm_concat with ordering
select flag, max(rx) rx, max(ry) ry, max(rz) rz
from (select flag,
to_char(wm_concat(x) over(partition by flag order by x)) as rx,
to_char(wm_concat(y) over(partition by flag order by y)) as ry,
to_char(wm_concat(z) over(partition by flag order by z desc)) as rz
from ttt)
group by flag;

-- listagg() in 11gR2
select flag,
listagg(x, ',') within group(order by x) rx,
listagg(y, ',') within group(order by y) ry,
listagg(z, ',') within group(order by z desc) rz
from ttt
group by flag
order by flag;

作者:wait4friend
Weibo:@wait4friend
Twitter:@wait4friend
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。
分享到:
评论

相关推荐

    Oracle10g自定义聚合函数(字符串拼接)

    * 自定义聚合函数 wmsys.wm_concat 替换办法 * 超大字符串拼接,单个字符串4000、分隔符100... * Oracle11g Release2版本引入了LISTAGG 函数,使得聚集连接字符串变得很容易。并且允许使用我们指定连接串中的字段顺序

    Oracle字段转字符串/多行记录合并/连接/聚合字符串的几种方法

    `LISTAGG()`是Oracle 11g引入的一个聚合函数,用于将多行数据合并为一个字符串,特别适合于分组查询。它允许指定分隔符: ```sql SELECT column1, LISTAGG(column2, ', ') WITHIN GROUP (ORDER BY column2) FROM...

    Oracle接收长度大于4000的字符串

    ### Oracle接收长度大于4000的字符串 在Oracle数据库中,默认情况下,`VARCHAR2`类型字段的最大长度为4000个字符。当需要处理更长的字符串时(例如,超过4000个字符),可以采用多种方法来解决这一问题。本文将详细...

    Oracle 多行记录合并_连接_聚合字符串的几种方法_oracle_脚本之家1

    Oracle数据库在处理多行记录合并、连接和聚合字符串时,有多种方法,下面将详细介绍其中的几种常见技术。 1. 被集合字段范围小且固定型 这种方法适用于字段值有限且已知的情况。通过使用`DECODE`函数,我们可以为每...

    【Oracle】LISTAGG函数的使用.pdf

    Oracle LISTAGG 函数是 Oracle 11.2 中引入的一种新特性,主要功能类似于 wmsys.wm_concat 函数,即将数据分组后,把指定列的数据再通过指定符号合并。LISTAGG 函数有两个参数:要合并的列名和自定义连接符号。 ...

    解决Oracle没有WM_CONCAT函数.zip

    1. 使用LISTAGG函数:从Oracle 11g开始,官方提供了LISTAGG函数,它可以按照指定的分隔符连接字符串。例如: ```sql SELECT LISTAGG(column, ', ') WITHIN GROUP (ORDER BY column) AS aggregated_column FROM ...

    ORACLE 超长字符串问题的解决办法

    在Oracle数据库中,有时我们需要处理超出标准VARCHAR2类型限制的超长字符串。本文将深入探讨这个问题,以及如何解决Oracle中的超长字符串问题。 首先,Oracle的VARCHAR2类型最多可以存储4000个字节的单字节字符,...

    数据库-oracle-学习之路.docx

    - Oracle还提供了许多其他实用函数,如`LI`开头的函数可能是`LISTAGG`,用于聚合数据并返回一个字符串,常用于生成逗号分隔的列表。 在学习Oracle的过程中,深入理解这些核心概念和操作是必要的。此外,还需要掌握...

    Oracle多行记录合并

    - **`LISTAGG()` 函数:** 这是Oracle 11g及以后版本引入的一个强大的聚合函数,专门用于将多行数据合并为单行字符串。它接受一个排序表达式和一个分隔符,返回所有行的合并结果。例如,假设我们有一个名为`orders`...

    Oracle函数之LISTAGG

    Oracle数据库中的LISTAGG函数是一个非常实用的聚合函数,它允许你在一组数据中对特定列的值进行排序和拼接,生成一个字符串结果。这个函数特别适用于需要将多个行的数据合并到一行的情况,例如,当你想要在一个报告...

    存储过程拼接字符串

    Oracle提供了`LISTAGG()`函数,用于将一组值聚合为单个字符串,特别适合于生成分组列表。 ```sql SELECT LISTAGG(ColName, ', ') WITHIN GROUP (ORDER BY ColName) AS ConcatenatedValues FROM TableName GROUP BY ...

    异种数据库函数和标准SQL语法比较Oracle与SqlServer.rar

    - Oracle的`RANK`, `DENSE_RANK`和`ROW_NUMBER`在SQL Server中也存在,但Oracle的`LISTAGG`函数用于字符串合并,SQL Server需要使用`FOR XML PATH`或`STRING_AGG`(SQL Server 2017及以上版本)。 5. **事务控制**...

    聚集函数(自定义分隔符拼接字符串)

    在Oracle数据库中,有时我们需要将多个字符串连接成一个单一的字符串,这在处理报告或聚合数据时非常常见。Oracle提供了一些内置的聚集函数,如`LISTAGG`,但它们可能无法满足所有需求,比如在特定场景下需要自定义...

    ORA-00904 WMSYS.WM_CONCAT标识符无效解决方案

    1. **使用新的聚合函数**:Oracle 11gR2引入了新的聚合函数LISTAGG,可以实现相同的功能。例如,`LISTAGG(column, delimiter) WITHIN GROUP (ORDER BY column)` 将按指定列排序并以指定分隔符连接所有行的值。 2. **...

    Oracle中SQL语句连接字符串的符号使用介绍

    - Oracle提供了多种字符串函数,如 `LPAD`, `RPAD`, `SUBSTR`, `TRIM`, `UPPER`, `LOWER` 等,它们可以与 `||` 符号一起使用,以在连接字符串时进行格式化和操作。例如,如果你需要在每个 `tcdm` 前加一个前缀,...

    Oracle重建WMSYS用户及WMSYS.WM_CONCAT函数(更低分)

    在Oracle中,若需要替换`WM_CONCAT`功能,可以使用`LISTAGG`函数(从11g版本开始提供),它更为强大且标准,支持分组内的字符串聚合。例如: ```sql SELECT LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY ...

    oracle实现行转列功能,并使用逗号进行隔开拼接,成为一条数据.pdf

    LISTAGG 函数是 Oracle 11g 及更高版本中引入的,用于替代 WM_CONCAT 函数。 示例代码: ```sql SELECT sfc_no, LISTAGG(mark_operation_id, ',') WITHIN GROUP (ORDER BY mark_operation_id) FROM bp_marking ...

    Oracle 多行记录合并/连接/聚合字符串的几种方法

    例如,可以使用`LISTAGG`函数(Oracle 11g及以后版本),这是一个聚合函数,用于将一组值连接成一个字符串。如果没有`LISTAGG`,可以使用`XMLAGG`或者自定义的用户定义函数来达到类似的效果。 ```sql -- 如果使用...

    Oracle迁postgre 不兼容字段、函数等更改

    2. 字符串函数:Oracle 中的 CONCAT 函数在 PostgreSQL 中对应的函数是 CONCAT_WS 或者 ||。例如,在 Oracle 中的 CONCAT('a', 'b') 等同于 PostgreSQL 中的 CONCAT_WS('', 'a', 'b') 或者 'a' || 'b'。 3. 日期...

    ORA-00904: "WM_CONCAT": 标识符无效

    WM_CONCAT是Oracle 10g及更早版本中用于字符串合并的一个非标准函数,但在11g版本中被废弃,取而代之的是新的标准SQL聚合函数LISTAGG。 在Oracle 11g R2及更高版本中,WM_CONCAT函数不再可用,因此在19c中遇到这个...

Global site tag (gtag.js) - Google Analytics