阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

PostgreSQL 9.3 自定义聚合函数实现多行数据合并成一列

222次阅读
没有评论

共计 4191 个字符,预计需要花费 11 分钟才能阅读完成。

前言

常见的一种需求,如下图(1):
PostgreSQL 9.3 自定义聚合函数实现多行数据合并成一列
目前需要按右边的 factor_code 进行分组,将左边的 behavior_code 这一列通过指定分隔符连接起来,比如通过 <br /> 来连接,理想的效果应当是如下图(2) 这样:
PostgreSQL 9.3 自定义聚合函数实现多行数据合并成一列
下面就来讨论一下实现方式,假如在别的数据库中来实现,例如 MySQL 或 Oracle,好像没有特别方便和直接的方式,同样的在早期的 Postgres 中也是一件麻烦的事情,下面先看一下 Postgres 早期版本的解决方案~

postgres 8.x 的解决方案

没错,同标题一样,在 postgres 8.x 的版本中有几种解决办法,可以通过内置的数组函数array_to_string,也可以通过自定义 function 结合自定义聚合函数来实现,下面先看一下第一种。

array_to_string

通过嵌套子查询的方式来完成,缺点是写法是略微麻烦,而且 SQL 层次不清晰,效率也不高,下面看一下 SQL:

select array_to_string
(array(select behavior_code from t_evaluation_behavior t2 where t1.factor_code = t2.factor_code), '<br />') as behavior_code,
factor_code from t_evaluation_behavior t1 group by factor_code order by factor_code;

运行效果如图 (2) 一致,接下来再看一下第二种解决方案,通过自定义 function 结合自定义聚合函数来实现。

创建自定义聚合函数(CREATE AGGREGATE)

这种方式使用简便,就是第一次写起来略微麻烦一点。思路和上一种一致,同样是间接的利用了 array_to_string 函数,只不过是在一个自定义的 function 中去做了。关于自定义聚合函数,我们可以在官方文档中的 CREATE ARRGREAGTE 章节中看到,在 postgres 8.x 和 9.x 中基本是一致的,仅有略微差别,如下图(3) 所示:
PostgreSQL 9.3 自定义聚合函数实现多行数据合并成一列
由于我本地的 postgres 是 9.3,所以在此就着重演示一下 9.3 的 CREATE AGGREGATE(关于实现多行数据合并成一列的最简便方式不是这种,稍后在后面说)。
– name
要创建的聚集函数名(可以有模式修饰的)。
– base_type
本聚集函数要处理的基本数据类型。对于不检查输入类型的聚集来说,这个参数可以声明为”ANY”。(比如 count(*))。
– sfunc
用于处理源数据列里的每一个输入数据的状态转换函数名称。它通常是一个双参数的函数,第一个参数的类型是 state_data_type 而第二个参数的类型是 input_data_type. 另外,对于一个不检查输入数据的聚集,该函数只接受一个类型为 state_data_type 的参数。不管是哪种情况,此函数必须返回一个类型为 state_data_type 的值。这个函数接受当前状态值和当前输入数据条目,而返回下个状态值。
– state_data_type
聚集的状态值的数据类型。
– ffunc
在转换完所有输入域 / 字段后调用的最终处理函数。它计算聚集的结果。此函数必须接受一个类型为 state_data_type 的参数。聚集的输出数据类型被定义为此函数的返回类型。如果没有声明 ffunc 则使用聚集结果的状态值作为聚集的结果,而输出类型为 state_data_type
– initial_condition
状态值的初始设置(值)。它必须是一个数据类型 state_data_type 可以接受的文本常量值。如果没有声明,状态值初始为 NULL。
– sort_operator
用于 MIN 或者 MAX 类型的聚集的相关的排序操作符。这个只是一个操作符名(可以有模式修饰)。这个操作符假设接受和聚集一样的输入数据类型。

OK,看完了所有的参数介绍,我们现在实现自己的聚合函数。

准备 sfunc

这是第一步,sfunc 需要我们自定义一个 function,根据官方文档的描述,sfunc 是一个状态转换函数,下面看一下文档中的这一段话:

PostgreSQL 创建一个类型为 stype 的临时变量。它保存这个聚集的当前内部状态。对于每个输入数据条目,都调用状态转换函数计算内部状态值的新数值。在处理完所有数据后,调用一次最终处理函数以计算聚集的返回值。如果没有最终处理函数,则将最后的状态值当做返回值。

