The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

array_agg in vertica

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

Answers

  • Jim_KnicelyJim_Knicely Administrator
    edited September 2021

    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)
    
  • BHIFPBHIFP
    edited September 2021

    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
    
  • 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)

  • 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_KnicelyJim_Knicely Administrator
    edited September 2021

    @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!

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file