percentiles - Cálculo del rango percentil en MySQL

rango percentil wikipedia

Question

Tengo una gran tabla de datos de medición en MySQL y necesito calcular el rango percentil para cada uno de estos valores. Oracle parece tener una función llamada percent_rank pero no puedo encontrar nada similar para MySQL. Claro que podría hacerlo con fuerza bruta en Python, que de todos modos utilizo para poblar la tabla, pero sospecho que sería bastante ineficiente porque una muestra podría tener 200.000 observaciones.

Answer 1

Aquí hay un enfoque diferente que no requiere una unión. En mi caso (una tabla con más de 15,000 filas), se ejecuta en aproximadamente 3 segundos. (El método JOIN toma un orden de magnitud más largo).

En el ejemplo, suponga que la medida es la columna en la que está calculando el rango porcentual, e id es solo un identificador de fila (no es necesario):

SELECT
    id,
    @prev := @curr as prev,
    @curr := measure as curr,
    @rank := IF(@prev > @curr, @rank+@ties, @rank) AS rank,
    @ties := IF(@prev = @curr, @ties+1, 1) AS ties,
    (1-@rank/@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

El crédito por este método es para Shlomi Noaj. Él escribe sobre esto en detalle aquí:

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

He probado esto en MySQL y funciona muy bien; ni idea de Oracle, SQLServer, etc.

Answer 2

Esta es una respuesta relativamente fea, y me siento culpable al decirlo. Dicho esto, podría ayudarte con tu problema.

Una forma de determinar el porcentaje sería contar todas las filas y contar el número de filas que son mayores que el número que proporcionó. Puede calcular mayor o menor y tomar el inverso según sea necesario.

Crea un índice en tu número. total = seleccionar cuenta ( ); less_equal = selecciona count ( ) donde value> indexed_number;

El porcentaje sería algo como: less_equal / total o (total - less_equal) / total

Asegúrese de que ambos estén usando el índice que creó. Si no lo son, pellizcalos hasta que lo estén. La consulta de explicación debe tener "usando índice" en la columna de la derecha. En el caso del recuento seleccionado (*), debería estar usando el índice para InnoDB y algo como const para MyISAM. MyISAM sabrá este valor en cualquier momento sin tener que calcularlo.

Si necesitaba tener el porcentaje almacenado en la base de datos, puede usar la configuración de arriba para mejorar el rendimiento y luego calcular el valor de cada fila utilizando la segunda consulta como una selección interna. El valor de la primera consulta se puede establecer como una constante.

¿Esto ayuda?

Jacob

Answer 3

MySQL 8 finalmente introdujo las funciones de ventana, y entre ellas, la función PERCENT_RANK() que estaba buscando. Entonces, sólo escribe:

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

Su pregunta menciona "percentiles", que son una cosa ligeramente diferente. Para completar, hay funciones de distribución inversa PERCENTILE_DISC y PERCENTILE_CONT en el estándar SQL y en algunos RBDMS (Oracle, PostgreSQL, SQL Server, Teradata), pero no en MySQL. Con MySQL 8 y las funciones de ventana, puede emular PERCENTILE_DISC , sin embargo, nuevamente utilizando las funciones de ventana PERCENT_RANK y FIRST_VALUE .

Answer 4

No estoy seguro de qué se entiende por operación con ''rango percentil'', pero para obtener un percentil dado para un conjunto de valores, visite http://rpbouman.blogspot.com/2008/07/calculating-nth-percentile-in-mysql.html El cálculo de sql podría cambiarse fácilmente para producir otros percentiles o múltiples.

Una nota: tuve que cambiar el cálculo ligeramente, por ejemplo, el percentil 90 - "90/100 * COUNT (*) + 0.5" en lugar de "90/100 * COUNT (*) + 1". A veces saltaba dos valores más allá del punto de percentil en la lista ordenada, en lugar de elegir el siguiente valor más alto para el percentil. Tal vez la forma en que el redondeo de enteros funciona en mysql.

es decir:

.... SUBSTRING_INDEX (SUBSTRING_INDEX (GROUP_CONCAT (fieldValue ORDER BY fieldValue SEPARATOR '',''), '','', 90/100 * COUNT (*) + 0.5 ), '','', -1) as 90thPercentile ...

Answer 5

No hay una manera fácil de hacer esto. ver http://rpbouman.blogspot.com/2008/07/calculating-nth-percentile-in-mysql.html

Answer 6

Para obtener el rango, diría que necesita (izquierda) unirse a la tabla en sí misma algo como:

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 

Para cada fila, contará cuántas filas (si las hay) de la misma tabla tienen un valor inferior.

Tenga en cuenta que estoy más familiarizado con sqlserver, por lo que la sintaxis podría no ser correcta. Además, los distintos pueden no tener el comportamiento correcto para lo que usted desea lograr. Pero esa es la idea general.
Luego, para obtener el rango percentil real, primero deberá obtener el número de valores en una variable (o valores distintos dependiendo de la convención que desee tomar) y calcular el rango percentil utilizando el rango real que se indica arriba.

Answer 7

Si está combinando su SQL con un lenguaje de procedimiento como PHP, puede hacer lo siguiente. Este ejemplo desglosa el exceso de tiempo de bloqueo de vuelo en un aeropuerto, en sus percentiles. Utiliza la cláusula LIMIT x, y en MySQL en combinación con ORDER BY . No es muy bonito, pero cumple su función (perdón por el formato):

$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";
        }
    }
}

Answer 8

Supongamos que tenemos una tabla de ventas como:

user_id, unidades

Luego la siguiente consulta le dará el percentil de cada usuario:

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 ;

Tenga en cuenta que esto se aplicará a la combinación cruzada, por lo que la complejidad de O (n2) puede considerarse como una solución no optimizada, pero parece simple dado que no tenemos ninguna función en la versión mysql.

Answer 9

SELECT 
    c.id, c.score, ROUND(((@rank - rank) / @rank) * 100, 2) AS percentile_rank
FROM
    (SELECT 
    *,
        @prev:=@curr,
        @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;