基于配送时间与成本筛选最优承运商：SQL/Pandas实现方案问询

一、Python(Pandas)实现步骤 #

核心是先把非结构化的配送时间转成可量化的数值（比如总小时数），再结合成本计算最优解。

1. 数据预处理（转换配送时间） #

先写个函数把类似"1 day 24:55:38.765958"的字符串转换成总小时数：

import pandas as pd
import re

def convert_delivery_time_to_hours(time_str):
    # 提取天数和时间部分
    day_match = re.search(r'(\d+) day', time_str)
    time_part = re.search(r'(\d+:\d+:\d+\.?\d*)', time_str).group(1)
    
    days = int(day_match.group(1)) if day_match else 0
    # 把时间转成小时
    h, m, s = time_part.split(':')
    total_hours = days * 24 + int(h) + int(m)/60 + float(s)/3600
    return total_hours

# 加载示例数据
data = pd.DataFrame({
    '配送时间': ['1 day 24:55:38.765958', '2 days 13:44:12.084918', '2 days 21:47:49', '2 days 28:21:07.42914'],
    '配送成本': [11.3057, 8.6606, 13.0, 35.5323]
})

# 转换配送时间为总小时数
data['配送时间_小时'] = data['配送时间'].apply(convert_delivery_time_to_hours)

2. 筛选最优方案 #

方式1：加权评分法（自定义权重）

比如业务认为时间和成本同等重要，各占50%权重，先标准化数据再计算评分：

# 数据标准化（缩至0-1区间，值越高越优）
data['时间标准化'] = 1 - (data['配送时间_小时'] - data['配送时间_小时'].min())/(data['配送时间_小时'].max() - data['配送时间_小时'].min())
data['成本标准化'] = 1 - (data['配送成本'] - data['配送成本'].min())/(data['配送成本'].max() - data['配送成本'].min())

# 计算综合评分（权重可根据业务调整）
data['综合评分'] = 0.5 * data['时间标准化'] + 0.5 * data['成本标准化']

# 取评分最高的记录
best_option = data.loc[data['综合评分'].idxmax()]
print("最优承运商方案：")
print(best_option)

方式2：帕累托最优筛选（无更优替代）

找出那些没有其他方案同时比它时间更短、成本更低的记录：

# 筛选帕累托最优集
pareto_optimal = []
for idx, row in data.iterrows():
    # 检查是否存在更优的替代方案
    has_better = ((data['配送时间_小时'] < row['配送时间_小时']) & (data['配送成本'] < row['配送成本'])).any()
    if not has_better:
        pareto_optimal.append(row)

pareto_df = pd.DataFrame(pareto_optimal)
print("帕累托最优方案集：")
print(pareto_df)

二、SQL实现步骤 #

SQL的核心也是先转换配送时间为数值，再筛选最优解，不同数据库语法略有差异，以下以PostgreSQL和MySQL为例：

1. PostgreSQL实现 #

第一步：转换配送时间为总小时数

WITH processed_data AS (
    SELECT
        "配送时间",
        "配送成本",
        -- 提取天数并转小时
        (regexp_match("配送时间", '(\d+) day'))[1]::INT * 24 +
        -- 提取时间部分转小时
        EXTRACT(HOUR FROM (regexp_match("配送时间", '(\d+:\d+:\d+\.?\d*)'))[1]::INTERVAL) +
        EXTRACT(MINUTE FROM (regexp_match("配送时间", '(\d+:\d+:\d+\.?\d*)'))[1]::INTERVAL)/60 +
        EXTRACT(SECOND FROM (regexp_match("配送时间", '(\d+:\d+:\d+\.?\d*)'))[1]::INTERVAL)/3600 AS "配送时间_小时"
    FROM your_table_name
)

第二步：加权评分筛选最优

-- 接上面的CTE
SELECT *
FROM processed_data
ORDER BY (
    0.5 * (1 - ("配送时间_小时" - (SELECT MIN("配送时间_小时") FROM processed_data))/(SELECT MAX("配送时间_小时") - MIN("配送时间_小时") FROM processed_data)) +
    0.5 * (1 - ("配送成本" - (SELECT MIN("配送成本") FROM processed_data))/(SELECT MAX("配送成本") - MIN("配送成本") FROM processed_data))
) DESC
LIMIT 1;

第二步：帕累托最优筛选

SELECT *
FROM processed_data pd1
WHERE NOT EXISTS (
    SELECT 1
    FROM processed_data pd2
    WHERE pd2."配送时间_小时" < pd1."配送时间_小时"
      AND pd2."配送成本" < pd1."配送成本"
);

2. MySQL实现 #

第一步：转换配送时间为总小时数

WITH processed_data AS (
    SELECT
        `配送时间`,
        `配送成本`,
        -- 提取天数并转小时
        CAST(SUBSTRING_INDEX(`配送时间`, ' day', 1) AS UNSIGNED) * 24 +
        -- 提取时间部分转小时
        HOUR(STR_TO_DATE(SUBSTRING_INDEX(`配送时间`, ' ', -1), '%H:%i:%s.%f')) +
        MINUTE(STR_TO_DATE(SUBSTRING_INDEX(`配送时间`, ' ', -1), '%H:%i:%s.%f'))/60 +
        SECOND(STR_TO_DATE(SUBSTRING_INDEX(`配送时间`, ' ', -1), '%H:%i:%s.%f'))/3600 AS `配送时间_小时`
    FROM your_table_name
)

第二步：加权评分或帕累托筛选逻辑和PostgreSQL一致，替换语法即可

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