⓵悟!从12306改签困惑到数据库设计——高铁随记

梁敬彬梁敬弘兄弟出品

1. 故事从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:1013F座 ->2:1007C座 ->3:1007C座

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

福州到杭州(段1)坐10车13F

杭州到南京(段2)换到10车07C

南京到北京(段3)继续坐10车07C

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

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

4. 延伸&花絮

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

在动车上做试验,写的SQL脚本是Oracle语法,结果写完脚本后发现自己的Oracle测试环境连不上,咋办,不验证一下脚本正确与否心里不踏实啊。忽然想起了达梦数据库和Oracle兼容性很高,于是给达梦的老朋友黄海明老师打了个电话求救,结果他给了一个免费的在线达梦环境,于是我测试了一下,一次性输出正确的我要的结果,看来达梦数据库对Oracle的兼容性名不虚传啊。

12306如果支持分段分座,不仅能造福百姓,也能为自己创收到底会不会实现呢?

未完待续…
⓶惊!12306业务系统升级也能被普通人推动?

三分钟讲述个人感悟——感恩,回馈

公众号:收获不止数据库

系列回顾

“大白话人工智能” 系列
“数据库拍案惊奇” 系列
“世事洞明皆学问” 系列

12306数据库设计 原⽂地址:http://blog.csdn.net/hnkontecna/article/details/61672983 标签 标签 PostgreSQL , 12306 , 春节 , ⼀票难求 , 门禁⼴告 , 数组 , 范围类型 , 抢购 , 排他约束 , ⼤盘分析 , ⼴告查询 , ⽕车票 背景 背景 马上春节了,⼜到了⽕车票的销售旺季,⼀票难求的问题依旧存在吗? 还记得10年前春节前买⽕车票得在放票前1天搬个⼩板凳去排队,对于热门路线,排⼀个晚上都有可能买不到票。 随着互联⽹的发展,⼏年前建设了12306⽹上购票系统,可以从电脑上买票,但是不要以为在电脑上就能买到票。 我记得12306刚推出时,经常发⽣12306⽹站打不开,⽆法付款的问题。 为什么呢? 原因很简单,春节期间⽹上购票的⼈可能达到⼏亿的级别,⽽且放票⽇期是同⼀天同⼀个时间点,也就是说同⼀时刻12306要接受⼏亿⽤ 户的访问。 处理能⼒和实际的访问需求更不上,带来的结果就是⽹站打不开,系统不稳定的现象。 后来12306想了分线路分时段开启的办法,想办法把不同线路的⽤户错开时间来访问12306的⽹站,但是这个⽅法起初的效果不明显,并 不是所有⽤户都知道的(就好像你临时通知今天不上班,但还是有⽤户会来单位的),所以⼤多数⽤户还是集中在⼀个点去访问12306的 ⽹站。 随着硬件的发展,技术的演进,12306的系统越来越趋于成熟,稳定性和响应速度也越来越好。 据说现在很多商家还开通了云抢票业务,本质上是让你不要冲击12306系统了,把需求提前收集,在放票时,这些系统会进⾏排队与合并 购买,这种⼿段可以减少12306的访问并发。 抢⽕车票是很有意思的⼀个课题,对IT⼈的智商以及IT系统的健壮性,尤其是数据库的功能和性能都是⼀种挑战。 接下来我们⼀起来缕⼀缕有哪些难点,⼜有怎样的解决⼿段。 ⼀、铁路售票系统 ⼀、铁路售票系统 - 西天取经之路开始啦 西天取经之路开始啦 铁路售票系统最基本的功能包括 查询余票、余票统计、购票、车次变化、退票、改签、中转乘车规划 等。 每个需求都有各⾃的特点,例如 1. 查询余票,⽤户在购票前通常会查⼀下到达⽬的地有哪些余票,它属于⼀个⾼并发的操作,同时需要统计余票张数,需要很强的CPU来 ⽀撑实时的查询。 2. 购票,购票和查询不⼀样,购票是会改变库存的,所以对数据库来说是更新的操作。 ⽽且购票很可能发⽣冲突,例如很多⼈要买同⼀趟车的票,那就出现冲突了,到底卖给谁呢? 需要考虑锁冲突,尽量的让不同的⼈购买时可并⾏,或者可以合并多⼈的购票请求,来减少数据库的更新操作。 3. 中转乘车,当⽤户需要购买的起点和到达站⽆票时,需要计算中转的搭乘⽅案。 ⽐如从北京到上海,如果没有直达车,是不是该转车呢?转哪趟,在哪⾥转就成了问题,简单⼀点就是买票的⼈⾃⼰想。 ⾼级⼀点的话,可以让12306给你推荐路线,这个涉及的是数据库的路径规划功能。 我们来逐⼀分析⼀下这些需求的特点。 1 查询余票 查询余票 1. 普通的余票查询需求 你如果要买从北京到上海的⽕车票,通常会查⼀下哪些车次还有余票。 查询的过滤条件可能很多,⽐如 1.1. 上车站、下车站、中转站 1.2. 车次类型(⾼铁、动车、直达、快速、普客、...) 1.3. 出发⽇期、时段 1.4. 到达⽇期、时段 1.5. 席别(硬座、硬卧、...站票) 1.6. 过滤掉没有余票的车次 展⽰给⽤时还要考虑到怎么排序(是按始发时间排呢,还是按票价,或者按余票数量排?),怎么分页。 眼见不⼀定为实 查询余票通常不是实时的、或者说不⼀定是准确的,有可能是后台异步统计的结果。 即使是实时统计的结果,在⾼并发的抢票期间,你看到的信息对你来说也许很快就会失效。 ⽐如你看到某趟车还有100张票,很可能等你付款的时候,已经卖光了。 所以在⾼峰期,余票信息的参考价值并不⼤,不要被迷惑了。 2. 查询余票的另⼀个更⾼级的需求是路径规划, ⾃动适配(根据⽤户输⼊的中转站点s) 这个功能以前可能没有,但是总有⼀天会暴露出来,特别是车票很紧张的情况下。 就⽐如从北京到上海,直达的没有了,系统可以帮你看看转⼀趟车的,转2趟车的,转N趟车的。(当然,转的越多越复杂)。 从中转这个⾓度来讲,实际上已经扯上路径规划的技术了。 怎么中转是时间最短的、价格最低的、中转次数最少的等等。(⾥⾯还涉及转车的输⼊要求(⽐如⽤户要求在⼀线城市转车,或者必须要转 ⾼铁))。 关于路径规划,可以参考⼀下PostgreSQL pgrouting,已⽀持多种路径规划算法,⽀持算法的⾃定义扩展。 简直是居家旅⾏,杀⼈灭⼝的必备良药。 师⽗⼩⼼,有妖怪。。。 师⽗⼩⼼,有妖怪。。。 1. ⼤多数⽤户是有选择综合症的,通常来说,⽤户可能会查
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

收获不止数据库

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值