维修工单完成率为什么会算错？用 SQL 复现一个跨月统计问题

原创

用户7491109

修改于 2026-09-14 16:59:53

940

看设备维修报表时，我会先问一句：这个“完成率”，到底对应哪一批工单？

同一组记录，可以算出75%，也可以算出50%。除法都没有算错，区别在于分子和分母是不是来自同一批任务。

本文用一组人工构造的数据，把跨月任务、异常记录和未关闭任务放在一起核对。不是某家工厂的真实记录，也不是某款产品的实测结果。文中 SQL 已在 SQLite 3.46.1 中运行验证。

一、先固定统计范围，再准备数据

本例统计2026年8月，时间范围为：

2026-08-01 00:00:00 ≤ 时间 < 2026-09-01 00:00:00

所有时间统一为 UTC+08:00 的当地时间，并采用固定格式 YYYY-MM-DD HH:MM:SS。

以下使用一个简化模型：**每张工单只创建一次、最多关闭一次，没有撤销、重开或多轮关闭。**数据快照晚于示例中的关闭时间，状态表示快照时的状态，而不是8月末的历史状态。

SQLite 支持以文本保存规定格式的日期时间。示例中的格式和时区已经统一，才采用文本比较筛选月份；不能把混合格式、混合时区的原始数据直接套进来。

在空白 SQLite 测试数据库中依次执行以下代码，不要连接生产数据库：

CREATE TABLE work_orders (
    ticket_id TEXT PRIMARY KEY NOT NULL,
    status TEXT NOT NULL
        CHECK (status IN ('CLOSED', 'PROCESSING')),
    created_at TEXT NOT NULL,
    closed_at TEXT
);

INSERT INTO work_orders VALUES
('W01','CLOSED',    '2026-08-05 08:00:00','2026-08-05 09:15:00'),
('W02','CLOSED',    '2026-07-31 23:00:00','2026-08-01 00:10:00'),
('W03','CLOSED',    '2026-08-31 23:30:00','2026-09-01 00:30:00'),
('W04','PROCESSING','2026-08-31 20:00:00',NULL),
('W05','CLOSED',    '2026-08-10 10:00:00','2026-08-10 09:30:00'),
('W06','CLOSED',    '2026-08-12 14:00:00',NULL),
('W07','CLOSED',    '2026-08-08 08:00:00','2026-08-08 09:00:00');

其中，W02是7月创建、8月关闭的历史任务；W03是8月创建、9月关闭的跨月任务；W04尚未关闭。

W05和W06则故意保留了两种数据问题。

二、异常记录先列出来，不要让它们悄悄影响结果

W05的关闭时间早于创建时间；W06显示已关闭，却没有关闭时间。

这两张单不能直接拿去计算处理时长，也不能因为影响报表，就删除原始记录。这里用 CASE 生成问题标签，再建立只包含通过示例校验记录的视图。

CREATE VIEW checked_orders AS
SELECT *,
  CASE
    WHEN julianday(created_at) IS NULL
      THEN '创建时间不可解析'
    WHEN closed_at IS NOT NULL
         AND julianday(closed_at) IS NULL
      THEN '关闭时间不可解析'
    WHEN status = 'CLOSED' AND closed_at IS NULL
      THEN '已关闭但缺少关闭时间'
    WHEN status <> 'CLOSED' AND closed_at IS NOT NULL
      THEN '状态与关闭时间冲突'
    WHEN closed_at < created_at
      THEN '关闭早于创建'
    ELSE 'OK'
  END AS issue
FROM work_orders;

CREATE VIEW valid_orders AS
SELECT *
FROM checked_orders
WHERE issue = 'OK';

SELECT ticket_id, issue
FROM checked_orders
WHERE issue <> 'OK'
ORDER BY ticket_id;

异常查询结果是：

工单	待核查问题
W05	关闭早于创建
W06	已关闭但缺少关闭时间

原始数据7条，通过本例校验5条，待核查2条。下文所有指标仅基于这5条记录，不代表原始7条记录已经全部确认。

这只是时序和状态关联的示例校验，不是完整的数据清洗程序。正式使用还需核对日期合法性、字段格式、重复编号、业务状态及时间来源。

三、“本月新建任务的完成率”，分子必须来自本月新建任务

本例将指标定义为：

8月新建且在8月关闭的有效工单数 ÷ 8月新建的有效工单数。

先筛选创建时间，再在同一批记录中统计关闭数量：

