Como Usar SUM() con OVER(PARTITION BY) en SQL

Descubra casos reales de uso de la función SUM() con la cláusula OVER(PARTITION BY). Aprenda la sintaxis y vea 5 ejemplos diferentes.

Utilizamos las funciones de ventana de SQL para realizar operaciones en grupos de datos. Estas operaciones incluyen las funciones matemáticas SUM(), COUNT(), AVG(), y más. En este artículo, explicaremos qué hace SUM() con OVER(PARTITION BY) en SQL. Le mostraremos los casos de uso más comunes en aplicaciones del mundo real para determinar la relación entre el valor de la fila individual y el valor total, calcular totales corridos y encontrar un total de pedido personalizado que incluya descuentos para ciertos productos.

Para obtener una guía detallada sobre el uso de SUM() con OVER() y otras funciones de ventana, siga nuestro curso interactivo Funciones de ventana interactivo. Explica en detalle todos los conceptos de las funciones de ventana de SQL y contiene más de 200 ejercicios.

Comencemos.

La Cláusula OVER() en SQL

En SQL, la cláusula OVER() se utiliza para introducir funciones ventana. La sintaxis general es

SELECT …
  <window function> OVER(...)
…

OVER() indica a la base de datos que queremos utilizar funciones ventana. La función ventana puede ser una función agregada, como SUM(), u otra función ventana.

Una función de ventana trabaja sobre un "marco de ventana", o un conjunto de filas relacionadas con la fila actual. OVER() define el marco de ventana para cada fila. Una cláusula OVER() vacía indica a la base de datos que todo el conjunto de resultados es el marco de ventana.

Veamos un ejemplo de la sintaxis de SUM() OVER(). Utilizaremos la tabla employees de los datos de ejemplo proporcionados por Oracle Live SQL. Esta tabla consta de las siguientes columnas

• emp_id es el identificador del empleado.
• name es el nombre del empleado.
• job es el título del puesto.
• dept_id es el identificador del departamento.
• salary es el salario del empleado.

Podemos utilizar la función SUM() con la cláusula OVER() para obtener el salario total de todos los empleados y mostrar el salario total junto al salario de cada empleado.

SELECT 
  emp_id, 
  name, 
  job, 
  dept_id, 
  salary, 
  SUM(salary) OVER() AS total_salary
FROM employees;

La función SUM() se ejecuta para cada fila de la tabla. Cada vez que se llama, obtiene la información salarial de todas las filas de la tabla para calcular el importe total del salario. El salario total se muestra junto a los detalles de cada fila individual. Los detalles de las filas individuales se conservan y se muestran junto al salario total.

En este ejemplo, el marco de la ventana (el conjunto de filas sobre el que opera SUM() ) es todo el conjunto de datos. Puede añadir cláusulas adicionales en OVER() para cambiar el marco de la ventana.

Ejemplo 1A: Cálculo de la suma de cada grupo - SUM() con OVER(PARTITION BY ...)

La cláusula OVER() puede contener detalles sobre la forma en que queremos particionar los datos. Utilizamos la cláusula PARTITION BY en OVER() para dividir los datos en particiones, o grupos. El uso de PARTITION BY es similar al uso de GROUP BY en que las filas se dividen en grupos basados en el valor de algunas columnas. Cuando usamos SUM() OVER(PARTITION BY …), podemos calcular la suma de valores para cada grupo, o partición, de datos. Por ejemplo, podemos calcular el salario total de cada departamento:

SELECT 
  emp_id, 
  name, 
  job, 
  dept_id, 
  salary,
  SUM(salary) OVER(PARTITION BY dept_id) AS dept_total_salary
FROM employees;

Este es el resultado:

Utilizamos SUM(salary) OVER(PARTITION BY dept_id) para obtener el salario total por departamento. La cláusula PARTITION BY divide las filas en grupos en función de la columna dept_id. Las filas con dept_id igual a 10 se colocan en un grupo (marcado en amarillo en la tabla anterior), las filas con dept_id igual a 20 se colocan en otro grupo (marcado en verde) y, por último, las filas con dept_id igual a 30 se colocan en otro grupo (marcado en rojo). La función SUM() calcula la suma de las filas de cada grupo.

Tenga en cuenta que cuando se utiliza SUM() OVER(PARTITION BY), se conservan los detalles de las filas individuales. Puede, por ejemplo, ver los detalles del empleado llamado Ford: su puesto, su salario y cómo se compara con el total de salarios de su departamento.

Este es el uso más típico de SUM() OVER(PARTITION BY): se calcula el valor de la suma para cada grupo de datos y se mantienen los detalles de las filas individuales. Veamos un ejemplo similar.

Ejemplo 1B: Calcular el salario de cada puesto de trabajo

Podemos utilizar SUM(salary) OVER(PARTITION BY job) para obtener el salario total por puesto de trabajo. Veámoslo:

SELECT 
  emp_id, 
  name, 
  job, 
  dept_id, 
  salary,
  SUM(salary) OVER(PARTITION BY job) AS job_total_salary
FROM employees;

Este es el resultado:

Esta vez, las filas se agrupan en función del valor del puesto en lugar del ID del departamento. Los empleados con el mismo puesto de trabajo se ponen en un grupo y calculamos el salario total de las personas en este puesto. La función SUM() se aplica a todos los salarios de cada grupo: el salario total del grupo "Manager" es la suma de 2450, 2850 y 2975, que son los salarios de los tres managers que hay en nuestra tabla.

Ejemplo 2: Calcular la Relación entre el Valor de una Fila y el Total

Típicamente, queremos ver la comparación entre cada fila individual y la suma total. Calculemos el porcentaje de cada salario individual del total de salarios de su departamento.

SELECT 
  emp_id, 
  name, 
  job, 
  dept_id, 
  salary,
  ROUND(100.0 * salary / SUM(salary) OVER(PARTITION BY dept_id), 2) 
           AS percentage_of_dept_total_salary
FROM employees;

Esta vez, las filas se agrupan en función del valor job en lugar del ID del departamento. Los empleados con el mismo puesto de trabajo se colocan en un grupo y calculamos el salario total de las personas en este puesto. La función SUM() se aplica a todos los salarios de cada grupo: el salario total del grupo "Manager" es la suma de 2450, 2850 y 2975, que son los salarios de los tres managers que hay en nuestra tabla.

Ejemplo 2: Calcular la Relación entre el Valor de una Fila y el Total

Típicamente, queremos ver la comparación entre cada fila individual y la suma total. Calculemos el porcentaje de cada salario individual con respecto a los salarios totales de su departamento.

SELECT 
  emp_id, 
  name, 
  job, 
  dept_id, 
  salary,
  ROUND(100.0 * salary / SUM(salary) OVER(PARTITION BY dept_id), 2) 
           AS percentage_of_dept_total_salary
FROM employees;

Podemos ver, por ejemplo, que el analista llamado Scott tiene un salario individual de 3000; esto supone el 27,59% del total de salarios de su departamento.

A continuación se desglosan las funciones utilizadas para realizar esta tarea:

• Tomamos cada salario individual y lo dividimos por el salario total del departamento:

salary / SUM(salary) OVER(PARTITION BY dept_id)

• Para obtener un porcentaje, lo multiplicamos por 100%:
  
  • 0 * salary / SUM(salary) OVER(PARTITION BY dept_id)
• A continuación, utilizamos la función ROUND() para obtener dos dígitos decimales:

ROUND(100.0 * salary / SUM(salary) OVER(PARTITION BY dept_id), 2)

Calcular la relación entre la fila individual y la suma de un grupo es otro caso de uso muy común de SUM() con OVER(PARTITION BY). De forma similar, puedes calcular la diferencia entre el total del grupo y la fila individual.

¿Cuál es la diferencia entre SUM() con OVER(PARTITION BY …) y utilizar SUM() con GROUP BY?

La cláusula PARTITION BY realiza una tarea similar a la cláusula GROUP BY. Tanto OVER(PARTITION BY) como GROUP BY dividen el conjunto de datos en particiones o grupos. Al utilizar la función SUM(), ambas expresiones calculan la suma de cada grupo. Entonces, ¿cuál debe elegir?

Cuando se utiliza GROUP BY, los detalles de las filas individuales se contraen. Eche un vistazo:

SELECT 
  job, 
  SUM(salary) AS total_salary
FROM employees
GROUP BY job;

En este caso, obtenemos el valor salarial total de cada puesto sin tener en cuenta los salarios individuales de los empleados.

Sin embargo, si desea comparar el valor de la suma total con los valores de las filas individuales (por ejemplo, para calcular la relación entre el valor individual y el total), la forma de hacerlo es OVER(PARTITION BY …). Devuelve todas las filas individuales junto con el valor de la suma total de cada fila. Este valor de la suma total puede variar en función de la partición a la que pertenezca la fila.

SELECT 
  name, 
  job, 
  salary, 
  SUM(salary) OVER(PARTITION BY job) AS total_salary
FROM employees;

El valor total_salary coincide con los resultados de la consulta anterior con GROUP BY. Pero aquí también se pueden ver los salarios individuales. Por ejemplo, hay dos analistas que ganan 6000 en total; cada uno de ellos gana 3000.

La regla general es: Si sólo desea conocer el valor de la suma de cada grupo y no le interesan los detalles de cada fila individual, debe utilizar la cláusula GROUP BY. Si está interesado tanto en la suma de cada grupo como en los detalles de las filas individuales, debe utilizar SUM() OVER(PARTITION BY).

Ejemplo 3: Cálculo de un total acumulado mediante SUM() con OVER(PARTITION BY)

Otro uso común de la sintaxis SUM() OVER(...) es calcular el total acumulado.

Un total acumulado es la suma acumulada de los números anteriores de una columna. Los totales móviles se utilizan para calcular valores que se acumulan a lo largo del tiempo. Por ejemplo, con un total acumulado puedes calcular el uso mensual de datos móviles sumando el valor de cada día posterior a la suma de los valores de los días anteriores. Del mismo modo, puedes calcular cómo aumenta cada día el número de usuarios registrados o cómo aumentan los ingresos totales con cada transacción.

Utilizamos la sintaxis SUM() con OVER(PARTITION BY … ORDER BY …) para calcular el total actual.

Veamos un ejemplo. Utilizaremos la orders tabla de datos de ejemplo proporcionada por Oracle Live SQL. Esta tabla almacena datos históricos de pedidos. Cada pedido tiene su fecha (order_date), representante de ventas (sales_rep_id), y valor total (order_total).

Aquí calculamos el valor total actual para cada representante de ventas:

SELECT 
  order_date, 
  sales_rep_id, 
  order_total,
  SUM(order_total) OVER(PARTITION BY sales_rep_id ORDER BY order_date) 
          AS running_total
FROM orders;

Para ello, dividimos nuestra tabla por representante de ventas y, a continuación, ordenamos cada partición por fecha:

	SUM(order_total) OVER(PARTITION BY sales_rep_id ORDER BY order_date)

El nuevo elemento sintáctico es ORDER BY. Define cómo se ordenan las filas en cada marco de ventana. La función SUM() se aplica a cada fila; suma el valor actual de order_total con el valor anterior de running_total; los totales se calculan por separado para cada partición (aquí, para cada ID de representante de ventas).

Echemos un vistazo más de cerca al representante de ventas con un ID de 153. Su primer pedido fue el^{29 de} marzo y tuvo un valor total de 10794,6. En este momento, el total acumulado (la suma) es igual al valor del pedido. Su segundo pedido fue el 16^de agosto por 78279,6; ahora el total acumulado es igual a la suma de los valores de su primer y segundo pedido (10794,6 + 78279,6 = 89074,2). Después del tercer pedido, el total acumulado es igual al total acumulado anterior más el valor del tercer pedido (89074,2 + 129 = 89203,2). Este proceso es similar para el representante de ventas con el ID 154.

El cálculo de un total acumulado es un patrón común cuando se utiliza SQL para el análisis de datos. Puede leer cómo calcular un total en SQL en otra sección de nuestro blog.

Ejemplo 4: Recuento de objetos en categorías personalizadas

La función SUM() se utiliza a menudo con la sentencia CASE WHEN para contar objetos en categorías personalizadas. Por ejemplo, es posible que desee calcular el salario total de los empleados en puestos directivos de un departamento y mostrarlo junto a los detalles de cada empleado. Puede lograrlo utilizando SUM() OVER(PARTITION BY) en combinación con CASE WHEN.

Revisemos primero la sentencia CASE WHEN. Es similar a la sentencia if común a muchos lenguajes de programación. Se utiliza para definir el valor de una expresión en diferentes situaciones o casos.

En el siguiente ejemplo, utilizamos la sentencia CASE WHEN para identificar a cada empleado como directivo (gerentes y presidentes) o como empleado normal (todos los demás puestos de trabajo). Eche un vistazo:

SELECT 
  emp_id, 
  name, 
  job, 
  dept_id, 
  CASE WHEN job IN (‘PRESIDENT’, ‘MANAGER’) 
    THEN ‘MANAGEMENT’ 
    ELSE ‘REGULAR’ END AS emp_type
  salary
FROM employees;

La sentencia CASE evalúa todas las condiciones de WHEN. Si encuentra la condición coincidente, devuelve el valor en la rama THEN. Si no encuentra la condición coincidente, devuelve el valor dado después de ELSE. En nuestro ejemplo, los empleados en el puesto de presidente o gerente se etiquetan como 'Management'. A todos los demás puestos de trabajo se les asigna la etiqueta 'Regular'. Este es el resultado de la consulta:

Puede obtener más información sobre CASE en nuestro artículo Cómo utilizar CASE en SQL.

También puede utilizar la sentencia CASE con SUM() para sumar valores en categorías personalizadas. Esta es la consulta:

SELECT
  dept_id,
  SUM(
    CASE WHEN job IN (‘PRESIDENT’, ‘MANAGER’) 
    THEN salary
    ELSE 0 END) 
    AS dept_management_salary,
  SUM(
    CASE WHEN job IN (‘PRESIDENT’, ‘MANAGER’) 
    THEN 0
    ELSE salary END) 
    AS dept_regular_salary
FROM employees
GROUP BY dept_id;

Para los empleados en puestos directivos, la primera sentencia CASE devuelve el valor de la columna salary. A continuación, la función SUM() en combinación con GROUP BY suma todos los salarios de los empleados en puestos directivos con la misma dept_id. De este modo, se calcula el salario total de los directivos por departamento. La segunda sentencia CASE calcula el salario total por departamento para todos los empleados fijos.

Puede calcular el salario total de los empleados en posición directiva de un departamento y mostrarlo junto a los detalles del empleado utilizando SUM() con PARTITION BY:

SELECT 
  emp_id, 
  name, 
  job, 
  dept_id, 
  CASE WHEN job IN (‘PRESIDENT’, ‘MANAGER’) 
    THEN ‘MANAGEMENT’ 
    ELSE ‘REGULAR’ END AS emp_type
  salary,
  SUM(
    CASE WHEN job IN (‘PRESIDENT’, ‘MANAGER’) 
    THEN salary 
    ELSE 0 END) OVER(PARTITION BY dept_id) AS dept_management_salary
FROM employees;

Las filas se dividen en grupos en función de la columna dept_id. Hay tres grupos, uno por cada departamento. La función SUM() se aplica a la expresión CASE WHEN. En lugar de calcular el salario total de cada departamento, calculamos el salario total de los empleados que ocupan puestos directivos en el departamento. CASE devuelve 0 para los empleados normales (la suma no se incrementa) y el valor del salario para los empleados de dirección. Puede utilizar este patrón en muchas situaciones diferentes. De hecho, en la siguiente sección veremos otro ejemplo de combinación de CASE WHEN con SUM() y OVER(PARTITION BY).

Ejemplo 5: Cálculo del total de un pedido con descuentos utilizando SUM() con CASE WHEN

