Azure SQL Studio 18：按工作区和月份统计活跃员工数优化咨询

针对你的需求，无需手动创建临时日期表，可以通过递归CTE生成动态月份序列或利用系统表快速生成月份范围两种方式优化，以下是具体实现：

方案1：递归CTE生成月份序列 #

递归CTE可以灵活生成指定日期范围内的所有月份，逻辑直观且易于调整：

DECLARE @StartDate DATE = '2022-01-01';
DECLARE @EndDate DATE = '2022-05-31';

-- 生成目标日期范围内的所有月份（包含每月第一天和最后一天）
WITH MonthRange AS (
    SELECT
        DATEFROMPARTS(YEAR(@StartDate), MONTH(@StartDate), 1) AS MonthStart,
        EOMONTH(@StartDate) AS MonthEnd
    UNION ALL
    SELECT
        DATEADD(MONTH, 1, MonthStart),
        EOMONTH(DATEADD(MONTH, 1, MonthStart))
    FROM MonthRange
    WHERE MonthStart < @EndDate
)
-- 关联源表统计活跃员工
SELECT
    wr.Workspace,
    FORMAT(mr.MonthStart, 'yyyy-MM') AS Month,
    COUNT(DISTINCT wr.Username) AS ActiveEmployeeCount
FROM MonthRange mr
LEFT JOIN YourSourceTable wr
    ON wr.[Added Date] <= mr.MonthEnd
    AND (wr.[Deletion Date] >= mr.MonthStart OR wr.[Deletion Date] IS NULL)
WHERE wr.Workspace IS NOT NULL -- 过滤无匹配的月份（可选）
GROUP BY wr.Workspace, FORMAT(mr.MonthStart, 'yyyy-MM')
ORDER BY wr.Workspace, Month
OPTION (MAXRECURSION 1000); -- 若日期范围超过1000个月需调整此值

逻辑说明 #

• 递归CTE先初始化起始月份，再逐月生成后续月份直到结束日期
• 关联条件确保用户在该月内至少有一天处于活跃状态：入职日期不晚于当月末，且离职日期不早于当月初（或未离职）
• COUNT(DISTINCT Username)避免同一用户在同一工作区同一月被重复统计

方案2：利用系统表生成月份序列 #

如果日期范围较大，递归CTE可能性能稍差，可借助系统表（如sys.all_objects）生成足够多的行，再计算月份范围，性能更优：

DECLARE @StartDate DATE = '2022-01-01';
DECLARE @EndDate DATE = '2022-05-31';

-- 生成月份序列（利用系统表生成足够行数）
WITH MonthRange AS (
    SELECT
        DATEADD(MONTH, n.Number, DATEFROMPARTS(YEAR(@StartDate), MONTH(@StartDate), 1)) AS MonthStart,
        EOMONTH(DATEADD(MONTH, n.Number, DATEFROMPARTS(YEAR(@StartDate), MONTH(@StartDate), 1))) AS MonthEnd
    FROM (
        SELECT DISTINCT TOP (DATEDIFF(MONTH, @StartDate, @EndDate) + 1)
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS Number
        FROM sys.all_objects
    ) n
)
-- 统计逻辑同方案1
SELECT
    wr.Workspace,
    FORMAT(mr.MonthStart, 'yyyy-MM') AS Month,
    COUNT(DISTINCT wr.Username) AS ActiveEmployeeCount
FROM MonthRange mr
LEFT JOIN YourSourceTable wr
    ON wr.[Added Date] <= mr.MonthEnd
    AND (wr.[Deletion Date] >= mr.MonthStart OR wr.[Deletion Date] IS NULL)
WHERE wr.Workspace IS NOT NULL
GROUP BY wr.Workspace, FORMAT(mr.MonthStart, 'yyyy-MM')
ORDER BY wr.Workspace, Month;

逻辑说明 #

• 借助sys.all_objects的行数生成连续数字，再转换为对应月份的起止日期
• 无需递归，适合大范围日期统计，性能更稳定

额外优化建议 #

• 将@StartDate和@EndDate设置为参数，方便快速调整筛选范围
• 若源表数据量大，建议给[Added Date]、[Deletion Date]、Workspace字段建立联合索引，提升关联查询速度
• 若需要包含没有活跃员工的工作区-月份组合，可去掉WHERE wr.Workspace IS NOT NULL，此时无活跃员工的分组会显示ActiveEmployeeCount为0

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