sql - how to group by on column with separate comma

Question

SELECT
    account_id,
    app_name,
    triggered_signatures,
    DATE,
    COUNT(*) AS cnt
FROM "public"."bus_request"
WHERE DATE >= '2020-06-22' AND triggered_signatures IS NOT NULL AND triggered_signatures != ''
GROUP BY account_id, app_name, triggered_signatures, DATE
ORDER BY account_id, cnt, DATE DESC

I am getting:

aaaaa   uuuu    200004025             2020-06-22      1
bbbbb   rrrr    200003092,200004268   2020-06-23     12
bbbbb   kkkk    200004268             2020-06-23    412

how can I get for each triggered_signatures the number of records as it separated by comma?

i will want to get:

aaaaa   uuuu    200004025     2020-06-22      1
bbbbb   rrrr    200004268     2020-06-23      7
bbbbb   rrrr    200003092     2020-06-23      5
bbbbb   kkkk    200004268     2020-06-23    412

for example

Answer 1

You basically need to unnest the array of values. There is no nice way to do it in redshift - you have to cross join your table with an index table and then look up each element for an index.

It's the same problem as unnesting a json array described here https://stackoverflow.com/a/30198745/1680826, but instead of JSON_EXTRACT_ functions, you should use REGEXP_COUNT(triggered_signatures, ',') to get the array length, and SPLIT_PART(triggered_signatures, ',', index) to get array element.

I hope that helps.

---

Example query:

with input (account_id, app_name, triggered_signatures, date, cnt) as (
    select 'aaaaa', 'uuuu', '200004025', '2020-06-22', 1 
    union all
    select 'bbbbb', 'rrrr', '200003092,200004268', '2020-06-23', 12
    union all
    select 'bbbbb', 'kkkk', '200004268', '2020-06-23', 412
), sequence (index) as (
    select 1 union all
    select 2 union all
    select 3 union all
    select 4
)
select
    account_id,
    app_name,
    split_part(triggered_signatures, ',', index) as triggered_signautres,
    date,
    cnt
from input cross join sequence
where regexp_count(triggered_signatures, ',') + 1 >= index
;