GROUP_CONCAT in Vertica

Question

Suppose we have data something like this:

    date    | campaign | raw | unq 
------------+----------+-----+-----
 2016-06-01 | camp1    |   5 |   1
 2016-06-01 | camp2    |  10 |   1
 2016-06-01 | camp3    |  15 |   2
 2016-06-02 | camp4    |   5 |   3
 2016-06-02 | camp1    |   5 |   1

I need to group it in such a way as to obtain the following result:

    date    | campaigns           | raw  | unq 
------------+---------------------+----- +-----
 2016-06-01 | camp1, camp2, camp3 |   30 |   4
 2016-06-02 | camp4, camp1        |   10 |   4

Mysql for these purposes has a function GROUP_CONCAT. Vertica also supports GROUP_CONCAT but I cannot make proper query due to the OVER clause and mandatory partitioning

Why do you need an OVER clause if you are only grouping by date? — Little Bobby Tables, Jun 23 '16 at 14:20

score 3 · Answer 1 · answered Jun 29 '16 at 18:41

Assuming you've compiled and created the function in the sdk/examples directory, you should be able to do:

select date, sum(raw) "raw", sum(unq) unq, rtrim(agg_concatenate(campaign || ', '),', ')
from mytest
group by 1
order by 1

I use rtrim to get rid of the last ', '.

If you haven't created it, you can do so:

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

score 1 · Answer 2 · answered Jun 29 '16 at 19:06

The only way that I know to use group_concat this way would be to do just query the data separately and combine them at the end. It isn't pretty and I prefer the other method I posted, but this one is a more direct answer to your question.

with camps as (
  select date, group_concat(campaign) over (partition by date) campaigns
  from mytest
), sums as (
  select date, sum(raw) "raw", sum(unq) unq
  from mytest
  group by date
)
select c.date, "raw", unq, campaigns
from camps c
join sums s on (c.date = s.date)

score 1 · Answer 3 · answered Aug 27 '19 at 11:51

1

As mentioned in this link:

Vertica 9.1.1-4 now has a built-in function called LISTAGG that does the same thing...

Use "LISTAGG"

answered Aug 27 '19 at 11:51

Ganj Khani

1,229
15
20

GROUP_CONCAT in Vertica

3 Answers3

Linked