una - ¿Cómo calculo un total acumulado en SQL sin usar un cursor?

tipos de cursores sql

Question

Dejo fuera toda la configuración del cursor y el SELECCIONAR de la tabla temporal para abreviar. Básicamente, este código calcula un saldo corriente para todas las transacciones por transacción.

WHILE @@fetch_status = 0
BEGIN

    set @balance = @balance+@amount

    insert into @tblArTran values ( --from artran table
                @artranid, @trandate, @type, 
                @checkNumber, @refNumber,@custid,
                @amount, @taxAmount, @balance, @postedflag, @modifieddate )


    FETCH NEXT FROM artranCursor into 
            @artranid, @trandate, @type, @checkNumber, @refNumber,
            @amount, @taxAmount,@postedFlag,@custid, @modifieddate

END

Inspirado por este código de una respuesta a otra pregunta,

SELECT @nvcConcatenated = @nvcConcatenated + C.CompanyName + '', ''
FROM tblCompany C
WHERE C.CompanyID IN (1,2,3)

Me preguntaba si SQL tenía la capacidad de sumar números de la misma manera que es cadenas concatonantes, si entiendes lo que quiero decir. Es decir, para crear un "saldo en ejecución" por fila, sin usar un cursor.

¿Es posible?

Answer 1

En Oracle y PostgreSQL 8.4 puede usar funciones de ventana:

SELECT  SUM(value) OVER (ORDER BY id)
FROM    mytable

En MySQL , puede usar una variable de sesión para el mismo propósito:

SELECT  @sum := @sum + value
FROM    (
        SELECT  @sum := 0
        ) vars, mytable
ORDER BY
        id

En SQL Server , es un raro ejemplo de una tarea para la cual un cursor es una solución preferida.

Answer 2

En SQL Server 2012 en adelante, puede usar la función de ventana Sum directamente en la tabla original:

SELECT
   artranid,
   trandate,
   type,
   checkNumber,
   refNumber,
   custid,
   amount,
   taxAmount,
   Balance = Sum(amount) OVER (ORDER BY trandate ROWS UNBOUNDED PRECEDING),
   postedflag,
   modifieddate
FROM
   dbo.Sales
;

Esto funcionará muy bien en comparación con todas las soluciones y no tendrá el potencial de errores que se encuentran en la "actualización peculiar".

Tenga en cuenta que debe usar la versión ROWS cuando sea posible; la versión de RANGE puede funcionar peor.

Answer 3

En SQLTeam también hay un article sobre el cálculo de totales acumulados. Hay una comparación de 3 formas de hacerlo, junto con algunas medidas de rendimiento:

• usando cursores
• usando una subselección (según la publicación de SQLMenace)
• usando una unión CROSS

Los cursores superan por lejos a las otras soluciones, pero si no debes usar cursores, existe al menos una alternativa.

Answer 4

Es posible que desee consultar la actualización de la solución de variables locales aquí: http://geekswithblogs.net/Rhames/archive/2008/10/28/calculating-running-totals-in-sql-server-2005---the-optimal.aspx

DECLARE @SalesTbl TABLE (DayCount smallint, Sales money, RunningTotal money)

DECLARE @RunningTotal money

SET @RunningTotal = 0

INSERT INTO @SalesTbl 
SELECT DayCount, Sales, null
FROM Sales
ORDER BY DayCount

UPDATE @SalesTbl
SET @RunningTotal = RunningTotal = @RunningTotal + Sales
FROM @SalesTbl

SELECT * FROM @SalesTbl

Supera a todos los demás métodos, pero tiene algunas dudas sobre el orden de fila garantizado. Parece que funciona bien cuando la tabla temporal está indexada.

• Sub-consulta anidada 9300 ms
• Autocombinación 6100 ms
• Cursor 400 ms
• Actualización a la variable local 140 ms

Answer 5

Puede hacer un recuento de ejecución , aquí hay un ejemplo, tenga en cuenta que esto no es tan rápido ya que tiene que escanear la tabla para cada fila, si su tabla es grande, esto puede ser bastante lento y costoso.

create table #Test  (id int, Value decimal(16,4))
insert #Test values(1,100)
insert #Test values(2,100)
insert #Test values(3,100)
insert #Test values(4,200)
insert #Test values(5,200)
insert #Test values(6,200)
insert #Test values(7,200)

