Encontrando combinaciones numéricas con SQL Server

Cuando comencé a programar mis primeras aplicaciones en SQL Server tuve que trabajar mucho en el desarrollo de la  programación en T-SQL desde una perspectiva de lógica de conjuntos; al igual que muchos principiantes caí en el error de utilizar(o abusar?) de objetos que me resultaban familiares a los utilizados en  lenguajes de programación procedimental,  como  por ejemplo los cursores.  Con el objetivo de irme adaptando a este nuevo entorno de trabajo inicie realizando ejercicios prácticos, entre los cuales destaca el que presento a continuación y que servirá de base para el presente artículo.  El enunciado del problema es el siguiente:

 

“Se cuenta con un conjunto de series numéricas, en donde cada serie está formada por 6 números que oscilan en un rango de 1 a 49, dichos números nunca se repiten.  Se necesita saber cuáles son los pares numéricos que más se repiten en las series numéricas”.

 

Como base para este articulo adjunto un archivo de Excel con una serie de combinaciones numéricas como la mostrada a continuación: 

 

Sobre la base del problema planteado se  requiere conocer cuáles son los pares numéricos que más se repiten en estas series, tomemos una tabla modelo con cinco series numéricas como la que se muestra a continuación:

 

 

Podemos decir que tenemos tres pares numéricos que son los que se repiten con mayor frecuencia en este grupo, estos son:

 

Pares Numéricos

Repeticiones

12-34

2

13-32

2

27-39

2

 

Obviamente requerimos realizar este proceso de forma rápida y con un buen rendimiento para el manejo de un alto volumen de registros, por lo que iniciaremos con la importación de nuestro archivo de trabajo a una tabla para su posterior tratamiento.  El siguiente script nos permite crear nuestra base de datos de ejemplo.

 

CREATE DATABASE [ExampleSql] ON  PRIMARY

( NAME = N’ExampleSql’, FILENAME = N’C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAExampleSql.mdf’ , SIZE = 3072KB , FILEGROWTH = 1024KB )

 LOG ON

( NAME = N’ExampleSql_log’, FILENAME = N’C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAExampleSql_log.ldf’ , SIZE = 1024KB , FILEGROWTH = 10%)

GO

 

Importando de Excel a SQL Server 2005

Abrimos el SQL Server Management Studio, y nos posicionamos en nuestra BD de trabajo

  

Luego damos click derecho y marcamos la opción de Tarea y posteriormente  Importación de Datos .  Procedemos a seleccionar nuestro archivo de origen (marcamos la casilla que indica que nuestra primera fila tiene los nombres de las columnas).

 

Finalmente seleccionamos como destino nuestra base de datos.

 

 

Luego debemos seleccionar la opción que indica copiar datos de una o más tablas o vistas.

 

 Para finalizar la importación se nos presenta una pantalla en la que podemos dejar el nombre por defecto de la tabla (en este caso el mismo de la hoja de cálculo de donde se tomaron los datos), o escribir un nombre específico, en nuestro caso se definió el nombre Tabla_Excel.

 

 

A partir de este momento ya tenemos nuestra tabla inicial de trabajo, la cual contiene más de 4500 series numéricas.  Iniciaremos el proceso adaptando esta tabla al modelo que nosotros requerimos.

 Diseñando nuestra tabla de trabajo

El primer paso para el diseño de nuestra solución consiste en la creación de la tabla DetailNumbers  en donde se almacenaran los números que forman las series numéricas.

 

CREATE TABLE DetailNumbers

(IdSerie int NOT NULL,

 Number smallint NOT NULL CONSTRAINT [CHK_DetailNumbers_Number]      CHECK(Number BETWEEN 1 AND 49),

 PRIMARY KEY (IdSerie,Number)

)

 

En la definición de las tabla se incluyeron las declaraciones de claves primarias, la invocación de las funciones getdate y suser_name como valores por defecto, así como la inclusión de la restricción referida al rango de los números para que estos se encuentren en un rango de 1 a 49.   

 

Para la adaptación de la tabla DetailNumbers debemos de aplicar una función que nos permita transformar las columnas (Number 1..6) en filas, agrupadas por el IdNumber que representa el código único de la serie.

 

 

Para realizar este proceso de transformación de datos nos auxiliamos de la función Unpivot incorporada en SQL Server 2005, esta nos permite rotar columnas dentro de filas.

 

Insert Into DetailNumbers(IdSerie,Number)

Select IdSerie,Number

From dbo.Tabla_Excel

UNPIVOT(Number For Numbers IN([Number1],[Number2],[Number3],[Number4],[Number5],[Number6])) AS U

 

