如何计算流程起止的非周末有效工作时长（小时）？

第一步：提取每个流程的起止时间 #

首先从表1中拆分出每个deal的开始、结束时间（假设status_id=1代表流程启动，status_id=2代表流程结束）：

WITH deal_time AS (
    SELECT 
        deal_id,
        MAX(CASE WHEN status_id = 1 THEN created_at END) AS start_time,
        MAX(CASE WHEN status_id = 2 THEN created_at END) AS end_time
    FROM table1
    GROUP BY deal_id
    HAVING start_time IS NOT NULL AND end_time IS NOT NULL
)

第二步：计算非周末时长 #

通过生成起止时间范围内的所有日期，关联表2标记周末，再分别计算各时间段的周末时长，最终用总时长扣除周末时长得到结果：

SELECT 
    dt.deal_id,
    -- 计算总时长（小时）
    EXTRACT(EPOCH FROM (dt.end_time - dt.start_time)) / 3600 AS total_hours,
    -- 计算周末总时长（小时）
    SUM(
        CASE 
            -- 起止时间在同一天且当天是周末
            WHEN dt.start_time::DATE = dt.end_time::DATE AND t.type = 'hol' 
            THEN EXTRACT(EPOCH FROM (dt.end_time - dt.start_time)) / 3600
            -- 开始当天是周末，计算从开始到当天结束的时长
            WHEN date_range.dt = dt.start_time::DATE AND t.type = 'hol'
            THEN EXTRACT(EPOCH FROM (DATE_TRUNC('day', dt.start_time) + INTERVAL '1 day' - dt.start_time)) / 3600
            -- 结束当天是周末，计算从当天开始到结束的时长
            WHEN date_range.dt = dt.end_time::DATE AND t.type = 'hol'
            THEN EXTRACT(EPOCH FROM (dt.end_time - DATE_TRUNC('day', dt.end_time))) / 3600
            -- 中间完整的周末日期，按24小时计算
            WHEN t.type = 'hol'
            THEN 24
            ELSE 0
        END
    ) AS weekend_hours,
    -- 非周末时长 = 总时长 - 周末时长
    (EXTRACT(EPOCH FROM (dt.end_time - dt.start_time)) / 3600) - 
    SUM(
        CASE 
            WHEN dt.start_time::DATE = dt.end_time::DATE AND t.type = 'hol' 
            THEN EXTRACT(EPOCH FROM (dt.end_time - dt.start_time)) / 3600
            WHEN date_range.dt = dt.start_time::DATE AND t.type = 'hol'
            THEN EXTRACT(EPOCH FROM (DATE_TRUNC('day', dt.start_time) + INTERVAL '1 day' - dt.start_time)) / 3600
            WHEN date_range.dt = dt.end_time::DATE AND t.type = 'hol'
            THEN EXTRACT(EPOCH FROM (dt.end_time - DATE_TRUNC('day', dt.end_time))) / 3600
            WHEN t.type = 'hol'
            THEN 24
            ELSE 0
        END
    ) AS non_weekend_hours
FROM deal_time dt
-- 生成起止时间之间的所有日期
CROSS JOIN LATERAL (
    SELECT generate_series(
        dt.start_time::DATE, 
        dt.end_time::DATE, 
        INTERVAL '1 day'
    )::DATE AS dt
) date_range
-- 关联表2判断日期类型
LEFT JOIN table2 t ON date_range.dt = t.dt
GROUP BY dt.deal_id, dt.start_time, dt.end_time;

适配不同SQL方言 #

如果使用MySQL（不支持LATERAL和generate_series），可以用递归CTE生成日期范围：

WITH RECURSIVE date_range AS (
    SELECT start_time::DATE AS dt, end_time, deal_id
    FROM deal_time
    UNION ALL
    SELECT dt + INTERVAL 1 DAY, end_time, deal_id
    FROM date_range
    WHERE dt < end_time::DATE
)

替换原SQL中的CROSS JOIN LATERAL部分即可。

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