如何按严重程度统计开放Bug平均生命周期天数的SQL查询实现

原SQL问题说明 #

• 自连接未指定同bug的关联条件，会生成无效笛卡尔积数据
• 逻辑运算符使用错误，SQL中多值匹配需用IN或OR，不能使用位运算符|
• 状态判断逻辑错误：单条记录不可能同时为开启、关闭状态，需分别取同一bug的两个状态时间
• 聚合逻辑不符合需求：原语句统计天数计数，而非开闭间隔的平均值

正确实现代码 #

SELECT 
    severity,
    AVG(DATEDIFF(close_date, open_date)) AS avg_lifecycle_days
FROM (
    -- 子查询获取每个bug的首次开启时间、首次关闭时间
    SELECT 
        bug,
        severity,
        MIN(CASE WHEN status IN ('open', 'opened') THEN date_assigned END) AS open_date,
        MIN(CASE WHEN status IN ('close', 'closed', 'closed with fix') THEN date_assigned END) AS close_date
    FROM table1
    GROUP BY bug, severity
) AS bug_lifecycle
-- 过滤未完成关闭流程的bug，不需要可删除该行
WHERE close_date IS NOT NULL
GROUP BY severity
ORDER BY severity;

适配提示：不同数据库的日期差计算函数略有差异，可按需调整：

• PostgreSQL：替换DATEDIFF(close_date, open_date)为(close_date::date - open_date::date)
• SQL Server：替换为DATEDIFF(day, open_date, close_date)

示例数据运行结果 #

基于提供的测试数据，运行后输出结果如下：

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