Вычисление медианы с использованием процентиля на красном смещении

У меня есть большая таблица с более чем 18 миллионами строк, и я хочу рассчитать медиану, и я использую для этого PRECENTILE. Однако затраченное время составляет около 17 минут, что не идеально.

Вот мой запрос

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

Есть ли способ ускорить это?

Спасибо


amazon-redshift percentile
person Yogi    schedule 02.07.2019    source источник
comment
Я удивлен вашими таймингами/объемом данных! Что-то еще работает? сколько узлов какого типа у вас есть? Я только что запустил ваш код для своих данных, состоящих из 26 миллионов строк, и это заняло 45 секунд. Медианы и процентили - довольно сложные вычисления в большой базе данных, поэтому я ожидаю, что это займет ‹ 45 секунд.   -  person Jon Scott    schedule 03.07.2019
comment
Я использую 8 вычислительных узлов.   -  person Yogi    schedule 03.07.2019

Ответы (3)


arrow_upward
1
arrow_downward

Ваш запрос просит Redshift сделать много работы. Данные должны быть распределены в соответствии с вашим столбцом PARTITION и отсортированы в соответствии с вашим столбцом ORDER BY.

Есть два варианта сделать это быстрее:

  1. Используйте больше оборудования. Производительность Redshift масштабируется очень линейно. Большинство запросов будут выполняться в 2 раза быстрее на вдвое большем количестве оборудования.
  2. Поработайте заранее. Вы можете максимизировать производительность для этого запроса, реструктурировав таблицу. Используйте столбец PARTITION в качестве ключа распределения (DISTKEY(series)) и первого ключа сортировки. Используйте столбец ORDER BY в качестве второго ключа сортировки (SORTKEY(series,value)). Это сведет к минимуму работу, необходимую для ответа на запрос. Экономия времени будет разной, но я вижу, что при использовании этого подхода в моем небольшом тестовом кластере запрос PERCENTILE_CONT за 3 минуты 30 секунд сокращается до 30 секунд.
person Joe Harris    schedule 03.07.2019

arrow_upward
0
arrow_downward

Чтобы немного ускорить часть этого, попробуйте следующее

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

Это может быть быстрее, так как с большей вероятностью будет правильно использоваться sort/dist вашей таблицы. Вам нужно будет рассчитать минимум и максимум в другом месте. но по крайней мере посмотрите, работает ли он быстрее.

person Jon Scott    schedule 03.07.2019
comment
Привет, Джон. Спасибо за ваше предложение. Однако я вижу улучшение более чем на 2 минуты после использования вашего предложения. - person Yogi; 03.07.2019

arrow_upward
0
arrow_downward

Вы можете попробовать функцию APPROXIMATE PERCENTILE_DISC ( percentile ), которая оптимизирована для работы с распределенными данными с низким процентом ошибок, в т.ч. медиана будет 0,5

person AlexYes    schedule 03.07.2019