Permalink
Find file
Fetching contributors…
Cannot retrieve contributors at this time
487 lines (383 sloc) 30.4 KB

PostgreSQL aggregate function customize

作者

digoal

日期

2012-12-18

标签

PostgreSQL , 自定义聚合函数 , aggregate function , udf


背景

聚合操作是统计分析的常见需求,聚合实际上就是把多行变成单行。

比如count, sum, avg, min, max, 线性相关性等等

本文介绍一下如何编写聚合函数。

正文

昨天聊到了一个关于多行转换成单行以提高查询性能的场景。

http://blog.163.com/digoal@126/blog/static/163877040201211174617734/

这里用到了PostgreSQL的聚合函数, 将多行聚合成单行.

当然其他数据库也有聚合函数, 但是PostgreSQL 支持自建聚合函数, 这就给开发带来了较大的便利.

例如PostgreSQL 8.2 并没有昨天用到的array_agg聚合函数.

那么要实现同样的场景怎么办呢?

例如在GreenPlum 3.3.6中, 实际上是PostgreSQL 8.2.13的版本.

要实现同样的功能, 可以简单通过的创建一个自定义聚合函数来实现。

例如我们的目标是整合成app_id||'_'||rating 逗号隔开的一个text值.

那么可以通过||这个操作符或者函数来实现, 如下.

-- 创建聚合函数  

digoal=# create aggregate agg_append (text) (  
sfunc = textcat,  
stype = text);  

-- 创建测试表  

digoal=# create table recommendation_mpt (user_id int8, app_id numeric, rating numeric) distributed randomly;  
CREATE TABLE  

-- 插入测试数据  

digoal=# insert into recommendation_mpt select generate_series(1,1000000), generate_series(1,41), random();  
INSERT 0 41000000  

-- 创建聚合后的表  

digoal=# create table recommendation_mpt_new ( user_id int8, app_id text ) distributed randomly;  
CREATE TABLE  

-- 插入聚合后的数据  

digoal=# insert into recommendation_mpt_new select user_id, agg_append(app_id||'_'||rating||',')  from  recommendation_mpt group by user_id;  
INSERT 0 1000000  

-- 查询结果  

digoal=# \x  
Expanded display is on.  
digoal=# select * from recommendation_mpt_new limit 2;  
-[ RECORD 1 ]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
user_id | 1  
app_id  | 21_0.936229802202433,19_0.241772226057947,27_0.956521810032427,9_0.8754295501858,7_0.338058620225638,41_0.226744973100722,38_0.204116075765342,40_0.884842214174569,17_0.659971259534359,10_0.767988855019212,37_0.901241634506732,6_0.853149639908224,16_0.745244240853935,39_0.813799708615988,1_0.868315862957388,28_0.228956982959062,31_0.311310657765716,26_0.180641509592533,30_0.0854418091475964,29_0.289730631746352,36_0.90720307873562,18_0.533597331959754,8_0.459905118215829,20_0.144313692115247,11_0.18922403594479,25_0.202533770818263,24_0.994741758797318,2_0.458359555341303,34_0.530500751920044,4_0.789138578809798,14_0.557009539101273,15_0.853544842917472,22_0.110565081238747,13_0.238142486196011,3_0.734461918473244,32_0.154231588821858,5_0.299331326968968,33_0.53225368168205,35_0.422972090076655,12_0.806360349990427,23_0.380616633687168,  
-[ RECORD 2 ]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
user_id | 346  
app_id  | 21_0.923598561901599,39_0.534792800433934,29_0.0692782052792609,22_0.206713933963329,9_0.9313296796754,31_0.67749793222174,1_0.0934103117324412,10_0.695828275289387,41_0.621980169788003,11_0.87125027552247,30_0.079912081360817,40_0.478423351421952,19_0.387606668751687,20_0.496157462708652,8_0.552907709032297,24_0.0219633430242538,23_0.76717281434685,28_0.927976955194026,14_0.818206876516342,16_0.478273885324597,17_0.306423192843795,3_0.193629603367299,33_0.418421885930002,13_0.748691521584988,5_0.0270167640410364,36_0.358466576784849,34_0.444800178986043,27_0.790532193612307,26_0.380120156798512,35_0.933182283304632,2_0.253765208646655,37_0.922488207463175,4_0.0538468402810395,38_0.768641801085323,15_0.406475386582315,18_0.493758061900735,25_0.463594998698682,7_0.884454429149628,12_0.0971177448518574,32_0.395383660681546,6_0.936530550476164,  

