往期回顾

18. 有缘人是谁

老柯没有让老王失望，时序城堡很快就建成了。接下来，根据剧情经验来看，老王肯定会出现在时序城堡的最高处，而且是在老柯的陪同下检阅脚下的新城堡和四处的城堡，然后各种询问和赞叹。没错，你全猜对了！只是这次和以往有所不同，有彩蛋，老王带着一个神秘的盒子来了。

老柯：哎呀，来都来了，还带什么礼物…

老王：想得美，这可不能送你！这是我多年前从高人那得到的神秘宝贝，被告之今天才能开启。据说破解其中秘密后将收获巨大惊喜，所以想找你一起研究研究。

在这里插入图片描述

很快盒子被打开了，里面赫然一张纸条，标题为：有缘人将在王子成婚之日献上大礼。正文告知了游戏规则：盒子有十层，第一层都要根据既定的要求找到1号有缘人，然后根据1号有缘人的信息打开第二层盒子找到2号有缘人，以此类推，直到在第十层查到10号有缘人信息，即真正要找的有缘人。其中第一层的要求短短不过几十字，如下：请找出这个有缘人，他并非国王和王子的朋友，却是国王和王子共同朋友的朋友；他和国王在同一位医生那里接受过治疗；他和国王的顾问在同一所学校读过书；他担任过五种不同的工作岗位。

老王：看来冥冥之中有一股神秘的力量在牵引我做正确的事，这些年的努力，基本上全国所有行业纸质的记录都已经信息化了，神秘预言里要找的有缘人，我想在数据库城堡中应该可以搜到，看来今天和你一起揭秘是一个明智的选择，老柯，找到这个有缘人的任务就交给你了。

19. 查有缘人受挫

老柯：老这个数据在OLTP城堡中，不过因为数据有同步机制，所以在OLAP城堡应该都有的，由于这是分析型需求，所以我们应该在OLAP城堡去实现更为合适。

老柯说着，便于老王一同来到了OLAP城堡最高处的控制台，开始寻找有缘人了。老柯一边编写SQL边给老王讲述自己的实现思路。

步骤1，需要找到您和王子殿下的共同朋友，代码如下：

– 定义国王(ID=1)和王子(ID=2)的共同朋友

WITH king_and_prince_friends AS (
    SELECT person2_id AS friend_id FROM friendship WHERE person1_id = 1 -- 国王的朋友
    INTERSECT SELECT person2_id AS friend_id FROM friendship WHERE person1_id = 2 -- 王子的朋友
),

步骤2，接下来进一步挖掘这些共同朋友的朋友圈，但排除了国王和王子本身，代码如下：

common_friend_friends AS (
    SELECT DISTINCT f.person2_id AS friend_of_common_friend
    FROM king_and_prince_friends kpf
    JOIN friendship f ON kpf.friend_id = f.person1_id
    WHERE f.person2_id NOT IN (1, 2) -- 排除国王和王子
)

步骤3，最后再开始筛选这些人中满足特定条件的特殊朋友，包括曾在国王的医生那里接受过治疗、与国王的顾问在同一所学校接受过教育及至少担任过5种不同的工作岗位，代码如下：

SELECT p.name
FROM person p
JOIN common_friend_friends cff ON p.id = cff.friend_of_common_friend
JOIN treatment t ON p.id = t.person_id AND t.doctor_id = (SELECT doctor_id FROM treatment WHERE person_id = 1) -- 国王的医生
JOIN education e ON p.id = e.person_id AND e.school_id = (SELECT school_id FROM education WHERE person_id = (SELECT advisor_id FROM person WHERE id = 1)) -- 国王的顾问
JOIN work w ON p.id = w.person_id
GROUP BY p.id, p.name
HAVING COUNT(DISTINCT w.job) >= 5;

所有这些思路合并在一起，我们得到了这个完整的SQL指令，代码如下：

– 定义国王(ID=1)和王子(ID=2)的共同朋友

WITH king_and_prince_friends AS (
    SELECT person2_id AS friend_id FROM friendship WHERE person1_id = 1 -- 国王的朋友
    INTERSECT
    SELECT person2_id AS friend_id FROM friendship WHERE person1_id = 2 -- 王子的朋友
),

– 定义国王和王子的共同朋友的朋友，但排除国王和王子本身

common_friend_friends AS (
    SELECT DISTINCT f.person2_id AS friend_of_common_friend
    FROM king_and_prince_friends kpf
    JOIN friendship f ON kpf.friend_id = f.person1_id
    WHERE f.person2_id NOT IN (1, 2) -- 排除国王和王子
)
SELECT p.name
FROM person p
JOIN common_friend_friends cff ON p.id = cff.friend_of_common_friend

– 和国王在同一位医生那里接受过治疗

JOIN treatment t ON p.id = t.person_id AND t.doctor_id = (SELECT doctor_id FROM treatment WHERE person_id = 1) -- 国王的医生

– 和国王的顾问在同一所学校读过书

JOIN education e ON p.id = e.person_id AND e.school_id = (SELECT school_id FROM education WHERE person_id = (SELECT advisor_id FROM person WHERE id = 1)) -- 国王的顾问

– 至少担任过五种不同的工作岗位

JOIN work w ON p.id = w.person_id
GROUP BY p.id, p.name
HAVING COUNT(DISTINCT w.job) >= 5;

SQL指令编写的过程还算顺利，不过老柯也向老王表达了自己的担忧，他认为找这个有缘人的条件表面上看起来没那么复杂，实则并不易，因为该需求涉及到复杂的关联与递归与递归，而且数据量还特别大。

验证代码无误后，老柯手一敲，指令发出了，经过长达数小时的等待，结果出来了，果然查到了1号有缘人。老王兴高采烈的打开了盒子的第二层，原来新的需求是在原有第一层基础上再增加条件来找到有2号缘人。这会儿难度显然更大的多，老柯绞尽脑汁编写SQL再次执行，这次找到有缘人2的时间是之前的数倍。当老王打开盒子第三层时候，是在原有第二层基础上再增加条件来查找3号有缘人，等老柯找到有3号有缘人时，已到凌晨时分。

两人休息了一会儿，天亮再战，在基于第五层基础之上查询6号有缘人时，就再也查不出结果了，根据老柯观察数据块扫描的结果来预测，需要100小时。怎么办，工作才刚完成一半，第七层估计更难，而总共有十层！老柯失去了继续往下的信心了，同样老王也决定放弃了。

未完待续…

七座城堡⑤ 图（下）

公众号：收获不止数据库

微信：ljbyxl1213

系列回顾

ITPub博客

七座城堡⑤ 图【上】

18. 有缘人是谁

19. 查有缘人受挫

最新文章