力扣-Mysql-3278. 寻找数据科学家职位的候选人 II（中等）

2024-11-18 07:09:52

一、题目来源

3278. 寻找数据科学家职位的候选人 II - 力扣（LeetCode）

二、数据表结构

表：Candidates

+--------------+---------+ 
| Column Name  | Type    | 
+--------------+---------+ 
| candidate_id | int     | 
| skill        | varchar |
| proficiency  | int     |
+--------------+---------+
是这张表的主键（有不同值的列）。 
每一行包括 candidate_id 和技能，以及熟练程度（1-5）。

表：Projects

+--------------+---------+ 
| Column Name  | Type    | 
+--------------+---------+ 
| project_id   | int     | 
| skill        | varchar |
| importance   | int     |
+--------------+---------+
(project_id, skill) 是这张表的主键。
每一行包括 project_id，所需技能，以及项目的重要性（1-5）。

三、需求

Leetcode 正在为多个数据科学项目招聘人员。编写一个解决方案来根据以下条件为 每一个项目 找到 最佳候选人：

候选人必须拥有项目所需的所有技能。
为每个候选人-项目对计算如下的分数：
- 从 100 分 开始。
- 对于每一个技能，当 熟练程度 > 重要性加 10 分。
- 对于每一个技能，当 熟练程度 < 重要性减 5 分。

仅包括每个项目的最佳候选人（最高分）。如果相同，选择有更小 candidate_id 的候选人。如果一个项目 没有适合的候选人，不要返回 那个项目。

返回结果表以 project_id 升序排序。

四、示例数据

输入：

Candidates 表：

+--------------+-----------+-------------+
| candidate_id | skill     | proficiency |
+--------------+-----------+-------------+
| 101          | Python    | 5           |
| 101          | Tableau   | 3           |
| 101          | PostgreSQL| 4           |
| 101          | TensorFlow| 2           |
| 102          | Python    | 4           |
| 102          | Tableau   | 5           |
| 102          | PostgreSQL| 4           |
| 102          | R         | 4           |
| 103          | Python    | 3           |
| 103          | Tableau   | 5           |
| 103          | PostgreSQL| 5           |
| 103          | Spark     | 4           |
+--------------+-----------+-------------+

Projects 表：

+-------------+-----------+------------+
| project_id  | skill     | importance |
+-------------+-----------+------------+
| 501         | Python    | 4          |
| 501         | Tableau   | 3          |
| 501         | PostgreSQL| 5          |
| 502         | Python    | 3          |
| 502         | Tableau   | 4          |
| 502         | R         | 2          |
+-------------+-----------+------------+

输出：

+-------------+--------------+-------+
| project_id  | candidate_id | score |
+-------------+--------------+-------+
| 501         | 101          | 105   |
| 502         | 102          | 130   |
+-------------+--------------+-------+

解释：

对于项目 501, 候选人 101 有最高的 105 分。所有其他的候选人有相同的分数，但候选人 101 有比他们更小的 candidate_id。
对于项目 502，候选人 102 有最高的 130 分。

输出表以 project_id 升序排序。

五、分析

1.文字分析

本题需求为寻找数据科学家职位的候选人：

第一步：首先获取Projects表中的所有字段以及使用count()函数计算每个项目的技能数；

第二步：将第一步结果表起别名为t0，与candidates表关联查询，获取candidate_id 、project_id，以及总评分，并对数据进行筛选，让每个人的技能数> 第一步结果中技能数的最大值，起别名为t1；

第三步：最终对t1表中数据进行排序，根据项目id：project_id分组，对score的降序、candidate_id升序进行排名，获取排名为1的相关数据即为所求。

2.图解

六、代码实现

with t0 AS (
        SELECT
            *,
            COUNT(*) OVER (PARTITION BY project_id) AS cnt_project
        FROM projects
),t1 AS (
    SELECT
        project_id,
        candidate_id,
        100 + SUM(
                CASE
                    WHEN proficiency > importance THEN 10
                    WHEN proficiency < importance THEN -5
                    ELSE 0
                    END
              ) AS score
    FROM
        candidates a
            JOIN t0 t ON a.skill = t.skill
    GROUP BY project_id, candidate_id
    HAVING count(*) >= max(cnt_project)
),t2 AS (
    SELECT
        project_id,
        candidate_id,
        score,
        row_number() OVER (PARTITION BY project_id ORDER BY score desc ,candidate_id) as rn
    FROM t1
)
SELECT
    project_id, candidate_id, score
from t2
where rn = 1
ORDER BY project_id;

七、总结

本题需求为寻找数据科学家职位的候选人，本题需要注意的点有：

候选人必须拥有项目的所有技能，因此要对数据进行筛选；

计算每个人的评分时要注意，题目要求分数是从100分开始的，因此要先加 100 再使用 case when 条件判断以及 sum() 函数进行总分数的获取；

最终根据总分数以及候选人id求得每个项目的候选排名，筛选出每个项目排名为第一的候选人即为最佳候选人。

码农公寓