Con esta función tomamos las columnas correspondientes a los números que forman las series y son convertidas en filas para su inserción en la tabla DetailNumbers.  Para más información sobre el uso de esta función podemos acudir a los libros en Pantalla de SQL Server.

Actualmente  los datos han sido cargados de forma tal que nos permitirá implementar la solución del problema planteado.  La solución está basada en el uso del operador Cross Join, el cual es definido como un operador que enlaza  cada fila de la primera entrada con cada fila de la segunda entrada, en nuestro caso aplicaremos este operador creando una copia virtual de nuestra tabla de trabajo.  El siguiente grafico resume el uso de este operador:

  

Al igual que el primer número (30) es enlazado con cada uno de los valores de la tabla T2, sucede lo mismo con los siguientes valores de la serie. Los resultados de este primer cruce seria la  combinación:  (30,30),(30,27),(30,35),(30,38),(30,11),(30,26).

 

En términos de T-SQL se representaría de la siguiente forma:

 

Hasta el momento lo que tenemos es una consulta que nos da el total de todas las combinaciones posibles, obviamente esto no es lo solicitado pero es un buen punto de partida.  El siguiente paso será ir refinando nuestra consulta, como primer paso aplicaremos un filtro dentro de una condición, y es excluir del producto cartesiano a los números que sean iguales, partiendo del hecho de que ningún numero de la serie puede repetirse.  Al aplicar el filtro el resultado reducirá el número de combinaciones (de forma poco significativa), pero cumplimos con una de las condiciones planteadas.

 


Es posible observar un hecho interesante y es que al hacer esta autocombinación, hay productos que se repiten, por ejemplo al combinar el primer número de la serie (30)  se genera un producto (30,27), y al proceder a realizar la combinación del siguiente número (27) este generara un producto (27,30) habiendo una duplicidad, por lo que aplicamos otro filtro en la condición que nos permita solventar este inconveniente.

 

Lo siguiente que debemos hacer es aplicar un filtro para que el producto cartesiano se realice únicamente entre los números que pertenecen a una misma serie.

  

La fase final de nuestra consulta requiere la implementación de la instrucción clave “Group By”, hasta el momento habíamos obtenido las diferentes parejas de combinaciones numéricas por serie, pero al utilizar esta función nos permitirá la agrupación de los pares numéricos encontrados en la tabla DetailNumbers, además complementamos con el uso de la función Count y Distinct .  La consulta final se muestra a continuación:

SELECT COUNT(DISTINCT T1.IdSerie) AS Coincidencias,T1.Number AS Number1,T2.Number AS Number2

FROM DetailNumbers T1

CROSS JOIN

DetailNumbers T2

WHERE T1.Number<>T2.Number AND T1.Number<T2.Number AND T1.IdSerie=T2.IdSerie

GROUP BY T1.Number,T2.Number

ORDER BY Coincidencias DESC

 

 

Para verificar los resultados podemos ejecutar la siguiente consulta, en donde solo debemos introducir los valores que conforman el par numérico.

 

Select IdSerie From DetailNumbers

Where Number In (9,38)

group by IdSerie

having  Count(IdSerie)=2

 

La pantalla anterior muestra una pequeña muestra de las apariciones del par numérico (9,38), además de especificar el número de la serie en donde está presente, estos registros los podemos comparar contra el archivo original de Excel y así comprobamos la fiabilidad de los resultados. 

Bueno, ha sido un placer haber compartido este pequeño articulo con ustedes, y cualquier consulta no duden en escribirme.

8 comentarios en “Encontrando combinaciones numéricas con SQL Server”

  1. hey gio..encontre interesante el articulo,si lo aplico para ganarme le premio del Loto ,como lo hago?
    este consiste en acertar 6 numeros que se extraen de 39 bolitas ,el ganador es que le acierta..
    ahora,yo tengo una estadistica un poco primitiva pero es mia y no se si funciona,en fin…la idea es, si te digo ” sabes,el numero cuatro ,el nueve,van a salir…como lo hago para hacer una combinacion con estos numeros y cuantasa apuestas debo hacer
    ej. 4-9-?-?-?-?

  2. tengo un equipo de volley ball y tengo 53 jugadores verdad las combinaciones que quiero yo calcular son las siguientes digamos que una entra de mis jugadores es de 6 jugadores ej ? 02-34-52-18-30-45 estos son las 6 personas que entraran a jugar pero tengo 53 jugadores con estos 53 jugadores cuantas combinaciones de entradas puedo yo obtener hay algun programa que le meta la cantidad de jugadores o los numeros del 01 al 53 y que me acomode 500 combinaciones diferntes de jugadores???? alquien que responda por favor ( venezolano0@hotmail.com )

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *