Вычисление рейтинга процентиля в MySQL

Ответ 1

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

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

Создайте индекс на своем номере. total = select count(); less_equal = select count() где value > indexed_number;

Процент будет выглядеть примерно следующим образом: less_equal/total или (total-less_equal)/total

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

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

Помогает ли это?

Jacob

Ответ 2

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

В примере предположим, что мерой является столбец, на котором вы вычисляете процентный ранг, а 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 и т.д.

Ответ 3

нет простого способа сделать это. см. http://rpbouman.blogspot.com/2008/07/calculating-nth-percentile-in-mysql.html

Ответ 4

Если вы комбинируете свой SQL с процедурным языком, например 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";
        }
    }
}

Ответ 5

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;

Ответ 6

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

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, поэтому синтаксис может быть неправильным. Кроме того, четкие могут не иметь правильного поведения для того, чего вы хотите достичь. Но эта общая идея.
Затем, чтобы получить реальный рейтинг процентилей, вам нужно сначала получить количество значений в переменной (или различных значениях в зависимости от соглашения, которое вы хотите принять) и вычислить ранги процентиля с использованием настоящего ранга, указанного выше.

Ответ 7

Предположим, у нас есть таблица продаж, например:

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.

Ответ 8

В MySQL 8 наконец-то появились оконные функции, и среди них - функция PERCENT_RANK() вы искали. Итак, просто напишите:

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

Ваш вопрос упоминает "процентили", которые немного отличаются. Для полноты PERCENTILE_DISC PERCENTILE_CONT функции обратного распределения PERCENTILE_DISC и PERCENTILE_CONT в стандарте SQL и некоторых RBDMS (Oracle, PostgreSQL, SQL Server, Teradata), но не в MySQL. С MySQL 8 и оконными функциями вы можете эмулировать PERCENTILE_DISC, однако, снова используя оконные функции PERCENT_RANK и FIRST_VALUE.

Ответ 9

Не уверен, что в операторе подразумевается "процентильный ранг", но чтобы получить данный процентиль для набора значений, см. Http://rpbouman.blogspot.com/2008/07/calculation-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) как 90thPercentile....