如何基于订单记录统计月度NPWT活跃中心（含后续两个月延续）

需求规则 #

• 活跃判定：若中心在某月份产生订单，则该中心从订单当月起的连续3个月（当月+后续2个月）均视为活跃状态
• 统计要求：按月度累计统计活跃中心数，即下单当月的中心需计入对应三个月的统计结果中

原代码问题 #

原SQL仅统计了当月有订单发生的中心，未覆盖订单产生后两个月的活跃状态，无法满足累计统计需求。

解决方案SQL #

-- 1. 生成需要统计的月份维度表
WITH MonthList AS (
    SELECT 
        DATEFROMPARTS(YEAR(DmeOrderDate), MONTH(DmeOrderDate), 1) AS MonthStart,
        DATENAME(MONTH, DATEFROMPARTS(YEAR(DmeOrderDate), MONTH(DmeOrderDate), 1)) AS MonthName,
        MONTH(DmeOrderDate) AS MonthNum
    FROM [AtHome].[Reporting].[WoundQOrderDetails]
    WHERE DmeOrderDate BETWEEN '2024-01-01' AND GETDATE()
    GROUP BY YEAR(DmeOrderDate), MONTH(DmeOrderDate)
    UNION
    -- 补充订单后续两个月的月份（避免最后两个月无订单时缺失统计维度）
    SELECT 
        DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(DmeOrderDate), MONTH(DmeOrderDate), 1)) AS MonthStart,
        DATENAME(MONTH, DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(DmeOrderDate), MONTH(DmeOrderDate), 1))) AS MonthName,
        MONTH(DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(DmeOrderDate), MONTH(DmeOrderDate), 1))) AS MonthNum
    FROM [AtHome].[Reporting].[WoundQOrderDetails]
    WHERE DmeOrderDate BETWEEN '2024-01-01' AND GETDATE()
    GROUP BY YEAR(DmeOrderDate), MONTH(DmeOrderDate)
    UNION
    SELECT 
        DATEADD(MONTH, 2, DATEFROMPARTS(YEAR(DmeOrderDate), MONTH(DmeOrderDate), 1)) AS MonthStart,
        DATENAME(MONTH, DATEADD(MONTH, 2, DATEFROMPARTS(YEAR(DmeOrderDate), MONTH(DmeOrderDate), 1))) AS MonthName,
        MONTH(DATEADD(MONTH, 2, DATEFROMPARTS(YEAR(DmeOrderDate), MONTH(DmeOrderDate), 1))) AS MonthNum
    FROM [AtHome].[Reporting].[WoundQOrderDetails]
    WHERE DmeOrderDate BETWEEN '2024-01-01' AND GETDATE()
    GROUP BY YEAR(DmeOrderDate), MONTH(DmeOrderDate)
),
-- 2. 整理订单数据，标记每个订单对应的活跃月份范围
OrderActiveMonths AS (
    SELECT 
        c.CenterCode,
        w.DmeOrderNumberDisplay,
        DATEFROMPARTS(YEAR(w.DmeOrderDate), MONTH(w.DmeOrderDate), 1) AS OrderMonthStart,
        -- 订单对应的三个活跃月份：当月、+1月、+2月
        DATEADD(MONTH, n.Num, DATEFROMPARTS(YEAR(w.DmeOrderDate), MONTH(w.DmeOrderDate), 1)) AS ActiveMonthStart
    FROM [AtHome].[Reporting].[WoundQOrderDetails] w
    LEFT JOIN [AtHome].[Reporting].[DimCenter] c ON c.CenterSK = w.CenterSK
    -- 生成0、1、2三个数字，用来计算三个活跃月份
    CROSS JOIN (VALUES (0), (1), (2)) AS n(Num)
    WHERE w.DmeOrderDate BETWEEN '2024-01-01' AND GETDATE()
        AND w.DmeSignedByProvider = 1
        AND w.OrderType = 'NPWT'
        AND c.CenterCode IS NOT NULL
)
-- 3. 关联月份维度与活跃数据，统计结果
SELECT 
    ml.MonthName AS Month,
    -- 统计当月实际订单量（仅订单当月的订单）
    COUNT(DISTINCT CASE WHEN oam.OrderMonthStart = ml.MonthStart THEN oam.DmeOrderNumberDisplay ELSE NULL END) AS NPWT_Order_Volume,
    -- 统计当月活跃中心数（去重）
    COUNT(DISTINCT oam.CenterCode) AS NPWT_Active_Centers
FROM MonthList ml
LEFT JOIN OrderActiveMonths oam ON ml.MonthStart = oam.ActiveMonthStart
-- 过滤统计范围到2024-01至今
WHERE ml.MonthStart BETWEEN '2024-01-01' AND GETDATE()
GROUP BY ml.MonthName, ml.MonthNum
ORDER BY ml.MonthNum;

代码说明 #

• MonthList：生成所有需要统计的月份，包含订单当月及后续两个月，确保统计维度完整
• OrderActiveMonths：通过CROSS JOIN生成每个订单对应的三个活跃月份，建立订单与活跃月份的关联
• 最终统计：关联月份维度与活跃数据，分别计算当月订单量和累计活跃中心数，按月份排序

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