UDAnF ListAgg implimentation.

sKwa

Hi!

I wrote an UDAnF that mimics Oracle's function ListAgg: https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030

source code - https://github.com/sKwa/vertica/blob/master/UDx/ListAgg.cpp
(I will update a discussion about usage and limitations)

Some examples:

dbadmin=> select * from T ;
 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 id, val, ListAgg(val) over(partition by id) from T 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 id, val, ListAgg(val) over() from T group by id, val order by id;
 id | val |      ?column?       
----+-----+---------------------
  1 | eng | eng,rus,usa,afg,eng
  1 | rus | eng,rus,usa,afg,eng
  1 | usa | eng,rus,usa,afg,eng
  2 | afg | eng,rus,usa,afg,eng
  2 | eng | eng,rus,usa,afg,eng
(5 rows)

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

dbadmin=> select distinct date, ListAgg(val using parameters delimiter='; ') over() from T order by date;;
    date    |           ?column?           
------------+------------------------------
 2000-01-01 | rus; usa; usa; eng; afg; eng
 2000-01-02 | rus; usa; usa; eng; afg; eng
 2000-01-03 | rus; usa; usa; eng; afg; eng
(3 rows)