多条件场景下Case Statement应用及工单时间差计算技术咨询

你的需求核心是匹配同一car_key下的创建/完成时间，再基于近一个月的时间范围计算差值，不符合条件时返回null。以下是两种可行的实现方案，同时说明Case Statement的用法：

方案1：用窗口函数匹配时间（推荐单表处理） #

通过窗口函数按car_key分组，分别提取tasktype='created'的ticket_created和tasktype='finished'的ticket_completed，再用条件判断过滤时间范围：

SELECT 
    car_key,
    -- 提取同一car_key的创建时间
    MAX(CASE WHEN tasktype = 'created' THEN ticket_created END) OVER (PARTITION BY car_key) AS created_time,
    -- 提取同一car_key的完成时间
    MAX(CASE WHEN tasktype = 'finished' THEN ticket_completed END) OVER (PARTITION BY car_key) AS completed_time,
    -- 计算时间差，仅当两个时间都在近一个月内时返回差值，否则为null
    CASE 
        WHEN MAX(CASE WHEN tasktype = 'created' THEN ticket_created END) OVER (PARTITION BY car_key) >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH)
             AND MAX(CASE WHEN tasktype = 'finished' THEN ticket_completed END) OVER (PARTITION BY car_key) >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH)
        THEN DATEDIFF(
            MAX(CASE WHEN tasktype = 'finished' THEN ticket_completed END) OVER (PARTITION BY car_key),
            MAX(CASE WHEN tasktype = 'created' THEN ticket_created END) OVER (PARTITION BY car_key)
        )
        ELSE NULL
    END AS day_diff
FROM your_table
GROUP BY car_key; -- 按car_key去重，避免重复行

方案2：自连接匹配工单 #

如果每个car_key对应的created和finished是一一对应的，可以用自连接将两行数据合并为一行，再做时间判断：

SELECT 
    c.car_key,
    c.ticket_created AS created_time,
    f.ticket_completed AS completed_time,
    -- 条件判断：两个时间都在近一个月内才计算差值
    CASE 
        WHEN c.ticket_created >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH)
             AND f.ticket_completed >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH)
        THEN DATEDIFF(f.ticket_completed, c.ticket_created)
        ELSE NULL
    END AS day_diff
FROM your_table c
JOIN your_table f 
    ON c.car_key = f.car_key
    AND c.tasktype = 'created'
    AND f.tasktype = 'finished';

关于Case Statement的作用 #

你提到的Case Statement完全可以用来实现需求中的条件判断：

• 用来提取不同tasktype对应的时间（方案1中的窗口函数里的Case）
• 用来判断时间是否在近一个月范围内，符合条件则计算差值，否则返回null（两个方案中的外层Case）

如果用WHERE子句先过滤时间，会直接排除掉时间不符合的car_key，但你的需求是保留这些car_key并返回null，所以用Case Statement是更合适的选择。

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