Блог

Главная - Вопросы по программированию - Переписать хранимую процедуру, чтобы возвращать вместо медианы (50%) элементы с позицией 20% / 30% и т.д.

Переписать хранимую процедуру, чтобы возвращать вместо медианы (50%) элементы с позицией 20% / 30% и т.д.

#mysql #sql #stored-procedures

#mysql #sql #хранимые процедуры

Вопрос:

У меня есть хранимая процедура, которая возвращает медиану набора, основанного на некоторой группе.

 SET @myvar:='1';
SET @rownum=0;

SELECT result.readdate, 
       AVG(total_gallons) AS total_gallons
FROM   (SELECT middle_rows.readdate, 
               numerated_rows.rownum, 
               numerated_rows.total_gallons 
        FROM   (SELECT IF(@myvar = readdate, @rownum := IFNULL(@rownum,0)   1, @rownum := 0) AS rownum,
                       @myvar := readdate                                          AS readdate_alias,
                       total_gallons 
                FROM   _temp_total_gallons 
                ORDER  BY readdate, 
                          total_gallons) numerated_rows, 
               (SELECT readdate, 
                       COUNT(*) / 2 median 
                FROM   _temp_total_gallons
                GROUP  BY readdate) middle_rows 
        WHERE  numerated_rows.rownum BETWEEN ( middle_rows.median - IF(median = ROUND(median), 1, 0) - 0.5 ) AND ( middle_rows.median - IF(median = ROUND(median), 0, 0.5) )
               AND numerated_rows.readdate_alias = middle_rows.readdate) result
GROUP  BY readdate;

Как вы видите, здесь используется предложение BETWEEN для возврата строк between с позицией 50%. Если найдено несколько строк (даже заданных), внешний выбор выполняет усреднение.

Я пытался изменить COUNT(*) / 2 median на COUNT(*) / 5 median , но запрос возвращает намного меньший набор данных, вероятно, потому, что предложение between слишком строгое и не отражает 20%.

В наборе, подобном:

 1 (2) 3 4 5 6 7 8 9 100 110

Число 2 равно 20%.

В будущем я хотел бы, чтобы скрипт можно было использовать для возврата любого исходного выбора, такого как элемент, в 30% или 40%.

Ответ №1:

Взгляните на информацию на следующей странице, SQL может вычислить любой процентиль, который вы пожелаете

http://rpbouman.blogspot.com/2008/07/calculating-nth-percentile-in-mysql.html

Я сам использовал это в ряде областей, и это работает действительно хорошо. Вам просто нужно быть осторожным со значением параметра ‘group_concat_max_len’, поскольку слишком малое значение может испортить ваши результаты.

Надеюсь, это поможет!

Дэйв

Комментарии:

1. Отличная помощь, но, к сожалению, у меня действительно большой набор данных, превышающий максимальный размер пакета, и я не могу использовать объединение групп в этой ситуации.

Ответ №2:

Проблема в предложении BETWEEN, как вы и предполагали. Попробуйте следующее предложение BETWEEN вместо:

 WHERE  numerated_rows.rownum  BETWEEN ( middle_rows.median - 1.0001 ) AND ( middle_rows.median - 0.0001) )