vertica中的LISTAGG [英] LISTAGG in vertica

问题描述

Vertica中LISTAGG的等效项 大家好！ 我正在尝试在一个字段中汇总特定组的所有字段值. 例如，我的输入表如下所示:-

Equivalent of LISTAGG in Vertica Hello Everyone! I'm trying to aggregate all the field values of a particular group in one field. For example, My input table looks like :-

FIELD1 GROUP1

A 1

A 2

B 1

B 2

C 1

C 3

3 null

并且我的输出应如下所示:-

and my output should look like :-

1 A,B,C

2 A,B

3 C

我目前可以通过使用以下功能在Oracle上实现此目标

I can currently achieve this on Oracle by using the following function

SELECT GROUP1, LISTAGG(FIELD1, ',') WITHIN GROUP (ORDER BY FIELD1) AS GROUPED_FIELD
FROM <INPUT_TABLE>
GROUP BY GROUP1;

有没有一种方法可以在Vertica中做到这一点. 任何帮助将不胜感激！

Is there a way i can do this in Vertica. Any help would be appreciated!!

推荐答案

Vertica在最新的9.1.1版本中添加了对LISTAGG的支持:

Vertica just added support for LISTAGG in the latest 9.1.1: https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Functions/Aggregate/LISTAGG.htm

但是，它不支持ORDER BY子句，但是您可以使用以下解决方法:

However, it does not support ORDER BY clause, but you can use this workaround:

select listagg(col1), col2
from (
  select col1, col2 from table1 order by col2, col1
) x
group by col2

这篇关于vertica中的LISTAGG的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！