[Postgres]合并多行到一列(转)

转自http://csk83.sinaapp.com/?p=104

在实际应用中常常遇见这样的情况，见下表，我们现在需要统计出来每年每个人的工资总和以及发放月份。

user_name	year	month	money
张三	2011	1	900
张三	2011	2	1200
张三	2011	5	1100
张三	2011	6	1300
李四	2011	1	1100
李四	2011	3	1200

即我们要得到下面表的结果

user_name	year	monthes	total money
张三	2011	1,2,5,6	4500
李四	2011	1,3	2300

首先我们想到的是有这样的SQL语句：
SELECT user_name,year,myfunction(month) AS monthes, SUM(money) AS total_money FROM table GROUP BY user_name,year;

像count或者sum一样吧数据表中的某个字段按照需要拼接起来，但是在PostgreSQL好像没有直接的方法来实现（或许有笔者没有发现，正因为没有发现所以笔者才研究了半天得到下面的心得）。

方法一：在PostgreSQL中如果不自定义函数直接用SQL来实现

SELECT t1.user_name, t1.year, ARRAY_TO_STRING(array(SELECT t2.month FROM table t2 WHERE t2.user_name=t1.user_name AND  t2.year=t1.year), ‘,’)  AS monthes, SUM(t1.money) AS total_money FROM table t1 WHERE 1 GROUP BY t1.user_name, t1.year;

这里主要是用了PostgreSQL的内置数组函数array_to_string把数组拼接成字符串。

方法二：自己写一个聚合函数csk_test来实现

结合上面array_to_sting的用法，根据聚合函数定义方法研究出用聚合函数来实现的方法：

– 1 开启plpgsql的支持，如果没有需要运行下面两行SQL开启plpgsql支持
CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler AS ‘$libdir/plpgsql’ LANGUAGE C;
CREATE TRUSTED LANGUAGE plpgsql HANDLER “plpgsql_call_handler”;
– 2 创建函数
—- 2.1 收集数据到一个数组
CREATE FUNCTION csk_test_start(a TEXT[], s TEXT) RETURNS TEXT[] AS $$
BEGIN
RETURN a || s;
END;
$$ LANGUAGE plpgsql;
—- 2.2 最后处理函数，把数组转换成字符串输出
CREATE FUNCTION csk_test_final(a TEXT[]) RETURNS TEXT AS $$
BEGIN
RETURN array_to_string(a, ‘,’);
END;
$$ LANGUAGE plpgsql;
– 3 创建聚集函数
CREATE AGGREGATE csk_test(
BASETYPE = TEXT,
SFUNC = csk_test_start,
STYPE = TEXT[],
FINALFUNC = csk_test_final
);

现在我们再次用本文开始的SQL就可以实现我们的要求了
SELECT user_name,year,csk_test(CAST(month to varchar)) AS monthes, SUM(money) AS total_money FROM table GROUP BY user_name,year;

需要注意的是，上面的聚集函数接收的数据类型是text，所以在用的时候month整型转换成了varchar类型，当然也可以直接修改上面的代码让它们的类型一致。聚合函数在文本字段的处理上在PostgreSQL 8.4上测试通过了，至于数字类型其他类型转换后是否能行没有测试过。

方法二是通过聚合函数引用自定义函数来实现的，其实还可以直接调用系统函数array_append来实现：

CREATE AGGREGATE csk_test(
basetype = integer,
sfunc = array_append,
stype = integer[],
initcond = ‘{}’
);

 

可参考http://www.postgresql.org/docs/9.1/static/functions-array.html