MS SQL SERVER按每月1日起始周统计逻辑连接停机次数

需求说明 #

• 统计范围：仅统计conn_type = 'logical'（逻辑连接）的停机记录
• 周度规则：每月1日为当月第一个统计周的起点，每7天划分为一个统计周
• 统计维度：基于downtime（停机时间）、conn_type（连接类型）字段计算周度停机次数

源数据结构样例 #

期望输出格式 #

原有方案问题 #

原有代码使用DATEPART(WEEK,)取周编号，该函数默认周度规则为SQL Server全局配置的周起始（通常为周日/周一），和自定义的「每月1日为周起点」规则完全不匹配，无法得到正确结果。原有错误写法如下：

with temp as (
select dates, datepart(WEEK,dates) as vr, count(comp_type) over (partition by datepart(WEEK,dates)) as total_count
from date_buckets where comp_type = 'logical') select distinct min(dates) over (partition by vr) as week_start,
max(dates) over (partition by vr) as week_end,
total_count
from temp

正确实现代码 #

核心逻辑：先计算每条停机记录距离当月1日的天数偏移，通过偏移量整除7得到当月内的周序号，再基于周序号分组聚合，完全绕开默认周度规则的影响：

WITH record_with_month_start AS (
    SELECT
        downtime,
        -- 计算每条记录所在月的1号日期，作为周度计算基准
        DATEADD(DAY, 1 - DAY(CAST(downtime AS DATE)), CAST(downtime AS DATE)) AS month_start
    FROM 你的停机记录表名 -- 替换为实际表名
    WHERE conn_type = 'logical' -- 仅筛选逻辑连接记录
),
record_with_week_range AS (
    SELECT
        -- 计算所属周的起始日期
        DATEADD(
            DAY,
            (DATEDIFF(DAY, month_start, CAST(downtime AS DATE)) / 7) * 7,
            month_start
        ) AS From_date,
        -- 计算所属周的截止日期
        DATEADD(
            DAY,
            (DATEDIFF(DAY, month_start, CAST(downtime AS DATE)) / 7) * 7 + 6,
            month_start
        ) AS To_date
    FROM record_with_month_start
)
SELECT
    CAST(From_date AS DATE) AS From_date,
    CAST(To_date AS DATE) AS To_date,
    COUNT(1) AS Count
FROM record_with_week_range
GROUP BY From_date, To_date
ORDER BY From_date

逻辑说明 #

• 日期计算统一转为DATE类型，忽略停机时间的时分秒部分，避免时间精度导致分组错误
• 周度划分完全匹配自定义规则：每月1-7日为第一周，8-14日为第二周，以此类推，月底不足7天的时段会自动单独划分为最后一周
• 无需修改SQL Server全局周度配置，兼容性强

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