mysql - MATCH 多行与 SQL 查询中的多行并找到百分位数匹配？

Question

目前我正在搭建一个招聘平台。雇主可以发布工作并接收申请。雇主可以设置求职者必须匹配的许多技能要求。求职者还可以添加他们拥有的许多技能。

我想知道的是每个 jobseekers_skills 有多少匹配 employer_requirements对于每个作业，以便在 View 中显示百分比匹配。理想情况下，我想根据 skill_string 找到匹配项存在于 jobseeker_skills 中表和employer_requirements table

以下是 3 个表中每一个的数据库安排:

应用:
id | job_string | jobseeker_string | employer_string | application_string | date_created
雇主要求:
id | skill_name | requirement_level | skill_string | job_string | employer_string | date_created
求职者_技能:
id | skill_name | level | jobseeker_string | skill_string | string | date_created
我有以下代码可以获取所有 applications基于传递的'$job_str'。下面的代码只是一个简单的获取，但不确定从哪里开始。

function skills_match($job_str){

    $this->db->select('*')
             ->from('applications')
             ->where('job_string', $job_str)
             ->join('users', 'users.string = applications.jobseeker_string', 'left');

    $applications = $this->db->get();

    return $applications;

}

应用程序表 - 示例数据:
+--------+------------------+------------------+------------------+
| id | job_string | jobseeker_string | employer_string |
+--------+------------------+------------------+------------------+
| 1 | vs71FVTBb12DdGlf | uMIsuDJaBuDmo8iq | biQxyPekn6iayIgm |
| 2 | vs71FVTBb12DdGlf | x7phHsVnwJ1K1yHy | biQxyPekn6iayIgm |
| 3 | vs71FVTBb12DdGlf | Fm1TIJLxz6Xg6QPk | biQxyPekn6iayIgm |
+--------+------------------+------+-----+---------+-------+------+

雇主要求 - 样本数据:

+--------+------------------+-------------+------------------+------------------+
| id | job_string | skill_name | skill_string | employer_string |
+--------+------------------+-------------+------------------+-----------------+|
| 1 | vs71FVTBb12DdGlf |PHP | 9Y8XeCWqJXzkZ5dD | biQxyPekn6iayIgm |
| 2 | vs71FVTBb12DdGlf |JavaScript | O6es19t5CgcRHvct | biQxyPekn6iayIgm |
| 3 | vs71FVTBb12DdGlf |HTML | wx4evsXC62BWiN7p | biQxyPekn6iayIgm |
| 4 | vs71FVTBb12DdGlf |Python | jx15rH1vrGLmsVmq | biQxyPekn6iayIgm |
| 5 | vs71FVTBb12DdGlf |SQL | EksP7mEip0Hs4zKd | biQxyPekn6iayIgm |
| 6 | vs71FVTBb12DdGlf |LESS | fj40m4hkiuDGtbzr | biQxyPekn6iayIgm |
+--------+------------------+-------------+------+-----+---------+-------+------+
求职者技能 - 示例数据:

+--------+------------------+------------------+------------------+
| id | jobseeker_string | skill_name | skill_string |
+--------+------------------+------------------+------------------+
| 1 | uMIsuDJaBuDmo8iq | PHP | 9Y8XeCWqJXzkZ5dD |
| 2 | uMIsuDJaBuDmo8iq | Backbone | 4VIiAxZoL1VbPnTa |
| 3 | x7phHsVnwJ1K1yHy | LESS | fj40m4hkiuDGtbzr |
| 2 | x7phHsVnwJ1K1yHy | Ruby | gTZg4fwYuzMMFcBw |
| 3 | x7phHsVnwJ1K1yHy | SQL | EksP7mEip0Hs4zKd |
| 1 | Fm1TIJLxz6Xg6QPk | PHP | 9Y8XeCWqJXzkZ5dD |
| 2 | Fm1TIJLxz6Xg6QPk | Python | jx15rH1vrGLmsVmq |
| 3 | Fm1TIJLxz6Xg6QPk | HTML | wx4evsXC62BWiN7p |
| 3 | Fm1TIJLxz6Xg6QPk | Git | aR9B9ns1sHlGrzFw |
+--------+------------------+------+-----+---------+-------+------+

基于上述，这应该输出百分比或否。匹配技能:

应用程序 - 以下是每个应用程序匹配技能的数量/百分比:
uMIsuDJaBuDmo8iq - 1/6 (16.666%)
x7phHsVnwJ1K1yHy - 2/6 (33.333%)
Fm1TIJLxz6Xg6QPk - 3/6 (50%)

有任何问题请开火。提前感谢您的帮助。

Answer 1

首先，这是2个问题:

哪些申请人最符合我的业务

哪个雇主最适合我的技能。

这两个问题可能看起来相同，但实际上并非如此。

第一个问题:
我想要所有符合我的任何要求的申请人，按我的要求数量排序。首先我得到所有匹配项:

select *
from Requirements r 
inner join Jobseeker j
on r.skill_string = j.r.skill_string 
where job_string = 'vs71FVTBb12DdGlf';

然后我将它们分组，计算它们等:

select 
  jobseeker_string, 
  count(1) / (select count(1) from Requirements where job_string = 'vs71FVTBb12DdGlf') as match_percentage
from Requirements r 
inner join Jobseeker j
on r.skill_string = j.r.skill_string 
where job_string = 'vs71FVTBb12DdGlf'
group by jobseeker_string;

第二个问题:有点困难，因为申请人可能想知道他/她是否符合一定比例的工作技能，以及他自己的技能(这也可能适用于第一个问题)。查询如下:

select 
  job_string, 
  count(1) / (select count(1) from Requirements where jobseeker_string  = 'uMIsuDJaBuDmo8iq') as my_match,
  count(1) / (select count(1) from Requirements where job_string = r.job_string) as job_match
from Requirements r 
inner join Jobseeker j
on r.skill_string = j.r.skill_string 
where jobseeker_string = 'uMIsuDJaBuDmo8iq'
group by job_string;

请注意:查询是我脑子里写出来的，可能包含一些拼写错误

如果你想订购，你可以这样做:

select * from
  ([[insert the above query here]]) t
order by field.

合并:

select 
  job_string, 
  jobseeker_string
  count(1) / (select count(1) from Requirements where jobseeker_string  = r.jobseeker_string ) as seeker_match,
  count(1) / (select count(1) from Requirements where job_string = r.job_string) as job_match
from Requirements r 
inner join Jobseeker j
on r.skill_string = j.r.skill_string 
group by job_string, jobseeker_string;

应用

select * from 
  (select 
    job_string, 
    jobseeker_string
    count(1) / (select count(1) from Requirements where jobseeker_string  = r.jobseeker_string ) as seeker_match,
    count(1) / (select count(1) from Requirements where job_string = r.job_string) as job_match
  from Requirements r 
  inner join Jobseeker j
  on r.skill_string = j.r.skill_string 
  group by job_string, jobseeker_string) t
inner join applications a
on t.job_string = a.job_string and t.jobseeker_string = a.t.jobseeker_string