SQL编程大师-卢涛：1.299 秒的背后，如何用 Doris 突破百万级车票分配的 SQL 性能极限
云
卢涛约 3 分钟
卢涛约 3 分钟数据库编程大赛：只用一条 SQL 秒杀 100 万张火车票
2024 第二届数据库编程大赛于 12 月 5 日正式开启初赛！由 NineData 和云数据库技术社区主办，华为云、Doris等协办单位和媒体共同举办。比赛要求选手设计一套SQL算法，只用一条 SQL 秒杀 100 万张火车票，让乘客都都能顺利坐上火车回家过年。查看赛题详情
以下是本次决赛第8名，SQL编程大师卢涛的参赛介绍：
进阶版相比普通版，主要是对普通版的有票乘客增加了计算票车厢和座位，以及对车次定员额外的 10%增加站票。
1. 有座票车厢和座位的计算方法：
将相同（起点站，终点站）的车次分成一个虚拟组，组内各个车次首尾相连，从第1趟车到最后一趟编写座位的总序号，座位总序号减去当前车次之前的座位总数就是在当前车次的座位序号，把当前车次的座位序号减一，得到从 0~本车次座位总和减一的新序号，因为每个车厢固定 100 个座位，所以把上述新序号除以100 的商取整再加一就是车厢号。
座位号类似，将新序号除以 5，商为 0 放在第一排，1 放在第二排，以此类推，余数为 0-2 编ABC号，3-4 编 EF号。
2. 无座票的序号和车厢计算方法：
将相同（起点站，终点站）的虚拟组座位总数除以 10，就是无座票的总张数，序号从虚拟组座位总数加一
开始编，每达到一个车次的额定人数的十分之一，就换下一个车次。无座票的座位统一填写‘无座’，车厢号为空。
以下是卢涛选手的详细算法说明，结尾附完整SQL：
with p as (selectpassenger_id,departure_station,arrival_station,row_number() over (partition bydeparture_station,arrival_station) sidfrompassenger),t0 as (selecttrain_id,departure_station,arrival_station,seat_count,sum(seat_count) over (partition bydeparture_station,arrival_stationorder bytrain_id) sum_sid,sum(seat_count) over (partition bydeparture_station,arrival_station) sum_d_afromtrain),t as(selecttrain_id,departure_station,arrival_station,sum_sid,sum_sid-seat_count+1 start_sid,sum_d_a+(sum_sid-seat_count) div 10 +1 start_stand_id,sum_d_a+ sum_sid div 10 end_stand_idfromt0)selectpassenger_id,p.departure_station,p.arrival_station,train_id,case when train_id is not null then case when p.sid between start_sid and sum_sid then cast((sid-start_sid) div 100 +1 as varchar) else null end else null end coach_number ,case when train_id is not null then case when p.sid between start_sid and sum_sid then concat(cast((sid-1)%100 div 5 +1 as varchar), substr('ABCEF',(sid-1)%5+1,1)) else '无座' end else null end seat_numberfrompleft join t on p.departure_station=t.departure_station and p.arrival_station=t.arrival_station and(p.sid between start_sid and sum_sidor p.sid between start_stand_id and end_stand_id)order by passenger_id;