SQL编程大赛冠军-郭其：1.184 秒夺冠！通过 Oracle 完成百万车票分配的SQL 底层逻辑和算法技巧解读
郭其约 4 分钟数据库编程大赛：只用一条 SQL 秒杀 100 万张火车票
2024 第二届数据库编程大赛于 12 月 5 日正式开启初赛！由 NineData 和云数据库技术社区主办，华为云、Doris等协办单位和媒体共同举办。比赛要求选手设计一套SQL算法，只用一条 SQL 秒杀 100 万张火车票，让乘客都都能顺利坐上火车回家过年。查看赛题详情
以下是本次决赛冠军选手，郭其的参赛介绍：
第一诫：所有影响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批次分组拆分。
with tmp_train as (selectt.*,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_totalfrom train t),tmp_train_seed as(select t1.*,t2.n,case when n*20>seat_count then seat_total+no_seat_count_ac-seat_countelse seat_count_ac end seat_tmpfrom tmp_train t1,(select rownum n from train where rownum<=88) t2where t1.seat_count*1.1>=t2.n*20),passenger_rk as (selectpassenger_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)+1else null end coach_number,case when t2.train_id is null then nullwhen 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_numberfrom passenger_rk t1 left join tmp_train_seed t2on 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