Función analítica CUME_DIST

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

  • Preparar
  • CUME_DIST como una función Agregada
  • Función Analitica CUME_DIST

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;

CUME_DIST como función agregada

La función agregada CUME_DIST es predictiva y devuelve la distribución acumulativa 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 sola fila con el valor agregado. En el siguiente ejemplo, vemos la distribución acumulativa del valor especificado en relación con los salarios de todos los empleados.

SELECT CUME_DIST(2000) WITHIN GROUP (ORDER BY sal) AS cume_dist_sal
FROM   emp;

CUME_DIST_SAL
-------------
           .6

SQL>

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

SELECT deptno,
       CUME_DIST(2000) WITHIN GROUP (ORDER BY sal) AS cume_dist_sal_by_dept
FROM   emp
GROUP BY deptno
ORDER BY deptno;

    DEPTNO CUME_DIST_SAL_BY_DEPT
---------- ---------------------
        10                    .5
        20                    .5
        30            .857142857

SQL>

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

SELECT deptno,
       CUME_DIST(2000, 0.1) WITHIN GROUP (ORDER BY sal, comm) AS cume_dist_sal_by_dept
FROM   emp
GROUP BY deptno
ORDER BY deptno;

    DEPTNO CUME_DIST_SAL_BY_DEPT
---------- ---------------------
        10                    .5
        20                    .5
        30            .857142857

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.

Función analítica CUME_DIST

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

CUME_DIST() OVER ([ query_partition_clause ] order_by_clause)

La función analítica CUME_DIST 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. En el siguiente ejemplo mostramos la distribución acumulada, 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,
       CUME_DIST() OVER (ORDER BY sal) AS cume_dist_sal
FROM   emp;

     EMPNO ENAME          DEPTNO        SAL CUME_DIST_SAL
---------- ---------- ---------- ---------- -------------
      7369 SMITH              20        800   .0714285714
      7900 JAMES              30        950    .142857143
      7876 ADAMS              20       1100    .214285714
      7521 WARD               30       1250    .357142857
      7654 MARTIN             30       1250    .357142857
      7934 MILLER             10       1300    .428571429
      7844 TURNER             30       1500            .5
      7499 ALLEN              30       1600    .571428571
      7782 CLARK              10       2450    .642857143
      7698 BLAKE              30       2850    .714285714
      7566 JONES              20       2975    .785714286
      7788 SCOTT              20       3000    .928571429
      7902 FORD               20       3000    .928571429
      7839 KING               10       5000             1

SQL>

Agregar la cláusula de partición nos permite mostrar la distribución acumulativa de cada empleado dentro de una partición.

SELECT empno,
       ename,
       deptno,
       sal,
       CUME_DIST() OVER (PARTITION BY deptno ORDER BY sal) AS cume_dist_sal_by_dept
FROM   emp;

     EMPNO ENAME          DEPTNO        SAL CUME_DIST_SAL_BY_DEPT
---------- ---------- ---------- ---------- ---------------------
      7934 MILLER             10       1300            .333333333
      7782 CLARK              10       2450            .666666667
      7839 KING               10       5000                     1
      7369 SMITH              20        800                    .2
      7876 ADAMS              20       1100                    .4
      7566 JONES              20       2975                    .6
      7788 SCOTT              20       3000                     1
      7902 FORD               20       3000                     1
      7900 JAMES              30        950            .166666667
      7654 MARTIN             30       1250                    .5
      7521 WARD               30       1250                    .5
      7844 TURNER             30       1500            .666666667
      7499 ALLEN              30       1600            .833333333
      7698 BLAKE              30       2850                     1

SQL>

-- Bajar Articulo: Función analítica CUME_DIST como PDF --


Deja un comentario

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

error: Content is protected !!