3278. 寻找数据科学家职位的候选人 II 🔒

题目描述

表：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 升序排序。

解法

方法一：等值连接 + 分组统计 + 窗口函数

我们可以将表 Candidates 和表 Projects 通过 skill 列进行等值连接，统计每个候选人在每个项目中匹配的技能数量、总分数，记录在表 S 中。

然后我们再次统计每个项目所需的技能数量，记录在表 T 中。

接着我们将表 S 和表 T 通过 project_id 列进行等值连接，筛选出匹配的技能数量等于所需技能数量的候选人，记录在表 P 中，并计算每个项目的候选人排名，字段为 rk。

最后我们筛选出每个项目的排名为 1 的候选人，即为最佳候选人。

MySQL

WITH
    S AS (
        SELECT
            candidate_id,
            project_id,
            COUNT(*) matched_skills,
            SUM(
                CASE
                    WHEN proficiency > importance THEN 10
                    WHEN proficiency < importance THEN -5
                    ELSE 0
                END
            ) + 100 AS score
        FROM
            Candidates
            JOIN Projects USING (skill)
        GROUP BY 1, 2
    ),
    T AS (
        SELECT project_id, COUNT(1) required_skills
        FROM Projects
        GROUP BY 1
    ),
    P AS (
        SELECT
            project_id,
            candidate_id,
            score,
            RANK() OVER (
                PARTITION BY project_id
                ORDER BY score DESC, candidate_id
            ) rk
        FROM
            S
            JOIN T USING (project_id)
        WHERE matched_skills = required_skills
    )
SELECT project_id, candidate_id, score
FROM P
WHERE rk = 1
ORDER BY 1;

3278. 寻找数据科学家职位的候选人 II 🔒

题目描述

解法

方法一：等值连接 + 分组统计 + 窗口函数

评论