I am trying to write a query that counts the number of bidirectional (strong) connections between users who represent nodes in a graph.
To test the request, I created the following example
which is stored in the month_connections_test table:
calling_party, called_party, link_strength z1 z2 1,0000000 z1 z3 1,0000000 z3 z1 1,0000000 z1 z4 1,0000000 z1 z5 1,0000000 z5 z1 1,0000000 z2 z4 1,0000000 z2 z5 1,0000000 z5 z2 1,0000000 z2 z7 1,0000000 z7 z2 1,0000000 z4 z7 1,0000000 z7 z4 1,0000000 z2 z1 1,0000000
The following query returns 2 for strong bonds between z1 and z2 instead of 1:
SELECT user1, user2, 0 AS calling_calling, 0 AS calling_called, 0 AS called_calling, 0 AS called_called, COUNT(*) AS both_directions FROM (SELECT monthly_connections_test.calling_party AS user1, monthly_connections_test_1.calling_party AS user2 FROM monthly_connections_test INNER JOIN monthly_connections_test AS monthly_connections_test_1 ON monthly_connections_test.called_party = monthly_connections_test_1.called_party AND monthly_connections_test.calling_party < monthly_connections_test_1.calling_party) t1 INNER JOIN monthly_connections_test AS monthly_connections_test_2 ON t1.user2 = monthly_connections_test_2.called_party AND t1.user2 < monthly_connections_test_2.calling_party GROUP BY t1.user1, t1.user2
The following are the results:
z1 z2 0 0 0 0 2 z2 z3 0 0 0 0 3 z2 z4 0 0 0 0 1 z1 z5 0 0 0 0 3 z2 z5 0 0 0 0 3 z3 z5 0 0 0 0 2 z1 z7 0 0 0 0 4 z2 z7 0 0 0 0 1 z5 z7 0 0 0 0 1
Does anyone know how to modify the query to return the number of common neighbors that are connected in both directions (in this example, the correct value for z1, z2 is 1, since z5 is connected to both z1 and z2 in both directions)?
The problem is what I think about
INNER JOIN monthly_connections_test AS monthly_connections_test_2 ON t1.user2 = monthly_connections_test_2.called_party AND t1.user2 < monthly_connections_test_2.calling_party
The correct result should be as follows:
z1 z2 0 0 0 0 1 z2 z3 0 0 0 0 1 z2 z4 0 0 0 0 1 z1 z5 0 0 0 0 1 z2 z5 0 0 0 0 1 z3 z5 0 0 0 0 1 z1 z7 0 0 0 0 1 z2 z7 0 0 0 0 0 z5 z7 0 0 0 0 1
The condition of the connection should be formulated in such a way that each connection is counted only once (previously included connections should be emitted at this point), but did not determine a solution.
PS Since the original table consists of 24M records, the query must be written in such a way that it returns results at an acceptable point in time. Trying to write a query with multiple selections first took too long to complete.