如何编写MSSQL查询生成报表，验证任意时刻至少90%系统无停机？

现有系统停机信息表 #

实际系统数量多于5台，需生成报表验证任意时间点至少90%系统处于无停机状态，预期输出格式如下：

预期输出报表 #

技术解决方案 #

核心思路 #

1. 计算总系统数量：统计所有唯一的Hostname数量，作为验证基准。
2. 生成关键时间点：收集所有停机的开始/结束时刻、每天的起止时间，确保覆盖所有系统状态变化的节点。
3. 生成时间分段：基于关键时间点创建连续的时间段，每个分段内处于停机状态的系统数量固定。
4. 拆分跨天分段：将跨天的时间段拆分为单日分段，匹配预期输出的日期维度。
5. 统计每个分段的停机系统数、计算持续时长，筛选出不符合90%无停机要求的时间段。

MSSQL 查询代码 #

-- 1. 计算总系统数量
DECLARE @TotalSystems INT;
SELECT @TotalSystems = COUNT(DISTINCT Hostname) FROM YourDowntimeTable;

-- 2. 生成所有关键时间点（停机起止时刻 + 每日起止时刻）
WITH TimePoints AS (
    SELECT [Start of Downtime] AS PointTime FROM YourDowntimeTable
    UNION
    SELECT [End of Downtime] AS PointTime FROM YourDowntimeTable
    UNION
    -- 生成所有涉及日期的0点
    SELECT DATEADD(DAY, n, CAST(MIN([Start of Downtime]) AS DATE)) 
    FROM YourDowntimeTable
    CROSS JOIN (
        SELECT TOP (DATEDIFF(DAY, MIN([Start of Downtime]), MAX([End of Downtime])) + 2) 
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n
        FROM sys.all_columns
    ) AS Numbers
    CROSS JOIN (SELECT MIN([Start of Downtime]) AS MinStart FROM YourDowntimeTable) AS MinStart
    UNION
    -- 生成所有涉及日期的23:59:59
    SELECT DATEADD(SECOND, -1, DATEADD(DAY, n+1, CAST(MIN([Start of Downtime]) AS DATE)))
    FROM YourDowntimeTable
    CROSS JOIN (
        SELECT TOP (DATEDIFF(DAY, MIN([Start of Downtime]), MAX([End of Downtime])) + 2) 
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n
        FROM sys.all_columns
    ) AS Numbers
    CROSS JOIN (SELECT MIN([Start of Downtime]) AS MinStart FROM YourDowntimeTable) AS MinStart
),
-- 3. 生成最小粒度的连续时间分段
TimeSegments AS (
    SELECT 
        t1.PointTime AS SegmentStart,
        t2.PointTime AS SegmentEnd
    FROM TimePoints t1
    JOIN TimePoints t2 ON t2.PointTime > t1.PointTime
    WHERE NOT EXISTS (
        SELECT 1 FROM TimePoints t3 
        WHERE t3.PointTime > t1.PointTime AND t3.PointTime < t2.PointTime
    )
),
-- 4. 将跨天分段拆分为单日分段
DailySegments AS (
    SELECT
        CAST(SegmentStart AS DATE) AS [Date],
        CASE 
            WHEN CAST(SegmentStart AS DATE) = CAST(SegmentEnd AS DATE) THEN SegmentStart
            ELSE DATEADD(DAY, DATEDIFF(DAY, 0, SegmentStart), 0)
        END AS SegmentStartDaily,
        CASE 
            WHEN CAST(SegmentStart AS DATE) = CAST(SegmentEnd AS DATE) THEN SegmentEnd
            ELSE DATEADD(SECOND, -1, DATEADD(DAY, DATEDIFF(DAY, 0, SegmentStart) + 1, 0))
        END AS SegmentEndDaily
    FROM TimeSegments
    WHERE SegmentStart < SegmentEnd
    UNION ALL
    SELECT
        CAST(SegmentEnd AS DATE) AS [Date],
        DATEADD(DAY, DATEDIFF(DAY, 0, SegmentEnd), 0) AS SegmentStartDaily,
        SegmentEnd AS SegmentEndDaily
    FROM TimeSegments
    WHERE CAST(SegmentStart AS DATE) < CAST(SegmentEnd AS DATE)
      AND SegmentEnd > DATEADD(DAY, DATEDIFF(DAY, 0, SegmentStart) + 1, 0)
),
-- 5. 统计每个日分段内的停机系统数量
DowntimeCounts AS (
    SELECT
        ds.[Date],
        ds.SegmentStartDaily AS [Start of Downtime],
        ds.SegmentEndDaily AS [End of Downtime],
        COUNT(DISTINCT dt.Hostname) AS [Systems with downtime]
    FROM DailySegments ds
    LEFT JOIN YourDowntimeTable dt 
        ON dt.[Start of Downtime] <= ds.SegmentEndDaily 
        AND dt.[End of Downtime] >= ds.SegmentStartDaily
    GROUP BY ds.[Date], ds.SegmentStartDaily, ds.SegmentEndDaily
)
-- 6. 计算持续时长并输出最终报表
SELECT
    [Date],
    [Systems with downtime],
    DATEDIFF(SECOND, [Start of Downtime], [End of Downtime]) + 1 AS [Duration in seconds],
    [Start of Downtime],
    [End of Downtime]
FROM DowntimeCounts
WHERE [Systems with downtime] > @TotalSystems * 0.1  -- 筛选出停机系统占比超过10%的时间段
ORDER BY [Date], [Start of Downtime];

代码说明 #

• 替换YourDowntimeTable为实际存储停机信息的表名。
• TimePoints CTE确保覆盖所有可能导致停机系统数变化的时间节点，避免遗漏关键时间段。
• DailySegments处理跨天分段，保证每个输出行的时间段都属于单一日期。
• 最后通过筛选条件直接定位到不符合"至少90%系统无停机"要求的时间段，便于快速验证。

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