SQL基于服务启用与终止日期生成月度统计视图需求

要解决这个自动生成月度统计行的问题，我们可以利用**递归CTE（Common Table Expression）**来生成所需的时间序列，再结合服务数据进行关联统计，完全不需要手动创建月度行。下面是详细的实现方案：

1. 核心思路 #

• 自动生成覆盖所有需要统计的月度的日期序列（从最早的服务启用日期到报表运行日期）
• 对每个服务，判断其在每个月度是否处于活跃状态
• 统计每个月度内终止的服务数量
• 按区域、服务类型和报告月份分组汇总结果

2. 完整SQL实现（以SQL Server为例） #

首先，我们需要先生成月度日期序列，再关联服务数据进行统计：

-- 第一步：生成月度日期序列
WITH DateRange AS (
    -- 取最早的服务启用日期作为起始点，或手动指定2013-01-01
    SELECT 
        DATEFROMPARTS(YEAR(MIN([Put Into Service Date])), MONTH(MIN([Put Into Service Date])), 1) AS ReportMonth
    FROM YourServiceTable
    UNION ALL
    SELECT 
        DATEADD(MONTH, 1, ReportMonth) AS ReportMonth
    FROM DateRange
    -- 终止条件：生成到当前日期的上月（或报表运行日期）
    WHERE ReportMonth < DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
),
-- 第二步：处理服务的活跃和终止周期
ServicePeriods AS (
    SELECT
        Region,
        [Service No],
        [Type Of Service],
        [Put Into Service Date],
        -- 未终止的服务，终止日期设为当前日期的下月第一天
        ISNULL([Ceased Date], DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1))) AS CeasedDate
    FROM YourServiceTable
)
-- 第三步：统计月度活跃和终止服务数
SELECT
    sp.Region,
    -- 累计服务数（匹配你的预期结果格式）
    COUNT(DISTINCT sp.[Service No]) OVER (PARTITION BY sp.Region, sp.[Type Of Service] ORDER BY dr.ReportMonth) AS [Services To Date],
    sp.[Type Of Service],
    dr.ReportMonth,
    -- 统计当月活跃的服务数：服务启用日期<=当月最后一天，且终止日期>当月第一天
    SUM(CASE 
        WHEN sp.[Put Into Service Date] <= EOMONTH(dr.ReportMonth) 
             AND sp.CeasedDate > dr.ReportMonth 
        THEN 1 ELSE 0 
    END) AS [Active Services],
    -- 统计当月终止的服务数：终止日期落在当月范围内
    SUM(CASE 
        WHEN sp.CeasedDate BETWEEN dr.ReportMonth AND EOMONTH(dr.ReportMonth)
             AND sp.CeasedDate <> DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)) -- 排除未终止的服务
        THEN 1 ELSE 0 
    END) AS [Ceased Services]
FROM DateRange dr
CROSS JOIN ServicePeriods sp
-- 过滤掉服务周期完全不覆盖当前月度的情况
WHERE sp.[Put Into Service Date] <= EOMONTH(dr.ReportMonth)
  AND sp.CeasedDate > dr.ReportMonth
GROUP BY sp.Region, sp.[Type Of Service], dr.ReportMonth
ORDER BY dr.ReportMonth, sp.Region, sp.[Type Of Service]
OPTION (MAXRECURSION 0); -- 解除递归次数限制，适配长时间范围

3. 关键逻辑说明 #

• 递归CTE生成月度序列：从最早的服务启用日期开始，逐月生成到当前日期的月度第一天，自动覆盖所有需要统计的时间范围，彻底解决手动创建行的麻烦。
• 处理未终止服务：将Ceased Date为NULL的服务的终止日期设为下月第一天，确保这些服务会被持续统计到当前报表月份的活跃数中。
• 活跃服务判断：服务的启用日期不晚于当月最后一天，且终止日期晚于当月第一天，说明该服务在当月处于活跃状态。
• 终止服务判断：服务的终止日期落在当月范围内，且不是我们虚拟的“未终止日期”，才计入当月终止数。

4. 适配你的示例数据 #

针对你提供的示例数据：

在2018-06-01运行报表时，2018-06的统计行结果会是：

这是因为服务123456在2018-05终止，所以2018-06的终止数统计为1；服务124578仍处于活跃状态，所以活跃数为1。

5. 不同数据库的调整点 #

如果使用PostgreSQL，生成日期序列可以用generate_series函数替代递归CTE，写法更简洁：

SELECT generate_series(
    (SELECT DATE_TRUNC('month', MIN("Put Into Service Date")) FROM YourServiceTable),
    DATE_TRUNC('month', CURRENT_DATE),
    INTERVAL '1 month'
) AS ReportMonth

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