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

目录

0 上集回顾 

1 双先量化，得失权衡 

2 各守城池，妥协攻守

3 SQL演算，三策评详

4 寸土必争，利益倍增 

5 SQL再演，策略精进

6 棋道相通，治国有术

如果觉得本文对你有帮助，那么不妨也可以选择去看看我的博客专栏 ，部分内容如下：

数字化建设通关指南

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

---

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

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

 

0 上集回顾 

围棋收官，通军略之奥妙；秦楚争霸，展谋略之精微。考城池之广狭，序即有变；施后手逾逆收两倍之策，夺即臻善。然双先之妙未尽，深层策略渐显，八城之争将引何变？且看下文分解。

为便读者理解，特列上集所述围棋收官术语及与收官之类比城池，如下。

 

1 双先量化，得失权衡 

梁敬彬：大王，SQL语言可还得心应手？

秦孝公：甚善！恩公驾临，令寡人喜出望外。适逢吾等论八城之争最优解，梁先生快快助孤。

梁敬彬：大王，吾以为策无定法，唯有相对优劣。胡先生以为然否？

胡傲华：然也。人难尽知收官奥妙，能大致不差已属难得。先前所言双先是否可置之不理，实需精算双方得失。今不妨试将此得失量化，以求相对最优。

商鞅：如何量化？

胡傲华：譬如弈棋，黑落一子，白若不应，则白之损失几何？或言之，黑因对方不理而得利几何？此即量化之所在。

商鞅：此值未必甚大，须因盘面而定，不可一概而论吧？

胡傲华：商君所言甚是。然大致量化，或可助吾等深究收官之道。姑且假设此利至少逾当前先手价值两倍。

秦孝公：何以倍之？

胡傲华：此乃假设，亦有其理。盖因盘上大抵难寻两倍之利，能令对手心生恐惧而应之。

商鞅：实则或远超两倍。

胡傲华：然也。

嬴驷：胡先生，那单先可有不应之理乎？

胡傲华：理论上可也。然单先与双先迥异，双先若被占，几乎必遭损失。单先虽被抢，然先手仍在己方。为避繁琐，暂且只论双先之争。

嬴驷：明矣。

2 各守城池，妥协攻守

胡傲华：吾等所言双先、单先、逆收、后手之序，乃常理也。然后手逆收其序易变，双先亦然。试设想秦楚互不相让，秦取平原1，楚随即取平原2，而后双方皆不欲生事，各自回防。此为第三次改良攻城之策，诸君思之，此策若用，结果几何？

嬴华：容吾思之。嗯...若秦楚面对对方双先城之攻，皆选收兵。其结果如下表。

 

哇，胡先生，此策较前，楚竟多得平原2一城！

胡傲华：华公子，楚得平原2，实从秦手中夺之，此消彼长，可知其意？

嬴华：胡先生，秦楚之势消长，面积相去已达75*2=150之多！

胡傲华：诸君至此可明白了？围棋双先之妙，非同小可。

众人皆点头称是。

3 SQL演算，三策评详

 秦孝公：今日恩公在此，寡人以SQL神功再验之。然建表和插数如旧，如下。

-- 创建城池表
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);

 此次改良，乃在前策基础上更进一步。秦楚双方，皆从最大者始，依次争夺双先之城，SQL之策如下。

-- 初始化城池数据，为每个城池分配类型顺序和大小排名
WITH city_data AS (
    SELECT 
        id, name, types, sizes,
CASE
            WHEN types = '双先' THEN 1
            WHEN types = '秦先手' THEN 2
            WHEN types = '楚先手' THEN 3
            WHEN types = '后手' THEN 4
        END AS type_order,
        ROW_NUMBER() OVER (PARTITION BY types ORDER BY sizes DESC) AS size_rank
    FROM 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_楚先手_size
    FROM city_data
),
-- 根据战略规则决定每个城池的归属
capture_process AS (
    SELECT 
        id, name, types, sizes, type_order, size_rank,
CASE
            -- 双先城池按大小轮流分配给秦楚
            WHEN types = '双先' THEN
                CASE
                    WHEN MOD(size_rank, 2) = 1 THEN '秦'
                    ELSE '楚'
                END
            -- 秦先手城池归秦
            WHEN 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_by
    FROM strategic_choice
)
-- 汇总结果，按秦楚分类显示占领的城池数量、总大小和详细列表
SELECT
    captured_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查询模拟了秦楚争城之过程，关键步骤如下：

a) city_data 子查询：为每城池分配类型次序及同类型中之大小排名。

b) strategic_choice 子查询：计算最大后手城池与楚先手城池大小，用于决策。

c) capture_process 子查询：核心逻辑所在，决每城池归属：

