人力资源匹配数据表设计

最近一直在看《SQL puzzles and answers》，其中EMPLOYMENT AGENCY一文让我眼前一亮。在这里把我对此文的概括与理解写下来与大家分享（代码在SQL Server 2005中测试通过）。

需求描述

对于人力资源网站或职业中介，会保存大量求职者的简历信息。通过让求职者勾选技能关键字或对简历进行分词都可以得到求职者所掌握的技能列表，而且可提供的技能关键字很多（书中提到的系统中的关键字来自于某职业字典，超过250000个）。

求职者的技能列表如下：

create table CandidateSkills
(
        candidate_id int not null,
        skill_code char(15) not null,
        primary key(candidate_id, skill_code)
);

insert into
        CandidateSkills
select 100, 'accounting'
union all
select 100, 'inventory'
union all
select 100, 'manufacturing'
union all
select 200, 'accounting'
union all
select 200, 'inventory'
union all
select 300, 'manufacturing'
union all
select 400, 'inventory'
union all
select 400, 'manufacturing'
union all
select 500, 'accounting'
union all
select 500, 'manufacturing';

另一方面，人力资源网站还会存储企业开放出来的职位信息，这些职位对于技能有不同的要求。例如：某职位要求技能为('inventory’ and ‘manufacturing’) or 'accounting’。现在的问题是:

1. 如何设计职位技能要求表（JobOrders）？

2. 如何把求职者技能（CandidateSkills）与职位技能要求（JobOrders）进行匹配？

职位要求表设计

首先我们来解决JobOrders表设计的问题。要表达技能间的逻辑关系，我们可以引入了skill_group列。JobOrders表的脚本如下：

create table JobOrders
(
        job_id int not null,
        skill_group int not null,
        skill_code char(15) not null,
        primary key(job_id, skill_group, skill_code)
);

假设有如下职位需求：

Job 1 = (‘inventory’ and ‘manufacturing’) or ‘accounting’

Job 2 = (‘inventory’ and ‘manufacturing’) or (‘accounting’ and ‘manufacturing’)

Job 3 = ‘manufacturing’

Job 4 = (‘inventory’ and ‘manufacturing’ and ‘accounting’)

插入上述需求的脚本如下：

insert into
        JobOrders
select 1, 1, 'inventory'
union all
select 1, 1, 'manufacturing'
union all
select 1, 2, 'accounting'
union all
select 2, 1, 'inventory'
union all
select 2, 1, 'manufacturing'
union all
select 2, 2, 'accounting'
union all
select 2, 2, 'manufacturing'
union all
select 3, 1, 'manufacturing'
union all
select 4, 1, 'inventory'
union all
select 4, 1, 'manufacturing'
union all
select 4, 1, 'accounting';

JobOrders中数据的规则如下：

• 对于相同job_id，相同skill_group的skill_code之间为‘与’关系。对于相同job_id，不同skill_group的skill_code之间为‘或’关系。
• 除此之外，还有一条约束为‘与’关系优先‘或’关系。

对于'manufacturing’ and (‘inventory’ or ‘accounting’)逻辑，可以转化为('manufacturing’ and ‘inventory’) or (‘manufacturing’ and ‘accounting’)。

职位要求与求职者的匹配

现在我们来解决如何匹配职位要求和求职者技能。

• 解决方案1

select distinct
        J1.job_id,
        C1.candidate_id
from
        JobOrders as J1
        inner join
        CandidateSkills as C1
        on
                J1.skill_code = C1.skill_code
group by
        C1.candidate_id,
        J1.skill_group,
        J1.job_id
having
        count(*) >= (select count(*) from JobOrders as J2 where J1.skill_group = J2.skill_group and J1.job_id = J2.job_id);

• 解决方案2

With JobRequirementCnt as
(
        select
                job_id,
                skill_group,
                count(*) grp_cnt
        from
                JobOrders
        group by
                job_id,
                skill_group
),
CandidateSkillGrpCnt as
(
        select
                CS.candidate_id,
                JO.job_id,
                JO.skill_group,
                count(*) grp_cnt
        from
                CandidateSkills CS
                inner join
                JobOrders JO
                on
                        CS.skill_code = JO.skill_code
        group by
                CS.candidate_id,
                JO.job_id,
                JO.skill_group
)
select distinct
        CandidateSkillGrpCnt.candidate_id,
        CandidateSkillGrpCnt.job_id
from
        JobRequirementCnt
        inner join
        CandidateSkillGrpCnt
        on
                JobRequirementCnt.job_id = CandidateSkillGrpCnt.job_id
                and
                JobRequirementCnt.skill_group = CandidateSkillGrpCnt.skill_group
                and
                JobRequirementCnt.grp_cnt = CandidateSkillGrpCnt.grp_cnt;