Oracle SQL中订单运输批次的序列排序逻辑需求

需求说明 #

现有订单ORDER_1，起始地为MFG_1，目的地为PORT_B，包含7个运输批次（Shipment）。核心规则如下：

• 若批次的源地点与目的地点一致，Mode标记为PROCESS（加工维护）
• 序列编号生成规则：
  • 源地点为MFG_1的批次（SHP_A）直接分配序列1
  • 目的地为PORT_B的批次（SHP_G）分配最后一个序列（即7）
  • 中间批次排序逻辑：以上一批次的目的地点作为目标源地点，优先排序该源地点的批次；若同一源地点存在多个批次，PROCESS模式的批次优先级高于普通运输批次

示例数据 #

预期序列结果 #

实现步骤 #

1. 锚定首尾批次：

   • 给SHP_A分配序列1，记录其目的地点WH_C为下一批次的目标源地点
   • 预留SHP_G为最后一个序列（7）

2. 迭代排序中间批次：

   • 从当前目标源地点筛选所有匹配的批次
   • 对筛选出的批次排序：PROCESS模式批次优先于普通运输批次
   • 依次分配序列号，每分配一个批次，就将该批次的目的地点更新为新的目标源地点
   • 重复上述操作，直到所有中间批次完成排序

3. 补全末尾批次：

   • 给SHP_G分配最后一个序列号

代码实现示例（Python） #

# 模拟批次数据
shipments = [
    {"id": "SHP_A", "source": "MFG_1", "dest": "WH_C", "mode": "TRANSPORT"},
    {"id": "SHP_B", "source": "WH_C", "dest": "WH_C", "mode": "PROCESS"},
    {"id": "SHP_C", "source": "WH_C", "dest": "WH_D", "mode": "TRANSPORT"},
    {"id": "SHP_D", "source": "WH_D", "dest": "WH_E", "mode": "TRANSPORT"},
    {"id": "SHP_E", "source": "WH_E", "dest": "WH_E", "mode": "PROCESS"},
    {"id": "SHP_F", "source": "WH_E", "dest": "PORT_B", "mode": "TRANSPORT"},
    {"id": "SHP_G", "source": "PORT_B", "dest": "PORT_B", "mode": "PROCESS"},
]

# 存储最终序列结果
sequence_map = {}
total_count = len(shipments)

# 处理起始批次
start_batch = next(b for b in shipments if b["source"] == "MFG_1")
sequence_map[start_batch["id"]] = 1
current_source = start_batch["dest"]

# 筛选需要排序的中间批次（排除首尾）
remaining_batches = [b for b in shipments if b["id"] not in [start_batch["id"], "SHP_G"]]

current_seq = 2
while remaining_batches:
    # 筛选当前源地点对应的批次
    candidates = [b for b in remaining_batches if b["source"] == current_source]
    # 按优先级排序：PROCESS模式优先
    candidates_sorted = sorted(candidates, key=lambda x: 0 if x["mode"] == "PROCESS" else 1)
    # 分配序列并更新当前源地点
    for batch in candidates_sorted:
        sequence_map[batch["id"]] = current_seq
        current_seq += 1
        current_source = batch["dest"]
        remaining_batches.remove(batch)

# 处理末尾批次
sequence_map["SHP_G"] = total_count

# 按序列顺序输出结果
for batch_id in sorted(sequence_map.keys(), key=lambda x: sequence_map[x]):
    print(f"{batch_id}: 序列编号 {sequence_map[batch_id]}")

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