注意

因为aggregate函数支持order by是从9.0开始才有的, 所以在greenplum 3.3.6也就是PostgreSQL8.2中创建的aggregate当然无法使用orderby来对rating排序. 语法错误如下 :

digoal=# insert into recommendation_mpt_new select user_id, agg_append(app_id||'_'||rating||',' order by rating desc)  from  recommendation_mpt group by user_id;  
ERROR:  syntax error at or near "order"  
LINE 1: ...lect user_id, agg_append(app_id||'_'||rating||',' order by r...  
                                                             ^  

当然array_agg也是可以实现的, 需要用到的函数是array_append. 如下 :

digoal=# create aggregate array_agg (anyelement) (  
  sfunc = array_append,  
  stype = anyarray);  

select user_id, array_agg(app_id||'_'||rating) from recommendation_mpt group by user_id;  
                                              array_agg                                                                               



---------+--------------------------------------------------------------------------------------------------------------------------  
------------------------------------------------------------------------------------------------------------------------------------  
------------------------------------------------------------------------------------------------------------------------------------  
------------------------------------------------------------------------------------------------------------------------------------  
------------------------------------------------------------------------------------------------------------------------------------  
------------------------------------------------------------------------------------------------------------------------------------  
--------------------------------------------------------------------------------------------  
       3 | {12_0.337564039975405,18_0.605327501893044,39_0.0606710785068572,30_0.249253052286804,31_0.68522938946262,1_0.58626456419  
006,11_0.342015425674617,3_0.383131602779031,29_0.485176796559244,9_0.489787132013589,21_0.478506665676832,22_0.881940244697034,19_0  
.501696343999356,38_0.611436820123345,23_0.740357991773635,40_0.657328587025404,2_0.973510192707181,13_0.987239361740649,20_0.827145  
711518824,28_0.930344801396132,32_0.726034674327821,41_0.175069946330041,10_0.876182099804282,7_0.609756181947887,33_0.3557396410033  
11,8_0.861955059692264,27_0.173304431606084,15_0.544581302441657,25_0.661107395309955,16_0.671136009506881,24_0.384254441130906,6_0.  
0688453107140958,34_0.290453566238284,17_0.787856891285628,14_0.0598205337300897,26_0.78198639722541,5_0.641678368207067,37_0.560569  
697991014,4_0.314639654941857,35_0.1592174815014,36_0.154369496740401}  
...  
略  

那么排序怎么来实现呢?

我们来看看aggregate的语法, 有一个finalfunc这个是在每次分组的聚合处理完后执行的函数. 例如上面得到的结果

12_0.337564039975405,18_0.605327501893044,39_0.0606710785068572,30_0.249253052286804,31_0.68522938946262,1_0.58626456419  
006,11_0.342015425674617,3_0.383131602779031,29_0.485176796559244,9_0.489787132013589,21_0.478506665676832,22_0.881940244697034,19_0  
.501696343999356,38_0.611436820123345,23_0.740357991773635,40_0.657328587025404,2_0.973510192707181,13_0.987239361740649,20_0.827145  
711518824,28_0.930344801396132,32_0.726034674327821,41_0.175069946330041,10_0.876182099804282,7_0.609756181947887,33_0.3557396410033  
11,8_0.861955059692264,27_0.173304431606084,15_0.544581302441657,25_0.661107395309955,16_0.671136009506881,24_0.384254441130906,6_0.  
0688453107140958,34_0.290453566238284,17_0.787856891285628,14_0.0598205337300897,26_0.78198639722541,5_0.641678368207067,37_0.560569  
697991014,4_0.314639654941857,35_0.1592174815014,36_0.154369496740401  

