如何查询给定速度所在区间的上下两行汽车油耗数据？

要实现针对指定车辆和车速，通过数据表中相邻区间的油耗值计算目标车速的油耗（替代存储过程），我们可以借助CTE（公共表表达式）编写SQL查询，即使数据表的id无有效排序、speed并非严格递增也能正常工作。

核心思路 #

1. 定义目标参数：指定待计算的car_id和目标车速target_speed
2. 获取下限行：找到该车辆中小于等于目标车速的最大speed对应的油耗数据
3. 获取上限行：找到该车辆中大于等于目标车速的最小speed对应的油耗数据
4. 插值计算：若目标车速恰好匹配现有数据则直接返回对应油耗，否则通过线性插值计算目标油耗

完整SQL查询 #

WITH target_params AS (
    -- 替换为你的目标车辆ID和车速
    SELECT 2 AS car_id, 12 AS target_speed
),
lower_bound AS (
    SELECT speed, fuel
    FROM your_table t
    JOIN target_params tp ON t.car_id = tp.car_id
    ORDER BY 
        -- 优先选择<=目标车速的记录，再按车速降序取最大的那个
        CASE WHEN t.speed <= tp.target_speed THEN 0 ELSE 1 END,
        t.speed DESC
    LIMIT 1
),
upper_bound AS (
    SELECT speed, fuel
    FROM your_table t
    JOIN target_params tp ON t.car_id = tp.car_id
    ORDER BY 
        -- 优先选择>=目标车速的记录，再按车速升序取最小的那个
        CASE WHEN t.speed >= tp.target_speed THEN 0 ELSE 1 END,
        t.speed ASC
    LIMIT 1
)
SELECT 
    tp.car_id,
    tp.target_speed,
    ROUND(
        CASE
            -- 目标车速匹配现有数据时直接返回对应油耗
            WHEN lb.speed = ub.speed THEN lb.fuel
            -- 线性插值公式：下限油耗 + (目标车速-下限车速)*(上限油耗-下限油耗)/(上限车速-下限车速)
            ELSE lb.fuel + (tp.target_speed - lb.speed) * (ub.fuel - lb.fuel)::FLOAT / (ub.speed - lb.speed)
        END,
        2 -- 结果保留两位小数（可选）
    ) AS interpolated_fuel
FROM target_params tp
CROSS JOIN lower_bound lb
CROSS JOIN upper_bound ub;

关键部分说明 #

• target_params：存储待计算的车辆ID和目标车速，可直接替换值或通过应用程序传入参数
• lower_bound/upper_bound：通过自定义排序规则，确保即使车速无序也能准确找到相邻区间的上下限数据，同时处理目标车速超出现有数据范围的边界情况
• 插值计算：使用标准线性插值公式，保证计算结果的准确性

示例输出 #

针对car_id=2和target_speed=12，查询结果为：

该结果与手动计算一致：14 + (12-10)*(20-14)/(15-10) = 16.4

注意事项 #

• 将your_table替换为实际的数据表名称
• 根据SQL方言调整浮点转换方式（如MySQL使用CAST( ... AS FLOAT)）
• ROUND函数为可选，用于优化结果可读性

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