viernes, 26 de abril de 2013

Un uso para la función RAND() en SQL

Gran parte de mi trabajo transcurre con los ojos pegados al Server Management Studio de SQL. En ocasiones tengo que buscar soluciones a ciertos problemas, y a veces encuentro maneras (unas veces más elegantes que otras) de salvar el escollo. El caso es que cuando un año después me surge otro problema semejante me mata tener que buscar un fragmento de código entre cientos de procedimientos almacenados con más o menos líneas de Transact-SQL... Y como no todo el monte es NetBean o Arquitecturas DDD, voy a poner aquí una "breve" reseña sobre un uso de la función RAND() que he necesitado en un par de ocasiones y que, a buen seguro, volveré a necesitar.


RAND() es una función integrada en Transact-SQL para generar valores float -entre 0 y 1 (ambos excluidos)- de manera pseudoaleatoria. No voy a profundizar en los problemas que tiene su uso; hay mucha documentación al respecto. Recomiendo la páginas oficiales de Microsoft (MSDN o TechNet), o esta otra, que combina las funciones NEWID y CHECKSUM para generar valores aleatorios...

Mi problema era otro muy concreto: generar una serie números aleatorios dentro de una muestra finita. Esto, obviamente, hace que usar RAND() a pelo sea inviable. Necesitamos delimitar los posibles valores generados. Vamos a plantearlo con un ejemplo:

Supongamos que tenemos una serie de clientes en una tabla y queremos seleccionar a algunos de ellos al azar para una promoción. Para complicarlo un poco más los clientes beneficiarios de la promoción sólo pueden ser seleccionados entre los que cumplan una condición concreta, por ejemplo que pertenezcan a una ciudad en particular. Es de suponer que nuestra tabla tiene, entre otros atributos, un identificador autonumérico (Id_Cliente), y otros datos como nombre y apellidos, domicilio (que incluye, por supuesto, una ciudad, sino vamos de culo...), etc. Si la tabla es muy grande y nosotros vamos a buscar aleatoriamente, por ejemplo, cinco clientes de Villanueva del Trabuco, esperar que una iteración con RAND() nos devuelva concretamente los identificadores de 5 lugareños del pueblo en cuestión, puede llegar a ser un infierno. Así que el primer paso lógico -o al menos el más fácil- es llevarnos a una tabla temporal a los clientes que habitan en tan hermosa localidad. La temporal podría ser algo así:

CREATE TABLE #TMP_Candidatos ([Id] INT identity (1, 1)
    ,[Id_Cliente] INT NOT NULL)

No necesito nada más: un nuevo identificador autonumérico, cuyo primer valor será 1, y que se incrementará de 1 en 1; y el identificador original del cliente, para cuando vayamos a buscar el resto sus datos a la tabla original.

Como RAND() sólo devuelve valores entre 0 y 1 (y sin incluir los extremos), necesito ampliar el rango de valores devueltos, para lograr valores aleatorios entre el menor y el mayor de los Id de mi tabla temporal. Si multiplicamos cada float generado, por el número de elementos de la tabla, obtendremos siempre un número cuya parte entera esté entre 0 y el número de elementos de la tabla. Eso es precisamente lo que nos interesa, pero como a veces esa parte entera puede valer 0 -y los Id de nuestra tabla empiezan por uno- vamos a sumarle el valor mínimo del identificador, en este caso un 1. El código SQL sería algo así:

DECLARE @size AS INT  -- Tamaño de la muestra
DECLARE @min AS INT   -- Id. mínimo de la muestra
DECLARE @counter AS INT  -- Contador
DECLARE @randomSelected AS INT -- Selección de la muestra

SELECT @size = COUNT(*)
  ,@min = MIN(Id)
FROM #TMP_Candidatos

SET @counter = 1
SET @randomSelected  = 5

WHILE (@counter <= @randomSelected) 
 BEGIN
    SELECT CAST(RAND() * @size + @min AS INT) randomClient
    SET @counter = @counter + 1
 END

Como puede verse, se hace en un bucle WHILE una selección aleatoria de 5 clientes, pero aquí hay un problema: en este código existe la posibilidad de que el destino elija más de una vez al mismo cliente (por numerosos que sean los habitantes de Villanueva del Trabuco, siempre existirá la posibilidad... Por ahí hay un tal Carlos Fabra a quien le ha tocado la lotería once veces...). Yo veo dos posibilidades: o borramos de la tabla temporal a los premiados -evitando que vuelvan a salir-, o los vamos metiendo en una temporal adicional y en cada iteración del bucle comprobamos que no haya sido ya seleccionado. ¿Cuál de los dos métodos da mejor rendimiento?, pues depende del propósito. Me explico, en ambos casos, después de la primera iteración del bucle existe la posibilidad de algo así como falsos positivos, es decir, en el primer método la función RAND() puede dar lugar a un identificador que ya no está en la tabla; y en el segundo caso puede devolverse un identificador que ya haya sido seleccionado, por lo que habría que descartarlo y generar otro.

Yo voy a tomar por bueno el segundo caso, pues mi intención es volver posteriormente a la tabla original de Clientes para recuperar todos los datos de los seleccionados, por lo que es interesante tener sus identificadores agrupados en una tabla temporal. Así que mi código quedaría más o menos así:

DECLARE @size AS INT  -- Tamaño de la muestra
DECLARE @min AS INT   -- Id. mínimo de la muestra
DECLARE @counter AS INT  -- Contador
DECLARE @randomSelected AS INT -- Selección de la muestra

SELECT @size = COUNT(*)
  ,@min = MIN(Id)
FROM #TMP_Candidatos

SET @counter = 1
SET @randomSelected  = 5

CREATE TABLE #TMP_Seleccionados ([Id_Selected] INT NOT NULL
     ,[Id_Cliente] INT NOT NULL)
DECLARE @elegible AS INT

WHILE (@counter <= @randomSelected) 
 BEGIN
    SELECT @elegible = CAST(RAND() * @size + @min AS INT) 
    IF NOT EXISTS(SELECT Id_Selected 
                  FROM #TMP_Seleccionados  
                  WHERE Id_Selected = @elegible) 
       BEGIN 
         INSERT INTO #TMP_Seleccionados
         SELECT Id, Id_Cliente FROM #TMP_Candidatos WHERE Id = @elegible
         SET @counter = @counter + 1
       END
 END

Puede observarse que he creado una nueva temporal (#TMP_Seleccionados) y declarado una nueva variable (@elegible). En la variable almacenaré el valor aleatorio generado y lo introduciré en la nueva tabla temporal, siempre y cuando no esté ya ahí de alguna iteración anterior. De este modo, en la nueva tabla, tendré los Id_Cliente seleccionados para la promoción y podré cruzar con la tabla original de Clientes para obtener los datos de los elegidos.

Probablemente no es la forma más bonita de hacerlo, no termina de gustarme la idea de un exceso de iteraciones del bucle WHILE ante la posibilidad de repeticiones en los resultados del random. Pero al menos funciona bastante bien y en el caso de muestras muy amplias y un moderado número de selecciones aleatorias, las probabilidades de repetir un resultado se reducen considerablemente. Claro que se aceptan sugerencias.

No hay comentarios:

Publicar un comentario