Relationships can be created from existing (relational) data. For example, customers who have purchased the same product or watched the same movie have a form of relationship, even if it is not a real social relationship. With this information, we can find a link between these people. This link can even be weighted, depending on the number of products they have bought.
Let's consider a case where we have the following simplified SQL schema with three tables:
- Users with a column ID
- Products with a column ID
- Orders with columns user_id and product_id
To find relationships between users who have purchased the same products, we could use the following query:
SELECT
u1.id,
u2.id,
count(*) as weight
FROM users u1
JOIN users u2 ON u1.id <> u2.id
JOIN orders o1 ON o1.user_id = u1.id
JOIN orders o2 ON o2.user_id = u2.id AND o1.product_id = o2.product_id
The result of this query can be saved into a CSV file and then imported...
