如何编写SQL计算一组路段合并后的每小时平均行程时间

问题根源 #

你当前的SQL逻辑存在两个核心问题，导致结果不符合预期：

1. CASE WHEN ... ELSE 0的写法会把非目标路段的行程时间按0计入均值，直接拉低了最终计算结果
2. 你直接对单个路段的行程时间取平均，得到的是单路段的平均耗时，而非全部目标路段总耗时的平均值

解决思路 #

你需要先把同一观测时间点下所有目标路段的行程时间求和，得到单次通行全路段的总耗时，再按小时维度对这些总耗时取平均，才能得到你要的结果。

修改后的SQL语句 #

SELECT
    DATEPART(YEAR, hour_group) AS "YEAR",
    DATEPART(MONTH, hour_group) AS "MONTH",
    DATEPART(DD, hour_group) AS "DAY",
    DATEPART(HH, hour_group) AS "HOUR",
    AVG(total_travel_time) AS "AVERAGE TRAVEL TIME"
FROM (
    -- 子查询：先计算每个观测时间点的全路段总行程时间
    SELECT
        RT1.[Segment_Date],
        -- 按小时取整用于后续分组
        DATEADD(HOUR, DATEDIFF(HOUR, 0, RT1.[Segment_Date]), 0) AS hour_group,
        SUM([TravelTimeMinutes]) AS total_travel_time
    FROM [Source].[dbo].[Source] RT1
    WHERE
        RT1.[Segment_Date] >= @StartDateTime
        AND RT1.[Segment_Date] < @EndDateTime
        -- 直接筛选目标路段，避免后续CASE判断的误差
        AND RT1.Code IN (
            '149242059',
            '393397506',
            '393507671',
            '429235061',
            '429235101',
            '429235108',
            '429235159',
            '429235160',
            '429235161',
            '429255654',
            '429255655'
        )
    GROUP BY RT1.[Segment_Date]
) AS daily_total
GROUP BY hour_group
ORDER BY hour_group

注意事项 #

如果你的数据存在「同一观测时间点部分路段无上报数据」的情况，可以根据业务需要在子查询中增加判断，比如仅当11个目标路段都有数据时才计入统计，避免总耗时计算不全的问题。

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