SQL如何根据乘客到站时间匹配对应公交班次并统计载客数

• 未先对公交和乘客的出发地、目的地做关联过滤，直接cross join会产生大量无效的跨线路匹配数据
• 匹配逻辑完全颠倒：规则是找乘客到站后最早发车的同线路公交，不是用相邻乘客的到站时间区间去套公交发车时间
• LEAD窗口排序仅按pass_no排序，未按出发地、目的地分区，跨线路的乘客时间混在一起计算，结果必然错误

匹配规则：出发地、目的地完全一致，公交发车时间大于等于乘客到站时间，取满足条件的最早发车班次，无匹配班次标记为9999。
实现SQL如下：

SELECT 
    t2.pass_no,
    t2.orgn,
    t2.dest,
    t2.arrvl_tm,
    ISNULL(t1.bus_no, 9999) AS matched_bus_no,
    t1.start_tm
FROM test_t2 t2
OUTER APPLY (
    SELECT TOP 1 bus_no, start_tm
    FROM test_t1
    WHERE orgn = t2.orgn 
      AND dest = t2.dest
      AND start_tm >= t2.arrvl_tm
    ORDER BY start_tm ASC
) t1

匹配结果对应：

• 前往Noida、9:00/9:30到站的乘客匹配1号车（10:00发车）
• 前往Noida、10:30到站的乘客匹配3号车（11:00发车）
• 前往Noida、11:30/12:30/14:00到站的乘客匹配4号车（16:00发车）
• 前往Noida、18:00到站的乘客无后续匹配班次，标记为9999
• 前往Agra、9:00/10:00到站的乘客匹配2号车（10:30发车）
• 前往Agra、12:00到站的乘客无后续匹配班次，标记为9999

基于上述匹配逻辑做聚合统计即可，可同时统计无匹配班次的乘客数量：

SELECT 
    matched_bus_no,
    COUNT(pass_no) AS total_passenger
FROM (
    SELECT 
        t2.pass_no,
        ISNULL(t1.bus_no, 9999) AS matched_bus_no
    FROM test_t2 t2
    OUTER APPLY (
        SELECT TOP 1 bus_no
        FROM test_t1
        WHERE orgn = t2.orgn 
          AND dest = t2.dest
          AND start_tm >= t2.arrvl_tm
        ORDER BY start_tm ASC
    ) t1
) match_result
GROUP BY matched_bus_no

统计结果为：

• 1号车：2人
• 2号车：2人
• 3号车：1人
• 4号车：3人
• 9999（无匹配班次）：2人

如果使用不支持APPLY语法的数据库版本（如低版本MySQL），可替换为窗口函数实现：先关联同线路下发车时间晚于乘客到站时间的所有组合，再按乘客分组取发车时间最小的班次即可。

内容的提问来源于stack exchange，提问作者Koppula