从SQL表计算IP设备总上线/下线时长的技术需求

当IP设备发生上线或下线事件时，系统会向connected表插入对应记录。以下是具体的表结构、示例数据，以及两类需求的实现方案：

示例表结构与测试数据 #

CREATE TABLE connected (
    IP NVARCHAR(50),
    stat CHAR(10),
    ts DATETIME
);

INSERT INTO connected VALUES
('10.0.0.1','down',CAST('2022-09-13 4:00:00.000' AS DATETIME)),
('10.0.0.2','down',CAST('2022-09-13 6:30:00.000' AS DATETIME)),
('10.0.0.1','up',CAST('2022-09-13 4:30:00.000' AS DATETIME)),
('10.0.0.2','up',CAST('2022-09-13 6:55:00.000' AS DATETIME)),
('10.0.0.3','down',CAST('2022-09-13 18:00:00.000' AS DATETIME)),
('10.0.0.1','down',CAST('2022-09-13 6:00:00.000' AS DATETIME)),
('10.0.0.1','up',CAST('2022-09-13 6:20:00.000' AS DATETIME));

数据规则说明 #

单IP单日可有多条事件记录，且设备的下线记录之后，下一条必为上线记录。

需求1：指定日期区间内各IP的总上线/下线时长 #

预期输出 #

实现SQL #

-- 定义查询的日期区间
DECLARE @StartDate DATETIME = '2022-09-13 00:00:00';
DECLARE @EndDate DATETIME = '2022-09-14 00:00:00';

WITH ip_ordered_events AS (
    SELECT 
        IP,
        stat,
        ts,
        -- 获取当前事件的下一条事件时间
        LEAD(ts) OVER (PARTITION BY IP ORDER BY ts) AS next_event_ts,
        -- 获取IP在区间内的第一条事件时间和状态
        FIRST_VALUE(ts) OVER (PARTITION BY IP ORDER BY ts) AS first_event_ts,
        FIRST_VALUE(stat) OVER (PARTITION BY IP ORDER BY ts) AS first_event_stat
    FROM connected
    WHERE ts BETWEEN @StartDate AND @EndDate
),
segment_duration AS (
    -- 计算每条事件对应的状态持续时长
    SELECT 
        IP,
        stat,
        DATEDIFF(MINUTE, ts, COALESCE(next_event_ts, CASE WHEN @EndDate > GETDATE() THEN GETDATE() ELSE @EndDate END)) AS duration_min
    FROM ip_ordered_events
    UNION ALL
    -- 计算区间开始到第一条事件的时长（这段时间为第一条事件状态的相反状态）
    SELECT 
        IP,
        CASE first_event_stat WHEN 'down' THEN 'up' ELSE 'down' END AS stat,
        DATEDIFF(MINUTE, @StartDate, first_event_ts) AS duration_min
    FROM (SELECT DISTINCT IP, first_event_stat, first_event_ts FROM ip_ordered_events) t
)
SELECT 
    IP,
    CONCAT(SUM(CASE WHEN stat = 'up' THEN duration_min ELSE 0 END), '分钟') AS 总上线时长,
    CONCAT(
        SUM(CASE WHEN stat = 'down' THEN duration_min ELSE 0 END),
        '分钟',
        CASE 
            WHEN EXISTS (SELECT 1 FROM connected WHERE IP = segment_duration.IP AND stat = 'down' AND NOT EXISTS (SELECT 1 FROM connected WHERE IP = segment_duration.IP AND stat = 'up' AND ts > connected.ts))
            THEN '（基于当前时间计算）' 
            ELSE '' 
        END
    ) AS 总下线时长
FROM segment_duration
GROUP BY IP
ORDER BY IP;

需求2：各设备每次下线事件的详细记录及单段下线时长 #

预期输出 #

实现SQL #

SELECT 
    d.IP,
    -- 格式化下线时间为日-月-年 时分秒格式
    CONVERT(VARCHAR(20), d.ts, 105) + ' ' + CONVERT(VARCHAR(8), d.ts, 108) AS 下线时间,
    -- 对应上线时间，无记录则用当前时间
    CASE 
        WHEN u.ts IS NOT NULL THEN CONVERT(VARCHAR(20), u.ts, 105) + ' ' + CONVERT(VARCHAR(8), u.ts, 108)
        ELSE CONVERT(VARCHAR(20), GETDATE(), 105) + ' ' + CONVERT(VARCHAR(8), GETDATE(), 108)
    END AS 上线时间,
    -- 计算单段下线时长，无上线记录则标注说明
    CONCAT(
        DATEDIFF(MINUTE, d.ts, COALESCE(u.ts, GETDATE())),
        '分钟',
        CASE WHEN u.ts IS NULL THEN '（因无上线记录，基于当前时间计算）' ELSE '' END
    ) AS 单段下线时长
FROM connected d
-- 关联当前下线记录之后的第一条上线记录
LEFT JOIN connected u 
    ON d.IP = u.IP 
    AND u.stat = 'up' 
    AND u.ts = (
        SELECT MIN(ts) 
        FROM connected 
        WHERE IP = d.IP AND stat = 'up' AND ts > d.ts
    )
WHERE d.stat = 'down'
ORDER BY d.IP, d.ts;

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