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>
Pingback: Funciones analíticas RANK, DENSE_RANK, FIRST y LAST - DBandTech.com