在Oracle中生成串联字符串的快速方法 [英] Fast way to generate concatenated strings in Oracle

问题描述

我们讨厌讨厌邪恶的编码再次出现吗?

Don't we hate when evil coding comes back to haunt?

前一段时间，我需要生成一个字符串，将某些字段连接起来，以便稍后进行更多处理.我认为如果直接查询将是一个好主意，并使用SO的帮助来获取它.有效.有一阵子...

Some time ago I needed to generate a string concatenating some fields for some more processing later. I thought it would be a good idea to do if straight in the query, and used SO's help to get it. It worked. For a while...

桌子变得很大，现在这种技巧(我知道是超级低效的)并不完全可行.这就是我正在做的:

The table got to big and now that trick (which I know is super inefficient) is not exactly viable. This what I'm doing:

with my_tabe as
(
    select 'user1' as usrid, '1' as prodcode from dual union
    select 'user1' as usrid, '2' as prodcode from dual union
    select 'user1' as usrid, '3' as prodcode from dual union
    select 'user2' as usrid, '2' as prodcode from dual union
    select 'user2' as usrid, '3' as prodcode from dual union
    select 'user2' as usrid, '4' as prodcode from dual
)
select
    usrid,
    ltrim(sys_connect_by_path(prodcode, '|'), '|') as prodcode
from 
    (
    select distinct prodcode, usrid,count(1)
    over (partition by usrid) as cnt,
    row_number() over (partition by usrid order by prodcode) as rn
    from my_tabe 
    )
where
    rn = cnt 
start with rn = 1
connect by prior rn + 1 = rn
and prior usrid = usrid

很好的产出:

USRID   PRODCODE
user1   1|2|3
user2   2|3|4

您可能已经注意到，这里的邪恶之处是where rn = cnt，如果删除它，您将看到Oracle确实在做的所有工作(我想):

The evil thing in here, as you might have noticed, is the where rn = cnt, which if you remove you'll see all the work (I suppose) Oracle is really doing:

USRID   PRODCODE
user1   1
user1   1|2
user1   1|2|3
user2   2
user2   2|3
user2   2|3|4

我实际上在很多没有很多记录的地方都使用它.多达五十万条记录是非常好的.

I'm actually using this in many places where I have not so many records. It is quite fine up to about a half million records.

最近我在具有〜15Mi记录的表中尝试了同样的操作，嗯...不好.

Recently I tried the same in a table with ~15Mi records, and well... no good.

问题:是否有一种方法可以在Oracle上更有效地执行此操作，或者现在该将其简化为实际代码了吗? 这不是实际的核心问题，因此只要速度很快，我仍然可以负担得起... 值得一提的是，我正在使用的"usrid"列有一个索引.

Question: is there a way to do this more efficiently on Oracle or is it time bring it down to the actual code? This is not actual core issue, so I can still afford kludging, as long as it's fast... Worth mentioning there's a index for the column "usrid" I'm using.

欢呼

推荐答案

Tom Kyte provides a very convenient way to do that, and it works from Oracle 9i, with a custom aggregation function. It aggregates with commas, but you can modify the function body for pipes.

从Oracle 11g开始，您可以执行以下操作:

Starting with Oracle 11g, you can do:

SELECT LISTAGG(column, separator) WITHIN GROUP (ORDER BY field)
  FROM dataSource
 GROUP BY grouping columns

此网页提供了其他方法，包括您列出的方法但这确实不是很有效.

This web page provides additional methods including the one that you listed and which is indeed not really efficient.

这篇关于在Oracle中生成串联字符串的快速方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！