MySQL5.7下序列化项目活动周期计算的SQL方案问询

前提假设 #

假设数据表project_cycle_time包含以下核心字段：

• project_id：项目ID
• main_activity：主活动名称/标识
• overall_seq：主活动的全局序列（同项目内按执行顺序排序）
• section_seq：主活动内的工序序列
• process_date：工序完成时间（DATETIME类型）
• status：工序状态（completed/incomplete）

核心思路 #

通过窗口函数+用户变量替代循环逻辑，一次性完成所有主活动的周期计算，避免逐行处理带来的性能损耗，适配百万级数据量：

1. 预计算每个主活动的两个关键时间点：首个工序的完成时间、最后一个已完成工序的完成时间
2. 利用用户变量按项目和主活动序列，关联前一个主活动的最后完成时间
3. 按规则计算各主活动的周期时长

完整SQL代码 #

-- 临时存储每个主活动的关键时间点（MySQL 5.7不支持CTE，用临时表提升性能）
CREATE TEMPORARY TABLE IF NOT EXISTS main_activity_dates
SELECT 
    project_id,
    main_activity,
    overall_seq,
    -- 取主活动内首个工序的完成时间
    MIN(CASE WHEN section_seq = min_section THEN process_date END) AS earliest_section_date,
    -- 取主活动内最后一个已完成工序的完成时间（无已完成工序则为NULL）
    MAX(CASE WHEN section_seq = max_completed_section THEN process_date END) AS latest_completed_date
FROM (
    SELECT 
        project_id,
        main_activity,
        overall_seq,
        section_seq,
        process_date,
        status,
        -- 窗口函数：获取当前主活动的最小工序序号
        MIN(section_seq) OVER (PARTITION BY project_id, main_activity) AS min_section,
        -- 窗口函数：获取当前主活动中已完成工序的最大序号
        MAX(CASE WHEN status = 'completed' THEN section_seq END) OVER (PARTITION BY project_id, main_activity) AS max_completed_section
    FROM project_cycle_time
) t
GROUP BY project_id, main_activity, overall_seq;

-- 计算最终周期时长，用用户变量关联前序主活动的完成时间
SELECT 
    project_id,
    main_activity,
    overall_seq,
    CASE 
        -- 首个主活动：用首尾工序时间差，无已完成最终工序则为NULL
        WHEN overall_seq = 1 THEN 
            IF(latest_completed_date IS NOT NULL, TIMESTAMPDIFF(HOUR, earliest_section_date, latest_completed_date), NULL)
        -- 后续主活动：用前序主活动最后完成时间与当前主活动最后完成时间的差，任一未完成则为NULL
        ELSE 
            IF(latest_completed_date IS NOT NULL AND prev_latest_completed_date IS NOT NULL, TIMESTAMPDIFF(HOUR, prev_latest_completed_date, latest_completed_date), NULL)
    END AS cycle_time
FROM (
    SELECT 
        project_id,
        main_activity,
        overall_seq,
        earliest_section_date,
        latest_completed_date,
        -- 用户变量：记录前一个主活动的最后完成时间（同项目内生效）
        @prev_date := CASE 
            WHEN @current_project = project_id THEN @prev_date 
            ELSE NULL 
        END AS prev_latest_completed_date,
        -- 更新当前项目标识和前序时间变量
        @current_project := project_id,
        @prev_date := latest_completed_date
    FROM main_activity_dates,
         -- 初始化变量
         (SELECT @current_project := NULL, @prev_date := NULL) vars
    -- 必须按项目+主活动序列排序，保证变量逻辑正确
    ORDER BY project_id, overall_seq
) ranked_activities;

规则覆盖说明 #

1. 首个主活动计算：取overall_seq=1的首个工序时间与最后已完成工序时间的差值，若最后工序未完成则返回NULL
2. 后续主活动计算：仅关联前序主活动的最后完成时间与当前主活动的最后完成时间，符合补充更新要求
3. 未完成处理：若主活动无已完成的工序（latest_completed_date为NULL），则周期时长为NULL
4. 缺失工序处理：自动取主活动内已完成的最大工序序号，无需额外判断最高工序是否缺失

性能优化建议 #

为提升百万级数据的处理速度，建议建立以下联合索引：

CREATE INDEX idx_project_activity_seq ON project_cycle_time(project_id, main_activity, overall_seq, section_seq);

该索引可大幅加速窗口函数的分区和排序逻辑，减少全表扫描的开销。

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