How can I count the number of unique pairs in a table that has a recursive relationship?
select count(*) from table
where personID in (select coupleID from table)
and coupleID in (select personid from table)
and coupleID > personID
hope it helps!
Logic
- use inner join + join table's PersonId = your table's CoupleId and join table's CoupleId = your table's PersonId
- because inner join will get double rows so it have to
count(1) / 2
get the count.
Schema (Oracle v11g)
CREATE TABLE T
("PersonId" int, "CoupleId" int)
INSERT ALL
INTO T ("PersonId", "CoupleId")
VALUES (1, 2)
INTO T ("PersonId", "CoupleId")
VALUES (2, 1)
INTO T ("PersonId", "CoupleId")
VALUES (3, null)
INTO T ("PersonId", "CoupleId")
VALUES (4, 5)
INTO T ("PersonId", "CoupleId")
VALUES (5, 4)
SELECT * FROM dual
Not Allow PersonId and CoupleId Duplicate Version
select count(1) / 2 cnt from T T1
inner join T T2 on T1."PersonId" = T2."CoupleId" and T1."CoupleId" = T2."PersonId"
cnt
---
2
Allow PersonId and CoupleId Duplicate Version
with cte as (
select distinct * from T
)
select count(1) / 2 cnt from CTE T1
inner join CTE T2 on T1."PersonId" = T2."CoupleId" and T1."CoupleId" = T2."PersonId"
cnt
---
2
View on db<>fiddle
You can apply least()
and greatest()
functions to personid
and coupleid
and group by
them and finally count the number of rows:
select count(*) counter from (
select least(personId, coupleId), greatest(personId, coupleId)
from people_table
where personId is not null and coupleId is not null
group by least(personId, coupleId), greatest(personId, coupleId)
)
See the demo