对这个结果进行处理, 每个分组都会调用一次

我们可以利用这个finalfunc指定的函数来对每个分组的聚合结果进行排序.

例如 :

digoal=# select split_part(i,'_',1) as app_id, split_part(i,'_',2) as rating from regexp_split_to_table('21_0.923598561901599,39_0.534792800433934,29_0.0692782052792609,22_0.206713933963329,9_0.9313296796754,31_0.67749793222174,1_0.0934103117324412,10_0.695828275289387,41_0.621980169788003,11_0.87125027552247,30_0.079912081360817,40_0.478423351421952,19_0.387606668751687,20_0.496157462708652,8_0.552907709032297,24_0.0219633430242538,23_0.76717281434685,28_0.927976955194026,14_0.818206876516342,16_0.478273885324597,17_0.306423192843795,3_0.193629603367299,33_0.418421885930002,13_0.748691521584988,5_0.0270167640410364,36_0.358466576784849,34_0.444800178986043,27_0.790532193612307,26_0.380120156798512,35_0.933182283304632,2_0.253765208646655,37_0.922488207463175,4_0.0538468402810395,38_0.768641801085323,15_0.406475386582315,18_0.493758061900735,25_0.463594998698682,7_0.884454429149628,12_0.0971177448518574,32_0.395383660681546,6_0.936530550476164',',') t(i) order by 2 desc;  
 app_id |       rating         
--------+--------------------  
 6      | 0.936530550476164  
 35     | 0.933182283304632  
 9      | 0.9313296796754  
 28     | 0.927976955194026  
 21     | 0.923598561901599  
 37     | 0.922488207463175  
 7      | 0.884454429149628  
 11     | 0.87125027552247  
 14     | 0.818206876516342  
 27     | 0.790532193612307  
 38     | 0.768641801085323  
 23     | 0.76717281434685  
 13     | 0.748691521584988  
 10     | 0.695828275289387  
 31     | 0.67749793222174  
 41     | 0.621980169788003  
 8      | 0.552907709032297  
 39     | 0.534792800433934  
 20     | 0.496157462708652  
 18     | 0.493758061900735  
 40     | 0.478423351421952  
 16     | 0.478273885324597  
 25     | 0.463594998698682  
 34     | 0.444800178986043  
 33     | 0.418421885930002  
 15     | 0.406475386582315  
 32     | 0.395383660681546  
 19     | 0.387606668751687  
 26     | 0.380120156798512  
 36     | 0.358466576784849  
 17     | 0.306423192843795  
 2      | 0.253765208646655  
 22     | 0.206713933963329  
 3      | 0.193629603367299  
 12     | 0.0971177448518574  
 1      | 0.0934103117324412  
 30     | 0.079912081360817  
 29     | 0.0692782052792609  
 4      | 0.0538468402810395  
 5      | 0.0270167640410364  
 24     | 0.0219633430242538  
(41 rows)  

但是greenplum中有点问题, 如下 :

create table recommendation_mpt (user_id int8, app_id numeric, rating numeric);  
insert into recommendation_mpt select generate_series(1,10000), generate_series(1,41), random();  

create aggregate agg_append (text) (  
sfunc = textcat,  
stype = text,  
FINALFUNC = final_array_agg);  

create aggregate array_agg (anyelement) (  
sfunc = array_append,  
stype = anyarray);  

create or replace function final_array_agg (i_text text) returns text[] as $$  
declare  
  result text[];  
