SQL Server 如何统计指定月份区间内各月的周日总天数

业务场景 #

给定任意一段可自定义的起止日期区间，需要按每个自然月做分组，统计区间内每个自然月包含的周日总个数。
例如统计区间设置为2020年10月到2020年12月时，预期输出结果如下：

可落地实现方案 #

• 无需依赖额外的系统日历表，直接通过递归CTE生成指定起止区间内的所有连续自然日期
• 内置数据库DATEFIRST参数适配逻辑，无论当前SQL Server实例配置的每周起始日是周几，都能准确识别日期是否为周日，避免因配置差异导致的统计偏差
• 按日期所属的年、月维度分组，直接输出对应的月份名称和周日统计值

可直接执行的统计SQL：

-- 替换为实际需要统计的起止日期
DECLARE 
    @StartDate DATE = '20201001',
    @EndDate DATE = '20201231'

;WITH DateSequence AS (
    -- 递归生成区间内全量连续日期
    SELECT @StartDate AS CalendarDate
    UNION ALL
    SELECT DATEADD(day, 1, CalendarDate)
    FROM DateSequence
    WHERE CalendarDate < @EndDate
)
SELECT
    DATENAME(month, CalendarDate) AS [月份名称],
    COUNT(*) AS [周日数量]
FROM DateSequence
-- 适配不同DATEFIRST配置，精准识别周日
WHERE DATEPART(WEEKDAY, CalendarDate) = CASE WHEN @@DATEFIRST = 7 THEN 1 ELSE 8 - @@DATEFIRST END
GROUP BY YEAR(CalendarDate), MONTH(CalendarDate), DATENAME(month, CalendarDate)
ORDER BY YEAR(CalendarDate), MONTH(CalendarDate)
-- 取消递归层数限制，支持跨年度大范围统计
OPTION (MAXRECURSION 0)

注意事项 #

• 统计逻辑做了通用适配，在不同DATEFIRST配置的实例上都能得到一致的准确结果，不会出现漏算、错算
• 默认递归层数限制被取消后，单次查询支持跨多年的大范围日期统计，不会触发SQL Server默认100层递归的报错
• 如果生产环境有维护完善的日历维表，可以直接从维表中过滤对应区间的周日数据替换CTE部分的逻辑，整体查询性能会更优

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