Hoy es 22 de Diciembre, día en que se celebra el sorteo de la lotería de Navidad en España. A mi personalmente no me gustan los juegos de azar y menos con dinero de por medio, me parece que la ludopatía es una enfermedad que se debe combatir y no lucrarse con ella como administración. Sin embargo, me parece una buena ocasión para aprender a generar datos aleatorios en SQL Server. Vamos a crear nuestro propio sorteo de navidad. Vais a ver que hay formas mucho más fáciles de llegar al objetivo final, sin embargo vamos a tratar de aprender el mayor número posible de cosas durante el ejercicio y por tanto a complicarlo.
Nuestro sorteo de lotería
Para nuestro sorteo, igual que en la lotería de navidad, tendremos 100.000 números distintos con los que jugar, es decir números entre 0 y 99.999. También tendremos 5 premios. Nuestro objetivo será crear una consulta SQL Server que devuelva 5 números aleatorios y los asigne a un número de premio entre el 1 y 5. Para terminar creamos un comprobador de premios que nos diga cuánto dinero nos ha tocado.
Crear nuestro sorteo de Navidad
Modelo de datos del sorteo
Siempre que iniciamos un proyecto nuevo en SQL tenemos que definir el que va a ser el modelo de datos que vamos a usar. Esto implica las tablas y los tipos de datos que van a admitir. Tenemos que pensarlo muy bien ya que cambiarlo luego será costoso y debe estar preparado para dimensionarse y añadirle nuevas funcionalidades en un futuro. Nuestro ejemplo es sencillo y como es solo un ejercicio no tenemos que pensar en tantas cosas. Vamos a crear una tabla con todos los posibles números, una tabla con los premios y el importe del premio y otra donde vamos a almacenar los resultados.
Tabla números del sorteo:
Esta es una tabla sencilla, simplemente vamos a necesitar una columna para almacenar todos los posibles números. Si recordáis el primer artículo de este blog hablamos sobre los tipos de datos numéricos y vimos cual era el mejor para cada situación, en nuestro caso el que mejor se adapta es el int. Será un campo que no admite nulos y como tampoco va a tener duplicados será nuestra PK.
CREATE TABLE Numeros (
Numero int NOT NULL
,CONSTRAINT [PK_Numeros] PRIMARY KEY (Numero)
)Tabla premios
En esta tabla vamos a almacenar dos consultas, el premio y el importe del mismo. El premio será un número del 1 al 5 por lo que nos vale con un campo tinyint y el importe lo vamos a crear como numérico. Ninguno de los campos admitirá nulos y el número de premio será nuestra PK
CREATE TABLE Premios (
Premio tinyint NOT NULL
,Importe decimal (9,2) NOT NULL
,CONSTRAINT [PK_Premios] PRIMARY KEY (Premio)
)Tabla resultados de la lotería
Aquí vamos a almacenar los resultados del sorteo de la lotería. Vamos a necesitar una columna con el número del premio y otra con el número premiado. Las columnas tendrán el mismo tipo de datos que en las otras tablas, no admitirán nulos, no podrán contener ningún valor que no exista en las otras tablas ni valores duplicados. Para ello además de una PK compuesta por ambos campos vamos a crear una FK hacia las otras tablas.
CREATE TABLE Resultados (
Premio tinyint NOT NULL
,Numero int NOT NULL
,CONSTRAINT [PK_Resultados] PRIMARY KEY (Premio,Numero)
,CONSTRAINT [FK_Resultados_Premios] FOREIGN KEY (Premio) REFERENCES Premios(Premio)
,CONSTRAINT [FK_Resultados_Nuemros] FOREIGN KEY (Numero) REFERENCES Numeros(Numero)
)Cargar datos en las tablas de la lotería
Vamos a cargar datos en nuestras tablas de loterías, para los números vamos a usar un bucle. Declararemos una variable i con un valor igual a 0 y mientras cumpla la condición de ser menor que 100.000 insertaremos su valor en la tabla y luego le sumaremos 1. Para los premios definiremos directamente los valores a insertar
declare @i int = 0
WHILE @i<100000
BEGIN
INSERT INTO Numeros VALUES (@i)
SET @i = @i+1
END
INSERT INTO Premios VALUES (1,400000),(2,125000),(3,50000),(4,20000),(5,6000)Sorteo de la lotería de Navidad
Llegó el momento que toda España está esperando, ya tenemos cargados los dos bombos, digo las dos tablas y es la hora de iniciar el sorteo. Vamos a crear una consulta que seleccione uno de los números y uno de los premios al azar, el único requisito es que no hayan salido antes. Esa combinación ganadora la vamos a insertar en nuestra tabla de resultados. Ejecutaremos la consulta en bucle hasta que deje de devolver valores porque no queden premios sin un número asignado. Esta es mi solución propuesta:
DECLARE @Filas TINYINT = 1
WHILE @Filas=1
BEGIN
WITH Agraciado AS (
SELECT TOP 1 Numero
FROM Numeros
WHERE NOT EXISTS (
SELECT 1
FROM Resultados
WHERE Resultados.Numero = Numeros.Numero)
ORDER BY NEWID()
),
Gratificacion AS (
SELECT TOP 1 Premio
FROM Premios
WHERE NOT EXISTS (
SELECT 1
FROM Resultados
WHERE Resultados.Premio = Premios.Premio)
ORDER BY NEWID()
)
INSERT INTO Resultados (Premio, Numero)
SELECT Premio, Numero
FROM Agraciado
CROSS APPLY Gratificacion
SET @Filas = @@ROWCOUNT
ENDEn mi caso este ha sido el resultado:
Comprobar décimos lotería de navidad
Ya tenemos a los ganadores de nuestra lotería de navidad, ahora es el momento de facilitar a los usuarios una herramienta para que puedan comprobar si su décimo ha sido o no premiado. Vamos a crear un procedimiento almacenado que nos diga si nuestro número ha sido premiado o no. Si ha sido premiado nos dirá cuánto dinero nos ha tocado.
CREATE PROCEDURE CompruebaPremio @decimo int
AS
DECLARE @resultado varchar(255)
IF 1=(select 1 from Resultados where Numero=@decimo)
BEGIN
SELECT @Resultado='El número ' + RIGHT('00000'+CAST(Resultados.Numero AS varchar(5)),5)
+ ' ha sido un ' + CONVERT(varchar(1),Resultados.Premio)
+ 'º premio y te han tocado ' + Convert(varchar(9),Premios.Importe) + '€.'
FROM Resultados
INNER JOIN Premios ON Resultados.Premio = Premios.Premio
WHERE numero= @decimo
END
ELSE
BEGIN
SET @Resultado = 'El número ' + RIGHT('00000'+CAST(@decimo AS varchar(5)),5) + ' no ha sido premiado.'
END
PRINT @Resultado
GOAhora podremos ejecutar nuestro procedimiento almacenado pasándole el número que estamos jugando como parámetro y nos dirá si hemos sido o no premiados.
Conclusión
Con el juego de hoy hemos tenido la oportunidad de ver variedad de sintaxis de SQL Server. Para crear nuestro sorteo hemos creado tablas con Primary y Foreign Keys, hemos visto como crear consultas que se ejecutan en bucle y CTEs. Para el comprobador no nos hemos quedado cortos, hemos creado un procedimiento almacenado con validaciones, conversiones de tipos de datos y textos concatenados.
Como os he dicho al principio hay maneras más sencillas de conseguir el mismo resultado, esta ha sido mi propuesta para enseñaros todo lo posible en un espacio limitado. Ahora os reto a que juguéis vosotros también, pero no a la lotería sino a crearla en vuestras bases de datos. Hacedlo a vuestra manera y compartidme vuestras soluciones, seguro que entre todos encontramos soluciones ingeniosas. Os leo en los comentarios, mucha suerte !!
