按周、月统计每日事件平均时长的SQL实现求助

一、修复存储过程的时长计算问题 #

你的存储过程里有两个核心问题导致duration返回NULL：

1. 变量未初始化：DECLARE duration TIME; 没有设置初始值，默认是NULL，任何与NULL的运算结果都会是NULL，需要把它初始化为'00:00:00'。
2. 游标逻辑错误：两个游标都只查询event_code=50的事件，永远无法匹配到对应的54结束事件，而且双游标遍历的逻辑完全没必要。

其实我们可以不用游标，用窗口函数LEAD()更简洁高效地匹配每个开始事件（50）对应的结束事件（54）：

SELECT
  e_start.event_datetime AS start_time,
  e_end.event_datetime AS stop_time,
  TIMEDIFF(e_end.event_datetime, e_start.event_datetime) AS duration,
  DATE(e_start.event_datetime) AS event_date
FROM (
  SELECT 
    event_datetime,
    -- 找到当前50事件之后的下一个54事件
    LEAD(event_datetime) OVER (ORDER BY event_datetime) AS next_54_time
  FROM events
  WHERE event_code = '50'
) e_start
JOIN events e_end ON e_end.event_datetime = e_start.next_54_time AND e_end.event_code = '54';

如果一定要修复你的存储过程，调整后的版本如下：

DELIMITER #
CREATE PROCEDURE tablediff()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE v_start DATETIME;
  DECLARE v_end DATETIME;
  DECLARE duration TIME DEFAULT '00:00:00'; -- 初始化时长为0
  -- 游标1：获取所有50事件的开始时间
  DECLARE cur_start CURSOR FOR SELECT event_datetime FROM events WHERE event_code = '50' ORDER BY event_datetime ASC;
  -- 游标2：获取所有54事件的结束时间
  DECLARE cur_end CURSOR FOR SELECT event_datetime FROM events WHERE event_code = '54' ORDER BY event_datetime ASC;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  CREATE TEMPORARY TABLE IF NOT EXISTS tmp (
    start_time DATETIME,
    stop_time DATETIME,
    duration TIME
  );

  OPEN cur_start;
  OPEN cur_end;

  forLoop: LOOP
    FETCH cur_start INTO v_start;
    FETCH cur_end INTO v_end;
    
    IF done THEN LEAVE forLoop; END IF;

    -- 计算单组事件的时长
    SET duration = TIMEDIFF(v_end, v_start);
    -- 插入临时表
    INSERT INTO tmp VALUES (v_start, v_end, duration);
  END LOOP;

  CLOSE cur_start;
  CLOSE cur_end;

  -- 查询临时表结果
  SELECT * FROM tmp;
END#
DELIMITER ;

二、实现需求：每日时长统计 + 每月周均时长 #

步骤1：计算每日总时长总和 #

基于事件配对结果，我们按日期分组，把时长转换为小时数方便后续计算：

WITH event_pairs AS (
  SELECT
    DATE(e_start.event_datetime) AS event_date,
    -- 将TIME类型的时长转换为小时数
    TIME_TO_SECONDS(TIMEDIFF(e_end.event_datetime, e_start.event_datetime)) / 3600 AS hours
  FROM (
    SELECT 
      event_datetime,
      LEAD(event_datetime) OVER (ORDER BY event_datetime) AS next_54_time
    FROM events
    WHERE event_code = '50'
  ) e_start
  JOIN events e_end ON e_end.event_datetime = e_start.next_54_time AND e_end.event_code = '54'
),
daily_total AS (
  SELECT
    DATE_FORMAT(event_date, '%Y-%m') AS month,
    event_date,
    COALESCE(SUM(hours), 0) AS daily_hours -- 无事件的日期总时长设为0
  FROM (
    -- 生成每月所有日期，确保无事件日期也能被统计
    SELECT DATE_ADD(month_start, INTERVAL day-1 DAY) AS event_date
    FROM (
      SELECT DISTINCT DATE_FORMAT(event_date, '%Y-%m-01') AS month_start FROM event_pairs
      UNION SELECT '2018-02-01' -- 手动补充需要统计的月份，可替换为通用日期生成逻辑
    ) m
    CROSS JOIN (
      SELECT 1 day UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
      UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14
      UNION SELECT 15 UNION SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20 UNION SELECT 21
      UNION SELECT 22 UNION SELECT 23 UNION SELECT 24 UNION SELECT 25 UNION SELECT 26 UNION SELECT 27 UNION SELECT 28
    ) d
    -- 过滤掉超过当月实际天数的日期
    WHERE DATE_ADD(month_start, INTERVAL day-1 DAY) <= LAST_DAY(month_start)
  ) all_dates
  LEFT JOIN event_pairs ON all_dates.event_date = event_pairs.event_date
  GROUP BY month, event_date
)

步骤2：按每月4周计算每日平均时长 #

按每月第1-7天=周1，8-14=周2，15-21=周3，22+=周4的规则分组，计算每周的每日平均时长：

SELECT
  DATE_FORMAT(STR_TO_DATE(month, '%Y-%m'), '%b') AS Month, -- 转换为月份缩写（Jan/Feb）
  ROUND(SUM(CASE WHEN DAY(event_date) BETWEEN 1 AND 7 THEN daily_hours ELSE 0 END) / 7, 2) AS `Week 1`,
  ROUND(SUM(CASE WHEN DAY(event_date) BETWEEN 8 AND 14 THEN daily_hours ELSE 0 END) / 7, 2) AS `Week 2`,
  ROUND(SUM(CASE WHEN DAY(event_date) BETWEEN 15 AND 21 THEN daily_hours ELSE 0 END) / 7, 2) AS `Week 3`,
  ROUND(SUM(CASE WHEN DAY(event_date) >=22 THEN daily_hours ELSE 0 END) / 7, 2) AS `Week 4`
FROM daily_total
GROUP BY month
ORDER BY STR_TO_DATE(month, '%Y-%m');

完整SQL与结果说明 #

把两部分合并后，针对你提供的测试数据，运行会得到如下结果：

其中Jan的Week1平均是(3+16+0+0+0+0+0)/7 ≈2.71，和你例子中的计算逻辑完全一致。

关键注意点 #

• 通用日期生成：如果需要统计多个月份，建议用递归CTE生成日期，避免手动补充月份。
• 周划分规则：这里的周划分完全匹配你给出的例子，若有其他规则可调整CASE WHEN中的日期范围。
• 空值处理：用COALESCE确保无事件日期的总时长为0，避免平均计算时遗漏数据。

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