PERCENT_RANK Función analítica

Este artículo ofrece una descripción general de la función analítica PERCENT_RANK. Si es nuevo en las funciones analíticas, probablemente debería leer primero esta introducción a las funciones analíticas.

  • Preparar
  • PERCENT_RANK como una Función Agregada
  • Función Analítica PERCENT_RANK

Preparar

Los ejemplos de este artículo requieren la siguiente tabla.

--DROP TABLE emp PURGE;

CREATE TABLE emp (
  empno    NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY,
  ename    VARCHAR2(10),
  job      VARCHAR2(9),
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2)
);

INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;

PERCENT_RANK como función agregada

La función agregada PERCENT_RANK es predictiva y devuelve el rango como un porcentaje (entre 0 y 1) del valor especificado en un conjunto. Como función agregada, reduce el número de filas, de ahí el término «aggregate». Si los datos no están agrupados, convertimos las 14 filas de la tabla EMP en una la fila con el valor agregado. En el siguiente ejemplo, vemos la clasificación porcentual del valor especificado en relación con los salarios de todos los empleados.

SELECT PERCENT_RANK(2000) WITHIN GROUP (ORDER BY sal) AS percent_rank_sal,
       ROUND(PERCENT_RANK(2000) WITHIN GROUP (ORDER BY sal)*100,2) AS percent_rank_sal_pct
FROM   emp;

PERCENT_RANK_SAL PERCENT_RANK_SAL_PCT
---------------- --------------------
      .571428571                57.14

SQL>

Podemos obtener más granularidad de la información al incluir una cláusula GROUP BY. En el siguiente ejemplo, vemos la clasificación porcentual del valor especificado en relación con los salarios por departamento.

SELECT deptno,
       PERCENT_RANK(2000) WITHIN GROUP (ORDER BY sal) AS percent_rank_sal_by_dept,
       ROUND(PERCENT_RANK(2000) WITHIN GROUP (ORDER BY sal)*100,2) AS percent_rank_sal_pct_by_dept
FROM   emp
GROUP BY deptno
ORDER BY deptno;

    DEPTNO PERCENT_RANK_SAL_BY_DEPT PERCENT_RANK_SAL_PCT_BY_DEPT
---------- ------------------------ ----------------------------
        10               .333333333                        33.33
        20                       .4                           40
        30               .833333333                        83.33

SQL>

Se puede especificar más de una columna en la función agregada.

SELECT deptno,
       PERCENT_RANK(2000, 0.1) WITHIN GROUP (ORDER BY sal, comm) AS percent_rank_sal_by_dept,
       ROUND(PERCENT_RANK(2000, 0.1) WITHIN GROUP (ORDER BY sal, comm)*100,2) AS percent_rank_sal_pct_by_dept
FROM   emp
GROUP BY deptno
ORDER BY deptno;

    DEPTNO PERCENT_RANK_SAL_BY_DEPT PERCENT_RANK_SAL_PCT_BY_DEPT
---------- ------------------------ ----------------------------
        10               .333333333                        33.33
        20                       .4                           40
        30               .833333333                        83.33

SQL>

En ambos casos, hemos agregado los datos para obtener los valores, devolviendo menos filas de las que comenzamos. Las funciones analíticas nos permiten devolver estos valores agregados conservando los datos de la fila original.

PERCENT_RANK Función analítica

La descripción básica de la función analítica PERCENT_RANK se muestra a continuación.

PERCENT_RANK() OVER ([ query_partition_clause ] order_by_clause)

La función analítica PERCENT_RANK es sensible al orden, por lo que la cláusula ORDER BY es obligatoria. Omitir una cláusula de partición de la cláusula OVER significa que todo el conjunto de resultados se trata como una sola partición. A la primera fila del conjunto ordenado se le asigna 0 y a la última fila del conjunto se le asigna 1. Si hay una sola fila en el conjunto, se le asigna 0. A los vínculos se les asigna el mismo valor. En el siguiente ejemplo mostramos el rango porcentual, o la posición relativa en el conjunto, de cada uno de todos los empleados, así como todos los datos originales.

SELECT empno,
       ename,
       deptno,
       sal,
       PERCENT_RANK() OVER (ORDER BY sal) AS percent_rank_sal,
       ROUND(PERCENT_RANK() OVER (ORDER BY sal)*100) AS percent_rank_sal_pct
