基于SQL计算交付业务时长并按最终交付日期聚合求均值

需求与核心规则 #

• 需求：计算每批货物的有效交付工作时长，再按最终交付时间的日期分组，统计每日的平均交付时长
• 核心业务规则：
  • 仅统计周一至周五（WEEKDAY 0=周一，4=周五）的9:00-18:00时段
  • 初始交付时间在非业务时段：从进入下一个业务时段开始计时
  • 最终交付时间在非业务时段：到上一个业务时段结束停止计时
  • 跨天/跨工作日的订单，需分段计算每日有效时长后累加

原SQL的问题 #

原查询用UNION拼接的逻辑完全错误，无法处理跨天、非工作日、首尾时段在非业务时间的场景，也未实现按最终交付日期的聚合逻辑。

解决方案（适配Metabase无变量限制） #

以下以MySQL为例，通过递归CTE生成日期范围，分段计算每日有效时长，最终实现按日聚合的平均时长统计：

WITH date_range AS (
    -- 递归生成每个订单覆盖的所有日期
    SELECT 
        data_inicial,
        data_final,
        DATE(data_inicial) AS current_date
    FROM your_table
    UNION ALL
    SELECT 
        data_inicial,
        data_final,
        DATE_ADD(current_date, INTERVAL 1 DAY) AS current_date
    FROM date_range
    WHERE current_date < DATE(data_final)
),
daily_working_hours AS (
    -- 计算每个订单在单日的有效工作时长
    SELECT 
        dr.data_inicial,
        dr.data_final,
        -- 仅工作日计算有效时长
        CASE 
            WHEN WEEKDAY(dr.current_date) BETWEEN 0 AND 4 THEN
                TIMESTAMPDIFF(
                    HOUR,
                    -- 取初始时间与当日上班时间的较大值作为实际开始点
                    GREATEST(dr.data_inicial, TIMESTAMP(dr.current_date, '09:00:00')),
                    -- 取最终时间与当日下班时间的较小值作为实际结束点
                    LEAST(dr.data_final, TIMESTAMP(dr.current_date, '18:00:00'))
                )
            ELSE 0 -- 非工作日时长记为0
        END AS daily_hours
    FROM date_range dr
    -- 过滤掉无有效时长的记录
    WHERE GREATEST(dr.data_inicial, TIMESTAMP(dr.current_date, '09:00:00')) < LEAST(dr.data_final, TIMESTAMP(dr.current_date, '18:00:00'))
),
order_total_hours AS (
    -- 累加每个订单的总有效时长
    SELECT 
        data_inicial,
        data_final,
        SUM(daily_hours) AS total_working_hours
    FROM daily_working_hours
    GROUP BY data_inicial, data_final
)
-- 按最终交付日期分组，计算每日平均交付时长
SELECT 
    DATE(data_final) AS delivery_date,
    AVG(total_working_hours) AS avg_delivery_hours
FROM order_total_hours
GROUP BY delivery_date
ORDER BY delivery_date;

注意事项 #

1. 替换your_table为实际表名
2. 若Metabase连接PostgreSQL，需调整部分语法：
   • 日期增量改为current_date + INTERVAL '1 day'
   • 时间戳拼接改为current_date + TIME '09:00:00'
   • 时长计算改为EXTRACT(EPOCH FROM (LEAST(...) - GREATEST(...))) / 3600
3. 大数据量场景下，可提前创建日期维度表替代递归CTE，提升查询性能

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