如何用SQL计算平均故障间隔时间（MTBF）及生成月度MTBF报表

需求说明 #

现有包含故障发生日期（Issue Raised）、故障数量（Count）、**间隔天数（Datediff）**的原始故障数据，需通过SQL完成两项任务：

1. 计算Datediff字段（当前故障与上一次故障的间隔天数）
2. 生成按月份统计的平均故障间隔时间（MTBF）报表

原始数据示例 #

Issue Raised | Count | Datediff  
1/12/22         1      12
2/23/22         1      42
4/1/22          2      37
4/7/22          1      6

期望输出报表 #

Month | MTBF
Jan     12/1=12
Feb     42/1=42
Mar     
Apr     (37+6)/(2+1)=14.33

一、计算Datediff字段 #

Datediff是当前故障记录与上一条故障记录的日期间隔，可通过窗口函数LAG()实现，以MySQL为例：

SELECT 
    `Issue Raised`,
    `Count`,
    -- 转换字符串日期为日期格式，再计算与上一条记录的间隔
    DATEDIFF(
        STR_TO_DATE(`Issue Raised`, '%m/%d/%y'),
        LAG(STR_TO_DATE(`Issue Raised`, '%m/%d/%y')) OVER (ORDER BY STR_TO_DATE(`Issue Raised`, '%m/%d/%y'))
    ) AS Datediff
FROM 
    fault_data;

说明：

• 第一条记录因无前置数据，Datediff会返回NULL，可按需用IFNULL(Datediff, 0)处理
• 若使用其他数据库（如SQL Server），需替换日期转换函数（如CONVERT(DATE, Issue Raised, 101)）和窗口函数语法

二、生成按月统计的MTBF报表 #

MTBF计算逻辑为：当月总间隔天数 ÷ 当月总故障数，需确保无故障的月份也能显示（如示例中的3月），分两步实现：

1. 生成连续月份列表 #

若数据库无日期维度表，可临时生成目标月份：

WITH months AS (
    SELECT 'Jan' AS month_name, 1 AS month_num UNION ALL
    SELECT 'Feb' AS month_name, 2 AS month_num UNION ALL
    SELECT 'Mar' AS month_name, 3 AS month_num UNION ALL
    SELECT 'Apr' AS month_name, 4 AS month_num
)

2. 聚合数据并关联月份列表 #

WITH months AS (
    SELECT 'Jan' AS month_name, 1 AS month_num UNION ALL
    SELECT 'Feb' AS month_name, 2 AS month_num UNION ALL
    SELECT 'Mar' AS month_name, 3 AS month_num UNION ALL
    SELECT 'Apr' AS month_name, 4 AS month_num
),
fault_agg AS (
    SELECT
        MONTH(STR_TO_DATE(`Issue Raised`, '%m/%d/%y')) AS month_num,
        SUM(Datediff) AS total_datediff,
        SUM(`Count`) AS total_count,
        -- 保留单条数据的原始值，用于格式化输出
        GROUP_CONCAT(Datediff SEPARATOR '+') AS diff_list,
        GROUP_CONCAT(`Count` SEPARATOR '+') AS count_list
    FROM
        fault_data
    GROUP BY
        MONTH(STR_TO_DATE(`Issue Raised`, '%m/%d/%y'))
)
SELECT
    m.month_name AS Month,
    CASE
        WHEN f.total_datediff IS NULL THEN ''
        WHEN f.total_count = 0 THEN ''
        ELSE CONCAT(
            -- 多个数据时加括号，单个数据直接显示
            IF(CHAR_LENGTH(f.diff_list) > CHAR_LENGTH(CAST(f.total_datediff AS CHAR)), CONCAT('(', f.diff_list, ')'), f.diff_list),
            '/',
            IF(CHAR_LENGTH(f.count_list) > CHAR_LENGTH(CAST(f.total_count AS CHAR)), CONCAT('(', f.count_list, ')'), f.count_list),
            '=',
            ROUND(f.total_datediff / f.total_count, 2)
        )
    END AS MTBF
FROM
    months m
LEFT JOIN
    fault_agg f ON m.month_num = f.month_num
ORDER BY
    m.month_num;

说明：

• 用GROUP_CONCAT拼接单月内的Datediff和Count值，实现示例中的格式显示
• 左连接月份列表确保所有目标月份都出现在结果中
• ROUND()函数保留两位小数，可按需调整精度

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