vertica的LISTAGG

Question

相当于在Vertica的LISTAGG，大家好！我试图在一个字段中聚合特定组的所有字段值。例如，我的输入表如下:-

FIELD1 GROUP1

A 1

A 2

B 1

B 2

C 1

C 3

3 null

我的输出应该是:-

1 A,B,C

2 A,B

3 C

我目前可以使用以下函数在Oracle上实现这一目标

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

我能在Vertica做这件事吗。任何帮助都将不胜感激！！

Answer 1

Vertica刚刚在最新的9.1.1：https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Functions/Aggregate/LISTAGG.htm中增加了对LISTAGG的支持

但是，它不支持ORDER子句，但可以使用此解决方法：

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

Answer 2

您没有内置函数，但是可以使用UDX，请参阅vertica github，您需要GroupConcat函数。

Answer 3

看看我实现的LISTAGG，它实际上是模仿LISTAGG函数的。git集线器中的GROUP_CONCAT和CONCATENATE具有一定的劣势。

编译：

g++ -D HAVE_LONG_INT_64  -I /opt/vertica/sdk/include \
    -Wall -shared -Wno-unused-value -fPIC            \
    -o ListAggLib.so ListAgg.cpp /opt/vertica/sdk/include/Vertica.cpp

安装：

CREATE LIBRARY ListAggLib AS '/home/dbadmin/ListAggLib.so';
CREATE ANALYTIC FUNCTION ListAgg AS LANGUAGE 'C++' NAME 'ListAggFactory' LIBRARY ListAggLib;

示例：

dbadmin=> select * from foo;
 id |    date    | val 
----+------------+-----
  1 | 2000-01-01 | rus
  1 | 2000-01-01 | usa
  1 | 2000-01-02 | usa
  1 | 2000-01-03 | eng
  2 | 2000-01-01 | afg
  2 | 2000-01-02 | eng
(6 rows)

dbadmin=> select distinct id, ListAgg(val) over(partition by id) from foo;
 id |    ?column?     
----+-----------------
  1 | rus,usa,usa,eng
  2 | afg,eng
(2 rows)

dbadmin=> select id, val,
dbadmin-> ListAgg(val) over(partition by id) from foo group by id, val order by id;
 id | val |  ?column?   
----+-----+-------------
  1 | eng | eng,rus,usa
  1 | rus | eng,rus,usa
  1 | usa | eng,rus,usa
  2 | afg | afg,eng
  2 | eng | afg,eng
(5 rows)

dbadmin=> select 
dbadmin->     id,
dbadmin->     val,
dbadmin->     ListAgg(val using parameters delimiter=';') over(partition by id) 
dbadmin-> from foo 
dbadmin-> group by id, val 
dbadmin-> order by id;
 id | val |  ?column?   
----+-----+-------------
  1 | eng | eng;rus;usa
  1 | rus | eng;rus;usa
  1 | usa | eng;rus;usa
  2 | afg | afg;eng
  2 | eng | afg;eng
(5 rows)