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 24

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

    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 25

    @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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.