Oracle使用listagg,wm_concat+distinct,自定义聚合函数完成去重聚合
一:简介
最近在修改一个视图时发现了一个distinct_concat的函数,奇奇怪怪的。点开一看是一个用户自定义的聚合函数。这个函数用来解决去重聚合的。
二:问题重现
比如我们有一个表数据如下
ID
27870917
27981533
27981533
我们需要去重其中重复的,然后拼接成一行,如下所示:
27870917,27981533
三:解决方法
(1)使用wm_concat distinct函数
--wm_concat函数聚合的结果是clob数据类型
select to_char(wm_concat(distinct id)) cat_id from gsc_test;
输出
注意:wm_concat在Oracle12c以上版本已经被官方弃用,如果在以上版本使用会报 【ORA-00904: “WM_CONCAT”: invalid identifier】错误
输出
(2)使用listagg函数进行聚合
1:我们在Oracle19C版本以前可以先使用group by分组,然后再进行listagg聚合。
select listagg(id,',') within group(order by id) cat_id from (select id from gsc_test group by id);
输出
2:Oracle在19C版本中对listagg函数进行了增强
我们可以直接使用listagg(distinct…)来进行去重聚合了
select listagg(distinct id,',') within group(order by id) cat_id from gsc_test;
输出
三:自定义聚合函数
回到我们最开始的问题,我在视图中发现的distinct_concat自定义函数是什么东西?Oracle提供了许多预定义的聚合函数比如AGV,SUM,MAX等,这些预定义的聚合函数只能与标量数据一起使用,不能对对象类型,LOB类型使用。用户自定义的聚合函数可以实现对这些类型的处理,也可以与标量数据一起使用。它们的实现通过Oracle提供的ODCIAggregate接口来实现。下面我们来一步步编写一个自定义聚合函数完成聚合去重。
1:创建聚合类型,类似于java中的接口类,里边有四个固定的构造方法。注意这里面除了对象名,自定义对象变量,其他啥都不用动,都是固定的。
--聚合函数的实质就是一个对象
create or replace type distinct_concat_type as object
--对象变量
cat_string varchar2(4000),
--对象初始化
static function odciaggregateinitialize(cs_ctx in out distinct_concat_type) return number,
--聚合函数的迭代方法(这是最重要的方法)
member function odciaggregateiterate(self in out distinct_concat_type,value in varchar2) return number,
--当查询语句并行运行时,才会使用该方法,可将多个并行的查询结果聚合
member function odciaggregatemerge(self in out distinct_concat_type,ctx2 in out distinct_concat_type) return number,
--终止聚合函数的处理,返回聚集函数处理的记过
member function odciaggregateterminate(self in out distinct_concat_type,returnvalue out varchar2,flags in number) return number
)
这四个构造方法具体的调用顺序如下图所示,
2:实现对象的主体,相当于对接口类的实现
create or replace type body distinct_concat_type is
-- 对象初始化,这里面基本不用动
static function odciaggregateinitialize(cs_ctx in out distinct_concat_type) return number is
begin
cs_ctx := distinct_concat_type(null);
return odciconst.success;
end;
--聚合函数的迭代方法,我们的实现逻辑都在这里面
member function odciaggregateiterate(self in out distinct_concat_type,value in varchar2) return number is
begin
--对传递过来的值(VALUE)进行判断,和我们在接口cat_string变量进行比较,第一次和已存在的值不进行拼接,最终拼接完成。其实这里面最重要的就是对(value)进行逻辑处理。最后的return不用动。
if self.cat_string is null or (instr(self.cat_string,value,1,1) = 0) then
self.cat_string := self.cat_string||','||value;
end if;
return odciconst.success;
end;
--当查询语句并行运行时,才会使用该方法,可将多个并行的查询结果聚合
member function odciaggregatemerge(self in out distinct_concat_type,ctx2 in out distinct_concat_type) return number is
begin
--这里的逻辑其实和迭代的一样就行,就是对多线程的运行结果拼接成最终的数据。
if self.cat_string is null or (instr(self.cat_string,ctx2.cat_string,1,1) = 0) then
self.cat_string := self.cat_string||','||ctx2.cat_string;
end if;
return odciconst.success;
end;
--终止聚合函数的处理,返回聚集函数处理的记过
member function odciaggregateterminate(self in out distinct_concat_type,returnvalue out varchar2,flags in number)
return number is
begin
--这是整个聚合函数的出口,到这里已经是最终的数据了,我们把数据两端的逗号去除一下。
returnvalue := ltrim(rtrim(self.cat_string,','),',');
return odciconst.success;
end;
end;
3:对刚刚创建的聚合函数进行定义。
--注意这里面除了函数名和类型名其他都是固定的语法
create or replace function distinct_concat(p_str varchar2) return varchar2 aggregate using distinct_concat_type;
4:使用刚刚创建好的聚合函数,使用方法和Oracle预定于聚合函数一模一样,是不是很简单?
select distinct_concat(id) cat_id from gsc_test;
输出
5:Oracle官方还提供了一个返回给定数据中第二大数字的自定义聚合函数,和更详细的介绍,可以参考参考。
Oracle聚合函数介绍
这篇好文章是转载于:学新通技术网
- 版权申明: 本站部分内容来自互联网,仅供学习及演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系,请提供相关证据及您的身份证明,我们将在收到邮件后48小时内删除。
- 本站站名: 学新通技术网
- 本文地址: /boutique/detail/tanhfkeeeg
-
photoshop保存的图片太大微信发不了怎么办
PHP中文网 06-15 -
《学习通》视频自动暂停处理方法
HelloWorld317 07-05 -
word里面弄一个表格后上面的标题会跑到下面怎么办
PHP中文网 06-20 -
Android 11 保存文件到外部存储,并分享文件
Luke 10-12 -
photoshop扩展功能面板显示灰色怎么办
PHP中文网 06-14 -
微信公众号没有声音提示怎么办
PHP中文网 03-31 -
excel下划线不显示怎么办
PHP中文网 06-23 -
excel打印预览压线压字怎么办
PHP中文网 06-22 -
TikTok加速器哪个好免费的TK加速器推荐
TK小达人 10-01 -
怎样阻止微信小程序自动打开
PHP中文网 06-13