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

以下是本次决赛第4名，SQL编程大师周仟荣的参赛介绍：

参赛选手：周仟荣

选手简介：数据分析师，来自东风日产数据服务有限公司

参赛数据库：PostgreSQL

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

综合得分：83

以下是周仟荣选手的代码说明思路简介：

1. 该需求中主要影响SQL性能的为大表连接和排序，优先分配有票也是与顺序有关，结题中将主要围绕这两点展开：减少表连接行数，减少非必要的排序但要达到排序效果。

2. 选用PostgreSQL解题，PostgreSQL数组包含顺序，将乘客和座位表分别生成两个起始站-终点站聚合 数组，按起始站-终点站关联后展开，即可达到从头对齐后进行批量分配，极大的减少了表连接行数，可显著提升性能。

步骤（对应第N个CTE）：

• 将乘客按起始站和终点站分组并聚合为数组
• 为每列车的座位编号生成车厢和座位信息：
• 座位信息按起始站-终点站聚合为数组：
• 合并乘客和座位信息：
• 返回最终的座位分配结果：
• 最终输出

算法说明

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

参赛完整SQL：

with temp_passenger_array as (
    select
        a.departure_station,
        a.arrival_station,
        array_agg(a.passenger_id) passenger_array
        --将乘客清单转换为数组，后续只进行站点的关联以提升关联性能，乘客与座位将由数组展开按长度批量匹配，超出乘客数组长度的座位表将被截去
    from
        passenger a
    group by 1,2 ),
    temp_train_list as(
    select
        a.departure_station,
        a.arrival_station,
        (select string_agg(concat_ws('-',a.train_id,(i.rn - 1) / 100 + 1,( (i.rn - 1) % 100 ) / 5 + 1 || ('{A,B,C,E,F}'::varchar[])[(i.rn - 1) % 5 + 1]),
                           ',') from generate_series(1, a.seat_count) i(rn)) seat_list,
        --各车厢分配座位号（含车次、车厢、座位），组成逗号分隔，待下一步构建数组
        (select string_agg(concat_ws('-',a.train_id,j.rn*0,'无座'), ',') from generate_series(1, a.seat_count*0.1) j(rn)) seat_list_no
        --各车厢座位数的10%分配无座，组成逗号分隔，待下一步构建数组
    from
        train a),
    temp_train_array as (
    select
        a.departure_station,
        a.arrival_station,
        string_to_array(string_agg(a.seat_list,','),',') ||string_to_array(string_agg(a.seat_list_no,','),',') seat_array
        --1.各车厢座位列表按站点分组后构建数组；2.无座放在数组后面，以优先分配有座座位
    from temp_train_list a
    group by 1,2 ),
    temp_passenger_x_train as(
    select
        a.departure_station,
        a.arrival_station,
        unnest(a.passenger_array) passenger_id,  --乘客数组展开
        string_to_array(unnest(b.seat_array),'-') seat_no
        --1.先将车次车厢座位数组展开（超出乘客数组的将下一步被截去）；2.按-号生成新的座位数组（含1条车次、车厢、座位信息），方便下一步字段拆分
    from
    temp_passenger_array a        --乘客数组
    left join temp_train_array b  --按起始站-终点站分组的车次车厢座位数组
    on a.departure_station=b.departure_station and a.arrival_station=b.arrival_station
    )
 select
     a.passenger_id,
     a.departure_station,
     a.arrival_station,
     a.seat_no[1] train_id,                                                      --座位数组中的车次号
     case when a.seat_no[2]='0' then null else a.seat_no[2] end  coach_number,   --座位数组中的车厢号，含无座
     a.seat_no[3] seat_number                                                    --座位数组中的座位号，含无座
 from temp_passenger_x_train a
 where a.passenger_id is not null     --只保留乘客部分，截去未分配乘客的座位
order by substring(a.passenger_id,'\d+')::int4;   ---数字排序效率较高

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

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

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