时间: 2025-01-25 05:43:44 | 作者: W系列
2024 第二届数据库编程大赛于 12 月 5 日正式开启初赛！由 NineData 和云数据库技术社区主办，华为云、Doris等协办单位和媒体共同举办。
比赛要求选手设计一套SQL算法，只用一条 SQL 秒杀 100 万张火车票，让乘客都都能顺利坐上火车回家过年。查看赛题详情。在将近两周的赛程报名中，共有数百位数据库爱好者热情参加。选手们展现了极高的参与热情和竞争强度，每天高频次地提交评测，力求在性能上不断突破。
本次大赛的评委，均是数据库领域的领军人物，通过主办方NineData的邀请，组成2024年《数据库编程大赛》强大的评审嘉宾团。
总得分占比 50% ，由组委会给出测试成绩（第一名 50 分，第二名 45 分，第三名 42 分，第四名 40 分，第 5 名 39 分，后面每排名下降一名减少1分，以此类推，最低得 20 分）
经过大赛组委会对参赛数百份SQL代码的初步验证和确认，确保其正确性。在接下来的百万级别性能评测中，我们选出了最强的8位选手，成功晋级到决赛答辩环节。
本次大赛由8位权威评委组成的专家团队对选手的SQL参赛代码和答辩解读进行评分。评委们将着重考察代码的创新性和易读性，并将分别对这两个方面做打分。最终，大赛组委会将根据综合得分，确定选手的最终排名。
1. 车厢分配：通过（乘客编号 - 车次起始乘客编号）÷ 100（车厢座位数）并向上取整来确定。
排数：总体是乘客编号最后两位 ÷5（每排座位数）并向上取整，针对乘客编号为 100 整数倍时结果异常的情况，通过小技巧转换处理。
列数：即具体座位（A,B,C,E,F），通过（乘客编号 - 1）÷5 的余数 + 1 确定，针对乘客编号为 5 整数倍时结果异常的情况，也通过小技巧转换处理。
个人简介：十多年Oracle经验，6年MySQL经验，擅长数据库性能调优、SQL性能优化与索引设计。
1.首先生成列车的车票信息。借用自增数列t_sequence，通过与train连接，生成车厢号、座位号。另外，生成10%的无座车票。为便于对车票排序，额外生成是否有座说明列。
2.以出发站和到达站点对高铁车次分组，并按照是否有座对可售车票进行顺序编号，有座车票编号在前，无座车票编号在后。
4.车票和乘客顺序编号之后，根据出发站、到达站点、顺序编号对可售座票与乘客进行外连接，即完成车票分配。
2. 所有火车按行程规划生成每个座位的供应序列号，先分配有座、再分配无座
1. 构建带座位类型的列车表：复制 train 表，新增 seat_type 列，0 代表有坐票，1 代表无坐票，无坐票数量为有坐票的 10%，新表命名为 train_with_emapy。
3. 在乘客表中，通过窗口计数函数，为 出发站, 到达站 组内的乘客生成从 1 开始的编号 passenger_rid。
4. 根据每个乘客在出发站, 到达站组里的编号里和车次在出发站, 到达站组里的编号范围，求出具体的车次号, 车票类型，车箱号 coach_number 和乘客在这个车次的编号 ans_id, 命名为 result
1. 该需求中主要影响SQL性能的为大表连接和排序，优先分配有票也是与顺序有关，结题中将主要围绕这两点展开：减少表连接行数，减少非必要的排序但要达到排序效果。
2. 选用PostgreSQL解题，PostgreSQL数组包含顺序，将乘客和座位表分别生成两个起始站-终点站聚合 数组，按起始站-终点站关联后展开，即可达到从头对齐后进行批量分配，极大的减少了表连接行数，可明显提升性能。
进阶版相比普通版，主要是对普通版的有票乘客增加了计算票车厢和座位，以及对车次定员额外的 10%增加站票。
将相同（起点站，终点站）的车次分成一个虚拟组，组内各个车次首尾相连，从第1趟车到最后一趟编写座位的总序号，座位总序号减去当前车次之前的座位总数就是在当前车次的座位序号，把当前车次的座位序号减一，得到从 0~本车次座位总和减一的新序号，因每个车厢固定 100 个座位，所以把上述新序号除以100 的商取整再加一就是车厢号。
座位号类似，将新序号除以 5，商为 0 放在第一排，1 放在第二排，以此类推，余数为 0-2 编ABC号，3-4 编 EF号。
1. 因为有10%的站票，而且必须买完所有坐票才能买站票，所以我们应该把座位票和无座票分开来卖。即把一列车变成成两列车卖，一列是有座的票，二列是10%的无座票。
2. 先处理有座的车，根据路径分组，车号排序。计算出每列车的起始编号。按照 200个座位一页拆分。比如800座的火车，拆分成4个页，也就是变成4条记录，以此类推。
3. 拆分后，使用窗口函数进行编号座位页号，根据路径分组，车号排序。因为每页的大小都是一样的。所以 路径+页号 是唯一的。
5. 因为有页号，所以能乘客编号可以对 200 取模，使用JOIN快速映射到记录。
6. 通过这次JOIN后，有座的票就分好了。同时能根据编号减去列车的起始编号，获得列车上的座位序号，然后除法和取余计算出座位号。
7. 将上一步的结果筛选出未分配列车的乘客，和上面一样的方式，只是按照20一页来取模计算页号。因为无座，所以不需要算车厢号，座位号直接标记无座即可。
8. 将结果和之前有座乘客UNION ALL 一下，然后做最终排序就是结果。
第一诫：所有影响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处理座位号的问题了。
本次数据库编程大赛的奖项安排：一等奖(1人）、二等奖(2人）、三等奖(3人)、阳光普照纪念奖（50人）。
本次《数据库编程大赛》前50名完成普通挑战并通过正确性验证的选手，也是会获得一份精美礼品，感谢大家对本次大赛的关注和支持，我们下次再相聚！
NineData是云原生智能数据管理平台，包含SQL开发、数据复制、备份和对比等功能。聚焦在云计算与数据管理基础技术领域，致力于让每人用好数据和云。
上一篇:在线抽奖小程序盈建云点亮你的幸运时刻
