Usar los percentiles con SQL

Nota del editor: Este artículo fue publicado originalmente en inglés. Con la aprobación del autor, el equipo de IDUG ha creado la traducción que a continuación se presenta.

Con las funciones SQL OLAP RANK y DENSE_RANK disponibles en programación SQL desde hace un tiempo, resulta bastante fácil preguntar por las “n” ocurrencias de valor más alto en un conjunto de datos ordenado. Lo que se conoce como “TOP n”. Tome como ejemplo la consulta que devuelve los TOP 5 empleados de la table EMP basándonos en la columna SALARY. La utilización de la función DENSE_RANK se debe a que algunos empleados pudieran tener el mismo salario, como ocurre en el siguiente ejemplo. Verá que el número de empleados que forman el grupo con los 5 salarios más altos es 6, y que hay 2 que tienen el salario más alto de $52,750.00.

WITH X AS
  (SELECT EMPNO, SALARY,
          DENSE_RANK() OVER (ORDER BY SALARY DESC) AS RANK
    FROM EMP)
 SELECT EMPNO, SALARY, RANK
 FROM X
 WHERE RANK < 6;

 

 Result:

andrews-percentiles-1.png

Pero cuando hablamos de percentiles, cómo preguntamos y obtenemos resultados para el 5% más alto o el 10% más bajo del conjunto de datos? Veamos primero algunas definiciones:

Percentile: Percentile es un valor utilizado en estadísticas para indicar el valor, en porcentaje, del número de ocurrencias en un grupo ordenado de datos que quedan por debajo de dicho valor percentile. Por ejemplo: percentile 20 es el valor utilizado para indicar el 20% más bajo de las ocurrencias del conjunto de datos ordenados. (Diccionario RAE).

Rango-percentile: el rango-percentile de un ensayo es la posición en el orden de resultados, en su frecuencia de distribución, que son igual o menor que dicho rango. Por ejemplo, una ocurrencia en la que el valor buscado  es mayor que el valor del 75% del total de las ocurrencias de la prueba realizada, se dice que está en el percentile 75, siendo 75 el rango-percentile.

Tomemos la misma consulta anterior y recuperemos el 10% superior de los empleados según su salario. Usted hace esto por: 

  • Dividir su rango por el número de empleados en el grupo (debe saberlo o calcularlo) 
  • Multiplica por 100, y luego resta ese número de 100.

Por ejemplo, si hay 600 estudiantes en su curso y usted esta en el rango-percentile 120 (la posición 120 en el conjunto total ordenado de calificaciones es justo el 20%), usted está en el percentile 80 porque (120/600)*100=20, y 100-20=80. Así pues, su calificación con percentile 80 está en el TOP 20% (rango-percentile 20) de su curso.

En nuestra pequeña tabla de empleados (33 filas  donde hay 2 empleados ((‘000010’ and ‘000011’) con el salario más alto, tratemos de obtener el TOP 10%.Pero primero obtengamos todas las filas con el percentile calculado. Las primeras 24 filas son mostradas a continuación:

 

WITH TEMP_RANK AS                                             
   (SELECT EMPNO, LASTNAME, SALARY,                              
       SMALLINT(DENSE_RANK () OVER (ORDER BY SALARY ASC) ) AS RANK
     FROM EMP                                           
     )                                         
SELECT EMPNO, LASTNAME, SALARY, RANK,                         
              (RANK/33.0) * 100 AS PERCENTILE                         
FROM TEMP_RANK                                                 
ORDER BY PERCENTILE DESC

andrews-percentiles-2.png

Si usted no sabe exactamente su posición dentro del grupo consultado, puede calcularlo usando un SQL Scalar Fullselect en la “common table expression”. En este grupo, incluimos todas ocurrencias en la tabla.

WITH TEMP_RANK AS
    (SELECT EMPNO, LASTNAME, SALARY, 
         SMALLINT(DENSE_RANK () OVER (ORDER BY SALARY ASC) ) AS RANK ,
         (SELECT DEC(COUNT(*),5,2)  FROM EMP) AS NUM_EMPS
     FROM EMP )
SELECT EMPNO, LASTNAME, SALARY, RANK, 
       (RANK/NUM_EMPS) * 100 AS PERCENTILE FROM TEMP_RANK
ORDER BY PERCENTILE DESC                               

NOTA: Al ejecutar la función COUNT, necesita representarlo como DECIMAL porque en SQL, si dividimos un número entero por otro número entero, el resultado es otro número entero sin decimales. Y nosotros necesitamos los decimales en este caso.

La consulta siguiente nos devolverá el TOP 10% (PERCENTILE >=90) que estamos buscando. Para ello, necesitamos calcular el percentile dentro de “common table expression” para, después, podemos añadirlo en  la lógica “WHERE ” de la consulta:

WITH TEMP_RANK AS
   (SELECT EMPNO, LASTNAME, SALARY,
                   DENSE_RANK () OVER (ORDER BY SALARY ASC)  AS  RANK,
                  (DENSE_RANK () OVER (ORDER BY SALARY ASC)  / 33.0)  * 100 AS PERCENTILE
   FROM EMP
    )                                        
SELECT EMPNO, LASTNAME, SALARY, RANK, PERCENTILE
FROM TEMP_RANK
WHERE PERCENTILE >= 90
ORDER BY PERCENTILE DESC

 

andrews-percentiles-3.png

 

 

NOTA: En nuestros datos en EMP, ambos empleados ‘000010’ y ‘000011’ se llaman HAAS y tienen idéntico más alto salario.

De nuevo, si necesita calcular la función COUNT dentro de su grupo, añádalo dentro de la “common table expression”. 

WITH TEMP_RANK AS                                              
    (SELECT EMPNO, LASTNAME, SALARY,                             
                    DENSE_RANK () OVER (ORDER BY SALARY ASC)  AS  RANK,
                   (DENSE_RANK () OVER (ORDER BY SALARY ASC)  /
                         (SELECT DEC(COUNT(*),5,2) FROM EMP))  * 100 AS PERCENTILE
     FROM EMP                                           
     )                                         
  SELECT EMPNO, LASTNAME, SALARY, RANK,                         
          PERCENTILE                                             
   FROM TEMP_RANK                                                 
  WHERE PERCENTILE >= 90                                         
  ORDER BY PERCENTILE DESC                 

Usted podría también ejecutar esta lógica ordenando por ‘SALARY DESC’ primero, y luego cambiando la lógica WHERE sobre la columna PERCENTILE (percentil <= 10 pues el orden es DECENDENTE). Por ejemplo, la consulta siguiente también nos devuelve los mismos empleados que tiene los salarios TOP 10%.   

WITH TEMP_RANK AS
   (SELECT EMPNO, LASTNAME, SALARY,
      DENSE_RANK () OVER (ORDER BY SALARY DESC)  AS RANK,
      (DENSE_RANK () OVER (ORDER BY SALARY DESC)  /
                  (SELECT DEC(COUNT(*),5,2) FROM EMP)) * 100 AS PERCENTILE
    FROM EMP
    )
  SELECT EMPNO, LASTNAME, SALARY, RANK,
         PERCENTILE
  FROM TEMP_RANK
  WHERE PERCENTILE <= 10
  ORDER BY RANK

 

andrews-percentiles-4.png 

Recent Stories
Una introducción a REST

Partition By Growth Table Spaces - Partición 1, el comienzo

Los 10 temores sobre el futuro del campo de DBMS de acuerdo al Dr. Michael Stonebraker