Este artículo ofrece una descripción general de la función analítica SUM. Si es nuevo en las funciones analíticas, probablemente debería leer primero esta introducción a las funciones analíticas.
- Preparar
- SUM como una Función agregada
- Función Analítica SUM
- Enlace rápido
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;
SUM como función agregada
La función agregada SUM devuelve la suma de los valores especificados 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 el valor total de los salarios de todos los empleados.
SELECT SUM(sal) AS sum_total FROM emp; SUM_TOTAL ---------- 29025 SQL>
Podemos obtener más granularidad de la información al incluir una cláusula GROUP BY. En el siguiente ejemplo vemos la suma de los sueldos por departamento.
SELECT deptno, SUM(sal) AS sum_total_by_dept FROM emp GROUP BY deptno ORDER BY deptno; DEPTNO SUM_TOTAL_BY_DEPT ---------- ----------------- 10 8750 20 10875 30 9400 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 SUM
La descripción básica de la función analítica SUM se muestra a continuación.
SUM([ DISTINCT | ALL ] expr) [ OVER (analytic_clause)
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 los salarios totales de todos los empleados, así como todos los datos originales.
SELECT empno, ename, deptno, sal, SUM(sal) OVER () AS total_sal FROM emp; EMPNO ENAME DEPTNO SAL TOTAL_SAL ---------- ---------- ---------- ---------- ---------- 7369 SMITH 20 800 29025 7499 ALLEN 30 1600 29025 7521 WARD 30 1250 29025 7566 JONES 20 2975 29025 7654 MARTIN 30 1250 29025 7698 BLAKE 30 2850 29025 7782 CLARK 10 2450 29025 7788 SCOTT 20 3000 29025 7839 KING 10 5000 29025 7844 TURNER 30 1500 29025 7876 ADAMS 20 1100 29025 7900 JAMES 30 950 29025 7902 FORD 20 3000 29025 7934 MILLER 10 1300 29025 SQL>
Agregar la cláusula de partición nos permite mostrar el salario total dentro de una partición.
SELECT empno, ename, deptno, sal, SUM(sal) OVER (PARTITION BY deptno) AS total_sal_by_dept FROM emp; EMPNO ENAME DEPTNO SAL TOTAL_SAL_BY_DEPT ---------- ---------- ---------- ---------- ----------------- 7782 CLARK 10 2450 8750 7839 KING 10 5000 8750 7934 MILLER 10 1300 8750 7566 JONES 20 2975 10875 7902 FORD 20 3000 10875 7876 ADAMS 20 1100 10875 7369 SMITH 20 800 10875 7788 SCOTT 20 3000 10875 7521 WARD 30 1250 9400 7844 TURNER 30 1500 9400 7499 ALLEN 30 1600 9400 7900 JAMES 30 950 9400 7698 BLAKE 30 2850 9400 7654 MARTIN 30 1250 9400 SQL>
Agregar la cláusula ORDER BY nos permite mostrar un salario total acumulado dentro de una partición. En el siguiente ejemplo, se utiliza la cláusula de ventana predeterminada, además de especificarse explícitamente.
SELECT empno, ename, deptno, sal, SUM(sal) OVER (PARTITION BY deptno ORDER BY sal) AS running_tot_sal_by_dept_1, SUM(sal) OVER (PARTITION BY deptno ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_tot_sal_by_dept_2 FROM emp; EMPNO ENAME DEPTNO SAL RUNNING_TOT_SAL_BY_DEPT_1 RUNNING_TOT_SAL_BY_DEPT_2 ---------- ---------- ---------- ---------- ------------------------- ------------------------- 7934 MILLER 10 1300 1300 1300 7782 CLARK 10 2450 3750 3750 7839 KING 10 5000 8750 8750 7369 SMITH 20 800 800 800 7876 ADAMS 20 1100 1900 1900 7566 JONES 20 2975 4875 4875 7788 SCOTT 20 3000 10875 10875 7902 FORD 20 3000 10875 10875 7900 JAMES 30 950 950 950 7654 MARTIN 30 1250 3450 3450 7521 WARD 30 1250 3450 3450 7844 TURNER 30 1500 4950 4950 7499 ALLEN 30 1600 6550 6550 7698 BLAKE 30 2850 9400 9400 SQL>
La cláusula de ventana RANGE BETWEEN es un rango de informes, por lo que se incluyen todas las filas del mismo valor, lo que hace que los totales acumulados parezcan incorrectos, si eso no es lo que esperaba. Si cambiamos a la cláusula de ventanas ROWS BETWEEN, es posible que obtenga el resultado que esperaba. Mire la comparación entre los resultados de la primera llamada que usa la cláusula de ventana predeterminada y la cláusula de ventana explícita que usa ROWS BETWEEN a continuación.
SELECT empno, ename, deptno, sal, SUM(sal) OVER (PARTITION BY deptno ORDER BY sal) AS running_tot_sal_by_dept_1, SUM(sal) OVER (PARTITION BY deptno ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS row_running_tot_sal_by_dept_2 FROM emp; EMPNO ENAME DEPTNO SAL RUNNING_TOT_SAL_BY_DEPT_1 ROW_RUNNING_TOT_SAL_BY_DEPT_2 ---------- ---------- ---------- ---------- ------------------------- ----------------------------- 7934 MILLER 10 1300 1300 1300 7782 CLARK 10 2450 3750 3750 7839 KING 10 5000 8750 8750 7369 SMITH 20 800 800 800 7876 ADAMS 20 1100 1900 1900 7566 JONES 20 2975 4875 4875 7788 SCOTT 20 3000 10875 7875 7902 FORD 20 3000 10875 10875 7900 JAMES 30 950 950 950 7654 MARTIN 30 1250 3450 2200 7521 WARD 30 1250 3450 3450 7844 TURNER 30 1500 4950 4950 7499 ALLEN 30 1600 6550 6550 7698 BLAKE 30 2850 9400 9400 SQL>