Duplicate Percentage I am using SQL Server. I have a table like this:


+----+-------+-----------+
| Id | Type  |   Value   |
+----+-------+-----------+
|  1 | Phone | 999999001 |
|  1 | SSN   | 333444555 |
|  1 | Phone | 999999002 |
|  2 | Phone | 999999003 |
|  2 | SSN   | 444444444 |
|  2 | Phone | 999999004 |
|  3 | Phone | 999999001 |
|  3 | SSN   | 555555555 |
|  3 | Phone | 999999006 |
|  4 | Phone | 999999001 |
|  4 | SSN   | 555555555 |
|  4 | Phone | 999999006 |
|  4 | Phone | 999999008 |
+----+-------+-----------+


...that I need to turn into this:


+----+-----------+---------+----------+------------------+
| ID | RelatedID | Matches | Compared | PercetageMatched |
+----+-----------+---------+----------+------------------+
|  1 |         2 |       0 |        3 | 0                |
|  1 |         3 |       1 |        3 | 0.33             |
|  1 |         4 |       1 |        4 | 0.25             |
|  2 |         1 |       0 |        3 | 0                |
|  2 |         3 |       0 |        3 | 0                |
|  2 |         4 |       0 |        4 | 0                |
|  3 |         1 |       1 |        3 | 0.33             |
|  3 |         2 |       0 |        3 | 0                |
|  3 |         4 |       3 |        4 | 0.75             |
|  4 |         1 |       1 |        3 | 0.33             |
|  4 |         2 |       0 |        3 | 0                |
|  4 |         3 |       3 |        4 | 0.75             |
+----+-----------+---------+----------+------------------+


Every id grouping would have to compare to every other id grouping and report on their matches.  

I cannot find a way to do this simply and efficiently, with the millions of records I have.

My current solution is running a query per record and the query is still going.  Most likely even as you read this.  :)

I even cut the process in half because once one side is evaluated, there side of the equation is the same.

Also, in the end, I am only going to report on anything greater than a certain percentage.  Example > 90%.

Anyone have any ideas?
You don't need to find the ids with no matches when you finally filter for a percentage > 0. 

Standard SQL based, but fully untested:

with cte as
 (
   select Id, Type, Value,
      count(*) over (partition by Id) as RowCount -- maximum number of possible matches
   from tab
 )
select t.ID, cte.Id as RelatedID,
   count(*) as countMatches,
   max(cte.RowCount) as Compared,
   100.0 * count(*) / max(cte.RowCount) as PercetageMatched
from tab as t -- or cte if you need the RowCount, too
join cte
  on t.Type  = cte.Type
 and t.Value = cte.Value
where t.id &lt; cte.id
group by t.ID, cte.Id


I don't know about the actuallogic, but you might do the percentage calculation based on the min (or max) of both RowCounts.

Edit:

Of course this will create a huge intermediate result set, mainly based on the count of duplicate values. Following SQL calculates the number of rows before aggregation:

select sum(cnt * (cnt-1) / 2), max(cnt), count(*)
from 
 ( select count(*) as cnt
   from tab 
   group by type, value
 ) as dt


You might materialize the result of the cte in a table with a clustered index on (type, value, id), maybe this increases performance...