时间戳

首页 > 解决方案 > 使用 % 比较两个文本数组列并按匹配数排序

postgresql - 使用 % 比较两个文本数组列并按匹配数排序

问题描述

我有一个用户表，其中包含一个“技能”列，它是一个文本数组。给定一些输入数组，我想查找其技能百分比输入数组中的一个或多个条目的所有用户，并按匹配数排序（根据 pg_trgm 中的 % 运算符）。

例如，我有 Array['java', 'ruby', 'postgres'] 并且我希望拥有这些技能的用户按匹配数排序（在这种情况下最多为 3）。

我尝试unnest()使用inner join. 看起来我到了某个地方，但我仍然不知道如何捕获匹配数组条目的计数。关于查询结构可能是什么样子的任何想法？

编辑：详细信息：

这是我的programmers桌子的样子：

id |            skills             
----+-------------------------------
  1 | {javascript,rails,css}
  2 | {java,"ruby on rails",adobe}
  3 | {typescript,nodejs,expressjs}
  4 | {auth0,c++,redis}

哪里skills是一个文本数组。

这是我到目前为止所拥有的：

SELECT * FROM programmers, unnest(skills) skill_array(x)
    INNER JOIN unnest(Array['ruby', 'node']) search(y)
    ON skill_array.x % search.y;

输出以下内容：

id |            skills             |       x       |    y    
----+-------------------------------+---------------+---------
  2 | {java,"ruby on rails",adobe}  | ruby on rails | ruby
  3 | {typescript,nodejs,expressjs} | nodejs        | node
  3 | {typescript,nodejs,expressjs} | expressjs     | express

*假设 pg_trgm 已启用。

标签： postgresql

解决方案

对于用户技能和搜索技能之间的完全匹配，您可以这样进行：

  1. 您将搜索到的技能放在target_skills文本数组中
  2. 您从表user_table中过滤用户，其user_skills数组与target_skills数组至少有一个公共元素，使用&&运算符
  3. 对于每个选定的用户，使用unnest和选择常用技能INTERSECT，然后计算这些常用技能的数量
  4. 您按常用技能的数量排序结果DESC

在这个过程中，技能“ruby”的用户将被选择为目标技能“ruby”，而不是技能“ruby on rails”的用户。

这个过程可以实现如下：

SELECT u.user_id
     , u.user_skills
     , inter.skills
FROM user_table AS u
CROSS JOIN LATERAL
    (   SELECT array(   SELECT unnest(u.user_skills)
                        INTERSECT
                        SELECT unnest(target_skills)
                    ) AS skills
    ) AS inter
WHERE u.user_skills && target_skills
ORDER BY array_length(inter.skills, 1) DESC

或使用此变体：

SELECT u.user_id
     , u.user_skills
     , array_agg(t_skill) AS inter_skills
FROM user_table AS u
CROSS JOIN LATERAL unnest(target_skills) AS t_skill
WHERE u.user_skills && array[t_skill]
GROUP BY u.user_id, u.user_skills
ORDER BY array_length(inter_skills, 1) DESC

可以通过在user_table的user_skills列上创建 GIN 索引来加速此查询。

对于用户技能和目标技能的部分匹配（即目标技能“ruby”必须选择技能“ruby on rails”的用户），您需要使用模式匹配运算符LIKE或regular expression可以将它们与文本数组一起使用，因此您首先需要使用函数将user_skills文本数组转换为简单的文本array_to_string。查询变为：

SELECT u.user_id
     , u.user_skills
     , array_agg(t_skill) AS inter_skills
FROM user_table AS u
CROSS JOIN unnest(target_skills) AS t_skill
WHERE array_to_string(u.user_skills, ' ') ~ t_skill
GROUP BY u.user_id, u.user_skills
ORDER BY array_length(inter_skills, 1) DESC ;

然后，您可以通过创建以下GIN（或GiST）索引来加速查询：

DROP INDEX IF EXISTS user_skills ;
CREATE INDEX user_skills
  ON user_table
  USING gist (array_to_string(user_skills, ' ') gist_trgm_ops) ;    -- gin_trgm_ops and gist_trgm_ops indexes are compliant with the LIKE operator and the regular expressions

在任何情况下，如果出现输入错误或技能列表未标准化，以文本形式管理技能将永远失败。

推荐阅读