在SQL查询中将多行与多行匹配，并找到百分位数匹配？

Question

目前我正在建立一个招聘平台。雇主可以张贴一份工作并接受申请。雇主可以设定求职者必须符合的许多技能要求。求职者还可以增加他们所拥有的许多技能。

我试图找出每个jobseekers_skills中有多少匹配每个作业的employer_requirements，以便在视图中显示百分位数匹配。理想情况下，我希望基于skill_string查找jobseeker_skills表和employer_requirements表中存在的匹配。

以下是三个表中每个表的数据库安排：

申请：

id | job_string | jobseeker_string | employer_string | application_string | date_created

employer_requirements：

id | skill_name | requirement_level | skill_string | job_string | employer_string | date_created

jobseeker_skills：

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

首先，以下是两个问题：

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

Answer 2

MySQL给你提供了一个很好的方式来玩分组，如果和平均。你有没有和AVG玩过(如果.)？

假设您有两个有几个列的表。

类似这样的事情(对不起，sqlfiddle失业了)：

first_table：

id  category    element
1   number  two
2   number  three
3   number  four
4   number  five
5   number  eleven
6   fruit   banana
7   fruit   pineapple
8   fruit   pear
9   fruit   strawberry

second_table：

id  category    element
1   number  one
2   number  five
3   number  six
4   number  seven
5   number  three
6   fruit   apple
7   fruit   banana

1)您想知道第一个表中有多少元素可以在第二个表中找到：

    select count(*) as total
    from first_table t1 
    join second_table t2 
    on t1.element = t2.element

会回来

 total
 3

( 2)左联接时，你可能会得到有价值的信息：

    select 
        count(*) as total, 
        count(t2.element) as number_matching
    from first_table t1
    left join second_table t2
    on t1.element = t2.element

这将使用元素的总数和匹配元素的数量来登陆。除以，你有这个百分比。

  total    number_matching
  9        3

3)对于avg和if，我们可以直接得到0和1之间的比例：

    select
        AVG(IF(t2.element IS NULL, 0, 1)) as proportion_matching
    from first_table t1
    left join second_table t2
    on t1.element = t2.element

返回

proportion_matching
0.33333

4)按百分比排列，按你的召集.

    select
        ROUND(AVG(IF(t2.element IS NULL, 0, 1)) * 100, 1) as percent_matching
    from first_table t1
    left join second_table t2
    on t1.element = t2.element

然后你就会得到

percent_matching
33.3

5)您实际上可以将结果按类别分开。

    select
        t1.category,
        ROUND(AVG(IF(t2.element IS NULL, 0, 1)) * 100, 1) as percent_matching
    from first_table t1
    left join second_table t2
    on t1.element = t2.element
    group by t1.category

记住，这实际上是“表1中元素的百分比，可以在表2中找到”。

category  percent_matching
fruit     25.0
number    40.0

6)将此应用于应用程序和技能.你会按以下方式审查求职者的申请：

    SELECT
        a.job_string,
        ROUND(AVG(IF(jobseeker.skill_string IS NULL, 0, 1)) * 100, 1) as percent_matching
    FROM application a  
    JOIN employer_requirements er
    ON er.job_string = a.job_string
    LEFT JOIN jobseeker js
    ON a.jobseeker_string = js.jobseeker_string
    GROUP BY a.job_string

7)当然，如果你愿意的话，你可以在工作中过滤你的工作字符串。实际上，这里添加的与应用程序表的联接只是确保您只获得用户实际申请过的作业的结果。但是，如果您已经有了一个job_string，您可以通过：

    SELECT
        er.job_string,
        ROUND(AVG(IF(jobseeker.skill_string IS NULL, 0, 1)) * 100, 1) as percent_matching
    FROM        employer_requirements er    
    LEFT JOIN   jobseeker js
    ON          js.jobseeker_string = er.jobseeker_string
    WHERE       er.jobseeker_string = ?

7)我让你把它放到一个活动记录查询中(这不是我最了解的部分;)