postgresql 聚集函数加分组

MySQL有个聚集函数group_concat， 它可以按group的id，将字段串联起来，如

表：
id name
---------------
1 A
2 B
1 B

SELECT id, group_concat(name) from xxx group by id
得出的结果为

id group_concat(name)
---------------------------
1 A,B
2 B

PostgreSQL没有现成的group_concat聚集函数，但可以自定义聚集函数，所以可以容易的实现这功能。

自定义聚集函数 group_concat

CREATE AGGREGATE group_concat(anyelement)
(
sfunc = array_append, -- 每行的操作函数，将本行append到数组里
stype = anyarray, -- 聚集后返回数组类型
initcond = '{}' -- 初始化空数组
);

参数anyelement匹配任何类型，聚集后返回数组类型anyarray，该函数的功能是将每行的记录附加到数组里。

SELECT id, group_concat(name) from xxx group by id
得出的结果为

id array_accum(name)
---------------------------
1 {'A','B'}
2 {'B'}

array_accum(name)为数组类型，再用array_to_string函数将数组转换为字符串

SELECT id, array_to_string(group_concat(name),',') from xxx group by id
就可以得到group_concat相同的结果了。

但MySQL的group_concat的功能很强，比如可以排序等，postgresql若要模拟它，只能自己定义一个增强型的函数比如array_to_string_plus，可以对数组进行排序后再concat，这里就不用多述，留给各位动脑筋吧。

1、例子

select * from t2 where id in (1,2,3,100,1000,0);
select * from t2 where id = ANY ('{1,2,3,100,1000,0}'::integer[]);//用的这个
select * from t2 where id = ANY (VALUES (1),(2),(3),(100),(1000),(0));
select t2.* from t2 join (VALUES (1),(2),(3),(100),(1000),(0)) as t(id) on t2.id=t.id;

2、例子2  存储过程

CREATE OR REPLACE FUNCTION 存储过程(
    IN in_no text,
    IN in_type text)
  RETURNS TABLE(v_block_id integer, v_block_type integer, v_center_point integer, v_gird_center_point integer, v_direction integer, v_x integer, v_y integer, v_lat numeric, v_lon numeric, v_grid_points text, v_points text) AS
$BODY$
BEGIN
  RETURN QUERY
  SELECT b.id, 
         b.block_type_id, 
         b.center,
         b.gridcenter, 
         b.dirction,
         cg.x_count,
         cg.y_count,
         cp.x,
         cp.y,
         array_to_string(platform.array_accum(g.x_count || ',' || g.y_count),';'),   //postgresql数据聚集函数array_accum
         array_to_string(platform.array_accum(p.x || ',' || p.y),';')
  FROM 表8 b 
    INNER JOIN 表9 s 
         ON b.station_id = s.sid
    INNER JOIN  表1 bp 
         ON b.id = bp.block_id
    LEFT OUTER JOIN 表2  cg 
         ON b.gridcenter = cg.id
    LEFT OUTER JOIN 表3  cp 
         ON b.center = cp.id
    LEFT OUTER JOIN 表4  g 
         ON bp.girdpoint_id = g.id
    LEFT OUTER JOIN 表5 p 
         ON bp.point_id = p.id
  WHERE s.no = CAST(in_no AS integer)
  AND b.block_type_id = ANY (('{' || in_type || '}')::integer[])    //这部分
  GROUP BY b.id,cg.x_count,cg.y_count,cp.x,cp.y;
END
$BODY$
  LANGUAGE plpgsql VOLATILE；