MySQL遍历表计算ID非活跃天数的实现方案咨询

这个问题我之前也遇到过，直接统计所有Inactive记录的日期差确实会踩坑——中间的Active状态会把连续的非活跃期打断，所以得先把每个连续的Inactive区间找出来，再逐个计算天数累加。我给你两种可行的方案，一种是高效的窗口函数写法（优先推荐），另一种是你提到的游标+循环实现，方便你理解逻辑：

这种方法利用MySQL的窗口函数来关联每个Inactive状态对应的下一个Active日期，自动合并连续的Inactive区间，代码简洁且执行效率高，适合大数据量场景。

假设你的表名为status_records，Date字段是字符串格式，我们先转成日期类型再处理：

WITH ranked_status AS (
    SELECT 
        id,
        Status,
        -- 转换字符串日期为DATE类型，注意格式要匹配你的数据
        STR_TO_DATE(Date, '%m/%d/%y') AS actual_date,
        -- 获取当前行之后最近的Active日期
        LEAD(CASE WHEN Status = 'Active' THEN STR_TO_DATE(Date, '%m/%d/%y') END) OVER (
            PARTITION BY id 
            ORDER BY STR_TO_DATE(Date, '%m/%d/%y') ASC
        ) AS next_active_date
    FROM status_records
),
inactive_intervals AS (
    SELECT 
        id,
        MIN(actual_date) AS interval_start,
        next_active_date AS interval_end
    FROM ranked_status
    WHERE Status = 'Inactive'
    -- 同一个next_active_date属于同一个连续Inactive区间，分组去重
    GROUP BY id, next_active_date
)
SELECT 
    id,
    SUM(DATEDIFF(interval_end, interval_start)) AS `Days Inactive`
FROM inactive_intervals
GROUP BY id;

逻辑说明： #

1. ranked_status CTE：把字符串日期转成标准DATE类型，并用LEAD()窗口函数按ID分组、日期升序，为每个Inactive记录找到它之后的第一个Active日期。
2. inactive_intervals CTE：筛选出所有Inactive记录，按ID和next_active_date分组（连续的Inactive记录会共享同一个后续Active日期），得到每个连续非活跃区间的开始和结束日期。
3. 最终查询：按ID分组，用DATEDIFF()计算每个区间的天数并累加，得到总非活跃天数。

测试你的示例数据，这个查询会输出：

完全符合你的预期结果。

如果你更倾向于用循环和游标来实现状态切换的逻辑，下面是一个存储过程示例，它会逐行遍历记录，手动跟踪状态切换并累加天数：

DELIMITER //

CREATE PROCEDURE CalculateInactiveDays()
BEGIN
    -- 声明变量
    DECLARE done INT DEFAULT FALSE;
    DECLARE current_id INT;
    DECLARE current_status VARCHAR(20);
    DECLARE current_date DATE;
    DECLARE prev_id INT;
    DECLARE prev_status VARCHAR(20);
    DECLARE prev_date DATE;
    DECLARE inactive_start DATE;
    DECLARE total_days INT DEFAULT 0;
    
    -- 创建游标：按ID和日期升序遍历所有记录
    DECLARE status_cursor CURSOR FOR
        SELECT 
            id,
            Status,
            STR_TO_DATE(Date, '%m/%d/%y') AS actual_date
        FROM status_records
        ORDER BY id, actual_date ASC;
    
    -- 处理游标结束的情况
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- 创建临时表存储结果
    CREATE TEMPORARY TABLE IF NOT EXISTS inactive_results (
        id INT,
        `Days Inactive` INT
    );
    
    -- 初始化游标和变量
    OPEN status_cursor;
    FETCH status_cursor INTO current_id, current_status, current_date;
    SET prev_id = current_id;
    SET prev_status = current_status;
    SET prev_date = current_date;
    
    -- 开始循环遍历
    read_loop: LOOP
        FETCH status_cursor INTO current_id, current_status, current_date;
        
        -- 游标遍历结束时退出循环
        IF done THEN
            -- 如果最后一条记录是Inactive，可根据需求补充逻辑（比如统计到当前日期）
            -- IF prev_status = 'Inactive' THEN
            --     SET total_days = total_days + DATEDIFF(CURDATE(), inactive_start);
            -- END IF;
            LEAVE read_loop;
        END IF;
        
        -- 切换到新ID时，先保存上一个ID的结果
        IF current_id != prev_id THEN
            INSERT INTO inactive_results VALUES (prev_id, total_days);
            SET total_days = 0;
            SET prev_id = current_id;
        END IF;
        
        -- 处理状态切换逻辑
        IF prev_status = 'Active' AND current_status = 'Inactive' THEN
            -- 进入非活跃状态，记录开始日期
            SET inactive_start = current_date;
        ELSIF prev_status = 'Inactive' AND current_status = 'Active' THEN
            -- 从非活跃切换到活跃，计算区间天数并累加
            SET total_days = total_days + DATEDIFF(current_date, inactive_start);
        END IF;
        
        -- 更新前一次的状态和日期
        SET prev_status = current_status;
        SET prev_date = current_date;
    END LOOP;
    
    -- 保存最后一个ID的结果
    INSERT INTO inactive_results VALUES (prev_id, total_days);
    
    -- 输出最终结果
    SELECT * FROM inactive_results;
    
    -- 清理临时表
    DROP TEMPORARY TABLE IF EXISTS inactive_results;
    
    CLOSE status_cursor;
END //

DELIMITER ;

-- 调用存储过程获取结果
CALL CalculateInactiveDays();

逻辑说明： #

1. 游标初始化：按ID和日期升序读取所有记录，确保我们按时间顺序处理状态变化。
2. 循环遍历：逐行检查状态变化：
   • 当从Active切换到Inactive时，记录非活跃期的开始日期。
   • 当从Inactive切换到Active时，计算这个非活跃区间的天数并累加到总天数。
   • 切换到新ID时，把上一个ID的总天数存入临时表，重置累加器。
3. 结果输出：遍历结束后，输出临时表中的统计结果。

注意事项 #

• 如果你的Date字段已经是DATE类型，直接去掉STR_TO_DATE()转换即可。
• 确保日期格式符%m/%d/%y和你的数据匹配（比如如果是年-月-日格式，要改成%Y-%m-%d）。

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