如何在SQL中统计两个指定日期之间的周一数量？

你已经实现了各郡每天报告占比的统计，要计算每日平均报告数，核心是先算出日期范围内每个星期几的总天数，再用该天的报告总数除以对应天数即可。

步骤1：计算指定日期范围内某星期几的天数 #

以SQL Server为例（你的原代码用了DATEPART(DW, ...)，默认周日为1，周一为2，以此类推），计算两个日期之间某星期几的通用公式如下：

-- 以计算周一（DATEPART(DW)=2）的天数为例
DECLARE @StartDate DATETIME = '2023-01-01 00:00:00.000';
DECLARE @EndDate DATETIME = '2023-07-19 23:59:59.999';

SELECT 
    -- 总周数 + 首尾日期的补全调整
    DATEDIFF(WEEK, @StartDate, @EndDate)
    + CASE WHEN DATEPART(DW, @StartDate) <= 2 AND DATEPART(DW, @EndDate) >= 2 THEN 1 ELSE 0 END
    - CASE WHEN DATEPART(DW, @StartDate) > 2 THEN 1 ELSE 0 END AS 周一总天数;

公式逻辑：

• DATEDIFF(WEEK, ...)获取两个日期之间的完整周数，每个完整周包含一个目标星期几
• 首日期若早于/等于目标星期几，且尾日期晚于/等于目标星期几，加1（覆盖首尾周包含目标日的情况）
• 首日期若晚于目标星期几，减1（排除首周不包含目标日的情况）

步骤2：整合到原查询，生成平均报告数 #

把上述逻辑嵌入原SQL，即可同时输出占比和每日平均报告数：

DECLARE @StartDate DATETIME = '2023-01-01 00:00:00.000';
DECLARE @EndDate DATETIME = '2023-07-19 23:59:59.999';

