1

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 ?

Ram K
  • 1,746
  • 2
  • 14
  • 23

1 Answers1

1

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.

rajorshi
  • 697
  • 4
  • 9
  • I get the following error : No match found for function signature STRING_AGG(, ) – Ram K Jan 31 '22 at 06:00
  • same for array_agg , do you have any other ideas ? – Ram K Jan 31 '22 at 06:11
  • Are you running this on Postgresql? I have added link to the demo , vide Edit. – rajorshi Jan 31 '22 at 06:14
  • running this on aws kinesis analytics – Ram K Jan 31 '22 at 06:18
  • Checked the available aggregate functions for Kinesis Analytics here https://docs.aws.amazon.com/kinesisanalytics/latest/sqlref/sql-reference-functions.html , no AGG function is available , we will need to find some other way. Let me try. – rajorshi Jan 31 '22 at 06:29
  • thank you for your help , struggling with it past few hours . any help highly appreciated . – Ram K Jan 31 '22 at 06:33