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 ?
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.