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

以下是本次决赛第6名，大赛三等奖获得者程宁的参赛介绍：

参赛选手：程宁

个人简介：嘉兴市第二医院信息科，从事信息化工作多年

参赛数据库：ORACLE

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

综合得分：78.25

以下是程宁选手的代码说明思路简介：

以下是程宁选手的代码说明思路简介：

1. 每个乘客按行程规划分组生成需求序列号；

2. 所有火车按行程规划生成每个座位的供应序列号，先分配有座、再分配无座

3. 行程相同两个序列号相同，生成车票分配方案

优化措施：

• 增加一个行程方案，降低座位序列号计算量；用行程方案ID关联，降低最后JOIN关联字段消耗；
• 对火车进行预测，尽量少生成火车坐席；
• 添加了一个测试参数，可以将原始数据扩大N倍，用于性能测试；
• 加Hint /*+ PARALLEL(8) */ ，官方测试环境4C8G，网上查了一下，据说ORACLE默认安装的时候每核2个线程，故参数设置为8，榨干服务器。

算法说明

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

参赛完整SQL：

with  
/*参数表：1600个数字*/
  seat_id(n) AS (
    select ROW_NUMBER()  OVER(order by passenger_ID ) n 
    from passenger WHERE rownum<=1600),
 /*测试参数，在有限数据情况下，扩大测试倍数至第二阶段*/
  test_train(n) as(
   select ROW_NUMBER()  OVER(order by passenger_ID ) n 
    from passenger WHERE rownum<=1
  ),
  test_passenger(n) as(
    select ROW_NUMBER()  OVER(order by passenger_ID ) n 
    from passenger WHERE rownum<=1
  ),
/*计划表，用于减少检索数据量*/
 PlanS as (
      SELECT ROW_NUMBER() OVER(order by departure_station,arrival_station) Plan_id,
      departure_station,arrival_station,n_max 
      FROM (
        SELECT  
            departure_station,arrival_station,count(1) n_max
        FROM passenger a ,test_passenger b
        GROUP BY departure_station,arrival_station ) aa ),
/*需求表：为每个乘客生成一个需求号*/
needs as (
  select   a.passenger_id,c.Plan_id ,a.departure_station,a.arrival_station,
  ROW_NUMBER() OVER(PARTITION BY Plan_id ORDER BY Plan_id,passenger_id)  AS row_num
  from passenger a,test_passenger b,PlanS c 
  where a.departure_station=c.departure_station and a.arrival_station=c.arrival_station 
),
/*以下代码用于预测需要哪些火车生成座位信息，降低最后join计算关联的数据量*/
v_train0 AS 
    (SELECT a.train_id,c.Plan_id,a.seat_count ,b.n,c.n_max,1 px
    from train a,test_train b,PlanS c
    where a.departure_station=c.departure_station and a.arrival_station=c.arrival_station
    UNION ALL 
    SELECT a.train_id,c.Plan_id,a.seat_count ,b.n,c.n_max,2 px
    from train a,test_train b,PlanS c
    where a.departure_station=c.departure_station and a.arrival_station=c.arrival_station),
v_train1 as 
  (select a.train_id,a.Plan_id,a.seat_count ,a.n,a.n_max,a.px,
      SUM(CASE a.px WHEN 1 THEN a.seat_count ELSE a.seat_count * 0.1 END ) OVER (PARTITION BY a.Plan_id  order by a.px,a.seat_count desc) seat_count_all
   from v_train0 a),
v_train_min AS 
    (SELECT plan_id,min(seat_count_all) seat_count_all_min 
    FROM v_train1 
    WHERE seat_count_all >= n_max 
    GROUP BY plan_id),
v_train AS 
    (SELECT a.train_id,a.plan_id,a.px,a.seat_count 
        FROM v_train1 a WHERE a.seat_count_all < n_max
    UNION all
     SELECT a.train_id,a.plan_id,a.px,a.seat_count 
         FROM v_train1 a,v_train_min b 
         WHERE a.plan_id = b.plan_id AND a.seat_count_all <= b.seat_count_all_min AND a.seat_count_all>=n_max),
/*座位表：按有座、无座分别生成所有坐席*/
allzuowei as(
  select a.train_id,
    a.plan_id,
    px,
    b.n seat_id
    from v_train a,seat_id b
    where a.px = 1 AND b.n<=a.seat_count
  union all
    select a.train_id,
      a.plan_id,
      px,
      b.n seat_id
      from v_train a,seat_id b
    where a.px = 2 AND b.n<=a.seat_count/10
  ),
/*供应表：按题目要求分配一个流水号*/
stocks as(
  select   a.train_id,a.plan_id,a.px,a.seat_id,
  ROW_NUMBER() OVER(PARTITION BY plan_id ORDER BY plan_id,px)  AS row_num
  from allzuowei a
)
/*供需关联得到答案,*/
    select  /*+ PARALLEL(8) */ 
    a.passenger_id,
    a.departure_station,
    a.arrival_station,
    b.train_id,
    case b.px when 1 then CEIL(b.seat_id/100) end  coach_number  ,
    case b.px when 1 then 
      FLOOR((b.seat_id- FLOOR(b.seat_id/100) * 100) /5) + 1 ||
      case MOD(b.seat_id,5)  
        when 0 then 'F' when 1 then 'A' when 2 then 'B' when 3 then 'C' when 4 then 'E' end
    when 2 then  '无座' end seat_number 
    from needs a
    left OUTER join stocks b on a.plan_id=b.plan_id and a.row_num=b.row_num
  order by a.passenger_id 

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

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

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