MySQL - расчет процентиля и обновление его в другом столбце той же таблицы
У меня есть таблица в MySQL (phpMYAdmin) со следующими столбцами
Я пытаюсь определить процентиль для каждой строки и обновить это значение в столбце G1Ptile. Столбец G1Ptile - это расчет процентиля на основе G1%. Я использую следующее, основанное на ответе Джона Ву, заданном здесь
SELECT `G1%`,
(1-ranks/totals)*100 Percentile FROM (
SELECT distinct `G1%`,
@rank:=@rank + 1 ranks,
(SELECT COUNT(*) FROM PCount) totals
FROM PCount a,
(SELECT @rank:=0) s
ORDER BY `G1%` DESC ) s;
и получите следующий результат
Вывод находится в операторе выбора, я хочу иметь возможность обновить его до столбца G1Ptile в моей таблице, однако я не могу обновить его с помощью
UPDATE `PCount` SET `G1Ptile`= --(All of the select query mentioned above)
Не могли бы вы помочь с изменением запроса / предложить альтернативу, чтобы я мог использовать значения процентилей, полученные с помощью вышеуказанного запроса, и обновить их в G1Ptile в той же таблице. Еще одна проблема, с которой я столкнулся, состоит в том, что в G1% есть два значения 20%, однако перцентиль, присвоенный одному, равен 20, а другому - 30. Я хочу, чтобы оба они были равны 20, а следующая строка в серии была 30.
Ответы 1
Я бы записал ваш расчет как:
SELECT `G1%`,
(1 - ranks / totals) * 100 as Percentile
FROM (SELECT `G1%`,
(@rank := @rank + 1) ranks,
(SELECT COUNT(*) FROM PCount) as totals
FROM (SELECT DISTINCT `G1%`
FROM PCount
ORDER BY `G1%` DESC
) p CROSS JOIN
(SELECT COUNT(*) as totals, @rank := 0
FROM Pcount
) params
) p;
Я сделал некоторые изменения, более соответствующие тому, как MySQL обрабатывает переменные. В частности, SELECT DISTINCT и ORDER BY находятся в подзапросе. Это необходимо в более поздних версиях MySQL (хотя в самых последних вы можете использовать оконные функции).
Теперь это можно включить в обновление с помощью JOIN:
UPDATE PCount p JOIN
(SELECT `G1%`,
(1 - ranks / totals) * 100 as Percentile
FROM (SELECT `G1%`,
(@rank := @rank + 1) ranks,
(SELECT COUNT(*) FROM PCount) as totals
FROM (SELECT DISTINCT `G1%`
FROM PCount
ORDER BY `G1%` DESC
) p CROSS JOIN
(SELECT COUNT(*) as totals, @rank := 0
FROM Pcount
) params
) pp
) pp
ON pp.`G1%` = p.`G1%`
SET p.G1Ptile = pp.percentile;
@Quark. . . Эту строку следовало удалить. Я решил поместить логику в `params.
Гордон, ваше решение работает хорошо, моя таблица G1Ptile была обновлена процентилем, однако я заметил, что процентили начинаются с 20, если у меня есть два одинаковых значения G1%, процентили начинаются с 60, если у меня есть 6 аналогичных значений, и так далее. Я удалил DISTINCT из вашего запроса, однако он показывает процентили как 0,10,20,80 * 6,90 ... Я не ожидаю, что вы предоставите мне готовые запросы, однако я буду признателен, если вы можете объяснить запрос немного, чтобы я мог попытаться изменить его в соответствии со своими требованиями.
Могу я сделать что-то вроде ОТЛИЧНЫХ итогов?
@Quark. . . Вы наверняка захотите count(distinct "G1%''
Большое спасибо, Гордон, к сожалению, я получаю сообщение об ошибке при выполнении вашего запроса. Вы могли бы посоветовать. # 1064 - У вас есть ошибка в синтаксисе SQL; проверьте руководство, соответствующее вашей версии сервера MySQL, чтобы найти правильный синтаксис для использования рядом с '(SELECT COUNT (*) FROM p) pp) pp ON pp.
G1%= p.G1%SET' в строке 15