begin  
  select array_agg(app_id||'_'||rating) into result from   
    (select split_part(i,'_',1) as app_id, split_part(i,'_',2) as rating from   
      regexp_split_to_table(i_text,',') t(i)  
      order by 2 desc) t;  
  return result;  
end;  
$$ language plpgsql strict;  

select user_id, agg_append(app_id||'_'||rating||',') from recommendation_mpt group by user_id;  

digoal=# select user_id, agg_append(app_id||'_'||rating||',') from recommendation_mpt group by user_id;  
ERROR:  Functions that execute SQL statements from the segDBs are not yet supported (spi.c:203)  (seg10 slice2 dw-host6-if0:50001 pid=19758)  
DETAIL:    
         SQL statement "select array_agg(app_id||'_'||rating) from (select split_part(i,'_',1) app_id, split_part(i,'_',2) rating from regexp_split_to_table( $1 ,',') t(i) order by 2 desc) t"  
PL/pgSQL function "final_array_agg" line 4 at SQL statement  

在PostgreSQL 8.3的版本中测试正常, 如下 :

digoal=# select user_id, agg_append(app_id||'_'||rating||',') from recommendation_mpt group by user_id;  
 user_id |                                                                                                                            


                                           agg_append                                                                                 



---------+--------------------------------------------------------------------------------------------------------------------------  
------------------------------------------------------------------------------------------------------------------------------------  
------------------------------------------------------------------------------------------------------------------------------------  
------------------------------------------------------------------------------------------------------------------------------------  
------------------------------------------------------------------------------------------------------------------------------------  
------------------------------------------------------------------------------------------------------------------------------------  
---------------------------------------------------------------------------------------  
       1 | {25_0.981541138608009,15_0.955886580049992,17_0.955500768963248,28_0.950485301204026,41_0.941958157345653,8_0.93016316043  
213,34_0.903705002740026,24_0.898381588049233,38_0.884811857715249,14_0.84847314003855,2_0.812469538301229,21_0.789327840320766,19_0  
.777864037547261,22_0.730009535793215,9_0.729899833910167,10_0.662983400281519,32_0.638831827323884,12_0.624156034551561,5_0.6165286  
74494475,31_0.61100034089759,16_0.598527556750923,27_0.596998091321439,7_0.546153449919075,37_0.539897245820612,1_0.534418095834553,  
26_0.485467836726457,20_0.484848829451948,35_0.434873293153942,36_0.328121873084456,30_0.325269365217537,40_0.27276889514178,6_0.267  
069734167308,11_0.221765073016286,29_0.164679736364633,33_0.162994640879333,3_0.161364886444062,18_0.151077626738697,13_0.1455773869  
52937,39_0.126275546848774,4_0.113149816170335,23_0.109499100595713,_}  
...  
略  

以上的解决办法是 聚合(agg_append) - 每条聚合结果打散(regexp_split_to_table) - 每条打散后的结果集再次进行聚合(array_agg) 的过程. 所以效率不高.

注意到前面的排序后的结果中末尾出现了一个array元素='_', 这个是怎么造成的呢?

digoal=# select split_part(i,'_',1) as app_id, split_part(i,'_',2) as rating from regexp_split_to_table('21_0.923598561901599,39_0.534792800433934,29_0.0692782052792609,22_0.206713933963329,9_0.9313296796754,31_0.67749793222174,1_0.0934103117324412,10_0.695828275289387,41_0.621980169788003,11_0.87125027552247,30_0.079912081360817,40_0.478423351421952,19_0.387606668751687,20_0.496157462708652,8_0.552907709032297,24_0.0219633430242538,23_0.76717281434685,28_0.927976955194026,14_0.818206876516342,16_0.478273885324597,17_0.306423192843795,3_0.193629603367299,33_0.418421885930002,13_0.748691521584988,5_0.0270167640410364,36_0.358466576784849,34_0.444800178986043,27_0.790532193612307,26_0.380120156798512,35_0.933182283304632,2_0.253765208646655,37_0.922488207463175,4_0.0538468402810395,38_0.768641801085323,15_0.406475386582315,18_0.493758061900735,25_0.463594998698682,7_0.884454429149628,12_0.0971177448518574,32_0.395383660681546,6_0.936530550476164,',',') t(i) where split_part(i,'_',1) = '' order by 2 desc;  
 app_id | rating   
