求MySQL查询各process_id对应TAT（周转时间）的正确SQL语句

根据你的需求和给定的示例数据，以下是实现预期结果的MySQL查询语句：

WITH date_blocks AS (
    -- 对每个候选人的同一流程，按日期去重并标记连续日期块
    SELECT 
        name,
        process_id,
        date,
        SUM(CASE WHEN prev_date IS NULL OR DATEDIFF(date, prev_date) > 1 THEN 1 ELSE 0 END) 
            OVER (PARTITION BY name, process_id ORDER BY date) AS block_id
    FROM (
        SELECT 
            name,
            process_id,
            date,
            LAG(date) OVER (PARTITION BY name, process_id ORDER BY date) AS prev_date
        FROM candidate_stages
        GROUP BY name, process_id, date
    ) t
),
block_validation AS (
    -- 检查每个日期块之后是否存在其他流程的记录
    SELECT 
        process_id,
        block_id,
        EXISTS(
            SELECT 1 
            FROM candidate_stages cs 
            WHERE cs.name = db.name 
              AND cs.date > MAX(db.date) 
              AND cs.process_id != db.process_id
        ) AS has_subsequent_process
    FROM date_blocks db
    GROUP BY name, process_id, block_id
),
process_final_check AS (
    -- 检查流程是否存在最后一个块无后续流程的情况
    SELECT 
        DISTINCT process_id,
        EXISTS(
            SELECT 1 
            FROM block_validation bv 
            WHERE bv.process_id = cs.process_id 
              AND bv.has_subsequent_process = FALSE
        ) AS has_unfinished_block
    FROM candidate_stages cs
)
SELECT 
    pfc.process_id,
    CASE 
        WHEN pfc.has_unfinished_block THEN 0
        ELSE COALESCE(SUM(CASE WHEN bv.has_subsequent_process THEN 1 ELSE 0 END), 0)
    END AS number_of_days
FROM process_final_check pfc
LEFT JOIN block_validation bv ON pfc.process_id = bv.process_id
GROUP BY pfc.process_id, pfc.has_unfinished_block
ORDER BY pfc.process_id;

逻辑说明： #

1. date_blocks CTE：对每个候选人的同一流程，先按日期去重，再用窗口函数标记连续的日期块（日期连续或相同的为一个块）。
2. block_validation CTE：针对每个日期块，检查该块之后是否存在其他流程的记录，标记为has_subsequent_process。
3. process_final_check CTE：检查每个流程是否存在“无后续流程的日期块”（即该块是候选人最后处理的流程）。
4. 主查询：如果流程存在无后续流程的块，则总TAT为0；否则累加所有有后续流程的块的数量（每个块按1天计算），得到总周转时间。

匹配示例结果： #

• process_id=1001的两个日期块（2024-02-01~2024-02-02、2024-02-04）之后均有其他流程记录，所以总天数为2。
• process_id=1002存在最后一个日期块（2024-02-05）无后续流程记录，所以总天数为0。

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