趣味SQL | 从围棋收官到秦楚大战的数据库SQL实现(中）

目录

0  上集回顾 

1城池广狭，胜于多寡

2 量地制谋，攻取有方

3 攻伐之序，尚可再优

4 秦公展技，尽显神功

......未完待续

欲知后事如何，想进一步了解SQL这门艺术语言的，可以订阅我的专栏数字化建设通关指南，且听下回分解。

专栏 原价99，现在活动价39.9，按照阶梯式增长，直到恢复原价

0  上集回顾 

上集回顾：趣味SQL | 从围棋收官到秦楚大战的数据库SQL实现

昔秦孝公以SQL神技，推演秦楚争地之策，二公子谨记围棋收官之法：双先、单先、逆收、后手，皆心领神会。

然，收官之道，非止于先后，更须计城池之广狭。此中奥妙，待我慢慢道来。

为便于读者理解，特将上集描述的围棋收官术语、及与收官相类比的城池之争，再列于此。

​ ​

注：1. 本文代码可左右滑动观看；2. 建表SQL语句出现size 和type字段，此为Oracle关键字，如果在Oracle中执行需要加引号，考虑到排版等问题，这里就略去了。 

1城池广狭，胜于多寡

秦孝公：SQL语言确实厉害，真得感谢那位授寡人SQL神功的恩公，不知何日能再得见一面。

商鞅：我也盼能一见，当面请教。

秦孝公：嗯。对了，驷儿、华儿，围棋的每一手棋并非一样大，城池亦如此，若考虑城池大小，攻城之策又当如何？

嬴华：父王，是否应在原先双先、秦先手、楚先手、后手的顺序基础上，再按城池大小排序。

秦孝公：吾儿聪明，我们改造先前的建表语句，增加字段SIZES，以表示城池的大小，如下：

-- 创建城池表
CREATE TABLE cities (id NUMBER(5) PRIMARY KEY,name VARCHAR2(50),types VARCHAR2(10),sizes NUMBER(5)
);
-- 插入城池数据，包括大小
INSERT INTO cities (id, name, types, sizes) VALUES (1, '平原1', '双先', 80);
INSERT INTO cities (id, name, types, sizes) VALUES (2, '平原2', '双先', 75);
INSERT INTO cities (id, name, types, sizes) VALUES (3, '草原1', '秦先手', 70);
INSERT INTO cities (id, name, types, sizes) VALUES (4, '草原2', '秦先手', 65);
INSERT INTO cities (id, name, types, sizes) VALUES (5, '山水1', '楚先手', 60);
INSERT INTO cities (id, name, types, sizes) VALUES (6, '山水2', '楚先手', 85);
INSERT INTO cities (id, name, types, sizes) VALUES (7, '高原1', '后手', 55); 
INSERT INTO cities (id, name, types, sizes) VALUES (8, '高原2', '后手', 180);

 若按原先顺序不考虑大小，秦将获得6座城池（平原1、平原2、草原1、草原2、山水1、高原2），楚则得2座城池（山水2、高原1）。结果如下，吾儿还记得否？

​

嬴驷和嬴华：记得。

秦孝公：既知城池大小，且算算秦楚各占领多大面积？

嬴华：秦占得城池为：平原1（80）、平原2（75）、草原1（70）、草原2（65）、山水1（60）、高原2（180），合计555。楚占得城池为：山水2（85）、高原1（55），合计140。秦比楚多占415，如下表所示。

 ​

2 量地制谋，攻取有方

秦孝公：吾儿，若以城池面积为目标，秦楚攻城之策可有不妥？

嬴驷：父王，儿以为秦楚双方均有失误，秦当先取面积更大的山水2而非山水1，楚应占面积更大的高原2而非高原1。这样秦得430，楚占240，见下表。 

​ 

 秦孝公：吾儿所言甚是，来，且让我用SQL神功验证一番。

WITH capture_process AS (SELECT c.id, c.name, c.types, c.sizes,CASEWHEN c.types = '双先' THEN '秦'WHEN c.types = '秦先手' THEN '秦'WHEN c.types = '楚先手' THENCASE WHEN MOD(ROW_NUMBER() OVER (PARTITION BY c.types ORDER BY c.sizes DESC), 2) = 1 THEN '秦' ELSE '楚' ENDWHEN c.types = '后手' THENCASE WHEN MOD(ROW_NUMBER() OVER (PARTITION BY c.types ORDER BY c.sizes DESC), 2) = 1 THEN '楚' ELSE '秦' ENDEND AS captured_by,CASEWHEN c.types = '双先' THEN 1WHEN c.types = '秦先手' THEN 2WHEN c.types = '楚先手' THEN 3WHEN c.types = '后手' THEN 4END AS type_order,ROW_NUMBER() OVER (PARTITION BY c.types ORDER BY c.sizes DESC) AS size_rankFROM cities c
),
ordered_cities AS (SELECT captured_by,name || ' ('|| types || ', 大小: '|| TO_CHAR(sizes) || ')' AS city_info,type_order,CASE WHEN types IN ('楚先手', '后手') THEN size_rankELSE 0END AS sub_order,sizes  -- 保留sizes字段，用于后续计算总面积FROM capture_process
)
SELECTcaptured_by AS category,COUNT(*) AS count,SUM(sizes) AS total_size,  -- 新增：计算每个国家占领城池的总面积LISTAGG(city_info, ', ') WITHIN GROUP (ORDER BY type_order, sub_order, sizes DESC) AS cities
FROM ordered_cities
GROUP BY captured_by
ORDER BY CASE WHEN captured_by = '秦' THEN 1 ELSE 2 END,COUNT(*) DESC

SQL 核心逻辑说明：

输出结果：

秦6城，秦疆域430；楚2城，楚疆域240，如嬴驷所料。

1. capture_process 子查询
   此查询依据城池类型与大小，推断各城归属。规则为：

        a. 双先和秦先手的城池由秦直接占领。

        b. 楚先手之城以大小为序，交替分配给秦楚。通过ROW_NUMBER() 函数对大小排名，并通过MOD() 函数将其分配给两国。

         c. 后手的城池同样依据大小顺序交替分配。

2. ordered_cities 子查询
   此部分对城池按类型顺序及大小进行排序，并为最终结果展示做准备。这里使用了 LISTAGG() 进行字符串聚合，输出每一方占领的城池详情。

3. 最终查询

   据城池归属聚合数据，列出秦楚各得城池，依大小排序呈现结果。

​ 商鞅：大王的SQL可真厉害，围棋正是如此对弈，恭喜两位公子学有所成！

3 攻伐之序，尚可再优

 秦楚大战很快打响，最终秦得六城，疆域共计430。而楚占两城，总面积为240，果真如众人所料。

​

数月后，秦孝公复邀商鞅对弈。七局结束，商鞅竟零比七惨败，大为震惊。

秦孝公：商君，心服否？寡人近得高人指点，棋艺大进。

胡傲华：诸位好，商君这几局实际是输在收官上。

商鞅：收官？我等可刚总结过收官策略，还据此在八城之争中取得显著成果。

胡傲华：商君，八城之争其实还有提升空间。

商鞅、嬴驷、嬴华闻言，更觉困惑。

秦孝公：胡老师所言不虚。我大秦应该改抢山水2，而非高原2。

商鞅：不可，如此我军仅得五城，非六城矣。

嬴华：然也。若不逆收，楚军将连取山水1、山水2，再得高原1，共三城。我军仅得五城。

胡傲华：诸位不妨再细想一番。

嬴驷：哦，高原2面积180，即便我军只得五城，总疆域仍会增加。

嬴华：确实如此！我略作计算，秦军五城疆域可达470，楚军三城仅为200。我军将较楚军多出270之地（而非之前的190），见表如下。

​

商鞅：妙哉！270-190=80，攻城顺序一变，我大秦竟可多得80土地！

秦孝公：围棋收官亦是如此。寡人曾弃逆收而取后手，商君可曾注意？

商鞅：想起来了，大王高明！

胡傲华：一般而言，逆收优先于后手。然有一例外：若后手价值逾逆收两倍，可优先取之。

嬴驷：懂了，楚先手山水2面积为85，倍之也仅170，而后手的高原2面积为180，超山水2两倍有余，故优先取之。

胡傲华：驷公子慧眼如炬，围棋收官正合此理。我在先前诸位梳理的流程图上略作改进，若言之前图示为业余3段水平，新图则已达业余4段之境。如下所示，请诸位反复细品，定有收获。

​ 

4 秦公展技，尽显神功

秦孝公：诸位，胡老师教我等，后手大于逆收两倍者可先取。今寡人以SQL神功演之，看结果如何。

WITH city_data AS (SELECT id, name, types, sizes,
CASEWHEN types = '双先' THEN 1WHEN types = '秦先手' THEN 2WHEN types = '楚先手' THEN 3WHEN types = '后手' THEN 4END AS type_order,ROW_NUMBER() OVER (PARTITION BY types ORDER BY sizes DESC) AS size_rankFROM cities
),
strategic_choice AS (SELECT id,name,types,sizes,type_order,size_rank,MAX(CASE WHEN types = '后手' THEN sizes ELSE 0 END) OVER () AS max_后手_size,MAX(CASE WHEN types = '楚先手' THEN sizes ELSE 0 END) OVER () AS max_楚先手_sizeFROM city_data
),
capture_process AS (SELECT id, name, types, sizes, type_order, size_rank,
CASEWHEN types IN ('双先', '秦先手') THEN '秦'WHEN types = '后手' AND sizes = max_后手_size AND sizes > 2 * max_楚先手_size THEN '秦'WHEN types = '楚先手' AND NOT EXISTS (SELECT 1 FROM strategic_choice WHERE types = '后手' AND sizes > 2 * max_楚先手_size) THEN '秦'ELSE '楚'END AS captured_byFROM strategic_choice
)
SELECTcaptured_by AS category,COUNT(*) AS count,SUM(sizes) AS total_size,LISTAGG(name || ' ('|| types || ', 大小: '|| TO_CHAR(sizes) || ')', ', ') WITHIN GROUP (ORDER BY type_order, sizes DESC) AS cities
FROM capture_process
GROUP BY captured_by
ORDER BY CASE WHEN captured_by = '秦' THEN 1 ELSE 2 END;

SQL 核心逻辑说明（突出后手价值大于逆收两倍的实现）：

1. 在strategic_choice子查询中，我们计算了关键值：

2. MAX(CASE WHEN types = '后手' THEN sizes ELSE 0 END) OVER () AS max_后手_size,
   MAX(CASE WHEN types = '楚先手' THEN sizes ELSE 0 END) OVER () AS max_楚先手_size
   

   这里计算了最大的后手城池大小和最大的楚先手城池大小。

3. 在capture_process子查询中，关键的判断逻辑如下：

4. CASEWHEN types = '后手' AND sizes = max_后手_size AND sizes > 2 * max_楚先手_size THEN '秦'WHEN types = '楚先手' AND NOT EXISTS (SELECT 1 FROM strategic_choice WHERE types = '后手' AND sizes > 2 * max_楚先手_size) THEN '秦'ELSE '楚'
   END AS captured_by

这个CASE语句实现了核心逻辑：

• 如果是后手城池，且其大小等于最大后手城池大小，并且大于最大楚先手城池大小的两倍，则秦国占领。

• 如果是楚先手城池，但不存在大于楚先手两倍的后手城池，则秦国占领。

• 其他情况下，楚国占领。

• 这种实现方式确保了当后手城池的价值超过楚先手城池两倍时，秦国会优先选择后手城池，而不是按常规顺序选择楚先手城池。

输出结果：

秦占5城，总疆域470；楚夺3城，共计200。与我等所料一致。

商鞅：我悟了！大王，可再战一局？

秦孝公欣然应战，三百回合后，商鞅再败。

商鞅：臣何以再败？

秦孝公：商君啊，胡老师所授，岂止于此？方才八城之争，尚非最优。

商鞅闻言，惊愕转向胡傲华。

胡傲华：诚如大王所言，关键在于，若秦占双先城1，楚是否当置之不理，直取双先城2。

商鞅：置之不理，岂不遭罚？

胡傲华：楚若占双先城2而秦不应之，则秦同样会遭受惩罚。关键在于精确计算双方可能之损失，从而定最优策，此理同适用于围棋。每逢大王弈出双先，您必应之，从未反击，此乃败因所在。

商鞅：原来如此！受教了。

秦孝公：哈哈，商君，今可心服否？

就在此时，一个声音远远传来：大王，别来无恙。

秦孝公：恩公？正是传我SQL神功的恩公，他来了！

......未完待续

注意：本文出自梁敬彬 老师之手，原文链接如下：

https://mp.weixin.qq.com/s/hsvvvoEAnBxTBkCKpDbXHQ

欲知后事如何，想进一步了解SQL这门艺术语言的，可以订阅我的专栏数字化建设通关指南，且听下回分解。

专栏 原价99，现在活动价39.9，按照阶梯式增长，还差5个名额将上升至59.9，直到恢复原价。

数字化建设通关指南

主要内容：
（1）SQL进阶实战技巧
可以参考如下教程，具体链接如下

SQL很简单，可你却写不好？也许这才是SQL最好的教程

上面链接中的文章及技巧会不定期更新。

（2）数仓建模实战技巧和个人心得
       1）新人入职新公司后应如何快速了解业务？

       2）以业务视角看宽表化建设？

       3)  维度建模 or 关系型建模？

       4）业务模型与数据模型有什么区别？业务阶段的模型该如何建设？

       5）业务指标体系该如何建设？指标体系该如何维护？指标平台应如何建设？指标体系                           该由谁来搭建？

       6）如何优雅设计DWS层？DWS层模型好坏该如何评价？

       7）指标发生异常，该如何排查？应从哪些方面入手寻找问题点？

       8） 数据架构的选择，mpp or hadoop?

       9）数仓团队应如何体现自己的业务价值，讲好数据故事？

       10）BI与大数据有什么关系？BI与信息化、数字化之间有什么关系？BI与报表之间的关                          系？

       11）数据部门如何与业务部门沟通，并规划指引业务需求？

文章不限于以上内容，有新的想法也会及时更新到该专栏。

具体专栏链接如下：

数字化建设通关指南_莫叫石榴姐的博客-CSDN博客