--------+--------  
        |   
(1 row)  

因为agg_append聚合在处理finalfunc前的值是末尾带了个逗号的, 在regexp_split_to_table处理时会多出一行app_id, rating = ('','')的结果. 因此在final_array_agg中过滤即可.

create or replace function final_array_agg (i_text text) returns text[] as $$  
declare  
  result text[];  
begin  
  select array_agg(app_id||'_'||rating) into result from   
    (select split_part(i,'_',1) as app_id, split_part(i,'_',2) as rating from   
      regexp_split_to_table(i_text,',') t(i) where split_part(i,'_', 2) <> ''  
      order by 2 desc) t;  
  return result;  
end;  
$$ language plpgsql strict;  

现在的结果就正常了, 如下 :

postgres=# select user_id, agg_append(app_id||'_'||rating||',') from (select * from recommendation_mpt order by user_id,rating desc) t group by user_id;  
 user_id |                                                                                                                            


                                          agg_append                                                                                  



---------+--------------------------------------------------------------------------------------------------------------------------  
------------------------------------------------------------------------------------------------------------------------------------  
------------------------------------------------------------------------------------------------------------------------------------  
------------------------------------------------------------------------------------------------------------------------------------  
------------------------------------------------------------------------------------------------------------------------------------  
------------------------------------------------------------------------------------------------------------------------------------  
-------------------------------------------------------------------------------------  
       1 | {25_0.981541138608009,15_0.955886580049992,17_0.955500768963248,28_0.950485301204026,41_0.941958157345653,8_0.93016316043  
213,34_0.903705002740026,24_0.898381588049233,38_0.884811857715249,14_0.84847314003855,2_0.812469538301229,21_0.789327840320766,19_0  
.777864037547261,22_0.730009535793215,9_0.729899833910167,10_0.662983400281519,32_0.638831827323884,12_0.624156034551561,5_0.6165286  
74494475,31_0.61100034089759,16_0.598527556750923,27_0.596998091321439,7_0.546153449919075,37_0.539897245820612,1_0.534418095834553,  
26_0.485467836726457,20_0.484848829451948,35_0.434873293153942,36_0.328121873084456,30_0.325269365217537,40_0.27276889514178,6_0.267  
069734167308,11_0.221765073016286,29_0.164679736364633,33_0.162994640879333,3_0.161364886444062,18_0.151077626738697,13_0.1455773869  
52937,39_0.126275546848774,4_0.113149816170335,23_0.109499100595713}  

同样可以不需要rating的值, 也是修改final_array_agg函数, 如下 :

create or replace function final_array_agg (i_text text) returns text[] as $$  
declare  
  result text[];  
begin  
  select array_agg(app_id) into result from   
    (select split_part(i,'_',1) as app_id, split_part(i,'_',2) as rating from   
      regexp_split_to_table(i_text,',') t(i) where split_part(i,'_',2) <> ''  
      order by 2 desc) t;  
  return result;  
end;  
$$ language plpgsql strict;  

修改后执行结果如下 :

digoal=# select user_id, agg_append(app_id||'_'||rating||',') from recommendation_mpt group by user_id;  
 user_id |                                                     agg_append                                                        
