自定义DB2风格STRINGAGG聚合函数（支持DISTINCT，默认逗号分隔）

LISTAGG函数基础用法 #

LISTAGG用于将指定列的记录通过指定分隔符拼接成字符串：

• 基础用法（保留重复值）：

SELECT LISTAGG(COL1,',') FROM TABLE1

输出示例：record1,record2,record3,record4,record1,record3,record4

• 带DISTINCT去重：

SELECT LISTAGG(DISTINCT COL1,',') FROM TABLE1

输出示例：record1,record2,record3,record4

需求说明 #

需要创建名为STRINGAGG的聚合函数，满足：

1. 支持DISTINCT子句实现去重
2. 固定使用逗号作为分隔符，无需手动指定
3. 调用格式：

SELECT STRINGAGG(DISTINCT COL1) FROM TABLE1

输出示例：record1,record2,record3,record4

已尝试方案及问题 #

方案1：自定义聚合函数 #

自行创建了一套聚合逻辑，但最终生成的函数无法支持DISTINCT子句，代码如下：

CREATE OR REPLACE PROCEDURE erp.strag_initialize(OUT strag VARCHAR(4000))
LANGUAGE SQL 
CONTAINS SQL
BEGIN
               SET strag = '';

END
@

CREATE OR REPLACE PROCEDURE erp.strag_accumulate(IN record VARCHAR(4000),  INOUT strag VARCHAR(4000))
LANGUAGE SQL 
CONTAINS SQL
BEGIN
               SET strag = concat(concat(strag,record), ',');
END
@

CREATE OR REPLACE PROCEDURE erp.strag_merge(IN strag VARCHAR(4000), INOUT mergestr VARCHAR(4000))
LANGUAGE SQL 
CONTAINS SQL
BEGIN
               SET mergestr = concat(mergestr ,strag);
END
@

CREATE OR REPLACE FUNCTION erp.strag_fin(strag VARCHAR(4000))
LANGUAGE SQL
CONTAINS SQL
RETURNS VARCHAR(4000)
BEGIN
               RETURN strag;
END
@

CREATE OR REPLACE FUNCTION erp.stragg(VARCHAR(4000))
RETURNS VARCHAR(4000)
AGGREGATE WITH (strag VARCHAR(4000))
USING
INITIALIZE PROCEDURE erp.strag_initialize
ACCUMULATE PROCEDURE erp.strag_accumulate
MERGE PROCEDURE erp.strag_merge
FINALIZE FUNCTION erp.strag_fin
@

方案2：直接封装LISTAGG #

尝试基于LISTAGG封装函数，但无法将逗号设为默认分隔符，不符合调用要求。

解决方案 #

直接通过标量函数封装LISTAGG，复用其原生能力即可满足需求：

CREATE OR REPLACE FUNCTION erp.STRINGAGG(p_col VARCHAR(4000))
RETURNS VARCHAR(4000)
LANGUAGE SQL
CONTAINS SQL
RETURN LISTAGG(p_col, ',');
@

说明 #

• 该函数天然支持DISTINCT：调用SELECT STRINGAGG(DISTINCT COL1) FROM TABLE1时，DISTINCT会先对输入列去重，再传递给LISTAGG进行拼接
• 固定使用逗号作为分隔符，完全符合调用格式要求
• 若需要对拼接结果排序，可添加WITHIN GROUP子句：

RETURN LISTAGG(p_col, ',') WITHIN GROUP (ORDER BY p_col);

内容的提问来源于stack exchange，提问作者Sahan Aloka Mendis