Order by内自定义函数：按日期和模块计算区块周转平均时长

我明白你需要按模块（module）和日期分组，计算每个组内区块的平均周转时长——也就是后一个区块的start与前一个区块的end的时间差的平均值。这个需求可以通过SQL的窗口函数轻松实现，下面是具体的解决方案：

核心思路 #

1. 给每个模块+日期组内的区块按start时间排序，用LAG()函数获取前一个区块的结束时间prev_end；
2. 计算当前区块与前一个区块的时间差（转成分钟）；
3. 分组过滤掉无前置区块的记录，最终计算每个组的平均周转时长。

完整SQL查询（以MySQL为例） #

WITH block_intervals AS (
    SELECT
        module,
        DATE(start) AS day,
        -- 获取同组内上一个区块的结束时间
        LAG(`end`) OVER (
            PARTITION BY module, DATE(start)
            ORDER BY start
        ) AS prev_end,
        -- 计算周转时长（单位：分钟）
        TIMESTAMPDIFF(MINUTE, LAG(`end`) OVER (
            PARTITION BY module, DATE(start)
            ORDER BY start
        ), start) AS turnover_minutes
    FROM blocks
)
SELECT
    module,
    day,
    -- 计算平均周转时长，组内只有1条记录时返回NULL
    CASE
        WHEN COUNT(turnover_minutes) = 0 THEN NULL
        ELSE ROUND(AVG(turnover_minutes), 0) -- 可选：四舍五入到整数
    END AS turnover_avg_minutes,
    -- 可选：转换成友好的小时/分钟格式
    CASE
        WHEN AVG(turnover_minutes) IS NULL THEN NULL
        ELSE CONCAT(
            FLOOR(AVG(turnover_minutes)/60), 'hour',
            IF(MOD(AVG(turnover_minutes),60) > 0, CONCAT(' ', MOD(AVG(turnover_minutes),60), 'min'), '')
        )
    END AS turnover_avg_formatted
FROM block_intervals
GROUP BY module, day
ORDER BY module, day;

代码解释 #

• CTE block_intervals：

  • PARTITION BY module, DATE(start)：确保只在同一个模块、同一天的区块内计算间隔；
  • LAG(end)：获取当前区块的上一个区块的end时间，组内第一条记录的prev_end为NULL，对应的turnover_minutes也为NULL；
  • TIMESTAMPDIFF(MINUTE, prev_end, start)：直接计算两个时间的分钟差，比手动计算更简洁。

• 主查询：

  • 按module和day分组，用AVG(turnover_minutes)计算平均周转时长；
  • 通过CASE处理组内只有1条记录的情况（此时没有有效差值，返回NULL）；
  • 可选的格式化字段turnover_avg_formatted会把分钟转换成类似1hour 30min的友好格式，和你示例中的输出风格一致。

验证示例数据 #

运行上述查询后，会得到和你期望一致的结果：

如果使用其他数据库（比如PostgreSQL），只需要调整时间差的计算方式，例如用EXTRACT(EPOCH FROM (start - prev_end)) / 60代替TIMESTAMPDIFF即可。

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