数据库编程大赛：只用一条 SQL 秒杀 100 万张火车票

2024 第二届数据库编程大赛于 12 月 5 日正式开启初赛！由 NineData 和云数据库技术社区主办，华为云、Doris等协办单位和媒体共同举办。比赛要求选手设计一套SQL算法，只用一条 SQL 秒杀 100 万张火车票，让乘客都都能顺利坐上火车回家过年。查看赛题详情

以下是本次决赛冠军选手，郭其的参赛介绍：

参赛选手：郭其

选手简介：中国移动设计院网研中心，数据库技术专家负责人

参赛数据库：Oracle

性能评测：百万级数据代码性能评测 1.184 秒

综合得分：96

以下是郭其选手的代码说明思路简介：

第一诫：所有影响hash join性能的优化，在极致性能要求面前，都是负反馈。

1. tmp_train临时通过开窗函数，将坐票累加值、站票累加值都记录下来，为后面的记录乘客匹配做准备，人为创造票数区间。

2. 充分审题，还要要多审题，发现票数只有600，800，1200，1600四种，站票固定10%（60，80，120，160），则取公约数20为最合适的值，进行匹配，便于匹配的时候减少hash小表的数据量，进一步提升hash性能。

3. tmp_train_seed，2中已说明，只有四种可能，因此借train表当个序列(按最大1600+160，1-88)生成使用下，提升代码美观度,把train表按公约数20扩行。

4. passenger_rk表是passenger表的开窗所得，开了两个列，一个是纯顺序rk，便于后期算车厢后使用。一个是按公约数20做的分组，20个一组，正好可以匹配一个批次。

5. 按人员批次和火车批次，进行left join匹配，因全是等值条件，hash 效率较高，注意这里，不能贪一些过滤条件，比如使用or+between去判断，会得到负反馈性能。

6. hint parallel(8)充分利用oracle的并行特性叠加hash join，提升性能。匹配处理，行数正确，剩下的只是一些基本面字段通过数字求车厢号，以及substr处理座位号的问题了。

卷数据库：适当修改细节，将SQL放入doris中运行，观察执行情况，测试环境中，取速度更快的一个。

优化点：hash join+按20批次分组拆分。

SQL编程大赛冠军郭其：1.184 秒夺冠！通过 Oracle 完成百万车票分配的SQL 底层逻辑和算法技巧解读

以下是郭其选手的详细算法说明，结尾附完整SQL：

参赛完整SQL：

with tmp_train as ( select t.*, sum(seat_count) over(partition by departure_station,arrival_station order by train_id)-seat_count seat_count_ac, sum(seat_count*0.1) over(partition by departure_station,arrival_station order by train_id)-seat_count*0.1 no_seat_count_ac, sum(seat_count) over(partition by departure_station,arrival_station) seat_total from train t),tmp_train_seed as( select t1.*,t2.n, case when n*20>seat_count then seat_total+no_seat_count_ac-seat_count else seat_count_ac end seat_tmp from tmp_train t1,(select rownum n from train where rownum<=88) t2 where t1.seat_count*1.1>=t2.n*20) ,passenger_rk as ( select passenger_id, departure_station, arrival_station, row_number() over(partition by departure_station, arrival_station order by null) rk ,--直接顺序号 floor((row_number() over(partition by departure_station, arrival_station order by null)-1)/20)+1 rn --按20一个批次的批次号 from passenger t )select /*+parallel(8)*/ t1.passenger_id,t1.departure_station,t1.arrival_station,t2.train_id, case when t1.rk<=seat_total then floor((t1.rk-seat_count_ac-1)/100)+1 else null end coach_number, case when t2.train_id is null then null when t1.rk<=seat_total then mod(floor((t1.rk-seat_count_ac-1)/5),20)+1 || substr('ABCEF',mod(t1.rk-seat_count_ac-1,5)+1,1) else '无座' end seat_number from passenger_rk t1 left join tmp_train_seed t2 on t1.rn=t2.n+(t2.seat_tmp/20) and t1.arrival_station=t2.arrival_station and t1.departure_station=t2.departure_stationorder by t1.passenger_id

《数据库编程大赛-冠军挑战活动》

时间截止2025年1月5日22:00时

感谢大家对本次《数据库编程大赛》的关注和支持，欢迎加入技术交流群，更多精彩活动不断，欢迎各路数据库爱好者来挑战！