RaqForum 41 No.
23 View  • 
     ...

SQL: Identify associated member groups without common points of association

 

The PostgreSQL database uses a table to record the direct relationship between two IDs, such as [1,3] and [3,6]. In addition to direct relationships, two points can also form a layer of indirect relationships through common association points, such as the common association point of [3,5] being 1. There may also be multiple layers of indirect associations, which are not common points of association, but rather chains of association.

user_id1

user_id2

1

3

1

5

2

4

2

5

3

5

4

5

6

7

8

9

3

6

Please find the IDs that are directly related but do not have a common point of association, namely [6,7], [8,9], [3,6].

SPL code:

1

=PostgreSQL.query("select * from tb")

2

=direct=A2.array@b()

3

=undir=direct.conj(~[1:].(direct.~ % ~).select(~.len()==2))

4

=direct.(~.sort())\undir.(~.sort())

A1: Query the database through JDBC.

A2: Combine the fields of each record into a small set, and then union the records to a large set, which is a set with directly associated IDs.

A3: Find a set of IDs with common association points. ~[1:] represents getting the next member of the current member until the last member. direct.~ % ~ performs XOR operation on the current member and each subsequent member. If there is a common association point, the result will be 2 IDs. If there is no common association point, there will be 4 IDs. Use the select function to filter out a small set with 2 IDs. The conj function is used to merge these small sets.

A4: Calculate the difference set between two large sets. Note that the two set members composed of points with different orders are different, so the same rules should be used to sort the interior of the small set.

Problem source: https://stackoverflow.com/questions/78393715/find-users-pairs-who-have-no-mutual-friends


 
 •  23 View  •