Pairing Row Values in Any Order

I'm trying to find % of unique pairs that exist in two columns. For example Where 1,6 and 6,1 / 2,3 and 3,2 are unique pairs. So the % of matched pairs is 33%

• Please tag you database, version included Aug 21 '19 at 16:26

Doesn't make much sense:

• the number of pairs is 6, -- not relevant to the question...
• the number of unique permuted pairs is 4 - far more relevant...
• There are 2 matched pairs so I make the number of unique to matched pairs at 4/2, i.e. 50%...

Notwithstanding the objection above, what you can do is something like this, using the LEAST and GREATEST (almost standard - see discussion below) SQL functions.

This example (see fiddle) is from PostgreSQL, however see discussion at the end.

CREATE TABLE test (col1 INTEGER, col2 INTEGER);

INSERT INTO test VALUES (1, 6), (2,3), (3, 2), (4, 7), (5, 8), (6, 1);

First query:

SELECT LEAST(col1, col2) AS mn_c, GREATEST(col1, col2) AS mx_c,
COUNT(*)
FROM test
GROUP BY 1, 2

Result:

mn_c    mx_c    count
2       3        2
4       7        1
1       6        2
5       8        1

Then:

SELECT COUNT(cnt1) AS matched_count,
ROUND(COUNT(cnt1)/(SELECT COUNT(*) FROM test)::FLOAT * 100) AS percentage
FROM
(
SELECT LEAST(col1, col2) AS mn_c, GREATEST(col1, col2) AS mx_c,
COUNT(*) AS cnt1
FROM test
GROUP BY 1, 2
HAVING COUNT(*) > 1
) AS t

Result:

matched_count   percentage
2           33

A version of the above code should work for most servers - see here for a discussion of the LEAST and GREATEST functions in other servers - works pretty much for all of them except MS SQL Server.

p.s. have you considered what happens if you have duplicate columns as follows?

INSERT INTO test VALUES (1, 6), (2,3), (3, 2), (4, 7), (5, 8), (6, 1), (5,5), (5,5)

Note the duplication of a duplication - (5,5) - see here for the differences which emerge between my approach and that of @McNets. My solution says that there are 3/8 matched pairs, but McNets says different. Not sure if I completely understand what his SQL is doing?

Anyway, interesting question (+1) - why do you want to do this? p.s. welcome to the forum! :-)

Using standard SQL could be:

SELECT
COUNT(*) * 100 / (SELECT COUNT(*) FROM t)
FROM
t t1
WHERE NOT EXISTS (SELECT 1
FROM   t t2
WHERE  t2.col1 = t1.col2
AND    t2.col2 = t1.col1);
| Percent |
| ------: |
|      33 |

db<>fiddle here

• Would you care to have a look here and comment on the differences between your answer and mine for a duplication of a duplication - i.e. (5,5)? Aug 25 '19 at 23:28
• Hi @ Verace, I think OP should clarify whether duplicate pairs should be considered once or not. Aug 26 '19 at 7:16

You may want to do as follows (if your DB is MS SQL)

Create table #Pairs
(c1 int,
c2 int
);
go

insert into #Pairs
values
(1,6),
(2,3),
(3,2),
(4,7),
(5,8),
(6,1)
go

select p1.c1, p1.c2,
p2.PairCount,
--(select count (*) from PairTest) as TotalPairs,
(p2.PairCount + 0.0) / (select count (*) from PairTest) as Pair_Percent
from #Pairs as p1
join
(
select (c1+ c2) as Pair, COUNT (*) PairCount
from #Pairs
group by (c1+ c2)
) as p2 on (p1.c1 + p1.c2) = p2.pair
go

Drop table #Pairs;