select *,(select sum(Value) from  #Test t2 where t2.id <=t1.id) as SumValues
 from #test t1

id  Value       SumValues
1   100.0000    100.0000
2   100.0000    200.0000
3   100.0000    300.0000
4   200.0000    500.0000
5   200.0000    700.0000
6   200.0000    900.0000
7   200.0000    1100.0000

Answer 6

Puede simplemente incluir una subconsulta correlacionada en la cláusula de selección. (Esto funcionará mal para conjuntos de resultados muy grandes) pero

   Select <other stuff>,
       (Select Sum(ColumnVal) From Table
        Where OrderColumn <= T.OrderColumn) As RunningTotal
   From Table T
   Order By OrderColumn

Answer 7

Que ese bit SELECT @nvcConcatonated solo devuelve un solo valor concatenado. (Aunque está calculando los valores intermedios por fila, solo puede recuperar el valor final).

Entonces, creo que la respuesta es no. Si querías un único valor de suma final, por supuesto solo utilizarías SUM .

No digo que no puedas hacerlo, solo digo que no puedes hacerlo usando este ''truco''.

Answer 8

SQL puede crear totales acumulados sin utilizar cursores, pero es uno de los pocos casos en que un cursor es en realidad más eficaz que una solución basada en conjuntos (dados los operadores actualmente disponibles en SQL Server). Alternativamente, una función CLR a veces puede brillar bien. Itzik Ben-Gan hizo una excelente serie en SQL Server Magazine sobre la ejecución de agregados. La serie concluyó el mes pasado, pero puede obtener acceso a todos los artículos si tiene una suscripción en línea.

Editar: aquí está su último artículo de la serie (SQL CLR). Dado que puede acceder a toda la serie comprando un pase mensual en línea por un mes, menos de 6 dólares, vale la pena si está interesado en analizar el problema desde todos los ángulos. Itzik es un MVP de Microsoft y un codificador TSQL muy brillante.

Answer 9

Tenga en cuenta que el uso de una variable para lograr esto, como el siguiente, puede fallar en un sistema multiprocesador porque las filas separadas podrían calcularse en diferentes procesadores y pueden terminar utilizando el mismo valor inicial. Según tengo entendido, se podría utilizar una sugerencia de consulta para forzarla a utilizar un único hilo, pero no tengo esa información a mano.

ACTUALIZACIÓN @SalesTbl SET @RunningTotal = RunningTotal = @RunningTotal + Sales FROM @SalesTbl

Usar una de las otras opciones (un cursor, una función de ventana o consultas anidadas) suele ser la apuesta más segura para obtener resultados fiables.

Answer 10

Un ejemplo de cálculo de un total acumulado para cada registro, pero solo si el día del pedido de los registros está en la misma fecha. Una vez que OrderDate sea para un día diferente, se iniciará y acumulará un nuevo total acumulado para el nuevo día: (suponga la estructura de la tabla y los datos)

select O.OrderId,
convert(char(10),O.OrderDate,101) as ''Order Date'',
O.OrderAmt, 
(select sum(OrderAmt) from Orders 
                      where OrderID <= O.OrderID and 
                           convert(char(10),OrderDate,101)
                         = convert(char(10),O.OrderDate,101))
                               ''Running Total'' 
from Orders O
order by OrderID

Aquí están los resultados devueltos de la consulta usando la Tabla de órdenes de muestra:

OrderId     Order Date OrderAmt   Running Total                            
----------- ---------- ---------- ---------------
1           10/11/2003 10.50      10.50
2           10/11/2003 11.50      22.00
3           10/11/2003 1.25       23.25
4           10/12/2003 100.57     100.57
5           10/12/2003 19.99      120.56
6           10/13/2003 47.14      47.14
7           10/13/2003 10.08      57.22
8           10/13/2003 7.50       64.72
9           10/13/2003 9.50       74.22

Tenga en cuenta que el "Total acumulado" comienza con un valor de 10.50, y luego se convierte en 22.00, y finalmente se convierte en 23.25 para OrderID 3, ya que todos estos registros tienen el mismo OrderDate (10/11/2003). Pero cuando se muestra OrderID 4, el total acumulado se restablece y el total acumulado comienza de nuevo. Esto es porque OrderID 4 tiene una fecha diferente para su OrderDate, luego OrderID 1, 2 y 3. El cálculo de este total acumulado para cada fecha única se logra una vez más utilizando una sub consulta correlativa, aunque se requiere una condición WHERE adicional, que identificó que las fechas del pedido en diferentes registros deben ser el mismo día. Esta condición WHERE se logra utilizando la función CONVERT para truncar el OrderDate en un formato MM / DD / YYYY.

Answer 11

seleccione Fecha de transacción, cantidad, cantidad + (suma x.cantidad de transacciones x donde x.TransactionDate <Transacciones) Total de ejecución de transacciones

donde x.TransactionDate <Transacciones podría ser cualquier condición que representará todos los registros anteriores, aparte de la actual