在数据库中处理中会碰到这样的需求,将多行的数据转成一列,用特定分割符号分割。在GP中通常做法是衍生多列数据,再进行MAX聚合函数,最后拼装。这种做法无论在使用方便性,效率,及扩展性方面都极差。其实可以直接创建一个聚合函数:
DROP AGGREGATE STRCAT(
VARCHAR,
VARCHAR
);
CREATE AGGREGATE STRCAT(VARCHAR,--处理字段
VARCHAR --指定分割符号,可以是多个的字符串组合
)
(
SFUNC=STRCAT_SFUNC, STYPE=VARCHAR
,FINALFUNC =STRCAT_FINAL
,INITCOND=''
);
CREATE OR REPLACE FUNCTION STRCAT_SFUNC(in agg_field VARCHAR,in item_field VARCHAR,in delimiter VARCHAR) RETURNS VARCHAR AS $$
BEGIN
RETURN agg_field||delimiter||coalesce(item_field,'');
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION STRCAT_FINAL(agg_field VARCHAR,in delimiter VARCHAR) RETURNS VARCHAR AS $$
BEGIN
RETURN substring(agg_field,length(delimiter)+1);
END;
$$ LANGUAGE plpgsql;
演示:
create table wsbupt(a int ,b varchar(100))
distributed by(a);
insert into wsbupt values(1,'a');
insert into wsbupt values(1,'b');
insert into wsbupt values(1,'C');
insert into wsbupt values(2,'c');
insert into wsbupt values(2,'d');
insert into wsbupt values(2,null);
select a,STRCAT(b,'@!@') from wsbupt group by a;
gptest=# select a,STRCAT(b,'@!@') from wsbupt group by a;
a | strcat
---+-----------
2 | c@!@d@!@
1 | a@!@b@!@C
(2 rows)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/77270/viewspace-609172/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/77270/viewspace-609172/