Use SQL para calcular un Total en ejecución

El total en ejecución en SQL se puede calcular de varias maneras. Este artículo cubrirá dos métodos: las funciones Joins y Window.

Primero veremos cómo calcular el total continuo usando la UNIÓN INTERNA. Al hacerlo, no solo aprenderá más sobre las condiciones de unión, sino que también verá cómo tomar el resultado y resumirlo para obtener el total total.

Una vez que haya visto cómo hacerlo al estilo de la «vieja escuela», usaremos la cláusula OVER para calcular los totales en ejecución utilizando una función de ventana. Este método es más nuevo y más conciso de usar.

Todos los ejemplos de esta lección se basan en Microsoft SQL Server Management Studio y la base de datos WideWorldImporters. Puede comenzar a usar estas herramientas gratuitas utilizando mi guía Comenzar a usar SQL Server 2016.

¿Qué es un Total continuo?

Nuestro objetivo es calcular el total en ejecución que se restablece cada vez que cambia la fecha de transacción. Totalizaremos el importe de la transacción. Para cada factura posterior dentro de la fecha de transacción, el total en ejecución debe ser igual al total en ejecución del ID de factura anterior más el importe de transacción actual.

Verá esto en acción en el siguiente ejemplo. El total de ejecución para la Factura 3, es el total de ejecución anterior de 3110.75 más el Monto de Transacción de la Factura 3 de 103.50

Calcular un Total en ejecución en SQL usando una UNIÓN INTERNA

Primero calculamos el total en ejecución usando las UNIONES INTERNAS. Este método revela más de la mecánica de calcular un total en ejecución que el uso de la PARTICIÓN. Como tal, le da otra oportunidad de entender las UNIONES INTERNAS y aplicar esos conceptos a otro caso de uso.

Hay tres pasos para resolver este problema:

1. Obtener filas para el total en ejecución
2. Detalles de configuración para el total en ejecución usando uniones internas
3. Calcule el total en ejecución resumiendo los datos.

¡Comencemos!

Paso 1-Obtener filas para el Total en ejecución

Para calcular el total en ejecución, consultaremos la tabla CustomerTransactions. Incluiremos el InvoiceID, TransactionDate y TransactionAmount en nuestro resultado. Por supuesto, el total en ejecución se calcula a partir del importe de la transacción.

Aquí está la consulta para obtener los datos básicos.

SELECT InvoiceID ,TransactionDate ,TransactionAmountFROM Sales.CustomerTransactionsWHERE TransactionTypeID = 1ORDER BY TransactionDate

Aquí están los datos con los que trabajaremos.

En realidad, este paso está destinado a familiarizarte con la información básica. No hay necesidad de hacerlo. Sin embargo, a veces me gustaría recomendar la consulta básica solo para ver los datos. Además, también para asegurarme de que no haya anomalías o situaciones especiales que necesite acomodar.

Paso 2 – Detalles de configuración para el Total en ejecución usando Uniones internas

Para este paso, obtendremos los detalles configurados para que podamos calcular el total en ejecución. Para hacer esto, obtendremos cada InvoiceID el importe de la transacción y todos los importes de la transacción anteriores.

Para poder hacer esto, uniremos la tabla CustomerTransactions a sí misma.

Si hacemos esto sin condición de unión obtendríamos todas las combinaciones de transacciones, esto no es lo que queremos.

Para asegurarnos de obtener la combinación adecuada de filas de cada tabla, agregaremos dos condiciones de unión. Una para obtener cada factura y las anteriores (Verde).

El segundo garantiza que solo incluyamos facturas en la misma fecha de transacción (Rojo)

SELECT T1.InvoiceID ,T2.InvoiceID ,T1.TransactionDate ,T1.TransactionAmount ,T2.TransactionAmountFROM Sales.CustomerTransactions T1 INNER JOIN Sales.CustomerTransactions T2 ON T1.InvoiceID >= T2.InvoiceID AND T1.TransactionDate = T2.TransactionDateWHERE T1.TransactionTypeID = 1ORDER BY T1.InvoiceID, T1.TransactionAmount

Veamos cómo funciona esto.

La condición más fácil de entender es dónde coincidimos con la fecha de transacción. Esto garantiza que las facturas coincidentes tengan una fecha de transacción común. Si esta fuera la única unión que hiciéramos, estaríamos calculando un subtotal para todas las transacciones dentro de una fecha.

Dado que queremos calcular el total en curso, necesitamos de alguna manera obtener para cada InvoiceID el importe de transacción para la factura y todas las facturas anteriores. En otras palabras, devuelva todas las filas coincidentes donde la factura sea mayor o igual a las facturas correspondientes que estamos tratando de sumar.

Si observa el resultado anterior, verá que para cada factura enumerada en la primera columna (T1.InvoiceID), Es mayor o igual a los InvoiceID de la segunda columna (T2.InvoiceID).

Este es el resultado de la condición de unión T1.Id de factura > = T2.InvoiceID.

El resultado de esta unión y las condiciones de unión es que ahora tenemos las materias primas para calcular el total corriente.

Observe cómo se repiten las columnas primera, tercera y cuarta. Podemos usar esto a nuestro favor para resumir el resultado para llegar al total en curso.

Paso 3: Calcule el Total Corrido Resumiendo Filas

Con la información detallada a mano, el paso final es resumir las filas. Hacerlo nos permite calcular los totales acumulados.

Aquí está la consulta que utilizamos para realizar el resumen:

SELECT T1.InvoiceID ,T1.TransactionDate ,T1.TransactionAmount ,Sum(T2.TransactionAmount) RunningTotalFROM Sales.CustomerTransactions T1 INNER JOIN Sales.CustomerTransactions T2 ON T1.InvoiceID >= T2.InvoiceID AND T1.TransactionDate = T2.TransactionDateWHERE T1.TransactionTypeID = 1GROUP BY T1.InvoiceID ,T1.TransactionDate ,T1.TransactionAmountORDER BY T1.InvoiceID ,T1.TransactionAmount

Observe cómo agrupamos por T1.InvoiceID, T1.Fecha de transacción, y T1.Cantidad de transacción. Estos son los valores que se repitieron en nuestros datos detallados en el paso 2.

El Total corriente se deriva de T2.Cantidad de transacción. Recordar estos valores son el importe de transacción de todas las facturas anteriores a la factura mostrada. En otras palabras, la factura mostrada es mayor o igual que ellos.

Esto nos permite construir un total en ejecución.

Cada factura posterior en la lista, está calculando su valor total de ejecución sumando todo el importe de la transacción de su Factura y los anteriores.

Ahora que ha visto una forma tradicional de llegar al total en ejecución, y tal vez haya obtenido una mayor apreciación de cómo usar uniones y condiciones de unión para resolverlo, veamos una de las características más nuevas de SQL, particiones, y veamos cómo se pueden usar para lograr el mismo resultado.

Calcule un Total en ejecución en SQL usando una Cláusula OVER

La cláusula OVER si es una sentencia muy potente. Le permite definir un conjunto de filas, dentro de un conjunto de resultados al que afecta una operación.

Al igual que OFFSET y FETCH nos permiten recuperar un rango específico de filas de un conjunto de resultados, la cláusula OVER nos permite hacer una operación similar, relativa a la fila actual, para una columna específica.

Usando OVER, podemos definir una ventana sobre un conjunto especificado de filas, a las que podemos aplicar funciones, como sum.

Para que entiendas el concepto, lo dividiremos en dos pasos:

1. Particionar datos usando la cláusula OVER.
2. Ordene particiones con Orden.

Vamos a rodar.

Paso 1-Particionar datos usando la cláusula OVER

Cuando decimos que queremos crear un total de todas las facturas dentro de una Fecha de transacción, queremos particionar nuestros datos por Fecha de transacción. Para particionar los datos, podemos usar la cláusula over.

En la siguiente declaración notice sumamos el TransactionAmount y después de la SUMA hay una cláusula OVER.

También tenga en cuenta que no hay cláusula DE GRUPO POR. Esto es sorprendente, las funciones típicamente agregadas, como SUM, requieren una cláusula GROUP BY; ¿por qué es este el caso?

Dado que estamos utilizando la cláusula OVER, la SUMA se considera una función de ventana – opera sobre cualquier fila definida en la cláusula OVER.

Aquí está la función de ventana que usaremos:

SUM(TransactionAmount) OVER(PARTITION BY TransactionDate) runningTotal

Lo que hace de esta una función de Windows es la cláusula OVER. obtenga la PARTICIÓN DE la pieza MEDIANTE TransactionDate. Esto significa que la SUMA opera en todas las filas con la misma fecha de transacción. Esto define la ventana de filas a la que afecta la función SUM.

Aquí está la consulta hasta ahora.

SELECT InvoiceID ,TransactionDate ,TransactionAmount ,SUM(TransactionAmount) OVER(PARTITION BY TransactionDate) RunningTotalFROM Sales.CustomerTransactions T1WHERE TransactionTypeID = 1ORDER BY InvoiceID ,TransactionAmount

Paso 2 – Ordenar particiones con Orden POR

Hasta este punto, hemos particionado los datos y podemos calcular un subtotal para todos los valores de TransactionAmount dentro de una fecha de transacción. El siguiente paso es calcular ahora el subtotal.

Para hacer esto podemos usar la cláusula ORDER BY within the OVER para definir el «ámbito» de la función window. El ORDEN POR especificado el orden lógico que opera la función de ventana.

Aquí está la función de ventana que usaremos:

SUM(TransactionAmount) OVER(PARTITION BY TransactionDate ORDER BY InvoiceID) runningTotal

La diferencia entre esta función de ventana y la del primer paso, es ORDER BY InvoiceID. Esto especifica el orden lógico a procesar dentro de la partición.

Sin el ORDEN POR el orden lógico es esperar hasta que estemos al final de la ventana para calcular la suma. Con el ORDEN POR especificado, el orden lógico es calcular una suma para cada fila, incluidos los valores de transacción anteriores dentro de la ventana.

SELECT InvoiceID ,TransactionDate ,TransactionAmount ,SUM(TransactionAmount) OVER(PARTITION BY TransactionDate ORDER BY InvoiceID) RunningTotalFROM Sales.CustomerTransactions T1WHERE TransactionTypeID = 1ORDER BY InvoiceID ,TransactionAmount

Aquí está el resultado de ejecutar la consulta.

Cuando ejecutó esta consulta, ¿notó cuánto más rápido se ejecutó que la que usaba UNIONES INTERNAS? Me sorprendió. Sé que la operación de UNIÓN INTERNA consume muchos recursos a medida que las combinaciones o filas se vuelven grandes, pero habría pensado que tendría el mismo caso para la solución que usa OVER.

Le animo a que mire el plan de consulta de cada consulta. Empezarás a aprender un poco sobre SQL cuando empieces a hacer esto.