Contar ocurrencias en SQL
Lo que casi imposible de logar, se pudo logar con este query:
--alter table [Q6].[dbo].[q6.19ago22] add idserie int identity(1,1)
--inicio all--
Declare @tblConteos TABLE(Veces int, Nros varchar(30), Orden varchar(10))
/* Conocer Parejas*/
;WITH SEMILLA AS (Select [idserie] as Serie, Numero from [Q6].[dbo].[q6.19ago22]
UNPIVOT(Numero FOR Nros in (N1, N2, N3, N4, N5, N6)) as Num)
, SERIES2 AS (Select TOP 100 PERCENT S1.Serie, S1.Numero as Numero1, S2.Numero as Numero2
FROM SEMILLA AS S1
CROSS JOIN
SEMILLA AS S2
WHERE S2.Serie = S1.Serie AND S2.Numero <> S1.Numero AND S2.Numero > S1.Numero
ORDER BY S1.Numero)
insert into @tblConteos
SELECT COUNT(DISTINCT(Serie)) AS Veces, Numero1 + ',' + Numero2 as Parejas, 'Parejas'
FROM SERIES2
GROUP BY Numero1, Numero2
HAViNG COUNT(DISTINCT(Serie))>1
/*Trios*/
;WITH SEMILLA AS (Select [idserie] as Serie, Numero from [Q6].[dbo].[q6.19ago22]
UNPIVOT(Numero FOR Nros in (N1, N2, N3, N4, N5, N6)) as Num)
, SERIES3 AS (Select TOP 100 PERCENT S1.Serie, S1.Numero as Numero1, S2.Numero as Numero2, S3.Numero as Numero3
FROM SEMILLA AS S1
CROSS JOIN
SEMILLA AS S2
CROSS JOIN
SEMILLA AS S3
WHERE S1.Serie = S2.Serie AND S1.Numero <> S2.Numero AND S1.Numero < S2.Numero
AND S2.Serie = S3.Serie AND S2.Numero <> S3.Numero AND S2.Numero < S3.Numero
ORDER BY S1.Numero)
INSERT INTO @tblConteos
SELECT COUNT(DISTINCT(Serie)) AS Veces, Numero1 + ',' + Numero2 + ',' + Numero3 as Trios, 'Trios'
FROM SERIES3
GROUP BY Numero1, Numero2, Numero3
HAViNG COUNT(DISTINCT(Serie))>1
/*Cuartetos*/
;WITH SEMILLA AS (Select [idserie] as Serie, Numero from [Q6].[dbo].[q6.19ago22]
UNPIVOT(Numero FOR Nros in (N1, N2, N3, N4, N5, N6)) as Num)
, SERIES4 AS (Select TOP 100 PERCENT S1.Serie, S1.Numero as Numero1, S2.Numero as Numero2, S3.Numero as Numero3, S4.Numero as Numero4
FROM SEMILLA AS S1
CROSS JOIN
SEMILLA AS S2
CROSS JOIN
SEMILLA AS S3
CROSS JOIN
SEMILLA AS S4
WHERE S1.Serie = S2.Serie AND S1.Numero <> S2.Numero AND S1.Numero < S2.Numero
AND S2.Serie = S3.Serie AND S2.Numero <> S3.Numero AND S2.Numero < S3.Numero
AND S3.Serie = S4.Serie AND S3.Numero <> S4.Numero AND S3.Numero < S4.Numero
ORDER BY S1.Numero)
INSERT INTO @tblConteos
SELECT COUNT(DISTINCT(Serie)) AS Veces, Numero1 + ',' + Numero2 + ',' + Numero3 + ',' + Numero4 as Cuartetos, 'Cuartetos'
FROM SERIES4
GROUP BY Numero1, Numero2, Numero3, Numero4
HAViNG COUNT(DISTINCT(Serie))>1
/*Quintetos*/
;WITH SEMILLA AS (Select [idserie] as Serie, Numero from [Q6].[dbo].[q6.19ago22]
UNPIVOT(Numero FOR Nros in (N1, N2, N3, N4, N5, N6)) as Num)
, SERIES5 AS (Select TOP 100 PERCENT S1.Serie, S1.Numero as Numero1, S2.Numero as Numero2, S3.Numero as Numero3, S4.Numero as Numero4, S5.Numero as Numero5
FROM SEMILLA AS S1
CROSS JOIN
SEMILLA AS S2
CROSS JOIN
SEMILLA AS S3
CROSS JOIN
SEMILLA AS S4
CROSS JOIN
SEMILLA AS S5
WHERE S1.Serie = S2.Serie AND S1.Numero <> S2.Numero AND S1.Numero < S2.Numero
AND S2.Serie = S3.Serie AND S2.Numero <> S3.Numero AND S2.Numero < S3.Numero
AND S3.Serie = S4.Serie AND S3.Numero <> S4.Numero AND S3.Numero < S4.Numero
AND S4.Serie = S5.Serie AND S4.Numero <> S5.Numero AND S4.Numero < S5.Numero
ORDER BY S1.Numero)
INSERT INTO @tblConteos
SELECT COUNT(DISTINCT(Serie)) AS Veces, Numero1 + ',' + Numero2 + ',' + Numero3 + ',' + Numero4 + ',' + Numero5 as Quintetos, 'Quintetos'
FROM SERIES5
GROUP BY Numero1, Numero2, Numero3, Numero4, Numero5
HAViNG COUNT(DISTINCT(Serie))>1
/*Sextetos*/
;WITH SEMILLA AS (Select [idserie] as Serie, Numero from [Q6].[dbo].[q6.19ago22]
UNPIVOT(Numero FOR Nros in (N1, N2, N3, N4, N5, N6)) as Num)
, SERIES6 AS (Select TOP 100 PERCENT S1.Serie, S1.Numero as Numero1, S2.Numero as Numero2, S3.Numero as Numero3, S4.Numero as Numero4, S5.Numero as Numero5, S6.Numero as Numero6
FROM SEMILLA AS S1
CROSS JOIN
SEMILLA AS S2
CROSS JOIN
SEMILLA AS S3
CROSS JOIN
SEMILLA AS S4
CROSS JOIN
SEMILLA AS S5
CROSS JOIN
SEMILLA AS S6
WHERE
S1.Serie = S2.Serie AND S1.Numero <> S2.Numero AND S1.Numero < S2.Numero
AND S2.Serie = S3.Serie AND S2.Numero <> S3.Numero AND S2.Numero < S3.Numero
AND S3.Serie = S4.Serie AND S3.Numero <> S4.Numero AND S3.Numero < S4.Numero
AND S4.Serie = S5.Serie AND S4.Numero <> S5.Numero AND S4.Numero < S5.Numero
AND S5.Serie = S6.Serie AND S5.Numero <> S6.Numero AND S5.Numero < S6.Numero
ORDER BY S1.Numero)
INSERT INTO @tblConteos
SELECT COUNT(DISTINCT(Serie)) AS Veces, Numero1 + ',' + Numero2 + ',' + Numero3 + ',' + Numero4 + ',' + Numero5 + ',' + Numero6 as SEXTETOS, 'SEXTETOS'
FROM SERIES6
GROUP BY Numero1, Numero2, Numero3, Numero4, Numero5, Numero6
HAViNG COUNT(DISTINCT(Serie))>1
SELECT TOP 20 Veces, Nros FROM @tblConteos where orden = 'PAREJAS' ORDER BY Veces DESC
SELECT TOP 20 Veces, Nros FROM @tblConteos where orden = 'TRIOS' ORDER BY Veces DESC
SELECT TOP 20 Veces, Nros FROM @tblConteos where orden = 'CUARTETOS' ORDER BY Veces DESC
SELECT TOP 20 Veces, Nros FROM @tblConteos where orden = 'QUINTETOS' ORDER BY Veces DESC
SELECT TOP 20 Veces, Nros FROM @tblConteos where orden = 'SEXTETOS' ORDER BY Veces DESC
--fin all--