Función analítica SUM

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>

Enlace rápido

• SUM

Deja un comentario

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