在Vertica中用于连接字符串的聚合函数

Question

我有一个Vertica数据库中的表，名称为test，结构如下：

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

我该如何使用聚合函数或编写查询以获取类似于此的数据（Vertica语法）？

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

- yabchexu

Answer 1

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

-- 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;

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

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

使用rtrim来去掉最后的', '。

如果您需要按特定方式对聚合进行排序，则可能需要在内联视图或使用first中进行选择/排序。

- woot

---

抱歉回复晚了。有没有地方可以学习如何添加一般的功能呢？我的意思是，对于您提供的命令，我想学习，以便下次可以为其他用途做到这一点。谢谢！ - yabchexu

如果你查看/opt/vertica/sdk/examples目录，你会发现许多其他函数。还有一个通用的makefile和几个SQL文件，展示了如何安装和测试它们。 - woot

agg_concatenate 可以用于这个问题这里吗？在哪里可以下载它？ - hhh

Answer 2

SELECT id,
    MAX(DECODE(row_number, 1, a.name)) ||
    NVL(MAX(DECODE(row_number, 2, ',' || a.name)), '') ||
    NVL(MAX(DECODE(row_number, 3, ',' || a.name)), '') ||
    NVL(MAX(DECODE(row_number, 4, ',' || a.name)), '') ||
    NVL(MAX(DECODE(row_number, 5, ',' || a.name)), '') ||
    NVL(MAX(DECODE(row_number, 6, ',' || a.name)), '') ||
    NVL(MAX(DECODE(row_number, 7, ',' || a.name)), '') ||
    NVL(MAX(DECODE(row_number, 8, ',' || a.name)), '') ||
    NVL(MAX(DECODE(row_number, 9, ',' || a.name)), '') ||
    NVL(MAX(DECODE(row_number, 10, ',' || a.name)), '')||
    NVL(MAX(DECODE(row_number, 11, ',' || a.name)), '') ||
    NVL(MAX(DECODE(row_number, 12, ',' || a.name)), '') ag
FROM
    (SELECT id, name, ROW_NUMBER() OVER(PARTITION BY name ORDER BY id) row_number FROM test) a
GROUP BY a.id
ORDER BY a.id;

- Teja

---

不是最干净的方法，但适用于我无法安装新代码的问题。 - TrialAndError

Answer 3

另一种方法是使用 github上的字符串包 中的 GROUP_CONCAT。

select id, group_concat(name) over (partition by id order by name) ag
from mytable

然而，使用这种方法存在一些限制，因为分析型udx不允许您包含其他聚合函数（您必须将其内联或使用with来添加更多数据）。

- woot

Answer 4

我相信更好的方式（至少如果您的Vertica版本支持）是使用LISTAGG()函数。 LISTAGG函数将一组行中的非空值转换为由可配置分隔符分隔的值列表，可用于将行非规范化为逗号分隔值或其他格式的字符串。

- Omar AlSuwaidi