-- 预计算各星期几的总天数
WITH DayCounts AS (
    SELECT
        -- 周日（DW=1）天数
        DATEDIFF(WEEK, @StartDate, @EndDate)
        + CASE WHEN DATEPART(DW, @StartDate) <= 1 AND DATEPART(DW, @EndDate) >= 1 THEN 1 ELSE 0 END
        - CASE WHEN DATEPART(DW, @StartDate) > 1 THEN 1 ELSE 0 END AS SundayCount,
        -- 周一（DW=2）天数
        DATEDIFF(WEEK, @StartDate, @EndDate)
        + CASE WHEN DATEPART(DW, @StartDate) <= 2 AND DATEPART(DW, @EndDate) >= 2 THEN 1 ELSE 0 END
        - CASE WHEN DATEPART(DW, @StartDate) > 2 THEN 1 ELSE 0 END AS MondayCount,
        -- 周二（DW=3）天数
        DATEDIFF(WEEK, @StartDate, @EndDate)
        + CASE WHEN DATEPART(DW, @StartDate) <= 3 AND DATEPART(DW, @EndDate) >= 3 THEN 1 ELSE 0 END
        - CASE WHEN DATEPART(DW, @StartDate) > 3 THEN 1 ELSE 0 END AS TuesdayCount,
        -- 周三（DW=4）天数
        DATEDIFF(WEEK, @StartDate, @EndDate)
        + CASE WHEN DATEPART(DW, @StartDate) <= 4 AND DATEPART(DW, @EndDate) >= 4 THEN 1 ELSE 0 END
        - CASE WHEN DATEPART(DW, @StartDate) > 4 THEN 1 ELSE 0 END AS WednesdayCount,
        -- 周四（DW=5）天数
        DATEDIFF(WEEK, @StartDate, @EndDate)
        + CASE WHEN DATEPART(DW, @StartDate) <= 5 AND DATEPART(DW, @EndDate) >= 5 THEN 1 ELSE 0 END
        - CASE WHEN DATEPART(DW, @StartDate) > 5 THEN 1 ELSE 0 END AS ThursdayCount,
        -- 周五（DW=6）天数
        DATEDIFF(WEEK, @StartDate, @EndDate)
        + CASE WHEN DATEPART(DW, @StartDate) <= 6 AND DATEPART(DW, @EndDate) >= 6 THEN 1 ELSE 0 END
        - CASE WHEN DATEPART(DW, @StartDate) > 6 THEN 1 ELSE 0 END AS FridayCount,
        -- 周六（DW=7）天数
        DATEDIFF(WEEK, @StartDate, @EndDate)
        + CASE WHEN DATEPART(DW, @StartDate) <= 7 AND DATEPART(DW, @EndDate) >= 7 THEN 1 ELSE 0 END
        - CASE WHEN DATEPART(DW, @StartDate) > 7 THEN 1 ELSE 0 END AS SaturdayCount
)
SELECT
    c.CountyID,
    -- 原有报告占比统计
    (SUM(IIF(DATEPART(DW, c.TimestampCreated)=1,1,0))) * 100.0 / COUNT(c.CountyID) AS [% Sunday],
    (SUM(IIF(DATEPART(DW, c.TimestampCreated)=2,1,0))) * 100.0 / COUNT(c.CountyID) AS [% Monday],
    (SUM(IIF(DATEPART(DW, c.TimestampCreated)=3,1,0))) * 100.0 / COUNT(c.CountyID) AS [% Tuesday],
    (SUM(IIF(DATEPART(DW, c.TimestampCreated)=4,1,0))) * 100.0 / COUNT(c.CountyID) AS [% Wednesday],
    (SUM(IIF(DATEPART(DW, c.TimestampCreated)=5,1,0))) * 100.0 / COUNT(c.CountyID) AS [% Thursday],
    (SUM(IIF(DATEPART(DW, c.TimestampCreated)=6,1,0))) * 100.0 / COUNT(c.CountyID) AS [% Friday],
    (SUM(IIF(DATEPART(DW, c.TimestampCreated)=7,1,0))) * 100.0 / COUNT(c.CountyID) AS [% Saturday],
    -- 新增每日平均报告数
    SUM(IIF(DATEPART(DW, c.TimestampCreated)=1,1,0)) * 1.0 / dc.SundayCount AS [Avg Sunday],
    SUM(IIF(DATEPART(DW, c.TimestampCreated)=2,1,0)) * 1.0 / dc.MondayCount AS [Avg Monday],
    SUM(IIF(DATEPART(DW, c.TimestampCreated)=3,1,0)) * 1.0 / dc.TuesdayCount AS [Avg Tuesday],
    SUM(IIF(DATEPART(DW, c.TimestampCreated)=4,1,0)) * 1.0 / dc.WednesdayCount AS [Avg Wednesday],
    SUM(IIF(DATEPART(DW, c.TimestampCreated)=5,1,0)) * 1.0 / dc.ThursdayCount AS [Avg Thursday],
    SUM(IIF(DATEPART(DW, c.TimestampCreated)=6,1,0)) * 1.0 / dc.FridayCount AS [Avg Friday],
    SUM(IIF(DATEPART(DW, c.TimestampCreated)=7,1,0)) * 1.0 / dc.SaturdayCount AS [Avg Saturday]
FROM vCourt c
CROSS JOIN DayCounts dc
WHERE c.TimestampCreated BETWEEN @StartDate AND @EndDate
GROUP BY c.CountyID, dc.SundayCount, dc.MondayCount, dc.TuesdayCount, dc.WednesdayCount, dc.ThursdayCount, dc.FridayCount, dc.SaturdayCount;

注意事项 #

• 若你的SQL ServerDATEFIRST设置不是默认的7（即周日为1），需调整DATEPART(DW, ...)对应的数值，或结合@@DATEFIRST计算，避免结果偏差。
• 若某星期几在日期范围内无数据，除法会报错，可添加NULLIF处理：SUM(...) *1.0 / NULLIF(dc.SundayCount, 0)，此时结果显示NULL而非报错。

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