SQL query to transform graph path with flux into adjacent node flux-CodePudding

Given a table describing paths with flux in a graph

node1 | node2 | node3 | flux
A     | B     | C     | 15
E     | B     | D     | 10
A     | B     | D     | 5

I want to query the sum of flux for adjacent nodes, ie

out_node | in_node | flux
A        | B       | 20 (15   5)
B        | C       | 15
E        | B       | 10
B        | D       | 15 (10   5)

Node that A ~> C would be 0 since we don't have a direct edge between A and C

What I thought:

We could do multiple queries grouping by adjacent nodes, eg

SELECT node1 as out_node,
       node2 as in_node,
       sum(flux)
FROM table,
GROUP BY out_node, in_node

SELECT node2 as out_node,
       node3 as in_node,
       sum(flux)
FROM table,
GROUP BY out_node, in_node

Then concatenate these two queries and do another GROUP BY

Is there any better solution for a high number of nodes in a path? Since for eg 1000 nodes we would need to do 999 queries

CodePudding user response：

You can do:

select node1, node2, sum(flux) as flux
from (
  select node1, node2, flux from t
  union all select node2, node3, flux from t
) x
group by node1, node2

Result:

 node1  node2  flux 
 ------ ------ ---- 
 A      B      20   
 B      C      15   
 B      D      15   
 E      B      10

See running example at DB Fiddle.