SQL编程大师-周仟荣：从数组巧用到性能飞升，如何用 PostgreSQL 一条 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;   ---数字排序效率较高