SQL Server按小时/天分组计算订单交付时长滚动平均优化

核心思路 #

先将订单按指定时间粒度（小时/天）聚合，计算每组的平均交付时长，再基于聚合后的少量数据计算滚动平均，大幅减少返回的记录数，避免终端卡顿。通过单一参数即可快速切换分组粒度。

具体实现代码 #

-- 定义参数：修改此处即可切换分组粒度，可选值'Hour'（小时）或'Day'（天）
DECLARE @GroupGranularity VARCHAR(10) = 'Hour';
-- 定义时间范围，按需调整
DECLARE @StartDate DATETIME = '2024-04-22 00:00:00';
DECLARE @EndDate DATETIME = '2024-04-25 00:00:00';

WITH GroupedDeliveryAvg AS (
    SELECT
        -- 根据粒度截断录入时间，生成分组时间戳
        CASE @GroupGranularity
            WHEN 'Hour' THEN DATEADD(HOUR, DATEDIFF(HOUR, 0, EnteredOn), 0)
            WHEN 'Day' THEN DATEADD(DAY, DATEDIFF(DAY, 0, EnteredOn), 0)
        END AS 时间戳,
        -- 计算当前分组的平均交付时长（四舍五入为整数）
        ROUND(AVG(DATEDIFF(MINUTE, EnteredOn, DeliveredTime)), 0) AS 分组平均交付时长
    FROM Orders
    -- 过滤指定时间范围，避免全表扫描
    WHERE EnteredOn BETWEEN @StartDate AND @EndDate
    -- 按截断后的时间分组
    GROUP BY
        CASE @GroupGranularity
            WHEN 'Hour' THEN DATEADD(HOUR, DATEDIFF(HOUR, 0, EnteredOn), 0)
            WHEN 'Day' THEN DATEADD(DAY, DATEDIFF(DAY, 0, EnteredOn), 0)
        END
)
-- 基于分组结果计算滚动平均
SELECT
    时间戳,
    分组平均交付时长,
    ROUND(AVG(分组平均交付时长) OVER (ORDER BY 时间戳), 0) AS 滚动平均交付时长
FROM GroupedDeliveryAvg
ORDER BY 时间戳;

代码说明 #

1. 分组逻辑：使用DATEADD + DATEDIFF实现时间截断，这是SQL Server中高效的时间粒度对齐方式，兼容所有版本；SQL Server 2017+也可以用更简洁的DATE_TRUNC('HOUR', EnteredOn)替代。
2. 参数切换：只需修改@GroupGranularity的值为'Hour'或'Day'，即可快速切换分组粒度，无需调整大量代码。
3. 性能优化：通过先分组聚合减少数据量，滚动平均仅处理分组后的少量记录；配合EnteredOn字段的索引，能大幅提升大时间范围查询的速度。
4. 结果控制：用ROUND函数将平均时长取整，若需保留小数可去掉该函数，或改用CAST(AVG(...) AS DECIMAL(5,1))控制精度。

示例结果（按小时分组） #

（注：示例中数值与需求的差异源于取整方式，若需完全匹配需求中的整数结果，可将ROUND替换为CAST(AVG(...) AS INT)）

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