• 双先城池按大小轮流分配予秦楚，秦先取之。

• 秦先手城池直接归秦。

• 若最大后手城池大小逾最大楚先手城池两倍，则秦选该后手城池。

• 若无前述条件之后手城池，秦选楚先手城池。

• 其余城池归楚。

d) 最后的SELECT语句汇总结果，显示秦楚各自占城池之数量、总大小及明细。

输出结果：

秦楚各占4城，其中秦域395，楚域275。如诸君所料。

category | count | total_size | cities
---------|-------|------------|-------------------------------------------------------
秦       | 4     | 395        | 平原1 (双先, 大小: 80), 草原1 (秦先手, 大小: 70), 草原2 (秦先手, 大小: 65), 高原2 (后手, 大小: 180)
楚       | 4     | 275        | 平原2 (双先, 大小: 75), 山水2 (楚先手, 大小: 85), 山水1 (楚先手, 大小: 60), 高原1 (后手, 大小: 55)

梁敬彬：大王SQL之术，已臻炉火纯青之境。

4 寸土必争，利益倍增 

嬴驷：胡先生，若双方心有不甘，不肯各自收兵，当如何处之？

胡傲华：驷公子不妨一试。

嬴驷：嗯...楚若不应秦之进攻，则遭双倍平原1之损，秦可增加=160地（80*2）。同理，秦若不应楚之进攻，则遭双倍平原2之损，楚可增加75*2=150之地（75*2），结果如下表。

 

如此一来，秦必较楚多占130而非120之地。看来双先相争不相让，反使秦之收益增加。秦可选择，楚则无选择之余地。似乎此第四次改良攻城之策方为最优。余悟矣，围棋收官遇双先，当思针锋相对之策。

胡傲华：驷公子聪慧过人，他日必成大器。

5 SQL再演，策略精进

秦孝公：来，吾儿聪明，让寡人再以SQL验证此最新攻城之策。梁先生，可否在先前建表之上，增'二次利益'一项，以考虑对方不应将遭双倍损失之情？

梁敬彬：“然也，二次利益”英文为secondary benefits，可简称为sb。

秦孝公：善。建表和插数如旧，仅增加sb字段，如下。

-- 创建城池表（增加sb字段）
CREATE TABLE cities (
   id NUMBER(5) PRIMARY KEY,
    name VARCHAR2(50),
    types VARCHAR2(10),
    sizes NUMBER(5),
    sb NUMBER(5)
);

-- 插入城池数据，包括大小和二次利益
INSERT INTO cities (id, name, types, sizes, sb) VALUES (1, '平原1', '双先', 80, 160);
INSERT INTO cities (id, name, types, sizes, sb) VALUES (2, '平原2', '双先', 75, 150);
INSERT INTO cities (id, name, types, sizes, sb) VALUES (3, '草原1', '秦先手', 70, 0);
INSERT INTO cities (id, name, types, sizes, sb) VALUES (4, '草原2', '秦先手', 65, 0);
INSERT INTO cities (id, name, types, sizes, sb) VALUES (5, '山水1', '楚先手', 60, 0);
INSERT INTO cities (id, name, types, sizes, sb) VALUES (6, '山水2', '楚先手', 85, 0);
INSERT INTO cities (id, name, types, sizes, sb) VALUES (7, '高原1', '后手', 55, 0);
INSERT INTO cities (id, name, types, sizes, sb) VALUES (8, '高原2', '后手', 180, 0);

 今将以SQL之术，演绎第四次改良攻城之策。诸君且观。

