Вопрос по mysql – Расчет процентильного ранга в MySQL

18

У меня очень большая таблица данных измерений в MySQL, и мне нужно вычислить процентильный ранг для каждого из этих значений. Похоже, что в Oracle есть функция процент_ранка, но я не могу найти ничего похожего для MySQL. Конечно, я мог бы просто использовать его в Python, который я в любом случае использую для заполнения таблицы, но я подозреваю, что это будет довольно неэффективно, потому что один образец может иметь 200 000 наблюдений.

Не могли бы вы объяснить, что именно вы подразумеваете под процентильным рангом? Assaf Lavie
Я сделал функцию Mysql для любого процентиля:stackoverflow.com/a/40266115/1662956 dartaloufe
@AssafLavie:en.wikipedia.org/wiki/Percentile_rank eliasah

Ваш Ответ

9   ответов
4
То, что я ищу, на самом деле является обратным к этому, то есть, учитывая число, оно должно указывать мне его ранг. Я несколько уверен, что в Oracle это будет проще, но, к сожалению, это невозможно. lhahne
-1

что означает «процентиль ранга», но чтобы получить данный процентиль для набора значений, см.http://rpbouman.blogspot.com/2008/07/calculating-nth-percentile-in-mysql.html Расчет sql можно легко изменить, чтобы получить другой или несколько процентилей.

Одно примечание: мне пришлось немного изменить расчет, например, 90-й процентиль - «90/100 * COUNT (*) + 0,5». вместо "90/100 * COUNT (*) + 1". Иногда он пропускал два значения после точки процентиля в упорядоченном списке вместо выбора следующего более высокого значения для процентиля. Может быть, способ целочисленного округления работает в MySQL.

то есть:

