postgresql 9.3 自定义聚合函数实现多行数据合并成一列
来源:互联网 发布:优麒麟和ubuntu 编辑:程序博客网 时间:2024/05/16 18:14
前言
常见的一种需求,如下图(1):
目前需要按右边的factor_code进行分组,将左边的behavior_code这一列通过指定分隔符连接起来,比如通过<br />
来连接,理想的效果应当是如下图(2)这样:
下面就来讨论一下实现方式,假如在别的数据库中来实现,例如MySQL或Oracle,好像没有特别方便和直接的方式,同样的在早期的Postgres中也是一件麻烦的事情,下面先看一下Postgres早期版本的解决方案~
postgres 8.x的解决方案
没错,同标题一样,在postgres 8.x的版本中有几种解决办法,可以通过内置的数组函数array_to_string
,也可以通过自定义function结合自定义聚合函数来实现,下面先看一下第一种。
array_to_string
通过嵌套子查询的方式来完成,缺点是写法是略微麻烦,而且SQL层次不清晰,效率也不高,下面看一下SQL:
- 1
- 2
- 3
- 1
- 2
- 3
运行效果如图(2)一致,接下来再看一下第二种解决方案,通过自定义function结合自定义聚合函数来实现。
创建自定义聚合函数(CREATE AGGREGATE)
这种方式使用简便,就是第一次写起来略微麻烦一点。思路和上一种一致,同样是间接的利用了array_to_string
函数,只不过是在一个自定义的function中去做了。关于自定义聚合函数,我们可以在官方文档中的CREATE ARRGREAGTE 章节中看到,在postgres 8.x和9.x中基本是一致的,仅有略微差别,如下图(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的代码:
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
可以看到我们做的事情很简单,就是将聚合的数据放到一个数组里,当然也可以用一种更简便的写法来完成,即数组操作符 ||,它可以直接将元素put到数组里。
准备ffunc
完成了第一步之后,回归主题,我们要实现的是多行数据合并成一列,那么很简单,上面我们用过了array_to_string
这个数组函数,这里我们同样利用这个思路,将我们准备好的数组通过指定的分隔符转换成字符串。下面是ffunc的代码:
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
很简单,通过br分隔符将参数数组转换成字符串并返回。
CREATE AGGREGATE
上面的两个自定义函数都准备好之后,我们就可以创建我们自定义的聚合函数了,参考上面图(3)的语法,写出创建语句:
- 1
- 2
- 3
- 4
- 5
- 1
- 2
- 3
- 4
- 5
这样我们就创建完成了,赶紧尝试运行一下是否可以使用:
- 1
- 2
- 3
- 1
- 2
- 3
Congratulation!运行如上的SQL语句,依旧可以正确的得到和图(2)一模一样的结果。
最佳实践(Best Practice)
如果是在8.x的版本中仅仅只能通过上述的方式解决问题了,但自从postgres 9之后,又新增了一批内置的聚合函数,其中就包含我们上面实现的那种方式,所以9.x的版本也就不需要我们再去自己创建了!下面看一下官方文档中提供的9.3版本的内置聚合函数表:
注意一下我用红色标记出来的这个函数,是否有种豁然开朗的感觉呢?这里不得不再次赞叹一下postgres确实很强大!赶紧测试一下是否有效:
- 1
- 2
- 3
- 1
- 2
- 3
Perfect!和图(2)一模一样!第一个参数是需要聚合的列名,第二个参数是分隔符,这样就更加方便的完成了我们的需求~
总结
简单记录一下这种需求,以及postgres中自定义聚合函数的方法。如有错误的地方欢迎批评指正,The End。
参考资料:http://www.postgresql.org/docs/9.3/interactive/functions-aggregate.html
转载至: http://blog.csdn.net/wlwlwlwl015/article/details/50323791
- postgresql 9.3 自定义聚合函数实现多行数据合并成一列
- postgresql 9.3 自定义聚合函数实现多行数据合并成一列
- oracle wm_concat(column)函数的使用 多行数据合并成一列
- SQL将一列多行数据合并
- T_SQL 将一列多行数据合并为一行
- T_SQL 将一列多行数据合并为一行
- T_SQL 将一列多行数据合并为一行
- T_SQL 将一列多行数据合并为一行
- oracle多行数据转一列的内置函数
- 在MS SQL中将表中多行数据合并成一列数据
- 2行数据只有一列数据不同(不是主键),怎么合并成1行?
- mysql实现搜索多行数据合并成一行
- oracle使用聚合函数实现 多行合并
- 利用Oracle分析函数实现多行数据合并为一行
- 利用Oracle分析函数实现多行数据合并为一行
- 将多行数据合并成一行
- mysql 多行数据合并成一行
- oracle多行数据合并成一行
- Retrofit/OkHttp API接口加固技术实践(下)
- SOJ 1033
- Java的概述
- k-Nearest Neighbor 算法详解
- L2-015. 互评成绩
- postgresql 9.3 自定义聚合函数实现多行数据合并成一列
- 计算机视觉、机器学习相关领域论文和源代码大集合
- 案例——装备选择
- [boolan]这周的C++笔记,注意点
- Java中的几种循环写法
- 高性能网络编程(一)----accept建立连接
- stm32晶振起振异常波形与解决
- iOS
- js的全选、反选和不选