Query with best number combinations from table

Question

Hello,

I'm analyzing numbers probability.

I have table with 10 columns. All columns INTEGER, NOT NULL values. Value for every column is between 1 and 30.

In this table there are 1000 rows. I want to get best numbers combinations. I try to explain.

Let's say in table are stored these 3 rows:

1 2 3 4 5 6 7 10 15 25

1 2 3 4 5 6 7 14 19 25

1 2 3 4 5 6 7 17 19 25

For example I want to get combinations, which where repeated by 9 numbers more than once in format "combination count(*)"

The answer would be:

1 2 3 4 5 6 7 19 25         2

since these numbers was in 2 and 3 rows - total 2 times.

Now need get combinations, which where repeated by 8 numbers more than once in format "combination count(*)".

The answer would be:

1 2 3 4 5 6 7 25           3 (these numbers where in all 3 rows)

1 2 3 4 5 6 7 19           2 (these numbers were in 2,3 rows)

Can someone suggest method which would check such combinations?

Answer 1

If we assume that each row has a primary key, e.g.

declare @t table (Pk int identity(1,1), Num1 int, Num2 int, Num3 int, ..., Num10 int)

;with cte as (select PK, ColVal from @t UNPIVOT (ColVal for ColName IN ([Num1],[Num2],...)) unpvt)

select * from (select T1.PK, T2.PK, count(T1.PK) as CntVals from cte t1 inner join cte t2 on t1.ColVal = t2.ColVal and T1.PK < T2.PK

GROUP BY t1.PK, t2.Pk HAVING COUNT(distinct T1.PK) > @N) combinations

The above will return all primary keys for rows which satisfy the requirement.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

I have an article entitled “ Permutations, Combinations, Power Sets and Pizzas” waiting to be published at http://www.simple-talk.com/ which might help you. 

Why did you fail to post DDL? What you did post is a mess of non-1NF data that has to be fixed. Here is what we need for data integrity: 

CREATE TABLE Combinations 
(comb_name CHAR(3) NOT NULL, 
 comb_seq SMALLINT NOT NULL
  CHECK(comb_seq BETWEEN 1 AND 10), 
 PRIMARY KEY (comb_name, comb_seq), 
 element SMALLINT NOT NULL
  CHECK(element BETWEEN 1 AND 30));

INSERT INTO Combinations
VALUES ('A', 1, 1), ('A', 2, 2), ('A', 3, 3),.., 
       ('B', 1, 1), ('B', 2, 2), ('B', 3, 5), .., 
        ('C', 1, 1), ('C', 2, 5), ('C', 3, 6), ..;

There will be (1000 * 999) possible pairs of combinations, for triples (1000 * 999 * 998) and so on. This is called a combinatorial explosion. It means that there is no reasonable way to do this

I would suggest that you pass a particular pair of combinations to a procedure and get back the results for the pair. 

CREATE PROCEDURE Match_Combinations 
(@in_comb_name_1 VARCHAR(3), 
 @in_comb_name_2 VARCHAR(3))
AS
SELECT COUNT(X.element) AS matched_elements
FROM (SELECT C1.element 
        FROM Combinations AS C1
       WHERE C1.comb_name = @in_comb_name_1
      INTERSECT 
      SELECT C2.element 
        FROM Combinations AS C2
        WHERE C2.comb_name = @in_comb_name_2) AS X(element);

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL