¿Qué es un total acumulado de SQL y cómo se calcula?

El total acumulado de SQL es un patrón muy común, utilizado con frecuencia en finanzas y en el análisis de tendencias. En este artículo, aprenderás qué es un total acumulado y cómo escribir una consulta SQL para calcularlo.

La mejor manera de aprender SQL es a través de la práctica. LearnSQL.es ofrece más de 30 cursos interactivos de SQL con distintos niveles de dificultad. Cada curso es interactivo: hay un poco de lectura, seguida de un ejercicio para practicar lo que acabas de leer. Con cada ejercicio resuelto ganas confianza en tus conocimientos de SQL. ¡ Inscríbase ahora de forma gratuita!

¿Qué es un total de ejecución de SQL?

En SQL, un total acumulado es la suma de los números anteriores de una columna. Mira el ejemplo siguiente, que presenta el registro diario de usuarios de una tienda online:

La primera columna muestra la fecha. La segunda columna muestra el número de usuarios que se registraron en esa fecha. La tercera columna, total_usuarios, suma el número total de usuarios registrados en ese día.

Por ejemplo, el primer día (2020-03-05), se registraron 32 usuarios y el valor total de usuarios registrados fue de 32. Al día siguiente (2020-03-06) se registraron 15 usuarios; el valor de total_users pasó a ser 47 (32+15). El tercer día (2020-03-07), se registraron seis usuarios y el valor de total_users fue de 53. En otras palabras, total_users es un valor que cambia día a día. Es el número total de usuarios en cada día.

El siguiente ejemplo utiliza la columna total_running para tratar los ingresos de la empresa de forma similar. Observe la siguiente tabla:

Para cada día, la columna total_ingresos está calculando la cantidad de ingresos generados hasta el día en cuestión. En el día 2020-04-04, la empresa logró un ingreso total de 270.500 dólares, porque es la suma de todos los ingresos desde el 2020-04-02 hasta el 2020-04-04.

Las bases de datos relacionales (como SQL Server, Oracle, PostgreSQL y MySQL) e incluso los motores no relacionales, como Hive y Presto, ofrecen funciones de ventana que nos permiten calcular un total acumulado. Para aprender sobre las funciones de ventana, recomiendo el curso interactivo Funciones de ventana interactivo. Contiene más de 200 ejercicios para aprender las funciones ventana utilizándolas.

A continuación, hablaremos de la consulta SQL que construye dicha suma y aprenderemos más sobre las funciones de ventana.

Cómo calcular una suma acumulativa en SQL

Si desea calcular un total acumulativo en SQL, debe estar familiarizado con las funciones de ventana proporcionadas por su base de datos. Las funciones ventana operan sobre un conjunto de filas y devuelven un valor agregado para cada fila del conjunto de resultados.

La sintaxis de la función de ventana de SQL que calcula una suma acumulativa entre filas es:

window_function ( column ) 
OVER ( [ PARTITION BY partition_list ] [ ORDER BY order_list] )

Es obligatorio utilizar la cláusula OVER en una función ventana, pero los argumentos de esta cláusula son opcionales. Los discutiremos en los próximos párrafos de este artículo.

Ejemplo 1

En este ejemplo, calcularemos la suma total de los usuarios registrados cada día.

Esta consulta ...

SELECT registration_date,registred_users,
  SUM(registred_users) OVER (ORDER BY registration_date)
  AS total_users
FROM registration;

... selecciona la fecha de registro de todos los usuarios. También necesitamos la suma de todos los usuarios para cada día, empezando por el primer día dado (2020-03-05) hasta el día en esa fila.

Este es el conjunto de resultados:

Para calcular el total acumulado, utilizamos la función de agregación SUM() y ponemos como argumento la columna registered_users; queremos obtener la suma acumulada de usuarios de esta columna.

El siguiente paso es utilizar la cláusula OVER. En nuestro ejemplo, esta cláusula tiene un argumento: ORDER BY registration_date. Las filas del conjunto de resultados se ordenan según esta columna (registration_date). Para cada valor de la columna registration_date, se calcula la suma total de los valores de la columna anterior (es decir, la suma del número de usuarios anteriores a la fecha de la fila actual) y se le añade el valor actual (es decir, los usuarios registrados el día de la fila actual).

Observe que la suma total se muestra en la nueva columna, que hemos denominado total_users.

En el primer paso (la fecha de registro 2020-03-05), tenemos 57 usuarios registrados. La suma de usuarios registrados este día es la misma 57. En el siguiente paso, añadimos a este valor total (57). ¿Qué sumamos? El número de usuarios registrados en la fecha actual (2020-03-06), que es 27; lo que nos da un total acumulado de 84. En la última fila del conjunto de resultados (para la última fecha de registro, 2020-03-07) el total en curso es 100.

Gracias a las funciones de ventana de SQL, es fácil encontrar el número total acumulado de usuarios durante un periodo de tiempo determinado. Por ejemplo, durante 2020-03-05 - 2020-03-06, el número total de usuarios registrados fue de 84.

Ejemplo 2

En el segundo ejemplo, entraremos en más detalles sobre los usuarios. Mostraremos los usuarios con sus países. Observe la siguiente tabla:

Observe que para cada día tenemos el número de usuarios de cada país mostrado por separado. En este ejemplo, calcularemos una suma acumulada de usuarios registrados por separado para cada país.

Esta consulta ...

SELECT country, registration_date,registred_users,
  SUM(registred_users) 
  OVER (PARTITION BY country ORDER BY registration_date)
  AS total_users
FROM registration;

... calcula la suma de usuarios para cada día, primero para los usuarios de Inglaterra y luego para los de Polonia.

Este es el conjunto de resultados:

Para cada país, cada día de registro obtiene un total corrido. La cláusula PARTITION BY de la cláusula OVER tiene como argumento la columna country. Esto particiona las filas por país, lo que permite a SQL calcular un total acumulado sólo para ese país (en lugar de los dos países juntos). Así, en Inglaterra, desde 2020-03-05 hasta 2020-03-07, tenemos un total de 47 usuarios. Para el mismo periodo en Polonia, el total de usuarios registrados fue de 53.

Ejemplo 3

En el último ejemplo, analizaremos los datos de la tabla competition que almacena las columnas game_id, gamer_id, game_level, competition_date y score.

Necesitamos comprobar la puntuación total acumulada de cada jugador para cada día en dos juegos diferentes. Observa la siguiente consulta, que crea este total acumulado:

SELECT game_id,game_level,gamer_id,competition_date,score,
  SUM(score)
  OVER (PARTITION BY game_id, gamer_id 
        ORDER BY competition_date)
  AS total_score
FROM competition;

El resultado:

En esta tabla de resultados, podemos leer que el jugador con ID=4 empieza con una puntuación de 4 y termina con una puntuación total de 11. El mejor fue el jugador con ID=7, que terminó con una puntuación total de 12.

Una vez más, en la cláusula OVER utilizamos PARTITION BY. Esta vez, utilizamos una lista de columnas (game_id, gamer_id). Esto nos permite crear dos particiones: una para el juego 1 y otra para el juego 2.

A continuación, dividimos las filas por gamer_id para cada juego. En el juego 1, tenemos los jugadores 4 y 5; en el juego 2, tenemos los jugadores 6, 7 y 8. Dentro de cada grupo (un determinado jugador juega en una determinada partida), las filas se ordenan por fecha de competición y se suma la puntuación de cada día. En cada grupo, podemos observar la evolución de la puntuación de cada jugador en una partida determinada.

¿Cómo se utilizan los totales de ejecución de SQL?

El uso de un valor total en los informes SQL puede ser muy útil, especialmente para los especialistas en finanzas. Por lo tanto, merece la pena saber qué es una suma acumulada y cómo utilizar las funciones de ventana de SQL para crearla. Este artículo presenta algunos casos de uso seleccionados. Para obtener más información sobre las funciones de ventana, consulte nuestro artículo Ejemplo de función de ventana SQL con explicaciones o el curso LearnSQL Funciones de ventana.