LISTAGG in vertica

Question

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

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;

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

Answer 1

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

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

Answer 2

Take a look on my implementation of LISTAGG, it actually mimics an Oracle function LISTAGG. GROUP_CONCAT and CONCATENATEin git hub have some disadvantaged.

Compile:

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

Install:

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

Example:

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)

Answer 3

you have no built-in function but you can use UDX see vertica github you need GroupConcat function