-- 初始化城池数据，为每个城池分配类型顺序和大小排名
WITH city_data AS (
    SELECT 
        id, name, types, sizes, sb,
CASE
            WHEN types = '双先' THEN 1
            WHEN types = '秦先手' THEN 2
            WHEN types = '楚先手' THEN 3
            WHEN types = '后手' THEN 4
        END AS type_order,
        ROW_NUMBER() OVER (PARTITION BY types ORDER BY sizes DESC) AS size_rank
    FROM cities
),
-- 计算战略选择所需的最大后手和楚先手城池大小
strategic_choice AS (
    SELECT 
        id, name, types, sizes, sb, 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_楚先手_size
    FROM city_data
),
-- 确定每个城池的占领方和有效大小
capture_process AS (
    SELECT 
        id, name, types, sizes, sb, type_order, size_rank,
CASE
            -- 双先城池：秦占领第一个，楚占领第二个，之后交替分配
            WHEN types = '双先' THEN 
                CASE 
                    WHEN size_rank = 1 THEN'秦'
                    WHEN size_rank = 2 THEN'楚'
                    ELSE CASE WHEN MOD(size_rank, 2) = 1 THEN '秦' ELSE '楚' END
                END
            -- 秦先手城池：全部归秦
            WHEN types = '秦先手' THEN '秦'
            -- 后手城池：如果最大后手大于最大楚先手两倍，归秦
            WHEN types = '后手' AND sizes = max_后手_size 
                 AND max_后手_size > 2 * max_楚先手_size THEN '秦'
            -- 楚先手城池：如果没有符合条件的后手城池，最大楚先手归秦
            WHEN types = '楚先手' AND size_rank = 1 AND
                 max_后手_size <= 2 * max_楚先手_size THEN '秦'
            -- 其余城池归楚
            ELSE '楚'
        END AS captured_by,
        CASE
            WHEN types = '双先' THEN sizes + sb  -- 双先城池的有效大小为初始大小加双倍利益
            ELSE sizes
        END AS effective_size
    FROM strategic_choice
)
-- 最终结果汇总
SELECT
    captured_by AS category,
    COUNT(*) AS count,
    SUM(effective_size) AS total_size,
    LISTAGG(name || ' ('|| types || ', 初始利益: '|| TO_CHAR(sizes) || 
            CASE WHEN types = '双先' THEN ', 总利益: '|| TO_CHAR(sizes + sb) ELSE''END|| ')', ', ') 
    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 核心逻辑说明（突出二次利益的考虑）：

计算有效大小 (effective_size)：

CASE
    WHEN types = '双先' THEN sizes + sb  -- 双先城池的有效大小为初始大小加双倍利益
    ELSE sizes
END AS effective_size

此逻辑虑及双先城池额外之价值，取初始大小与二次利益相加为有效大小。双先城池之分配逻辑调整如下：

WHEN types = '双先' THEN 
    CASE 
        WHEN size_rank = 1 THEN '秦'
        WHEN size_rank = 2 THEN '楚'
        ELSE CASE WHEN MOD(size_rank, 2) = 1 THEN '秦' ELSE '楚' END
    END

结果展示中包含总利益。于最终结果之 LISTAGG 函数中，对于双先城池，并示初始利益和总利益（包括二次利益）：

CASE WHEN types = '双先' THEN ', 总利益: '|| TO_CHAR(sizes + sb) ELSE''END

输出结果：

秦楚各占4城，秦地555，楚地425。秦较楚多130，果不出诸君所料。

CATEGORY    COUNT    TOTAL_SIZE    CITIES
秦           4        555           平原1 (双先, 初始利益: 80, 总利益: 240), 草原1 (秦先手, 初始利益: 70), 草原2 (秦先手, 初始利益: 65), 高原2 (后手, 初始利益: 180)
楚           4        425           平原2 (双先, 初始利益: 75, 总利益: 225), 山水2 (楚先手, 初始利益: 85), 山水1 (楚先手, 初始利益: 60), 高原1 (后手, 初始利益: 55)

6 棋道相通，治国有术

秦孝公：收获甚丰，胡先生可否绘制新之收官流程图，以助吾等深入领悟？

胡傲华：大王，围棋之道，玄妙无穷，远不止于此，逾4段之境，再难以流程图述之。棋道如人生，规则难穷尽，唯有勤加实践，方为正道。

秦孝公：棋之奥秘，果真非吾等所能尽探啊。

梁敬彬：大王。有一技，名曰人工智能（AI），必令大王大开眼界。

秦孝公：AI者，比SQL更为神妙乎？

梁敬彬：AI之核心在于深度神经网络，模仿人脑之结构，然规模更大、速度更快。应用于围棋时，AI以数百万次自我对弈为训，每局皆可调整其亿万参数，逐步优化决策能力。此法使AI从零开始自我学习，却能发现人类所未及之全新策略。经此锤炼，无人类可与之匹敌矣。

秦孝公：连胡先生亦无法胜之乎？

胡傲华：AI之棋技远胜于吾。

秦孝公：叹为观止，闻所未闻！

梁敬彬：AI不拘泥于固定规则，通过海量实践自行总结最优策略。此理念不仅用于棋艺，亦可应用于治国安邦。这恰如大王治国，应因时制宜，灵活应对。

秦孝公：妙哉！此理贯通棋道与治国之术，当真乃智慧至高境界。

随即，胡傲华与梁敬彬起身告辞。秦孝公依依不舍，目送二人离去。

全剧终

本文出自梁敬彬老师之手，具体链接：从围棋收官到秦楚大战的数据库SQL实现(下）

 

如果觉得本文对你有帮助，那么不妨也可以选择去看看我的博客专栏 ，部分内容如下：

数字化建设通关指南

专栏 原价99，现在活动价39.9，按照阶梯式增长，还差3个名额将上升至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博客