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:
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
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
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
#espanol