En este ejemplo, queremos calcular el valor total de cada pedido, incluyendo los descuentos de algunos artículos del pedido. Utilizaremos la sintaxis SUM() OVER(PARTITION BY …) para sumar todos los valores de los artículos por pedido. Y para incorporar los descuentos de los productos, utilizaremos la sentencia CASE WHEN.

Utilizamos la tabla order_items de los datos de ejemplo proporcionados por Oracle Live SQL. Esta tabla almacena todos los artículos del pedido (product_id) pertenecientes a cada pedido (order_id). Contiene información sobre el precio unitario del producto (unit_price) y la cantidad del pedido (quantity).

Ahora queremos calcular los valores del pedido: uno por cada producto del pedido y un total para el pedido; el total del pedido debe incluir cualquier descuento aplicado a los productos.

El total del pedido es igual al precio unitario multiplicado por la cantidad del pedido. Sin embargo, cuando se aplica un descuento a un producto, utilizaremos un extracto de CASE WHEN para contabilizarlo.

SELECT 
  order_id, 
  product_id, 
  unit_price, 
  quantity,   

  SUM(CASE 
     -- 20% discount for this product
     WHEN product_id = 3143 THEN unit_price*quantity*0.8
     -- 40% discount for this product
     WHEN product_id = 3106 THEN unit_price*quantity*0.6
     ELSE unit_price*quantity END)
  OVER(PARTITION BY order_id, product_id) 
        AS order_product_total_with_discount,

  SUM(CASE 
     -- 20% discount for this product
     WHEN product_id = 3143 THEN unit_price*quantity*0.8
     -- 40% discount for this product
     WHEN product_id = 3106 THEN unit_price*quantity*0.6
     ELSE unit_price*quantity END)
  OVER(PARTITION BY order_id) 
        AS order_total_with_discount    

FROM order_items;

Para calcular el total por producto en un pedido, utilizamos la siguiente sintaxis:

SUM(CASE 
       WHEN product_id=3143 THEN unit_price*quantity*0.8 -- 20% discount 
       WHEN product_id=3106 THEN unit_price*quantity*0.6 -- 40% discount 
       ELSE unit_price*quantity END)
OVER(PARTITION BY order_id, product_id) AS order_product_total_with_discount

Pasamos la sentencia CASE WHEN como argumento a la función SUM(); si el ID de producto de la fila actual es 3143, aplicamos un descuento del 20%; para el ID de producto 3106, el descuento es del 40%. A continuación, dividimos el conjunto de datos por ID de pedido e ID de producto para obtener los valores totales de cada producto de un pedido.

Observe que utilizamos dos cláusulas PARTITION BY diferentes en la consulta. Para calcular el valor total del producto, dividimos el conjunto de datos por ID de pedido e ID de producto. Para calcular el total por pedido, dividimos el conjunto de datos sólo por ID de pedido. El valor total por pedido es igual para todas las filas con el mismo ID de pedido. Es decir, si se suman todos los valores totales de los productos de un determinado pedido, se obtiene el valor total del pedido. Por ejemplo, para el ID de pedido 2355, obtenemos lo siguiente 9200 + 10545 + 16337.2 + 3572 + 3230 + 211.2 + 216.7 + 4975 + 46226.4 = 94513.5.

Aquí tienes algunos ejemplos de la función SUM() con CASE WHEN.

Siga adelante y SUM() con OVER() y PARTITION BY

Ahora que sabe cómo utilizar la función de ventana SUM() y sus diversas opciones de sintaxis, puede probarla usted mismo.

Presentamos ejemplos sobre cómo calcular un porcentaje de un valor de una fila al total de la partición y mostramos cómo calcular el total corrido y el total de orden personalizado. Le animamos a que practique con sus propios datos.

Para aprender más, pruebe nuestro curso interactivo Funciones de ventana que explica en detalle todos los conceptos de las funciones de ventana. O consulte nuestra Funciones de ventana Cheat Sheet si desea una referencia rápida y sencilla de las funciones de ventana de SQL.

Mucha suerte.