listagg用mysql实现_Vertica 数据库实现LISTAGG 聚合函数

最新推荐文章于 2025-03-21 23:11:27 发布

miller姚明来

最新推荐文章于 2025-03-21 23:11:27 发布

阅读量722

点赞数

文章标签： listagg用mysql实现

本文链接：https://blog.csdn.net/weixin_36244678/article/details/113599306

版权

本文介绍了如何在Vertica数据库中实现类似MySQL的LISTAGG聚合函数，通过自定义的User Defined Aggregate Function (UDAF) `ListAgg`，将字符串连接在一起。该函数考虑了输入字符串的最大长度，并提供了处理分隔符的选项。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

通过改造例子中的 Concatenate 实现。

* Description: Example User Defined Aggregate Function: ListAgg strings

#include "Vertica.h"

#include

using namespace Vertica;

using namespace std;

/**

* User Defined Aggregate Function ListAgg that takes in strings and concatenates

* them together. Right now, the max length of the resulting string is ten times the

* maximum length of the input string.

class ListAgg : public AggregateFunction

{

virtual void initAggregate(ServerInterface &srvInterface, IntermediateAggs &aggs)

{

//初始化聚合函数返回结果

try {

VString &concat = aggs.getStringRef(0);

concat.copy("");

} catch(exception& e) {

// Standard exception. Quit.

vt_report_error(0, "Exception while initializing intermediate aggregates: [%s]", e.what());

}

void aggregate(ServerInterface &srvInterface,

BlockReader &argReader,

IntermediateAggs &aggs)

{

//获取分割字符串

// Get the value of splitstr from the parameters

ParamReader paramReader = srvInterface.getParamReader();

if (!paramReader.containsParameter("splitstr")) {

vt_report_error(0, "You must provide a value for parameter splitstr");

}

VString splitstr = paramReader.getStringRef("splitstr");

try {

VString &concat = aggs.getStringRef(0);

string word = concat.str();

uint32 maxSize = aggs.getTypeMetaData().getColumnType(0).getStringLength();

do {

const VString &input = argReader.getStringRef(0);

if (!input.isNull()) {

if ((word.length() + input.length()+splitstr.length()) > maxSize) break;//不能超过最大长度

word.append(input.str());

word.append(splitstr.str());

}

} while (argReader.next());

concat.copy(word);

} catch(exception& e) {

// Standard exception. Quit.

vt_report_error(0, "Exception while processing aggregate: [%s]", e.what());

}

virtual void combine(ServerInterface &srvInterface,

IntermediateAggs &aggs,

MultipleIntermediateAggs &aggsOther)

{

// Get the value of splitstr from the parameters

ParamReader paramReader = srvInterface.getParamReader();

if (!paramReader.containsParameter("splitstr")) {

vt_report_error(0, "You must provide a value for parameter splitstr");

}

VString splitstr = paramReader.getStringRef("splitstr");

try {

uint32 maxSize = aggs.getTypeMetaData().getColumnType(0).getStringLength();

VString myConcat = aggs.getStringRef(0);

do {

const VString otherConcat = aggsOther.getStringRef(0);

if ((myConcat.length() + otherConcat.length()+splitstr.length()) <= maxSize) {

string word = myConcat.str();

word.append(otherConcat.str());

//word.append(splitstr.str());

myConcat.copy(word);

}

} while (aggsOther.next());

} catch(exception& e) {

// Standard exception. Quit.

vt_report_error(0, "Exception while combining intermediate aggregates: [%s]", e.what());

}

virtual void terminate(ServerInterface &srvInterface,

BlockWriter &resWriter,

IntermediateAggs &aggs)

{

// Get the value of splitstr from the parameters

ParamReader paramReader = srvInterface.getParamReader();

if (!paramReader.containsParameter("splitstr")) {

vt_report_error(0, "You must provide a value for parameter splitstr");

}

VString splitstr = paramReader.getStringRef("splitstr");

try {

const VString &concat = aggs.getStringRef(0);

VString &result = resWriter.getStringRef();

result.copy(concat.str().substr(0,concat.length()-splitstr.length())); //截断最后的splitstr

} catch(exception& e) {

// Standard exception. Quit.

vt_report_error(0, "Exception while computing aggregate output: [%s]", e.what());

}

InlineAggregate()

};

class ListAggFactory : public AggregateFunctionFactory

{

virtual void getIntermediateTypes(ServerInterface &srvInterface, const SizedColumnTypes &inputTypes, SizedColumnTypes &intermediateTypeMetaData)

{

int input_len = inputTypes.getColumnType(0).getStringLength();

intermediateTypeMetaData.addVarchar(input_len*10);

}

virtual void getPrototype(ServerInterface &srvfloaterface, ColumnTypes &argTypes, ColumnTypes &returnType)

{

argTypes.addVarchar();//输入函数字符串类型(聚合函数只能一个参数，只能使用 using parameters 增加额外参数)

returnType.addVarchar();

}

virtual void getReturnType(ServerInterface &srvfloaterface,

const SizedColumnTypes &inputTypes,

SizedColumnTypes &outputTypes)

{

int input_len = inputTypes.getColumnType(0).getStringLength();

outputTypes.addVarchar(input_len*10);

}

virtual void getParameterType(ServerInterface &srvInterface,

SizedColumnTypes &parameterTypes)

{

parameterTypes.addVarchar(10,"splitstr");//额外参数，使用 using parameters 增加额外参数

}

virtual AggregateFunction *createAggregateFunction(ServerInterface &srvfloaterface)

{ return vt_createFuncObject(srvfloaterface.allocator); }

};

RegisterFactory(ListAggFactory);

测试例子

select DEALER_CD ,public.LISTAGG(TPTYPE using parameters splitstr=',') as TPTYPE from (

SELECT distinct

DEALER_CD,

trim( TPTYPE) TPTYPE

FROM

DW.F_CR_EXIST_CUSTOMER

where trim( TPTYPE)!=''

)t group by DEALER_CD