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>
