算法工程师-SQL进阶:集合之间的较量-二、实战篇
案例:
有如下两张候选人技能管理表,skills
是公司规定的候选人必会技能表,candidate_skills
是2021秋招候选人的技能明细表。
< skills >
+------------+
| skill_name |
+------------+
| python |
| spark |
| hive |
+------------+
< candidate_skills >
+---------+-----------+------------+
| user_id | user_name | skill_name |
+---------+-----------+------------+
| 1 | 刘红 | python |
| 1 | 刘红 | spark |
| 1 | 刘红 | hive |
| 1 | 刘红 | c++ |
| 2 | 肖磊 | python |
| 2 | 肖磊 | tensorflow |
| 2 | 肖磊 | spark |
| 2 | 肖磊 | hive |
| 3 | 王芳 | tensorflow |
| 3 | 王芳 | python |
| 3 | 王芳 | spark |
| 4 | 张阳 | python |
| 4 | 张阳 | c++ |
| 4 | 张阳 | spark |
| 5 | 李鑫 | spark |
| 5 | 李鑫 | python |
| 5 | 李鑫 | tensorflow |
+---------+-----------+------------+
1、简单集合运算
Eg1: 请筛选出会使用大数据开发工具(掌握hive或spark),但不会tensorflow的候选人集合。
分析: 可以先通过UNION运算
求出掌握大数据技能的全部候选人,然后进行INTERSECT
运算:减去掌握tensorflow的候选人。
参考SQL:
SELECT BigData.user_name
FROM
(SELECT user_name
FROM candidate_skills
WHERE skill_name="spark"
UNION SELECT user_name
FROM candidate_skills
WHERE skill_name = 'hive') BigData -- 掌握大数据技术的后续人集合
LEFT JOIN -- 利用left join求差集
( SELECT user_name
FROM candidate_skills
WHERE skill_name="tensorflow") Tensorflow ON Tensorflow.user_name = BigData.user_name
WHERE Tensorflow.user_name IS NULL;
结果:
+-----------+
| user_name |
+-----------+
| 刘红 |
| 张阳 |
+-----------+
2、集合是否相等
想一想,我们一般通过什么原则判断两个集合相等呢?
首先,如果有一个集合S,则S UNION S = S
和 S INTERSECT S = S
都是成立的,类似这种S * S = S
的性质,我们称之为幂等性
。UNION和INTERSECT都具有幂等性。
如果集合 A 和集合 B 相等,那么 A = B = A UNION B = A INTERSECT B,这个条件是A=B的充分必要条件
。因此,可以通过检测两个集合的并集和交集数量是否一致来检测它们是否相等。
Eg2: 请检查:掌握spark的候选人和掌握hive的候选人是不是同一批人?
分析: 先分别求出掌握两个技能的候选人集合s1、s2,然后分别求出s1与s2的交集和并集,如果交集和并集的数量相等,则s1和s2相等。
SQL参考:
SELECT
(SELECT count(*) from
(SELECT user_name
FROM candidate_skills
WHERE candidate_skills.skill_name ='spark'
UNION SELECT user_name
FROM candidate_skills
WHERE candidate_skills.skill_name ='hive') U) AS union_num,
(SELECT count(*)
FROM
(SELECT DISTINCT A.user_name
FROM candidate_skills A
INNER JOIN candidate_skills B ON A.user_name = B.user_name
WHERE A.skill_name='spark'
AND B.skill_name='hive') I) AS inter_num;
结果:
+-----------+-----------+
| union_num | inter_num |
+-----------+-----------+
| 5 | 2 |
+-----------+-----------+
解析:
上面的SQL用了两个标量子查询,分别求出s1和s2的并集元素数量和交集元素数量,很显然,4 != 2,可知掌握这两种技能的人并不是相同一批人(两个集合不同)。值得注意的是,求s1和s2的交集时,并没有采用不通用的intersect
,而是使用了inner join
实现,其中也运用了自连接
方法,思路比较巧妙,这种方法Hive和MySQL都能支持。
3、关系除法
Eg3: 请筛选出符合该公司技能要求
的全部候选人。
分析:
针对每个候选人逐一筛选,从规定的技术集合中减去该候选人自己的技术的集合,如果结果是空集
,则说明该候选人具备所要求的全部技术,否则说明该候选人不具备某些要求的技术。
参考SQL:
-- 用求差集的方法进行关系除法运算(有余数)
SELECT DISTINCT user_name
FROM candidate_skills S1
WHERE NOT EXISTS
(SELECT skill_name
FROM skills -- 所要求的全部技术
EXCEPT --相减,如果结果不为空,则存在没有掌握的必备技术
SELECT skill_name
FROM candidate_skills S2 -- 该候选人自己具备的全部技术
WHERE S1.user_name = S2.user_name);
结果:
+-----------+
| user_name |
+-----------+
| 刘红 |
| 肖磊 |
+-----------+
解析:
上面这段SQL,采用减法的思想去做除法,即让除数集合减去个人技能集合,如果剩余为空,则这个人掌握全部要求的技能(除数中的技能)。有个缺点就是,上面的SQL用到了expect这个不通用的减法运算符,如果改写成left join的减法形式,需要在exists() 中既要嵌套一层关联子查询,又要通过left join skills实现减法逻辑,实现起来比较复杂。其实,还有下面的方法可以实现关系除法运算。
【扩展】使用 HAVING
子句转换成一对一关系
SELECT user_name
FROM candidate_skills A
INNER JOIN skills ON A.skill_name = skills.skill_name -- 通过内连接,只保留公司要求的技能项
GROUP BY user_name HAVING count(*) = -- 按用户分组,检查每个用户在公司要求的技能范围内的技能数目是否达标
(SELECT count(*)
FROM skills);
4、寻找相等的子集
Eg4: 请筛选出技能栈完全相同的候选人。
分析:
我们可以将此问题拆解为两步。
第一步:求出有公共技能的候选人组合(pair);
第二步:验证左右两位候选人的公共技能,是否是其全部掌握的技能,如果都满足,则这两个候选人的技能栈是完全相同的。
参考SQL:
SELECT A.user_name,
B.user_name
FROM candidate_skills A,
candidate_skills B
WHERE A.user_id < B.user_id
AND A.skill_name = B.skill_name
GROUP BY A.user_name,
B.user_name -- 到这里,完成第一步:求出有公共技能的候选人组合(pair)
HAVING count(*) = -- 通过having 对每组匹配候选人依次判断
(SELECT count(*)
FROM candidate_skills C
WHERE C.user_name = A.user_name) -- 这里检查第一个候选人的全部技能数量是否等于两人功能的技能数
AND count(*) =
(SELECT count(*)
FROM candidate_skills D
WHERE D.user_name = B.user_name); -- 这里检查第二个候选人的全部技能数量是否等于两人功能的技能数
结果:
+-----------+-----------+
| user_name | user_name |
+-----------+-----------+
| 王芳 | 李鑫 |
+-----------+-----------+
解析:
通过候选人两两配对后,筛出共同技能,然后在分组筛选逻辑having中,通过关联子查询依次判断每个人是否符合条件,最终得到技能栈完全相同的候选人pair。这种方法通用且灵活,可以在实际中尝试使用并根据具体情景加以扩展。
上一篇: 使用bc命令在Linux中打开计算器
下一篇: 学习SQL【7】-函数