Calcular el percentil en MySQL

Este es un relativamente feo respuesta, y me siento culpable de decirlo. Dicho esto, puede ayudarle con su problema.

Una forma de determinar el porcentaje sería de contar todas las filas, y contar el número de filas que son mayores que la cantidad que usted proporcionó. Usted puede calcular ya sea mayor o menor que y tome la inversa como sea necesario.

Crear un índice en su número.
total = select count();
less_equal = select count (), donde el valor > indexed_number;

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

Asegúrese de que ambos de ellos están utilizando el índice que ha creado. Si no lo son, ajustar hasta que se. El explicar consulta debe tener «uso de índice» en la columna de la derecha. En el caso de la select count(*) se debe utilizar el índice para InnoDB y algo como const para MyISAM. MyISAM se conoce este valor en cualquier momento sin tener que calcularlo.

Si usted necesita tener el porcentaje almacenados en la base de datos, puede utilizar el programa de instalación desde arriba de rendimiento y, a continuación, calcular el valor de cada fila por el uso de la segunda consulta como un select interno. La primera consulta del valor puede ser establecido como una constante.

Hace esta ayuda?

Jacob

Aquí es un enfoque diferente que no requiere de una combinación. En mi caso (una tabla con 15.000+) filas, se ejecuta en unos 3 segundos. (El método de combinación se lleva un orden de magnitud mayor).

En el ejemplo, suponga que medida es la columna en la que se está calculando el rango de porcentaje, y id es sólo un identificador de fila (no obligatorio):

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

De crédito para este método va a Shlomi Noaj. Escribe sobre ello en detalle aquí:

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

He probado esta en MySQL y funciona muy bien; ninguna idea acerca de Oracle, SQLServer, etc.

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

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

Si usted es la combinación de SQL con un lenguaje procedimental como PHP, puede hacer lo siguiente. En este ejemplo se descompone el exceso de vuelo bloque veces en un aeropuerto, en sus percentiles. Utiliza el LÍMITE de x,y la cláusula en MySQL en combinación con ORDER BY. No muy bonito, pero hace el trabajo (lo siento luchado con 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";
        }
    }
}

MySQL 8 finalmente introdujo las funciones de la ventana, y entre ellos, el PERCENT_RANK() función que usted estaba buscando. Así, acaba de escribir:

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

Su pregunta menciona «percentiles», que son un poco diferente de la cosa. La integridad de’ bien, hay PERCENTILE_DISC y PERCENTILE_CONT inversa de la distribución de funciones 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 la ventana, usted puede emular PERCENTILE_DISC, sin embargo, de nuevo utilizando el PERCENT_RANK y FIRST_VALUE las funciones de la ventana.

Para obtener el rango, yo diría que usted necesita (a la izquierda) combinación externa de la tabla en sí mismo algo así 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, el recuento de la cantidad (si alguna) de las filas de la misma tabla tienen un valor inferior.

Nota que estoy más familiarizado con sqlserver por lo que la sintaxis puede no ser adecuado. También las distintas pueden no tener el comportamiento correcto para lo que quieres lograr. Pero esa es la idea general.

A continuación, para obtener la verdadera percentil usted tendrá que obtener primero el número de valores de una variable (o valores distintos dependiendo de la convención usted quiere tomar) y calcular el percentil mediante el real clasificación dada anteriormente.

Supongamos que tenemos una tabla de ventas como :

user_id,unidades

a continuación, siguiente consulta dará 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 va a ir para la cruz de unirse por lo que el resultado en O(n2) complejidad, de modo que puede ser considerado como unoptimized solución, pero parece simple, dado que no tiene ninguna función en la versión de mysql.

No está seguro de lo que el op entiende por ‘percentil’, pero para obtener un determinado percentil de un conjunto de valores que ver http://rpbouman.blogspot.com/2008/07/calculating-nth-percentile-in-mysql.html
El sql de cálculo puede ser fácilmente modificada para producir otro o varios percentiles.

Una nota: he tenido 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 era saltarse dos valores de pasado el punto porcentual en la lista ordenada, en lugar de elegir el siguiente valor más alto para el percentil. Tal vez la manera entero de redondeo funciona en mysql.

es decir:

…. SUBSTRING_INDEX(SUBSTRING_INDEX( GROUP_CONCAT(fieldValue ORDEN POR fieldValue SEPARADOR ‘,’), ‘,’, 90/100 * CONTAR(*) + 0.5), ‘,’, -1) como 90thPercentile ….