PostgreSQL中基于语言技能匹配顾问与项目的SQL查询实现

我需要在PostgreSQL中编写SQL查询，根据语言技能将顾问（Consultant）与项目（Project）匹配。比如一个位于柏林的项目，要求德语水平至少3级，要返回符合要求的顾问。

目前已有匹配城市的查询：

SELECT
    consultant_profiles.id,
    CASE WHEN (consultant_profiles.city = projects.city)
        THEN 0.5
        ELSE 0 END AS city_matching

FROM
    consultant_profiles

LEFT OUTER JOIN
    projects ON projects.id = 1; 

该查询返回结果：

id  city_matching
1   0.5
2   0

现在需要更新查询，添加语言技能匹配信息，得到如下格式的结果：

id  city_matching language_skills_matching
1   0.5           1      
2   0             0

数据库表结构及测试数据：

CREATE TABLE public.consultant_profiles (
  id bigint NOT NULL,
  name character varying,
  city character varying
);

CREATE TABLE public.projects (
  id bigint NOT NULL,
  name character varying,
  city character varying
);

CREATE TABLE public.language_skills (
  id bigint NOT NULL,
  language character varying,
  level integer,
  owner_type character varying,
  owner_id integer
);

INSERT INTO consultant_profiles (id, name, city) VALUES (1, '精通德语和英语的顾问', 'Berlin');
INSERT INTO consultant_profiles (id, name, city) VALUES (2, '精通法语的顾问', 'Warsaw');

INSERT INTO projects (id, name, city) VALUES (1, '要求德语水平至少3级的项目',  'Berlin');
INSERT INTO projects (id, name, city) VALUES (2, '要求英语水平至少3级的项目', 'Berlin');

INSERT INTO language_skills (id, language, level, owner_id, owner_type) VALUES (1, 'German',  4, 1, 'ConsultantProfile');
INSERT INTO language_skills (id, language, level, owner_id, owner_type) VALUES (2, 'English', 4, 1, 'ConsultantProfile');

INSERT INTO language_skills (id, language, level, owner_id, owner_type) VALUES (4, 'German',  3, 1, 'Project');
INSERT INTO language_skills (id, language, level, owner_id, owner_type) VALUES (5, 'English', 3, 2, 'Project');

可以实现这个需求，核心是通过关联language_skills表，检查顾问的语言水平是否满足对应项目的要求。以下是完整的查询语句：

SELECT
    cp.id,
    CASE WHEN cp.city = p.city THEN 0.5 ELSE 0 END AS city_matching,
    -- 判断是否满足项目的所有语言要求
    CASE 
        WHEN EXISTS (
            SELECT 1
            FROM language_skills proj_ls
            WHERE proj_ls.owner_type = 'Project' 
              AND proj_ls.owner_id = p.id
              AND NOT EXISTS (
                  SELECT 1
                  FROM language_skills cons_ls
                  WHERE cons_ls.owner_type = 'ConsultantProfile'
                    AND cons_ls.owner_id = cp.id
                    AND cons_ls.language = proj_ls.language
                    AND cons_ls.level >= proj_ls.level
              )
        ) THEN 0
        ELSE 1
    END AS language_skills_matching
FROM
    consultant_profiles cp
LEFT OUTER JOIN
    projects p ON p.id = 1;

查询解释： #

• 城市匹配：保留原有逻辑，判断顾问城市与项目城市是否一致，返回0.5或0。
• 语言技能匹配：
  1. 先获取目标项目（这里是id=1的项目）的所有语言要求。
  2. 检查顾问是否满足每一项语言要求：如果存在任何一项项目要求的语言，顾问没有达到对应等级，则返回0；否则返回1。

执行上述查询后，得到的结果如下：

id  city_matching language_skills_matching
1   0.5           1
2   0             0

内容的提问来源于stack exchange，提问作者Mateusz Urbański