---------+---------------------------------------------------------------------------------------------------------------------  
       1 | {25,15,17,28,41,8,34,24,38,14,2,21,19,22,9,10,32,12,5,31,16,27,7,37,1,26,20,35,36,30,40,6,11,29,33,3,18,13,39,4,23}  
       2 | {18,36,32,8,16,39,40,34,3,41,6,14,31,33,27,5,13,4,26,37,22,10,15,12,9,21,1,20,11,35,17,25,28,24,30,7,2,19,38,23,29}  
       3 | {2,11,15,34,24,39,30,28,17,20,41,23,7,25,36,26,8,5,22,9,1,3,12,13,40,29,4,37,27,19,21,14,38,10,31,33,18,35,6,16,32}  
       4 | {25,2,28,14,4,11,31,20,27,35,40,21,7,32,29,6,36,8,15,26,9,41,39,18,19,22,30,13,38,37,23,5,12,16,34,3,17,10,33,24,1}  
       5 | {18,31,22,29,8,30,38,39,32,25,15,13,21,10,40,27,36,12,6,23,3,1,4,33,16,17,2,35,5,19,7,26,11,37,41,24,28,20,14,34,9}  
...  
略  

与PostgreSQL 9.0开始自带的array_agg比较, 效率如何呢? 下面在PostgreSQL 9.2.1 同一个数据库中进行的测试 :

create table recommendation_mpt (user_id int8, app_id numeric, rating numeric, primary key (user_id,app_id));  
insert into recommendation_mpt select generate_series(1,100000), generate_series(1,41), random();  

-- 为了插入时避免xlog产生瓶颈, 使用unlogged table, 并且不使用PK :   

create unlogged table recommendation_mpt_new (user_id int8, app_id numeric[]);  
PostgreSQL 9.0 自带的array_agg :   
digoal=# \timing  
Timing is on.  
digoal=# insert into recommendation_mpt_new select user_id, array_agg(app_id order by rating desc)  from  recommendation_mpt group by user_id;  
INSERT 0 100000  
Time: 10670.576 ms  

执行计划

digoal=# explain select user_id, array_agg(app_id order by rating desc)  from  recommendation_mpt group by user_id;  
                                                     QUERY PLAN                                                       
--------------------------------------------------------------------------------------------------------------------  
 GroupAggregate  (cost=0.00..130574.58 rows=100338 width=24)  
   ->  Index Scan using recommendation_mpt_pkey on recommendation_mpt  (cost=0.00..108820.36 rows=4100000 width=24)  
(2 rows)  
digoal=# select * from recommendation_mpt_new limit 5;  
 user_id |                                                       app_id                                                          
---------+---------------------------------------------------------------------------------------------------------------------  
       1 | {19,3,38,36,2,15,16,31,39,14,6,29,37,8,33,40,25,21,23,34,4,18,10,1,27,13,11,20,9,32,26,17,35,24,12,5,22,30,28,41,7}  
       2 | {9,11,5,4,10,27,22,31,30,33,3,23,32,20,28,14,39,18,35,15,36,13,7,24,1,6,34,8,40,2,19,37,21,38,17,26,12,41,25,29,16}  
       3 | {11,32,25,35,34,21,40,19,24,38,12,37,20,2,31,17,5,1,8,33,28,22,14,10,29,9,23,18,6,41,27,13,16,4,26,36,15,39,3,30,7}  
       4 | {7,8,14,20,27,38,16,11,25,39,13,40,31,9,19,29,24,4,41,10,22,12,34,6,17,18,23,36,33,3,37,35,28,2,21,5,15,32,1,30,26}  
       5 | {2,31,40,20,27,16,30,5,32,26,28,33,41,29,14,7,39,12,1,19,13,22,17,4,10,9,24,3,8,11,15,36,34,23,25,6,38,18,21,37,35}  
(5 rows)  

结果HASH :

digoal=# select sum(hashtext((t.*)::text)) from recommendation_mpt_new t;  
     sum        
--------------  
 122980440213  
(1 row)  

自定义的agg_append :

create aggregate agg_array (anyelement) (  
sfunc = array_append,  
stype = anyarray);  

create or replace function final_agg_array (i_text text) returns text[] as $$  
declare  
  result text[];  
