基于优先级标签的交友APP用户匹配SQL查询及优化问询

需求概述 #

这款交友APP的标签规则：

• 白标签：用户用来描述自身属性
• 绿标签：用户期望匹配对象具备的属性，数值越小优先级越高
• 红标签：用户绝对排斥的匹配对象属性

用户情况：

• A：素食主义者，喜欢狗，讨厌猫
• B：素食主义者，喜欢狗
• C：和B属性相同，但把「喜欢狗」的优先级放在「素食主义者」之上
• D：素食主义者，喜欢狗和猫

目标：编写SQL查询返回A的最佳匹配（预期结果：B、C），并探讨如何利用对方的绿/红标签优化匹配精度。

数据库结构与初始化 #

CREATE TABLE users (
  id serial PRIMARY KEY,
  name varchar(255)
);

CREATE TABLE flags (
  id serial PRIMARY KEY,
  name varchar(255)
);

CREATE TABLE userflags (
  id serial PRIMARY KEY,
  user_id integer REFERENCES users (id),
  color varchar(255),
  flag_id integer REFERENCES flags (id),
  priority integer
);

INSERT INTO users (name) VALUES ('A'),('B'),('C'),('D');

INSERT INTO flags (name) VALUES 
('Vegetarian'),('Vegan'),('Dog owner'),('Cat owner');

INSERT INTO userflags (user_id, color, flag_id, priority) VALUES 
 (1,'white',1,1)
,(1,'white',3,2)
,(1,'green',1,1)
,(1,'green',3,2)
,(1,'red',  4,1)

,(2,'white',1,1)
,(2,'white',3,2)
,(2,'green',1,1)
,(2,'green',3,2)

,(3,'white',1,2)
,(3,'white',3,1)
,(3,'green',1,1)
,(3,'red',  3,1)

,(4,'white',1,1)
,(4,'white',3,2)
,(4,'white',4,3);

实现A的最佳匹配查询 #

核心思路 #

1. 先筛选A的红标签，直接排除带有这些属性的用户（比如带「猫主人」标签的D）
2. 提取A的绿标签，按优先级计算匹配得分：优先级越高（数值越小），权重占比越大，用(最高优先级值 - 当前优先级 + 1)计算单标签得分，确保高优先级标签贡献更多分数
3. 仅保留完全匹配A所有绿标签的用户，最后按得分降序、名称升序排序

SQL代码 #

WITH a_red_flags AS (
  SELECT flag_id FROM userflags WHERE user_id = 1 AND color = 'red'
),
a_green_flags AS (
  SELECT flag_id, priority FROM userflags WHERE user_id = 1 AND color = 'green'
),
max_priority AS (
  SELECT MAX(priority) AS max_p FROM a_green_flags
)
SELECT 
  u.name,
  SUM((mp.max_p - ag.priority + 1)) AS match_score
FROM users u
JOIN userflags uf ON u.id = uf.user_id AND uf.color = 'white'
JOIN a_green_flags ag ON uf.flag_id = ag.flag_id
CROSS JOIN max_priority mp
WHERE u.id != 1
  AND uf.flag_id NOT IN (SELECT flag_id FROM a_red_flags)
GROUP BY u.name
HAVING COUNT(DISTINCT uf.flag_id) = (SELECT COUNT(*) FROM a_green_flags)
ORDER BY match_score DESC, u.name;

结果说明 #

执行后返回结果符合预期：

B和C都完全匹配A的两个绿标签，得分相同，因此按名称排序得到[B, C]。

利用对方绿/红标签优化匹配结果 #

上面的查询仅单向考虑A的需求，实际场景中需要结合双向匹配逻辑，让匹配更精准：

优化方向 #

1. 排除被对方排斥的情况：如果A的白标签命中对方的红标签，对方会排斥A，这类用户应从匹配列表中移除（比如C的红标签是「喜欢狗」，而A的白标签包含该属性，实际C不会匹配A）
2. 匹配对方的绿标签需求：计算A的白标签满足对方绿标签的得分，结合A自身的匹配得分得到双向总分，优先级更高的双向匹配用户会排在前面

优化后SQL示例 #

WITH user_a AS (
  SELECT id FROM users WHERE name = 'A'
),
a_white_flags AS (
  SELECT flag_id FROM userflags uf JOIN user_a ua ON uf.user_id = ua.id WHERE uf.color = 'white'
),
a_green_flags AS (
  SELECT flag_id, priority FROM userflags uf JOIN user_a ua ON uf.user_id = ua.id WHERE uf.color = 'green'
),
a_red_flags AS (
  SELECT flag_id FROM userflags uf JOIN user_a ua ON uf.user_id = ua.id WHERE uf.color = 'red'
),
max_a_priority AS (
  SELECT MAX(priority) AS max_p FROM a_green_flags
)
SELECT 
  u.name,
  SUM((map.max_p - ag.priority + 1)) AS a_match_score,
  COALESCE(SUM((og.max_p - og.priority + 1)), 0) AS other_match_score,
  SUM((map.max_p - ag.priority + 1)) + COALESCE(SUM((og.max_p - og.priority + 1)), 0) AS total_score
FROM users u
JOIN userflags uf ON u.id = uf.user_id AND uf.color = 'white'
JOIN a_green_flags ag ON uf.flag_id = ag.flag_id
CROSS JOIN max_a_priority map
LEFT JOIN (
  SELECT uf.user_id, uf.flag_id, uf.priority, MAX(uf.priority) OVER (PARTITION BY uf.user_id) AS max_p
  FROM userflags uf
  WHERE uf.color = 'green'
) og ON u.id = og.user_id AND og.flag_id IN (SELECT flag_id FROM a_white_flags)
WHERE u.id != (SELECT id FROM user_a)
  AND uf.flag_id NOT IN (SELECT flag_id FROM a_red_flags)
  AND NOT EXISTS (
    SELECT 1 FROM userflags uf_red 
    WHERE uf_red.user_id = u.id AND uf_red.color = 'red' 
    AND uf_red.flag_id IN (SELECT flag_id FROM a_white_flags)
  )
GROUP BY u.name
HAVING COUNT(DISTINCT uf.flag_id) = (SELECT COUNT(*) FROM a_green_flags)
ORDER BY total_score DESC, u.name;

优化结果说明 #

执行后C会被排除（因为C排斥喜欢狗的用户，而A正好符合），最终仅返回B，这更符合实际双向匹配的逻辑，避免了单向匹配带来的无效结果。

内容的提问来源于stack exchange，提问作者wintermeyer