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

深藏阁楼爱情的钟 2021-10-06 14:42 296阅读 0赞

# 前言 #

常见的一种需求，如下图(1)：   
![这里写图片描述][20151216095804705]   
目前需要按右边的factor\_code进行分组，将左边的behavior\_code这一列通过指定分隔符连接起来，比如通过`<br />` 来连接，理想的效果应当是如下图(2)这样：   
![这里写图片描述][20151216095258828]   
下面就来讨论一下实现方式，假如在别的[数据库][Link 1]中来实现，例如[MySQL][Link 1]或[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;
     
      
       1
       2
       3
      
     
      
       1
       2
       3

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

## 创建自定义聚合函数（CREATE AGGREGATE） ##

这种方式使用简便，就是第一次写起来略微麻烦一点。思路和上一种一致，同样是间接的利用了`array_to_string`函数，只不过是在一个自定义的function中去做了。关于自定义聚合函数，我们可以在官方文档中的CREATE ARRGREAGTE 章节中看到，在postgres 8.x和9.x中基本是一致的，仅有略微差别，如下图(3)所示：   
![这里写图片描述][20151216122312416]   
由于我本地的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";
     
      
       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的代码：

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";
     
      
       1
       2
       3
       4
       5
       6
       7
       8
       9
       10
      
     
      
       1
       2
       3
       4
       5
       6
       7
       8
       9
       10

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

### CREATE AGGREGATE ###

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

CREATE AGGREGATE ToOneRow(TEXT) (
        SFUNC = SFUNC_ToOneRow,
        STYPE = TEXT[],
          FINALFUNC = FFUNC_ToOneRow
    );
     
      
       1
       2
       3
       4
       5
      
     
      
       1
       2
       3
       4
       5

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

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

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

# 最佳实践（Best Practice） #

如果是在8.x的版本中仅仅只能通过上述的方式解决问题了，但自从postgres 9之后，又新增了一批内置的聚合函数，其中就包含我们上面实现的那种方式，所以9.x的版本也就不需要我们再去自己创建了！下面看一下官方文档中提供的9.3版本的内置聚合函数表：   
![这里写图片描述][20151216163156766]

注意一下我用红色标记出来的这个函数，是否有种豁然开朗的感觉呢？这里不得不再次赞叹一下postgres确实很强大！赶紧[测试][Link 2]一下是否有效：

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

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

# 总结 #

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

参考资料：[http://www.postgresql.org/docs/9.3/interactive/functions-aggregate.html][http_www.postgresql.org_docs_9.3_interactive_functions-aggregate.html]

转载至： http://blog.csdn.net/wlwlwlwl015/article/details/50323791

[20151216095804705]: /images/20211006/c210e775db6f47b2a925d38201890a0a.png
[20151216095258828]: /images/20211006/db4e5ab1f0fe49ee8a8a90edaf0452f8.png
[Link 1]: http://lib.csdn.net/base/mysql
[Oracle]: http://lib.csdn.net/base/oracle
[20151216122312416]: /images/20211006/0916cb69100d450aa93664d7d099465d.png
[20151216163156766]: /images/20211006/25d797068b9c48c2b6f1a076dc338c33.png
[Link 2]: http://lib.csdn.net/base/softwaretest
[http_www.postgresql.org_docs_9.3_interactive_functions-aggregate.html]: http://www.postgresql.org/docs/9.3/interactive/functions-aggregate.html