OK,根据官方文档的描述需要两个参数,一个是 internal-state,一个是 next-data-values。下面是 sfunc 的代码:

CREATE FUNCTION "public"."NewProc"(aa _text, s text)
  RETURNS "pg_catalog"."_text" AS $BODY$
BEGIN
RETURN array_append(aa, s);
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE COST 100
;

ALTER FUNCTION "public"."NewProc"(aa _text, s text) OWNER TO "postgres";

可以看到我们做的事情很简单,就是将聚合的数据放到一个数组里,当然也可以用一种更简便的写法来完成,即 数组操作符 ||,它可以直接将元素 put 到数组里。

准备 ffunc

完成了第一步之后,回归主题,我们要实现的是 多行数据合并成一列 ,那么很简单,上面我们用过了array_to_string 这个数组函数,这里我们同样利用这个思路,将我们准备好的数组通过指定的分隔符转换成字符串。下面是 ffunc 的代码:

CREATE FUNCTION "public"."NewProc"(aa _text)
  RETURNS "pg_catalog"."text" AS $BODY$
BEGIN
RETURN array_to_string(aa, '<br />');
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE COST 100
;

ALTER FUNCTION "public"."NewProc"(aa _text) OWNER TO "postgres";

很简单,通过 br 分隔符将参数数组转换成字符串并返回。

CREATE AGGREGATE

上面的两个自定义函数都准备好之后,我们就可以创建我们自定义的聚合函数了,参考上面图 (3) 的语法,写出创建语句:

CREATE AGGREGATE ToOneRow(TEXT) (
    SFUNC = SFUNC_ToOneRow,
    STYPE = TEXT[],
      FINALFUNC = FFUNC_ToOneRow
);

这样我们就创建完成了,赶紧尝试运行一下是否可以使用:

select ToOneRow(behavior_code) as behavior_code,factor_code
    from t_evaluation_behavior 
        group by factor_code order by factor_code;

Congratulation!运行如上的 SQL 语句,依旧可以正确的得到和图 (2) 一模一样的结果。

最佳实践(Best Practice)

如果是在 8.x 的版本中仅仅只能通过上述的方式解决问题了,但自从 postgres 9 之后,又新增了一批内置的聚合函数,其中就包含我们上面实现的那种方式,所以 9.x 的版本也就不需要我们再去自己创建了!下面看一下官方文档中提供的 9.3 版本的内置聚合函数表:
PostgreSQL 9.3 自定义聚合函数实现多行数据合并成一列

注意一下我用红色标记出来的这个函数,是否有种豁然开朗的感觉呢?这里不得不再次赞叹一下 postgres 确实很强大!赶紧测试一下是否有效:

select string_agg(behavior_code,'<br />') as behavior_code,factor_code
    from t_evaluation_behavior 
        group by factor_code order by factor_code;

Perfect!和图 (2) 一模一样!第一个参数是需要聚合的列名,第二个参数是分隔符,这样就更加方便的完成了我们的需求~

总结

简单记录一下这种需求,以及 postgres 中自定义聚合函数的方法。如有错误的地方欢迎批评指正,The End。

———————————— 华丽丽的分割线 ————————————

在 CentOS 6.5 上编译安装 PostgreSQL 9.3 数据库 http://www.linuxidc.com/Linux/2016-06/132272.htm

CentOS 6.3 环境下 yum 安装 PostgreSQL 9.3 http://www.linuxidc.com/Linux/2014-05/101787.htm

PostgreSQL 缓存详述 http://www.linuxidc.com/Linux/2013-07/87778.htm

Windows 平台编译 PostgreSQL http://www.linuxidc.com/Linux/2013-05/85114.htm

Ubuntu 下 LAPP(Linux+Apache+PostgreSQL+PHP)环境的配置与安装 http://www.linuxidc.com/Linux/2013-04/83564.htm

Ubuntu 上的 phppgAdmin 安装及配置 http://www.linuxidc.com/Linux/2011-08/40520.htm

CentOS 平台下安装 PostgreSQL9.3 http://www.linuxidc.com/Linux/2014-05/101723.htm

PostgreSQL 配置 Streaming Replication 集群 http://www.linuxidc.com/Linux/2014-05/101724.htm

———————————— 华丽丽的分割线 ————————————

PostgreSQL 的详细介绍:请点这里
PostgreSQL 的下载地址:请点这里

本文永久更新链接地址:http://www.linuxidc.com/Linux/2016-09/135543.htm

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计4191字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中