domingo, 18 de marzo de 2012

Numerando

Que a Codd le apetecía utilizar los conjuntos para definir su modelo relacional ya lo sabíamos o estáis a punto de saberlo. Una característica importante de los conjuntos, matemáticamente hablando, es su ausencia de orden. Así, pues, partiendo de esta sencilla tabla


create table tabla(
num integer primary key,
tipo char(1),
precio decimal(6,2)
)

la teoría nos dice que aunque obtengamos la siguiente salida

select * from tabla; 


num tipo precio
241 A 241,44
503 A 202,68
544 A 243,58
604 A 3,98
904 A 3,91
985 A 84,54
54 B 54,33
254 B 253,58
828 B 228,07
894 B 293,75
155 C 155,08
560 C 259,89
636 C 36,47
957 C 57,16

nada nos garantiza que la fila que vemos arriba, justo debajo de la cabecera, sea "la primera" fila, ni la siguiente "la segunda", ni... Por mucho que nos empeñemos en seguir los dictados de nuestra educación de primaria, "de izquierda a derecha y de arriba a abajo" no se aplica, aunque lo parezca. Otra cosa es que se requiera ordenar, los listados son para eso, y es muy normal algo así:

select * from tabla order by tipo,num;

num tipo precio
241 A 241,44
503 A 202,68
544 A 243,58
604 A 3,98
904 A 3,91
985 A 84,54
54 B 54,33
254 B 253,58
828 B 228,07
894 B 293,75
155 C 155,08
560 C 259,89
636 C 36,47
957 C 57,16



Pero hete aquí que, de repente, necesitamos algo más, queremos numerar las filas. El no va más. MySQL tiene unas peculiaridades que nos permiten hacer cosas así.

select @fila:=@fila+1 orden, tabla.*
from tabla,
(select @fila:=0) xx
order by tipo,num;
 
orden num tipo precio
1 241 A 241,44
2 503 A 202,68
3 544 A 243,58
4 604 A 3,98
5 904 A 3,91
6 985 A 84,54
7 54 B 54,33
8 254 B 253,58
9 828 B 228,07
10 894 B 293,75
11 155 C 155,08
12 560 C 259,89
13 636 C 36,47
14 957 C 57,16


O sea, hemos añadido una columna "orden" que numera secuencialmente las filas. Esto se consigue mediante una variable de usuario, @fila. En primer lugar debemos inicializarla, lo que se consigue con la subconsulta "xx" que se utiliza como tabla temporal en el from. Para los muy programadores hay una sintaxis alternativa que igual les haga sentir más cómodos, pero los resultados son idénticos:

set @fila:=0;
select @fila:=@fila+1 orden, tabla.*
from tabla
order by tipo,num;


Pero, ¿y si quisiéramos más? ¿Y si nuestro problema es que queremos que el contador se reinicie para cada tipo diferente?

orden num tipo precio
1 241 A 241,44
2 503 A 202,68
3 544 A 243,58
4 604 A 3,98
5 904 A 3,91
6 985 A 84,54
1 54 B 54,33
2 254 B 253,58
3 828 B 228,07
4 894 B 293,75
1 155 C 155,08
2 560 C 259,89
3 636 C 36,47
4 957 C 57,16


La solución es la siguiente:

select if (@tipo=tipo,(@fila:=@fila+1),@fila:=1) orden, num, @tipo:=tipo tipo, precio
from tabla,
(select @fila:=0) xx,
(select @tipo:='') tt
order by tipo,num;

MySQL evalua las columnas de la parte select de izquierda a derecha, lo que permite que la condición if (@tipo=tipo,(@fila:=@fila+1),@fila:=1) añada 1 al contador si el tipo de la fila anterior (@tipo) es igual al de la fila que se esté tratando, y lo reinicie a 1 si son distintos. A continuación, se actualiza @tipo al valor de la fila actual y se deja preparado para la siguiente.

Para aclararnos, el orden de las columnas y las asignaciones a las variables de usuario es algo así como si hubiéramos escrito en algo parecido a PHP:
$fila = 0; $tipo='';
while ($tupla = obtenertupla() {
   if ($tipo == $tupla['tipo']) $fila++;
   else $fila=1;
   echo $fila . ":" . $tupla['num']. ":" . $tupla['tipo']. ":" . $tupla['precio'];
   $tipo =  $tupla['tipo']
}

Dicho de otra forma, si hubiéramos reordenado las columnas como

select @tipo:=tipo tipo, if (@tipo=tipo,(@fila:=@fila+1),@fila:=1) orden, num, precio from...

ya no hubiera funcionado. ¿Qué quieres reordenar las columnas? Vale.

select tipo, orden, num, precio
from
(
select if (@tipo=tipo,(@fila:=@fila+1),@fila:=1) orden, num, @tipo:=tipo tipo, precio
from tabla,
(select @fila:=0) xx,
(select @tipo:='') tt
order by tipo,num
) zz;

tipo orden num precio
A 1 241 241,44
A 2 503 202,68
A 3 544 243,58
A 4 604 3,98
A 5 904 3,91
A 6 985 84,54
B 1 54 54,33
B 2 254 253,58
B 3 828 228,07
B 4 894 293,75
C 1 155 155,08
C 2 560 259,89
C 3 636 36,47
C 4 957 57,16


En cualquier caso, aparte de las mencionadas variables de usuario y sus peculiaridades, hay que echarle un vistazo a los requisitos y uso de las subconsultas como tablas temporales.

Referencias adicionales: Jimmy's blogCambrico.net.