sqlamazon-web-servicescloudpostgresql-9.1amazon-kinesis-analytics

AWS Kinesis Analytics SQL to transform rows to a list


I have a Destination Stream that looks as follows for example :

Company_ID      Product      User_Tag    Count
   123            P1             T1        15
   123            P1             T2        10
   123            P1             T3        10

I want the Final Output as follows :

Company_ID      Product       User_Tag    Count
   123            P1         [T1,T2,T3]    35

I tried using listagg on User_Tag but I get an error saying no function signature found . Is this even possible on AWS Kinesis SQL on streaming data ?


Solution

  • SQL - Array Aggregate

    SELECT company_id,
           product,
           Array_agg(user_tag),
           Sum(count)
    FROM   stream
    GROUP  BY company_id,
              product; 
    
     company_id | product | array_agg  | sum
    ------------+---------+------------+-----
            123 | P1      | {T1,T2,T3} |  35
    

    SQL - String aggregate:

    SELECT company_id,
           product,
           String_agg(user_tag, ','),
           Sum(count)
    FROM   stream
    GROUP  BY company_id,
              product; 
        
    
       company_id | product | string_agg | sum
    ------------+---------+------------+-----
            123 | P1      | T1,T2,T3   |  35 
     
    

    Edit Link to demo.