.... SUBSTRING_INDEX (SUBSTRING_INDEX (GROUP_CONCAT (fieldValue ORDER BY fieldValue SEPARATOR ',' ',' ', 90/100 * COUNT (*) +0.5), ',', -1) как 90-й процент

1

PERCENT_RANK() Функция, которую вы искали. Итак, просто напишите:

SELECT col, percent_rank() OVER (ORDER BY col)
FROM t
ORDER BY col

Ваш вопрос упоминает «процентили», которые немного отличаются. Для полноты информации ради, естьPERCENTILE_DISC а такжеPERCENTILE_CONT функции обратного распределения в стандарте SQL и в некоторых RBDMS (Oracle, PostgreSQL, SQL Server, Teradata), но не в MySQL. С MySQL 8 и оконными функциями,ты можешь подражатьPERCENTILE_DISC, however, again using the PERCENT_RANK and FIRST_VALUE window functions.

3
SELECT 
    c.id, c.score, ROUND(((@rank - rank) / @rank) * 100, 2) AS percentile_rank
FROM
    (SELECT 
    *,
        @prev:[email protected],
        @curr:=a.score,
        @rank:=IF(@prev = @curr, @rank, @rank + 1) AS rank
    FROM
        (SELECT id, score FROM mytable) AS a,
        (SELECT @curr:= null, @prev:= null, @rank:= 0) AS b
ORDER BY score DESC) AS c;
2

и я чувствую себя виноватым, говоря это. Тем не менее, это может помочь вам с вашей проблемой.

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

Создайте индекс по вашему номеру. итого = выбрать количество (); less_equal = select count() где значение & gt; indexed_number;

Процент будет что-то вроде: less_equal / total или (total - less_equal) / total

Убедитесь, что они оба используют индекс, который вы создали. Если они не, настройте их, пока они не будут. Запрос объяснения должен иметь «использование индекса» в правой колонке. В случае счетчика выбора (*) следует использовать индекс для InnoDB и что-то вроде const для MyISAM. MyISAM будет знать это значение в любое время без необходимости его расчета.

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

Это помогает?

Иаков

Я действительно попробовал это несколько недель назад, и это было невероятно медленно, поэтому я закончил вычислять процентили в python и помещать значение в базу данных. lhahne
@TheJacobTaylor Правильный ответ, но короткий код. Если вы поставили функционал, выберите «различный». введите запрос вверх, вы получите мой +1. Кроме того, если вы можете это исправить, вы получите хороший блестящий +1 и проверьте! ;))stackoverflow.com/questions/13689434/…
Вы пытались использовать счетчик выбора () and select count() & lt; = ваше значение? Подтвердили ли вы, что оба они обрабатываются индексом, в котором есть только нужные вам столбцы? Если бы решение вообще касалось строк данных, я бы ожидал, что оно будет на один или два порядка медленнее. Если индексы включали больше столбцов, чем необходимо, или конфигурация памяти MySQL была неправильно настроена, это было бы очень медленно. Если так, это должно было быть быстро. Примерно сколько времени "невероятно медленно"? В зависимости от порядка ожидаемого ответа мой ответ может быть очень медленным.
19

который не требует объединения. В моем случае (таблица с 15 000+) строк, он запускается примерно за 3 секунды. (Метод JOIN занимает на порядок больше).

В примере предположим, чтоmeasure столбец, по которому вы рассчитываете процентный ранг, иid это просто идентификатор строки (не обязательно):

SELECT
    id,
    @prev := @curr as prev,
    @curr := measure as curr,
    @rank := IF(@prev > @curr, @[email protected], @rank) AS rank,
    @ties := IF(@prev = @curr, @ties+1, 1) AS ties,
    ([email protected]/@total) as percentrank
FROM
    mytable,
    (SELECT
        @curr := null,
        @prev := null,
        @rank := 0,
        @ties := 1,
        @total := count(*) from mytable where measure is not null
    ) b
WHERE
    measure is not null
ORDER BY
    measure DESC

Кредит на этот метод идет Шломи Ноах. Об этом он подробно пишет здесь:

http://code.openark.org/blog/mysql/sql-ranking-without-self-join

Я проверил это в MySQL, и оно прекрасно работает; понятия не имею об Oracle, SQLServer и т. д.

Это работает очень хорошо. Гений SQL.
К сожалению, это зависит от порядка оценки пользовательских переменных, который является неопределенным поведением. Первый комментарий в этой ссылке цитирует руководство по MySQL: «Порядок оценки пользовательских переменных не определен и может изменяться в зависимости от элементов, содержащихся в данном запросе .... Общее правило - никогда не назначать значение пользователю переменная в одной части оператора и использовать ту же переменную в другой части того же оператора. Вы можете получить ожидаемые результаты, но это не гарантировано. & Quot; Ссылка:dev.mysql.com/doc/refman/5.1/en/user-variables.html
0

я бы сказал, что вам нужно (слева) выполнить внешнее присоединение к таблице, например:

select t1.name, t1.value, count(distinct isnull(t2.value,0))  
from table t1  
left join table t2  
on t1.value>t2.value  
group by t1.name, t1.value 

Для каждой строки вы посчитаете, сколько (если таковые имеются) строки одной и той же таблицы имеют худшее значение.

Обратите внимание, что я больше знаком с sqlserver, поэтому синтаксис может быть неправильным. Также отличное может не иметь правильного поведения для того, чего вы хотите достичь. Но это общая идея.
Затем, чтобы получить реальный процентильный ранг, вам нужно сначала получить количество значений в переменной (или различных значений в зависимости от соглашения, которое вы хотите принять) и вычислить процентильный ранг, используя реальный ранг, указанный выше.

2

таким как PHP, вы можете сделать следующее. Этот пример разбивает лишние времена блокирования полета на аэропорт, на их процентили. Использует предложение LIMIT x, y в MySQL в сочетании сORDER BY, Не очень красиво, но делает работу (извините, боролся с форматированием):

$startDt = "2011-01-01";
$endDt = "2011-02-28";
$arrPort= 'JFK';

$strSQL = "SELECT COUNT(*) as TotFlights FROM FIDS where depdt >= '$startDt' And depdt <= '$endDt' and ArrPort='$arrPort'";
if (!($queryResult = mysql_query($strSQL, $con)) ) {
    echo $strSQL . " FAILED\n"; echo mysql_error();
    exit(0);
}
$totFlights=0;
while($fltRow=mysql_fetch_array($queryResult)) {
    echo "Total Flights into " . $arrPort . " = " . $fltRow['TotFlights'];
    $totFlights = $fltRow['TotFlights'];

    /* 1906 flights. Percentile 90 = int(0.9 * 1906). */
    for ($x = 1; $x<=10; $x++) {
        $pctlPosn = $totFlights - intval( ($x/10) * $totFlights);
        echo "PCTL POSN for " . $x * 10 . " IS " . $pctlPosn . "\t";
        $pctlSQL = "SELECT  (ablk-sblk) as ExcessBlk from FIDS where ArrPort='" . $arrPort . "' order by ExcessBlk DESC limit " . $pctlPosn . ",1;";
        if (!($query2Result = mysql_query($pctlSQL, $con)) ) {
            echo $pctlSQL  . " FAILED\n";
            echo mysql_error();
            exit(0);
        }
        while ($pctlRow = mysql_fetch_array($query2Result)) {
            echo "Excess Block is :" . $pctlRow['ExcessBlk'] . "\n";
        }
    }
}
0

user_id, узлы

тогда следующий запрос даст процентиль каждого пользователя:

select a.user_id,a.units,
(sum(case when a.units >= b.units then 1 else 0 end )*100)/count(1) percentile
from sales a join sales b ;

Обратите внимание, что это пойдет на перекрестное соединение, что приведет к сложности O (n2), поэтому может рассматриваться как неоптимизированное решение, но кажется простым, поскольку в версии mysql у нас нет никакой функции.

Похожие вопросы