从12306改签困惑到数据库SQL语言设计

目录

0 故事从L老师进京买车票说起

2  L老师分段分配新座的需求

3 最简模型设计与SQL实现

4 小结

---

0 故事从L老师进京买车票说起

L老师准备去北京参加DTCC大会。

于是预订了今天的从福州到北京的G302次高铁票，后来临时有事，尝试将G302（7:23出发）改签到G322（9:14出发）

结果发现系统提示G322"售罄"，无法完成改签。

2  L老师分段分配新座的需求

接下来我们做一个需求分析。

为便于分析，我们把福州到北京的路线简化为只分四段，路线：福州 → 杭州 → 南京 → 北京，同时把车厢也限制在仅有10号车厢。

心有不甘的L老师经过查询，发现了一些玄机，

10车13F：福州至杭州空闲，杭州至南京已售，南京至北京空闲 10车07C：福州至杭州已售，杭州至南京空闲，南京至北京空闲

发现玄机了，实际上是有可行的座位组合的：

福州到杭州坐10车13F 杭州到南京换到10车07C 南京到北京可以回到10车13F或继续坐10车07C

这意味着，如果系统支持分段分配新座位，是可以完成全程订票的。然而，现有系统只能查询和分配全程相同的座位，无法识别这种分段组合的可能性。于是L老师放弃了，继续乘坐原来的G302（7:23出发）。

当然了，理论上L老师是可以买一张福州到杭州的车票，再购买杭州到北京的车，不过L老师觉得麻烦，也就算了。其实如果12306允许自动给乘客分段分配座位（即到不同的站换不同的座位），问题就可以解决。那该怎么实现呢？

3 最简模型设计与SQL实现

我们可以做一个最小化的例子来示例说明一下该如何实现，以下是最简化的设计说明，实际情况当然远比这要复杂的多！

首先，创建表：

CREATE TABLE TRAIN_SEATS (
    SEAT_ID NUMBER PRIMARY KEY,
    TRAIN_ID VARCHAR2(10),
    CAR_NO NUMBER,
    SEAT_NO VARCHAR2(3),
    SEGMENT NUMBER,
    STATUS VARCHAR2(10)
);

插入示例数据：

INSERT INTO TRAIN_SEATS VALUES (1, 'G322', 10, '13F', 1, 'AVAILABLE');
INSERT INTO TRAIN_SEATS VALUES (2, 'G322', 10, '13F', 2, 'OCCUPIED');
INSERT INTO TRAIN_SEATS VALUES (3, 'G322', 10, '13F', 3, 'AVAILABLE');
INSERT INTO TRAIN_SEATS VALUES (4, 'G322', 10, '07C', 1, 'OCCUPIED');
INSERT INTO TRAIN_SEATS VALUES (5, 'G322', 10, '07C', 2, 'AVAILABLE');
INSERT INTO TRAIN_SEATS VALUES (6, 'G322', 10, '07C', 3, 'AVAILABLE');

以下是SQL实现的代码，具体如下。

WITH AVAILABLE_SEATS AS (
    SELECT 
        CAR_NO,              -- 车厢号
        SEAT_NO,             -- 座位号
        SEGMENT,             -- 行程段（1：福州到杭州，2：杭州到南京，3：南京到北京）
        STATUS,              -- 座位状态
        ROW_NUMBER() OVER (
            PARTITION BY SEGMENT 
            ORDER BY CAR_NO, SEAT_NO
        ) AS RN               -- 为每个行程段的可用座位分配一个唯一的行号
    FROM TRAIN_SEATS
    WHERE TRAIN_ID = 'G322'   -- 只选择G322列车的座位
    AND STATUS = 'AVAILABLE'  -- 只选择可用的座位
)
SELECT 
    CASE 
        -- 如果三个行程段都有可用座位，则可以全程乘坐
        WHEN COUNT(DISTINCT SEGMENT) = 3 THEN '可以全程乘坐'
        -- 如果至少有一个行程段有可用座位，则需要换座
        WHEN COUNT(DISTINCT SEGMENT) >= 1 THEN '需要换座'
        -- 如果没有任何可用座位，则无法完成行程
        ELSE '无法完成行程'
    END AS TRAVEL_PLAN,
    -- 使用LISTAGG函数将所有可用座位信息连接成一个字符串
    LISTAGG('段' || SEGMENT || ':' || CAR_NO || '车' || SEAT_NO || '座', ' -> ') 
    WITHIN GROUP (ORDER BY SEGMENT) AS SEAT_ARRANGEMENT
FROM AVAILABLE_SEATS
WHERE RN = 1  -- 只选择每个行程段的第一个可用座位
GROUP BY RN;  -- 由于RN总是1，这里的GROUP BY实际上是将所有结果合并成一行

SQL的详细说明：

1. AVAILABLE_SEATS 子查询：

•   从TRAIN_SEATS表中选择G322列车的所有可用座位。

•   使用ROW_NUMBER()函数为每个行程段的可用座位分配一个唯一的行号。这允许我们后续只选择每个段的第一个可用座位。

2. 主查询：

• CASE语句用于确定旅行计划：

 如果所有3个段都有可用座位，则可以全程乘坐。

如果至少有1个段有可用座位，则需要换座。

 如果没有任何可用座位，则无法完成行程。

• LISTAGG函数用于生成一个字符串，描述每个段的可用座位。

• WHERE RN = 1 确保我们只选择每个段的第一个可用座位。

• GROUP BY RN 将结果合并成一行，因为RN总是1。

这个查询有效地找出了允许换座情况下的最佳座位组合，展示了如果系统支持分段座位分配，是可以完成全程订票的。

具体实现结果如下所示：

TRAVEL_PLAN | SEAT_ARRANGEMENT
------------+--------------------------------------------------
需要换座     | 段1:10车13F座 -> 段2:10车07C座 -> 段3:10车07C座

这个结果准确地反映了L老师的分析：

福州到杭州（段1）坐10车13F 杭州到南京（段2）换到10车07C 南京到北京（段3）继续坐10车07C

这个查询展示了如果系统支持分段座位分配，是可以完成全程订票的。

看来打破现有系统（只能查询和分配全程相同的座位）的局限性，允许分段换座在理论上是比较容易实现的。

4 小结

如果有人嫌这种方式麻烦，其实铁路部门还是可以考虑对分段乘坐做一个折扣优惠。这样看在钱的份上，大家也不会嫌弃中途换座位的麻烦。当然了，在春运这种一票难求的时候，就不用考虑折扣了，能多送一个乘客回家团圆，那可是功德无量的事。

~希望12306早日支持分段分座，造福百姓，也为自己创收！~

原文链接：从12306改签困惑到数据库设计——高铁随记 (qq.com)

 

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

数字化建设通关指南
专栏原价99，现在活动价29.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博客