Grouping query in Redshift takes huge amount of time

Question

I have a following requirement: I have a table in following format.

and this is what I want it to be transformed into:

Basically I want number of users with various combination of activities

I want to have this format as I want to create a TreeMap visualization out of it.

This is what I have done till now. First find out number of users with activity groupings

WITH lookup AS
(
  SELECT listagg(name,',') AS groupings,
         processed_date,
         guid
  FROM warehouse.test
  GROUP BY processed_date,
           guid
)
SELECT groupings AS activity_groupings,
       LENGTH(groupings) -LENGTH(REPLACE(groupings,',','')) + 1 AS count,
       processed_date,
       COUNT(           guid) AS users
FROM lookup
GROUP BY processed_date,
         groupings

I put the results in a separate table

Then, I do a Split and coalesce like this:

SELECT NULLIF(SPLIT_PART(groupings,',', 1),'') AS grouping_1,
          COALESCE(NULLIF(SPLIT_PART(groupings,',', 2),''), grouping_1) AS grouping_2,
          COALESCE(NULLIF(SPLIT_PART(groupings,',', 3),''), grouping_2, grouping_1) AS grouping_3,
          num_users
   FROM warehouse.groupings) AS expr_qry
GROUP BY grouping_1,
         grouping_2,
         grouping_3

The problem is the first query takes more than 90 minutes to execute as I have more than 250M rows.

There must be a better and efficient way to di this. Any heads up would be greatly appreciated.

Thanks

Answer 1

You do not need to use complex string manipulation functions (LISTAGG(), SPLIT_PART()). You can achieve what you're after with the ROW_NUMBER() function and simple aggregates.

-- Create sample data
CREATE TEMP TABLE test_data (id, guid, name) 
AS        SELECT 1::INT, 1::INT, 'cooking'
UNION ALL SELECT 2::INT, 1::INT, 'cleaning'
UNION ALL SELECT 3::INT, 2::INT, 'washing'
UNION ALL SELECT 4::INT, 4::INT, 'cooking'
UNION ALL SELECT 6::INT, 5::INT, 'cooking'
UNION ALL SELECT 7::INT, 3::INT, 'cooking'
UNION ALL SELECT 8::INT, 3::INT, 'cleaning'
;
-- Assign a row number to each name per guid
WITH name_order AS (
    SELECT guid
         , name
         , ROW_NUMBER() OVER(PARTITION BY guid ORDER BY id) row_n
    FROM test_data
) -- Use MAX() to collapse each guid's data to 1 row
, groupings AS (
    SELECT guid
         , MAX(CASE WHEN row_n = 1 THEN name END) grouping_1
         , MAX(CASE WHEN row_n = 2 THEN name END) grouping_2
    FROM name_order
    GROUP BY guid
) -- Count the guids per each grouping
SELECT grouping_1
     , COALESCE(grouping_2, grouping_1) AS grouping_2
     , COUNT(guid) num_users
   FROM groupings
GROUP BY 1,2
;
-- Output
 grouping_1 | grouping_2 | num_users
------------+------------+-----------
 washing    | washing    |         1
 cooking    | cleaning   |         2
 cooking    | cooking    |         2