Yo tengo un gran tabla de medición de datos en MySQL y necesito calcular el percentil para cada uno de estos valores. Oracle parece tener una función llamada percent_rank pero no puedo encontrar nada similar para MySQL. Estoy seguro de que podría sólo fuerza bruta en Python que puedo usar de todos modos para rellenar la tabla, pero sospecho que sería muy ineficiente debido a que una muestra puede tener 200.000 observaciones.

InformationsquelleAutor lhahne | 2009-06-29

9 Comentarios

  1. 2

    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

    • Intenté que hace un par de semanas y era increíblemente lento, así que terminé el cálculo de los percentiles en python y poner el valor en la base de datos.
    • Has probado a usar el select count() y select count() <= yourvalue? ¿Ha confirmado que dos de ellos estaban siendo manejado por un índice que sólo tenía las columnas que usted necesita? Si la solución tenía que tocar las filas de datos a todos, espero que sea uno o dos órdenes de magnitud más lento. Si los índices se incluyó a más de las columnas necesarias o la memoria de configuración de MySQL no fue la instalación de la derecha, es muy lenta. Si es así, esto debería haber sido más rápido. Aproximadamente cuánto tiempo es «muy lento»? En función del orden de magnitud de la respuesta esperada, mi respuesta podría ser unwholesomely lento.
    • Respuesta correcta, pero corto en el código. Si pones un funcional «select distinct’ tipo de consulta, puede mi +1. También, si se puede solucionar este problema, consigue un bonito y brillante +1 y comprobar! ;)) stackoverflow.com/questions/13689434/…
  2. 19

    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.

    • Por desgracia, esto depende del orden de evaluación de variables de usuario, que es un comportamiento indefinido. El primer comentario en el enlace que citas MySQL manual: «El orden de evaluación de variables de usuario no está definido y puede cambiar en función de los elementos contenidos dentro de una determinada consulta….La regla general es nunca para asignar un valor a una variable de usuario en una parte de una declaración y uso de la misma variable en alguna otra parte de la misma declaración. Usted puede obtener los resultados que esperas, pero esto no está garantizado.» Referencia: dev.mysql.com/doc/refman/5.1/en/user-variables.html
  3. 5
    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;
  4. 2

    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";
            }
        }
    }
  5. 1

    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.

  6. 0

    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.

  7. 0

    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.

  8. -1

    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 ….

Dejar respuesta

Please enter your comment!
Please enter your name here