#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) )