begin  
  select array_agg(app_id) into result from   
    (select split_part(i,'_',1) as app_id, split_part(i,'_',2) as rating from   
      regexp_split_to_table(i_text,',') t(i) where split_part(i,'_',2) <> ''  
      order by 2 desc) t;  
  return result;  
end;  
$$ language plpgsql strict;  

create aggregate agg_append (text) (  
sfunc = textcat,  
stype = text,  
FINALFUNC = final_agg_array);  

digoal=# truncate recommendation_mpt_new ;  
TRUNCATE TABLE  

digoal=# insert into recommendation_mpt_new   
select user_id, (agg_append(app_id||'_'||rating||','))::numeric[] from recommendation_mpt group by user_id;  
INSERT 0 100000  
Time: 86763.557 ms  

执行计划

digoal=# explain select user_id, agg_append(app_id||'_'||rating||',') from recommendation_mpt group by user_id;  
                                                     QUERY PLAN                                                       
--------------------------------------------------------------------------------------------------------------------  
 GroupAggregate  (cost=0.00..227158.24 rows=100338 width=24)  
   ->  Index Scan using recommendation_mpt_pkey on recommendation_mpt  (cost=0.00..108820.36 rows=4100000 width=24)  
(2 rows)  
digoal=# select * from recommendation_mpt_new limit 5;  
 user_id |                                                       app_id                                                          
---------+---------------------------------------------------------------------------------------------------------------------  
       1 | {19,3,38,36,2,15,16,31,39,14,6,29,37,8,33,40,25,21,23,34,4,18,10,1,27,13,11,20,9,32,26,17,35,24,12,5,22,30,28,41,7}  
       2 | {9,11,5,4,10,27,22,31,30,33,3,23,32,20,28,14,39,18,35,15,36,13,7,24,1,6,34,8,40,2,19,37,21,38,17,26,12,41,25,29,16}  
       3 | {11,32,25,35,34,21,40,19,24,38,12,37,20,2,31,17,5,1,8,33,28,22,14,10,29,9,23,18,6,41,27,13,16,4,26,36,15,39,3,30,7}  
       4 | {7,8,14,20,27,38,16,11,25,39,13,40,31,9,19,29,24,4,41,10,22,12,34,6,17,18,23,36,33,3,37,35,28,2,21,5,15,32,1,30,26}  
       5 | {2,31,40,20,27,16,30,5,32,26,28,33,41,29,14,7,39,12,1,19,13,22,17,4,10,9,24,3,8,11,15,36,34,23,25,6,38,18,21,37,35}  
(5 rows)  

结果HASH :

digoal=# select sum(hashtext((t.*)::text)) from recommendation_mpt_new t;  
     sum        
--------------  
 122980440213  
(1 row)  

hash结果一致,
由此可见, 两者实现了同样的功能但是效率相差了8倍多.

参考

1. http://blog.163.com/digoal@126/blog/static/163877040201211174617734/

2. http://www.postgresql.org/docs/9.2/static/xaggr.html

3. http://www.postgresql.org/docs/9.2/static/sql-createaggregate.html

4. 查看当前所有的aggregate函数

\da *.*  
digoal=> \da *.*  
                                                                                 List of aggregate functions  
   Schema   |      Name      |      Result data type       |        Argument data types         |                                     
     Description                                           
------------+----------------+-----------------------------+------------------------------------+-----------------------------------  
---------------------------------------------------------  
 digoal     | agg_append     | text                        | text                               |   
 pg_catalog | array_agg      | anyarray                    | anyelement                         | concatenate aggregate input into a  
n array  
 pg_catalog | avg            | numeric                     | bigint                             | the average (arithmetic mean) as n  
umeric of all bigint values  
 pg_catalog | avg            | double precision            | double precision                   | the average (arithmetic mean) as f  
loat8 of all float8 values  
....  
略  

Count