PostgreSQL全文检索：如何按职位志愿优先级对匹配行排序

完全可以通过给不同志愿设置梯度权重实现预期排序，调整后的SQL如下：

WITH search_query AS (
    -- 统一声明搜索关键词，避免重复定义
    SELECT PHRASETO_TSQUERY('english', 'barista') AS query
)
SELECT 
    p.person_id,
    -- 各志愿匹配得分乘以对应权重，第一/第二/第三权重分别为3/2/1
    TS_RANK_CD(TO_TSVECTOR('english', a.job_position_title), q.query, 1) * 3 AS first_score,
    TS_RANK_CD(TO_TSVECTOR('english', b.job_position_title), q.query, 1) * 2 AS second_score,
    TS_RANK_CD(TO_TSVECTOR('english', c.job_position_title), q.query, 1) * 1 AS third_score,
    -- 汇总总得分用于排序
    (TS_RANK_CD(TO_TSVECTOR('english', a.job_position_title), q.query, 1) * 3
    + TS_RANK_CD(TO_TSVECTOR('english', b.job_position_title), q.query, 1) * 2
    + TS_RANK_CD(TO_TSVECTOR('english', c.job_position_title), q.query, 1) * 1) AS total_score
FROM people p
CROSS JOIN search_query q
LEFT JOIN job_positions a ON p.first_position_id = a.job_position_id
LEFT JOIN job_positions b ON p.second_position_id = b.job_position_id
LEFT JOIN job_positions c ON p.third_position_id = c.job_position_id
WHERE 
    TO_TSVECTOR('english', a.job_position_title) @@ q.query
    OR TO_TSVECTOR('english', b.job_position_title) @@ q.query
    OR TO_TSVECTOR('english', c.job_position_title) @@ q.query
ORDER BY total_score DESC;

简化版本（仅按志愿优先级排序，不关联全文匹配相关度） #

如果不需要考虑关键词和职位名称的匹配相关度，只需要严格按志愿优先级排序，可以使用更简单的判断逻辑：

WITH search_query AS (
    SELECT PHRASETO_TSQUERY('english', 'barista') AS query
)
SELECT 
    p.person_id,
    (CASE WHEN TO_TSVECTOR('english', a.job_position_title) @@ q.query THEN 3 ELSE 0 END
    + CASE WHEN TO_TSVECTOR('english', b.job_position_title) @@ q.query THEN 2 ELSE 0 END
    + CASE WHEN TO_TSVECTOR('english', c.job_position_title) @@ q.query THEN 1 ELSE 0 END) AS priority_score
FROM people p
CROSS JOIN search_query q
LEFT JOIN job_positions a ON p.first_position_id = a.job_position_id
LEFT JOIN job_positions b ON p.second_position_id = b.job_position_id
LEFT JOIN job_positions c ON p.third_position_id = c.job_position_id
WHERE 
    TO_TSVECTOR('english', a.job_position_title) @@ q.query
    OR TO_TSVECTOR('english', b.job_position_title) @@ q.query
    OR TO_TSVECTOR('english', c.job_position_title) @@ q.query
ORDER BY priority_score DESC;

• 权重设置：第一、第二、第三志愿分别设置3、2、1的权重，保证第一志愿匹配的用户总得分一定高于仅第二、第三志愿匹配的用户，符合优先级要求。以你举的搜索barista的场景为例，person_id为2的用户第一志愿匹配得3分，person_id为1的用户第二志愿匹配得2分，最终排序2在前1在后，完全符合预期。
• 优化了原SQL中重复声明三次相同搜索query的冗余逻辑，统一通过CTE定义搜索条件，修改关键词时只需要改一处即可。

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