Calculating Median using Percentile on Redshift

Publish date: 2024-03-15

I have a large table with over 18M rows and I want to calculate Median and I am using PRECENTILE for that. However the time taken is around 17 minutes, which is not ideal.

Here is my query

WITH raw_data AS ( SELECT name AS series, (duration) /(60000) AS value FROM warehouse.table ), quartiles AS ( SELECT series, value, PERCENTILE_CONT(0.25) WITHIN GROUP(ORDER BY value) OVER (PARTITION BY series) AS q1, MEDIAN(value) OVER (PARTITION BY series) AS median, PERCENTILE_CONT(0.75) WITHIN GROUP(ORDER BY value) OVER (PARTITION BY series) AS q3 FROM raw_data ) SELECT series, MIN(value) AS minimum, AVG(q1) AS q1, AVG(median) AS median, AVG(q3) AS q3, MAX(value) AS maximum FROM quartiles GROUP BY 1

Is there a way I could speed this up?

Thanks

2

3 Answers

Your query is asking Redshift to do a lot of work. The data must be distributed according to your PARTITION column and the sorted according to your ORDER BY column.

There are two options to make it faster:

  • Use more hardware. Redshift performance scales very linearly. Most queries will run 2x as fast on 2x as much hardware.
  • Do some work in advance. You can maximize performance for this query by restructuring the table. Use the PARTITION column as the distribution key (DISTKEY(series)) and first sort key. Use the ORDER BY column as the second sort key (SORTKEY(series,value)). This will minimize the work required to answer the query. Time savings will vary but I see a 3m30s PERCENTILE_CONT query drop to 30s using this approach on my small test cluster.

    • To get some speed up of part of this, try the following

    SELECT distinct series, value, PERCENTILE_CONT(0.25) WITHIN GROUP(ORDER BY value) OVER (PARTITION BY series) AS q1, MEDIAN(value) OVER (PARTITION BY series) AS median, PERCENTILE_CONT(0.75) WITHIN GROUP(ORDER BY value) OVER (PARTITION BY series) AS q3 FROM warehouse.table

    This may be faster as it is more likely to correctly use the sort/dist of your table. You would have to calculate the min and max elsewhere. but at least see if it runs faster.

    1

    You can try APPROXIMATE PERCENTILE_DISC ( percentile ) function which is optimized for working on distributed data with low error percentage, incl. median that will be 0.5

    ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJobG9wZmWCdYGOnJilm6WhrrW1zaBkpp2Unq6vedSsoKefXaWys6%2FEp6uipJVivK950Z6brKCZm8E%3D