array_agg in vertica

BHIFP

Array_agg is something supported by Postgres, but not Vertica.

What would be the best way to achieve the following?

SELECT id, business_time_minute,
      (array_agg(price ORDER BY business_time_second DESC))[1] AS minute_price,
      FROM ticks
      GROUP BY business_time_minute

Unfortunately, as far as I tried, last_value or ts_last_value would work with group by, otherwise it would be perfect.

Thanks

Jim_Knicely

Take a peek at LISTAGG.

verticademos=> SELECT * FROM some_data;
 c1 | c2
----+----
  1 | A
  2 | B
  1 | C
  1 | D
  1 | Z
(5 rows)

verticademos=> SELECT c1, STRING_TO_ARRAY('[' || LISTAGG(c2)::VARCHAR || ']') FROM some_data GROUP BY c1;
 c1 |  STRING_TO_ARRAY
----+-------------------
  1 | ["A","C","Z","D"]
  2 | ["B"]
(2 rows)

BHIFP

Thanks, Jim, how would you sort the list so that I can get the first or last ordered by c0, c1 below?

verticademos=> SELECT * FROM some_data;

 c0 | c1 | c2
----+----
  1 | 2 | A
  2 | 1 | B
  1 | 1 | C
  1 | 3 | D
  1 | 4 | Z
(5 rows)

I want to get if first:

 c0 | c1 | c2
----+----
  1 | 1 | C
  2 | 1 | B

or last:

 c0 | c1 | c2
----+----
  1 | 4 | Z
  2 | 1 | B

SruthiA

to get the first ordered by c0,c1 you can use the below query

dbadmin=> select * from some_data order by c1 asc,c0 asc;
c0 | c1 | c2
----+----+----
1 | 1 | C
2 | 1 | B
1 | 2 | A
1 | 3 | D
1 | 4 | Z
(5 rows)

BHIFP

these are the results I want, not the full set. array_agg allows for order by, I wonder how to do that with listagg.
```
c0 | c1 | c2
----+----
1 | 1 | C
2 | 1 | B

or last:

c0 | c1 | c2
----+----
1 | 4 | Z
2 | 1 | B
```

Jim_Knicely

@BHIFP - I think this what you mean ...

verticademos=> SELECT * FROM some_data;
 c0 | c1 | c2
----+----+----
  1 |  2 | A
  2 |  1 | B
  1 |  1 | C
  1 |  3 | D
  1 |  4 | Z
(5 rows)

verticademos=> SELECT c0, LISTAGG(c1) c1, LISTAGG(c2) c2 FROM (SELECT * FROM some_data ORDER BY c1 LIMIT 10000000) foo GROUP BY c0 ORDER BY c0;
 c0 |   c1    |   c2
----+---------+---------
  1 | 1,2,3,4 | C,A,D,Z
  2 | 1       | B
(2 rows)

verticademos=> SELECT c0, LISTAGG(c1) c1, LISTAGG(c2) c2 FROM (SELECT * FROM some_data ORDER BY c1 DESC LIMIT 10000000) foo GROUP BY c0 ORDER BY c0;
 c0 |   c1    |   c2
----+---------+---------
  1 | 4,3,2,1 | Z,D,A,C
  2 | 1       | B
(2 rows)

Unfortunately you have to do that LIMIT trick for now... See: Ordering the List of Values Returned from LISTAGG

The good news is that there is an ORDER BY feature coming to the LISTAGG function real soon! I will update this thread when it's avaible and whch version of Vertica it's in!