如何用SQL查找每条公路车流量最高的5英里路段

现有一张Highway表，包含道路名称、起始英里数、结束英里数、每英里车流量字段，需要为每条公路找出总车流量最高的5英里连续路段。具体场景为：多条公路已被划分为带每英里车流量的路段，需定位每条公路上车流量最大的5英里区间。

示例数据表 #

建表与插入数据SQL语句 #

CREATE TABLE Highway (Road varchar(255), MileStart decimal(10,2), MileEnd decimal(10,2), CarsPerMile decimal(10,2));

INSERT INTO Highway VALUES ('A',0,1, 10), ('A',1,3, 50), ('A',3,8.5,20);

预期查询结果示例 #

解决方案 #

实现思路 #

核心逻辑是遍历每条公路的所有潜在5英里区间，计算每个区间的总车流量，再筛选出每条公路中总流量最高的区间。具体分为四步：

1. 生成所有可能的区间起始点，覆盖原始路段的起止位置，避免遗漏跨路段的最优区间
2. 过滤无效区间（长度不合理的情况）
3. 计算每个有效区间的总车流量与平均流量
4. 按道路分组，筛选出总流量排名第一的区间

具体SQL代码 #

WITH RoadSegments AS (
    -- 生成所有可能的区间起始点：原始路段的起止点
    SELECT 
        Road,
        MileStart AS IntervalStart,
        LEAST(MileStart + 5, MAX(MileEnd) OVER (PARTITION BY Road)) AS IntervalEnd
    FROM Highway
    UNION ALL
    SELECT 
        Road,
        MileEnd AS IntervalStart,
        LEAST(MileEnd + 5, MAX(MileEnd) OVER (PARTITION BY Road)) AS IntervalEnd
    FROM Highway
),
ValidIntervals AS (
    -- 过滤无效区间，保留长度合理的记录
    SELECT 
        Road,
        IntervalStart,
        IntervalEnd,
        IntervalEnd - IntervalStart AS IntervalLength
    FROM RoadSegments
    WHERE IntervalEnd - IntervalStart >= 0
    GROUP BY Road, IntervalStart, IntervalEnd
),
IntervalTraffic AS (
    -- 计算每个区间的总车流量和平均流量
    SELECT 
        vi.Road,
        vi.IntervalStart,
        vi.IntervalEnd,
        SUM(
            -- 计算原始路段与目标区间的重叠长度，乘以每英里车流量
            (LEAST(h.MileEnd, vi.IntervalEnd) - GREATEST(h.MileStart, vi.IntervalStart)) * h.CarsPerMile
        ) AS TotalTraffic,
        SUM(
            (LEAST(h.MileEnd, vi.IntervalEnd) - GREATEST(h.MileStart, vi.IntervalStart)) * h.CarsPerMile
        ) / 5 AS AvgCarsPerMile
    FROM ValidIntervals vi
    JOIN Highway h ON vi.Road = h.Road 
        AND h.MileStart < vi.IntervalEnd 
        AND h.MileEnd > vi.IntervalStart
    GROUP BY vi.Road, vi.IntervalStart, vi.IntervalEnd
),
RankedIntervals AS (
    -- 按道路分组，对区间按总车流量降序排名
    SELECT 
        Road,
        IntervalStart AS 起始英里数,
        IntervalEnd AS 结束英里数,
        ROUND(AvgCarsPerMile, 2) AS 平均每英里车流量,
        RANK() OVER (PARTITION BY Road ORDER BY TotalTraffic DESC) AS TrafficRank
    FROM IntervalTraffic
)
-- 筛选每条公路总流量最高的区间（并列区间全部返回）
SELECT 
    Road AS 道路,
    起始英里数,
    结束英里数,
    平均每英里车流量
FROM RankedIntervals
WHERE TrafficRank = 1;

示例数据验证 #

针对道路A的示例数据，区间1-6的总流量计算为：(3-1)*50 + (6-3)*20 = 100 + 60 = 160，平均流量为160/5=32，与预期结果完全匹配。

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