基于default_appointments与actual_appointments表计算预约计划与实际时长

核心思路 #

• 第一步：生成你需要统计的时间范围内的连续日期序列，保证所有需要展示的日期都被覆盖，包括只有计划没有实际预约的日期
• 第二步：计算每日计划总时长：将日期序列和default_appointments按星期匹配，累加同一用户同一日期下所有规则的时长，得到total_planned_minutes
• 第三步：计算每日实际总时长：从actual_appointments中按日期、用户分组，累加所有预约的时长，得到total_actual_minutes
• 第四步：将两组统计结果按用户、日期关联，缺失值补0即可得到最终结果

示例SQL（MySQL 8.0+ 版本） #

WITH RECURSIVE date_range AS (
    -- 此处可自定义统计的起止日期
    SELECT '2021-09-01' AS appointment_date
    UNION ALL
    SELECT DATE_ADD(appointment_date, INTERVAL 1 DAY)
    FROM date_range
    WHERE appointment_date < '2021-09-30'
),
-- 计算每日计划时长
planned_stats AS (
    SELECT
        d.user_id,
        dr.appointment_date,
        SUM(TIMESTAMPDIFF(MINUTE, d.appointment_start_time, d.appointment_end_time)) AS total_planned_minutes
    FROM date_range dr
    JOIN default_appointments d
    -- 匹配逻辑：WEEKDAY返回0对应周一，和示例day_of_week=1对应周一的规则对齐，可根据实际业务调整
    ON WEEKDAY(dr.appointment_date) + 1 = d.day_of_week
    GROUP BY d.user_id, dr.appointment_date
),
-- 计算每日实际时长
actual_stats AS (
    SELECT
        user_id,
        DATE(appointment_start) AS appointment_date,
        SUM(TIMESTAMPDIFF(MINUTE, appointment_start, appointment_end)) AS total_actual_minutes
    FROM actual_appointments
    GROUP BY user_id, DATE(appointment_start)
)
-- 全量关联两组统计结果，覆盖所有（用户+日期）组合
SELECT
    COALESCE(p.user_id, a.user_id) AS user_id,
    COALESCE(p.appointment_date, a.appointment_date) AS appointment_date,
    COALESCE(p.total_planned_minutes, 0) AS total_planned_minutes,
    COALESCE(a.total_actual_minutes, 0) AS total_actual_minutes
FROM planned_stats p
LEFT JOIN actual_stats a
    ON p.user_id = a.user_id AND p.appointment_date = a.appointment_date
UNION
SELECT
    COALESCE(p.user_id, a.user_id) AS user_id,
    COALESCE(p.appointment_date, a.appointment_date) AS appointment_date,
    COALESCE(p.total_planned_minutes, 0) AS total_planned_minutes,
    COALESCE(a.total_actual_minutes, 0) AS total_actual_minutes
FROM actual_stats a
LEFT JOIN planned_stats p
    ON p.user_id = a.user_id AND p.appointment_date = a.appointment_date
ORDER BY user_id, appointment_date;

优化建议 #

• 索引优化：给default_appointments加联合索引(user_id, day_of_week)，给actual_appointments加联合索引(user_id, appointment_start, appointment_end)，如果数据库支持函数索引，可以额外加(user_id, DATE(appointment_start))进一步提速
• 大统计范围场景下建议按时间分片统计，避免一次性查询数据量过大
• 注意星期取值的对齐：不同数据库的星期函数返回值不同，比如Oracle的TO_CHAR(date, 'D')默认1对应周日，需要根据你实际day_of_week的取值规则调整关联条件

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