FROM   emp;

     EMPNO ENAME          DEPTNO        SAL PERCENT_RANK_SAL PERCENT_RANK_SAL_PCT
---------- ---------- ---------- ---------- ---------------- --------------------
      7369 SMITH              20        800                0                    0
      7900 JAMES              30        950      .0769230769                    8
      7876 ADAMS              20       1100       .153846154                   15
      7521 WARD               30       1250       .230769231                   23
      7654 MARTIN             30       1250       .230769231                   23
      7934 MILLER             10       1300       .384615385                   38
      7844 TURNER             30       1500       .461538462                   46
      7499 ALLEN              30       1600       .538461538                   54
      7782 CLARK              10       2450       .615384615                   62
      7698 BLAKE              30       2850       .692307692                   69
      7566 JONES              20       2975       .769230769                   77
      7788 SCOTT              20       3000       .846153846                   85
      7902 FORD               20       3000       .846153846                   85
      7839 KING               10       5000                1                  100

SQL>

Agregar la cláusula de partición nos permite mostrar el rango porcentual de cada empleado dentro de una partición.

SELECT empno,
       ename,
       deptno,
       sal,
       PERCENT_RANK() OVER (PARTITION BY deptno ORDER BY sal) AS percent_rank_sal_by_dept,
       ROUND(PERCENT_RANK() OVER (PARTITION BY deptno ORDER BY sal)*100) AS percent_rank_sal_pct_by_dept
FROM   emp;

     EMPNO ENAME          DEPTNO        SAL PERCENT_RANK_SAL_BY_DEPT PERCENT_RANK_SAL_PCT_BY_DEPT
---------- ---------- ---------- ---------- ------------------------ ----------------------------
      7934 MILLER             10       1300                        0                            0
      7782 CLARK              10       2450                       .5                           50
      7839 KING               10       5000                        1                          100
      7369 SMITH              20        800                        0                            0
      7876 ADAMS              20       1100                      .25                           25
      7566 JONES              20       2975                       .5                           50
      7788 SCOTT              20       3000                      .75                           75
      7902 FORD               20       3000                      .75                           75
      7900 JAMES              30        950                        0                            0
      7654 MARTIN             30       1250                       .2                           20
      7521 WARD               30       1250                       .2                           20
      7844 TURNER             30       1500                       .6                           60
      7499 ALLEN              30       1600                       .8                           80
      7698 BLAKE              30       2850                        1                          100

SQL>

Asignar un porcentaje nos permite hacer un tipo de consulta Top-N en función del porcentaje. La siguiente consulta devuelve el 30% superior de los empleados de la empresa en función de su salario.

SELECT *
FROM   (SELECT empno,
               ename,
               deptno,
               sal,
               PERCENT_RANK() OVER (ORDER BY sal) AS percent_rank_sal
        FROM   emp)
WHERE  percent_rank_sal >= 0.7;

     EMPNO ENAME          DEPTNO        SAL PERCENT_RANK_SAL
---------- ---------- ---------- ---------- ----------------
      7566 JONES              20       2975       .769230769
      7788 SCOTT              20       3000       .846153846
      7902 FORD               20       3000       .846153846
      7839 KING               10       5000                1

SQL>

Si hay una sola fila en el conjunto, se le asigna 0.

SELECT empno,
       ename,
       deptno,
       sal,
       PERCENT_RANK() OVER (PARTITION BY deptno ORDER BY sal) AS percent_rank_sal_by_dept,
       ROUND(PERCENT_RANK() OVER (PARTITION BY deptno ORDER BY sal)*100) AS percent_rank_sal_pct_by_dept
FROM   emp
WHERE  empno = 7934;

     EMPNO ENAME          DEPTNO        SAL PERCENT_RANK_SAL_BY_DEPT PERCENT_RANK_SAL_PCT_BY_DEPT
---------- ---------- ---------- ---------- ------------------------ ----------------------------
      7934 MILLER             10       1300                        0                            0

SQL>

1 comentario en “PERCENT_RANK Función analítica”

  1. Pingback: Funciones analíticas RANK, DENSE_RANK, FIRST y LAST - DBandTech.com

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *