第八章 使用 JSON + SQL 模拟图模型 - 第三节 实战：社交网络中好友推荐算法模拟

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

-- 插入新节点
INSERT INTO graph_nodes (label, properties) VALUES
('Person', '{"name": "David"}'),  -- id: 5
('Person', '{"name": "Emily"}');  -- id: 6

-- 更新边关系
-- Alice 是 Bob 和 Emily 的朋友
UPDATE graph_nodes SET edges = '[
    {"type": "FRIENDS_WITH", "target_id": 2},
    {"type": "FRIENDS_WITH", "target_id": 6}
]' WHERE properties->>'name' = 'Alice';

-- Bob 是 Alice 和 Charlie 的朋友
UPDATE graph_nodes SET edges = '[
    {"type": "FRIENDS_WITH", "target_id": 1},
    {"type": "FRIENDS_WITH", "target_id": 3}
]' WHERE properties->>'name' = 'Bob';

-- Charlie 是 Bob 和 David 的朋友
UPDATE graph_nodes SET edges = '[
    {"type": "FRIENDS_WITH", "target_id": 2},
    {"type": "FRIENDS_WITH", "target_id": 5}
]' WHERE properties->>'name' = 'Charlie';

-- David 是 Charlie 和 Emily 的朋友
UPDATE graph_nodes SET edges = '[
    {"type": "FRIENDS_WITH", "target_id": 3},
    {"type": "FRIENDS_WITH", "target_id": 6}
]' WHERE properties->>'name' = 'David';

-- Emily 是 Alice 和 David 的朋友
UPDATE graph_nodes SET edges = '[
    {"type": "FRIENDS_WITH", "target_id": 1},
    {"type": "FRIENDS_WITH", "target_id": 5}
]' WHERE properties->>'name' = 'Emily';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

WITH
-- 步骤 1: 定义目标用户
target_user AS (
    SELECT id FROM graph_nodes WHERE properties->>'name' = 'Alice'
),
-- 步骤 2: 找到目标用户的所有直接朋友 (一度关系)
friends_of_target AS (
    SELECT (edge.value->>'target_id')::BIGINT AS friend_id
    FROM graph_nodes, jsonb_array_elements(edges) AS edge, target_user
    WHERE id = target_user.id AND edge.value->>'type' = 'FRIENDS_WITH'
),
-- 步骤 3: 找到“朋友的朋友” (二度关系)
friends_of_friends AS (
    SELECT
        (edge.value->>'target_id')::BIGINT AS fof_id,
        f.friend_id AS mutual_friend_id -- 记录下这位共同好友
    FROM graph_nodes n
    JOIN friends_of_target f ON n.id = f.friend_id
    , jsonb_array_elements(n.edges) AS edge
    WHERE edge.value->>'type' = 'FRIENDS_WITH'
),
-- 步骤 4: 筛选并计算共同好友数量
recommendations AS (
    SELECT
        fof.fof_id,
        count(DISTINCT fof.mutual_friend_id) AS mutual_friends_count
    FROM friends_of_friends fof
    WHERE
        -- 排除目标用户自己
        fof.fof_id != (SELECT id FROM target_user)
        -- 排除已经是朋友的人
        AND fof.fof_id NOT IN (SELECT friend_id FROM friends_of_target)
    GROUP BY fof.fof_id
)
-- 最终步骤: 输出推荐列表，并附上姓名
SELECT
    (SELECT properties->>'name' FROM graph_nodes WHERE id = r.fof_id) AS recommended_friend,
    r.mutual_friends_count
FROM recommendations r
ORDER BY r.mutual_friends_count DESC;

1
2
3
4

 recommended_friend | mutual_friends_count
--------------------+----------------------
 Charlie            |                    1
 David              |                    1