SELECT
  COUNT(*) AS created_count,

  COUNT(
    CASE WHEN closed_at < '2026-09-01 00:00:00'
         THEN 1 END
  ) AS same_month_closed_count,

  ROUND(
    100.0 * COUNT(
      CASE WHEN closed_at < '2026-09-01 00:00:00'
           THEN 1 END
    ) / NULLIF(COUNT(*), 0),
    1
  ) AS completion_pct

FROM valid_orders
WHERE created_at >= '2026-08-01 00:00:00'
  AND created_at <  '2026-09-01 00:00:00';

结果是：

本月新建：4单
其中当月关闭：2单
完成比例：50.0%

COUNT(*) 统计筛选后的总行数；COUNT(CASE...) 只统计满足条件的非空结果。

这里还用 NULLIF 处理了分母为0的情况：没有新增任务时，返回空值，而不是把“没有新任务”解释成0%或100%。

这4张单是W01、W03、W04和W07。

W03虽然在数据快照时已经关闭，但关闭发生在9月，不能算入8月完成数量；W04仍未关闭。

还要注意，**这里计算的是“当月新建任务截至当月末的关闭比例”，并不是按期完成率。**没有任务期限字段，就无法判断是否按期。

四、“本月关闭多少单”是另一个问题

如果关心8月实际关闭了多少张单，就要按关闭时间筛选，而不是按创建时间：

SELECT
  COUNT(*) AS closed_count,

  ROUND(
    AVG(
      (julianday(closed_at) - julianday(created_at))
      * 1440.0
    ),
    2
  ) AS avg_close_minutes

FROM valid_orders
WHERE closed_at >= '2026-08-01 00:00:00'
  AND closed_at <  '2026-09-01 00:00:00';

查询结果为：

本月关闭：3单
完整创建至关闭间隔的平均值：68.33分钟

这3张单是W01、W02和W07。

W02虽然创建于7月，但确实在8月关闭，因此应计入8月关闭量。

此时拿关闭量3除以新建量4，会得到75%。它可以被命名为“本月关闭量与新建量之比”，但不能被解释成“本月新建任务有75%已经完成”，因为分子里混入了上月任务。

代码中通过 julianday() 相减得到天数，再乘1440换算为分钟，最后用 AVG 计算平均值。

这里得到的是这批工单完整的创建至关闭间隔，不是落在8月内的工时，也不是设备停机时长。

没有实际维修开始、结束、暂停和人员投入记录，不应该进一步把它称为维修人员的实际作业时间。

五、未关闭工单必须单独展示

只看已经关闭的任务，无法回答月末还有多少事情没结束。

本例按“创建早于9月1日0点，且关闭时间为空或不早于该时刻”，识别8月末截点前仍未关闭的有效工单：

SELECT
  ticket_id,

  ROUND(
    (
      julianday('2026-09-01 00:00:00')
      - julianday(created_at)
    ) * 1440.0,
    1
  ) AS age_minutes

FROM valid_orders
WHERE created_at < '2026-09-01 00:00:00'
  AND (
    closed_at IS NULL
    OR closed_at >= '2026-09-01 00:00:00'
  )
ORDER BY ticket_id;

结果是：

工单	截至月末截点的已历时长
W03	30分钟
W04	240分钟

这些是从创建到截点的已历时长，不是最终处理时长，更不能因为工单没关闭就填成0。

还要注意，W03在快照中是 CLOSED。如果直接拿当前状态筛选8月末未关闭任务，就会漏掉它。

本例能按创建、关闭时间还原，是因为明确排除了撤销和重开；存在多轮状态变化时，应改用状态事件历史或相应历史快照核对。

六、再做一次数量对账，看看口径有没有打架

在这组有效数据和简化模型中，可以核对：

期初未关闭1单
＋ 本月新建4单
－ 本月关闭3单
＝ 期末未关闭2单

因此，50%的新增任务关闭比例、3单的本月关闭量、2单的期末未关闭量并不矛盾，它们回答的是不同问题。

我更愿意把这几项放在一起看，而不是只留下一个看起来完整的百分比。同时保留2条待核查记录，修正后重新计算，不能把数据问题当作已经消失。

这一轮还验证了两个边界：没有新增任务时，比例返回空值；恰好在9月1日0点关闭的任务归入9月，不计入8月关闭量。

做设备管理报表，公式可以很短，但指标名称要说明任务范围、时间边界和数据质量。先把这些说清楚，数字才有讨论的基础。

下一篇继续拆分接单等待、维修处理和验收等待，避免用一个“维修时长”概括不同阶段。

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。