Fran Otero

Fila a fila sin cursores II: SELECT simple

Para los que les haya gustado mi anterior artículo en el cual substituiamos el uso de cursores por un bucle While, aquí está la continuación DEFINITIVA. Supongo que muchos pensareis que no ganamos mucho substituyendo una iteración por la otra, y en parte tenéis razón. No obstante la no utilización de cursores tiene beneficios intrínsecos que van más allá del propio rendimiento de la consulta, como podría ser la seguridad ante fallos de que no tendremos objetos instanciados en memoria.

De todas formas viendo el código anterior uno se queda con la sensación de que es un poco rudimentario. Hay otras alternativasparecidas, como puede ser incluir en la tabla temporal una columna IDENTITY que nos permita iterar la tabla en el While a través de una variable puntero que iremos incrementando en el propio While: más de lo mismo. Ahora bien, que os parecería si os digo que se puede substituir todo el bucle, el recorrido de filas, la comparación e incluso la tabla temporal por una única instrucción SELECT "corriente". Lo de corriente tiene un poco de trampa, pero nada de complicación: si utilizamos una instrucción select de asignación, la asignación se repetirá para cada fila de la consulta. Si además introducimos una expresión de tipo "case ... then .... else ..... end" podemos en cada fila comparar el valor máximo con el que llevamos agregado y asignar al máximo el mayor de ellos.

-- Creamos la tabla del ejemplo. Para simplificar introducimos solo los

--datos que utilizaremos, pero esto no afecta al ejemplo

declare @movimientos as table (Producto nvarchar(10), Cantidad int, Fecha date)

insert into @movimientos values ('tornillos',500,'20090101')

insert into @movimientos values ('tornillos',-200,'20090104')

insert into @movimientos values ('tornillos',-50,'20090102')

insert into @movimientos values ('tornillos',-220,'20090110')

insert into @movimientos values ('tornillos',500,'20090111')

insert into @movimientos values ('tornillos',-100,'20090120')

--Variables auxiliares que utilizaremos

declare @Acc int,@Max

int set @Acc=0 --Valor acumulado hasta la fila actual

set @Max=0 --Valor máximo hasta la fila actual

-- Consulta "importante"

select

@Acc=@Acc+Cantidad, --Valor acumulado hasta la fila actual

@Max=case sign(@Acc-@Max) when 1 then @Acc else @Max end --Asigna el nuevo máximo o se queda con el que había

From @movimientos

Where Producto= 'tornillos'

orderby fecha asc

print 'Máximo alcanzado:'

print @Max

Fila a fila sin cursores: columna agregado

Tenemos una tabla en nuestra base de datos llamada “Movimientos”, que registra entradas y salidas de material de nuestro almacén. La siguiente consulta es el resultado de consultar los movimientos de “tornillos” en el mes de enero. Cómo se puede ver la tabla carece de un autonumérico, los registros no tienen porqué estar en orden cronológico y hay más productos mezclados. 

Si en este escenario nos pidiesen obtener la cantidad máxima de tornillos que llegó a haber en el almacén podrían ponernos en un pequeño apuro. La idea sería conseguir una tercera columna con la cantidad en stock y poder seleccionar el máximo. El problema de eso es que necesitamos de alguna manera recorrer la tabla fila a fila, pero claro, dicen por ahí que eso de los cursores es cosa mala….

He aquí un método alternativo a la utilización de los cursores para obtener la columna “agregado” y poder seleccionar el máximo stock, que tiene lugar el 11 de enero con un valor de 530 tornillos.

1º) Creamos una tabla temporal con los registros que nos interesan, ordenándolos por orden cronológico

2º) Recorremos un bucle que analice la primera fila y la eliminamos, de esta forma podemos recorrer la tabla completa.

Veámoslo en el ejemplo:

--Creamos la tabla del ejemplo. Para simplificar introducimos solo los

--datos que utilizaremos, pero esto no afecta al ejemplo

declare @movimientos as table

(Producto nvarchar(10), Cantidad int, Fecha date)

insert into @movimientos values ('tornillos',500,'20090101')

insert into @movimientos values ('tornillos',-200,'20090104')

insert into @movimientos values ('tornillos',-50,'20090102')

insert into @movimientos values ('tornillos',-220,'20090110')

insert into @movimientos values ('tornillos',500,'20090111')

insert into @movimientos values ('tornillos',-100,'20090120')

--Definimos una tabla temporal, con la misma estructura que la original

declare @tmp as table

(Producto nvarchar(10), Cantidad int, Fecha date)

--Introducimos aquí los datos que nos interesen, ordenados convenientemente

insert into @tmp

      select * from @movimientos

      where Producto='tornillos'

      order by fecha asc

--Variables auxiliares que utilizaremos

declare @Acc int,@Max int

set @Acc=0 --Valor acumulado hasta la fila actual

set @Max=0 --Valor máximo hasta la fila actual

--Esta instrucción elimina de la ventana de mensajes el recuento de filas afectadas

set nocount on

--Mientras exista alguna fila en @tmp recorro el bucle

While(Exists(select * from @tmp))

      BEGIN

            --Sumamos la 1ªfila al acumulado

            select top(1) @Acc=@Acc+Cantidad from @tmp;

            --Eliminamos la 1ª fila; ya está computada

            delete top(1) from @tmp;

            --Comprobamos si el acumulado es máximo

            if(@Acc>@Max)

                  set @Max=@Acc;

            print @Acc;

      END

print ''

print 'Máximo alcanzado:'

print @Max

Lo que nos devuelve por pantalla la columna de acumulados y el máximo stock que llegó a haber en el almacén:

500

450

250

30

530

430

Máximo alcanzado:

530

La pega de este método son la cantidad de deletes que utiliza, así que en el siguiente post tocará un método alternativo (sin cursores, of course) más eficaz.