如何用T-SQL计算指定日期范围内水泵运行与停机总时长（分钟）

要解决这个问题，核心是把每条状态记录和下一次状态切换的时间配对，用DATEDIFF计算两段时间的分钟差，最后按状态汇总总时长。以下是具体实现：

假设条件 #

• 表名：PumpLogs
• Date字段为datetime类型，PumpStatus为字符串类型（取值Running/Stop）
• 需指定查询的开始/结束日期范围

基础版本（适用于最后一条状态已完成切换的场景） #

先通过窗口函数LEAD获取每条状态的结束时间，再计算时长并汇总：

WITH StatusDurations AS (
    SELECT
        Date,
        PumpStatus,
        -- 获取下一条状态记录的时间，作为当前状态的结束时间
        LEAD(Date) OVER (ORDER BY Date) AS NextStatusDate
    FROM PumpLogs
    -- 替换为你的目标日期范围
    WHERE Date BETWEEN '2024-01-01 00:00:00' AND '2024-01-02 23:59:59'
)
SELECT
    PumpStatus,
    SUM(DATEDIFF(minute, Date, NextStatusDate)) AS TotalDurationMinutes
FROM StatusDurations
WHERE NextStatusDate IS NOT NULL -- 排除最后一条无后续状态的记录
GROUP BY PumpStatus;

优化版本（处理最后一条未切换的状态） #

如果查询范围内最后一条记录是Running（没有后续的Stop记录），需要用查询的结束时间作为该状态的结束时间，避免遗漏这段时长：

-- 定义查询的开始和结束时间
DECLARE @StartDate DATETIME = '2024-01-01 00:00:00';
DECLARE @EndDate DATETIME = '2024-01-02 23:59:59';

WITH StatusDurations AS (
    SELECT
        Date,
        PumpStatus,
        -- 优先取下一条状态时间，没有则用查询结束时间补全
        COALESCE(LEAD(Date) OVER (ORDER BY Date), @EndDate) AS NextStatusDate
    FROM PumpLogs
    WHERE Date BETWEEN @StartDate AND @EndDate
)
SELECT
    PumpStatus,
    SUM(DATEDIFF(minute, Date, NextStatusDate)) AS TotalDurationMinutes
FROM StatusDurations
GROUP BY PumpStatus;

关键逻辑说明 #

1. LEAD(Date) OVER (ORDER BY Date)：按时间顺序，为每条记录获取下一条状态切换的时间，这就是当前状态的结束时间。
2. DATEDIFF(minute, Date, NextStatusDate)：计算两个时间点之间的分钟数，得到当前状态的持续时长。
3. COALESCE：处理最后一条无后续状态的记录，用查询的结束时间替代NULL，确保这段时长被计算在内。
4. 日期范围过滤：通过WHERE Date BETWEEN ...限制只计算指定范围内的状态时长。

示例数据测试结果 #

用你提供的数据测试（查询范围为2024-01-01 00:00到2024-01-02 23:59）：

• Running总时长：70 + 594 + 584 = 1248分钟
• Stop总时长：56 + 120 = 176分钟

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