Recorriendo una tabla actualizando un acumulador

Este es un problema bastante común; sobre todo si le toca a uno manipular tablas de contabilidad. Vamos al caso.

El problema

Tenemos una tabla con un conjunto de campos, donde nos interesa:

saldo_anterior, ingreso, egreso y saldo_actual

además una llave única que es idcta + fecha.

Esta sería la definición bajo SQL.


CREATE TABLE IF NOT EXISTS `cta_saldo` (

`idcta` int(11) NOT NULL,
`fecha` date NOT NULL,
`saldo_anterior` float NOT NULL,
`ingreso` float NOT NULL,
`egreso` float NOT NULL,
`saldo_actual` float NOT NULL,
UNIQUE KEY `cta_fec` (`idcta`,`fecha`)
) ENGINE=MyISAM

Nótese que queremos, sumar saldo_anterior + ingreso - egreso e introducirlo en saldo_actual.

Es un hecho que saldo_actual funciona como un campo calculado por lo que no lo deberíamos incluir, pero es ilustrativo de este ejemplo así que lo usaremos aunque sea redundante.

De inicio tenemos un tabla más o menos así (mostrando una parte intermedia).

fecha	idcta	saldo_anterior	ingreso	egreso	Saldo actual
'2010-06-10'	2	0	10	0	0
'2010-06-19'	2	0	10	0	0
'2010-07-05'	3	0	22	0	0
'2010-06-01'	4	0	938	0	0
'2010-06-02',	4	0	0	360	0

finalizado el proceso sería así

fecha	idcta	saldo_anterior	ingreso	egreso	Saldo actual
'2010-06-10'	2	50	10	0	60
'2010-06-19'	2	60	10	0	70
'2010-07-05'	3	0	22	0	22
'2010-06-01'	4	0	938	0	938
'2010-06-02',	4	938	0	360	578

Es de tomar en cuenta que el archivo está ordenado por idcta y fecha, así al cambiar cada idcta, nuestro acumulador (saldo_anterior) se va a cero. Y bueno el saldo anterior es precisamente el saldo_actual de la fecha inmediata anterior.


Resumiendo queremos copiar el valor de una fila a la fila siguiente en otro campo

Bueno al momento no hemos hecho sino explicar parte del problema, ahora toca analizarlo.

Forma de resolverlo

Bueno lo mas simple es recorrer la tabla, y según se avanza comparamos idcta anterior con idcta siguiente, si son iguales tomamos el saldo de la primera fila y lo llevamos a la segunda.

UPDATE cta_saldo SET saldo_anterior = fila_anterior.saldo_actual

El problema es que "fila anterior" no existe.

Pudiésemos crear una función o algo que nos de el saldo de la fila anterior.

UPDATE cta_saldo SET saldo_anterior = Saldo_fila_anterior(idtaq, fecha)

Lo anterior significa, posiblemente, que por cada registro que actualicemos hay que hacer una búsqueda por el indice de la tabla, ir a la fila y levantar el dato. ¡De una fila que acabamos de escribir en un ciclo anterior!.

Solución de un solo recorrido.

Otra alternativa es usar alguna variable de acumulador.

SET @saldo=0;
UPDATE cta_saldo SET saldo_anterior = @saldo, saldo_actual = @saldo:= @saldo + ingreso-egreso
ORDER BY idcta, fecha;

Se ve un poco tenebroso lo anterior, sobre todo porque asignamos una variable y un campo en la misma expresión. Pero vale.

Algo adicionalmente no muy comun es usar la clausula "ORDER BY" en un UPDATE, pero alguien lo pensó y bien, sino esto no funcionaría.

Tenemos que tomar en cuenta el cambio de idcta que involucra saldo a cero.

Debemos entonces usar algo como la función "if".

if(condicion, valor-1, valor-2)

Que se entiende como si(if) la "condicion" se da el resultado es "valor-1", si no es "valor-2"

Bien, aplicándolo sería algo como esto:

if (idcambió, 0, @saldo)

El cambio de id debería ser expresado con algo como, @id<>idcta


SET @saldo=0;

SET @id=0;

UPDATE cta_saldo
SET
saldo_anterior = if(@id=idcta,@saldo,0),
saldo_actual = @saldo:= if(@id=idcta,@saldo,0) + ingreso - egreso,
idcta = @id := idcta
ORDER BY idcta, fecha;

El problema con la anterior es como fijar el valor de @id a medida que se recorre la tabla. La solución fue asignar a idcta el valor de id que es idcta. Esto como mínimo es un "asco". Funciona pero no me gusta.

Otra alternativa es usar una tabla temporal, aunque serían dos recorridos. Mi solución al final fue crear un "Stored Procedure". Continuará...