Given a table holding edges in a directed graph like this:
CREATE TABLE edges (
from_here int not null,
to_there int not null
)
What's the nicest way to get the number of distinct undirected links for a specific node? There aren't any duplicate directed edges nor are any nodes directly linked to themselves, I just want to avoid counting duplicate undirected edges (such as (1,2)
and (2,1)
) twice.
This works but the NOT IN
smells bad to me:
SELECT COUNT(*)
FROM edges
WHERE from_here = 1
OR (to_there = 1 AND from_here NOT IN (
SELECT to_there
FROM edges
WHERE from_here = 1
))
PostgreSQL-specific solutions are fine for this.
If it were the case that for every edge, there was a reciprocal (e.g. if (1,2)
exists, then (2,1)
must exist), then you could simply narrow your list like so:
Select Count(*)
From edges
Where from_here < to_here
And from_here = 1
If we cannot assume that a reciprocal edge always exists, then you could use the Except predicate:
Select Count(*)
From (
Select from_here, to_there
From edges
Where from_here = 1
Or to_there = 1
Except
Select to_there, from_here
From edges
Where from_here = 1
) As Z
select count(*) from (
select to_there from edges where from_here = 1
union
select from_here from edges where to_there = 1
) as whatever
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With