聚合函数以在Vertica中连接字符串 [英] aggregate function to concatenate strings in Vertica

问题描述

在vertica中有一个表：像这样测试：

have a table in vertica: test like this:

ID     |   name
1      |    AA
2      |    AB
2      |    AC
3      |    AD
3      |    AE
3      |    AF

我该如何使用聚合函数或如何编写查询以获取此类数据（vertica

how could I use an aggregate function or how to write a query to get data like this (vertica syntax)?

ID    |  ag
1     |  AA
2     |  AB, AC
3     |  AD, AE, AF 

推荐答案

首先，您需要为 agg_concatenate 编译udx。

First, you'll need to compile the udx for agg_concatenate.

-- Shell commands
cd /opt/vertica/sdk/examples/AggregateFunctions/
g++ -D HAVE_LONG_INT_64 -I /opt/vertica/sdk/include -Wall -shared -Wno-unused-value -fPIC -o Concatenate.so Concatenate.cpp /opt/vertica/sdk/include/Vertica.cpp

-- vsql commands
CREATE LIBRARY AggregateFunctionsConcatenate AS '/opt/vertica/sdk/examples/AggregateFunctions/Concatenate.so';
CREATE AGGREGATE FUNCTION agg_concatenate AS LANGUAGE 'C++' NAME 'ConcatenateFactory' LIBRARY AggregateFunctionsConcatenate;

然后您可以执行以下查询：

Then you can do a query like:

select id, rtrim(agg_concatenate(name || ', '),', ') ag
from mytable
group by 1
order by 1

使用rtrim删除最后一个'，'。

Uses rtrim to get rid of the last ', '.

如果需要以某种方式对聚合进行排序，则可能需要在嵌入式视图中或使用first进行选择/排序。

If you need the aggregate to be sorted a certain way, you may need to select/sort in an inline view or with first.

这篇关于聚合函数以在Vertica中连接字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！