如何基于日期变量高效构建滚动12个月的季度取消率统计数据？

1. 动态生成季度维度表（核心思路） #

先通过递归CTE生成每个ID对应的所有目标季度序列，自动计算每个季度对应的过去12个月时间范围，彻底替代手动编写UNION的方式。

DECLARE @StartDate DATE = '2022-01-01', @EndDate DATE = '2023-06-30';

WITH QuarterlyPeriods AS (
    -- 初始化第一个季度的时间边界
    SELECT
        ID,
        -- 季度起始日（如2023Q1为2023-01-01）
        DATEFROMPARTS(YEAR(@StartDate), ((DATEPART(QUARTER, @StartDate)-1)*3)+1, 1) AS QuarterStart,
        -- 季度结束日（如2023Q1为2023-03-31）
        DATEADD(DAY, -1, DATEADD(QUARTER, 1, DATEFROMPARTS(YEAR(@StartDate), ((DATEPART(QUARTER, @StartDate)-1)*3)+1, 1))) AS QuarterEnd,
        -- 过去12个月起始日（如2023Q1对应2022-04-01）
        DATEADD(MONTH, -11, DATEFROMPARTS(YEAR(@StartDate), ((DATEPART(QUARTER, @StartDate)-1)*3)+1, 1)) AS Rolling12Start,
        -- 过去12个月结束日（和季度结束日一致）
        DATEADD(DAY, -1, DATEADD(QUARTER, 1, DATEFROMPARTS(YEAR(@StartDate), ((DATEPART(QUARTER, @StartDate)-1)*3)+1, 1))) AS Rolling12End
    FROM (SELECT DISTINCT ID FROM YourTransactions) AS UniqueIDs
    UNION ALL
    -- 递归生成后续所有季度
    SELECT
        ID,
        DATEADD(QUARTER, 1, QuarterStart) AS QuarterStart,
        DATEADD(DAY, -1, DATEADD(QUARTER, 2, QuarterStart)) AS QuarterEnd,
        DATEADD(MONTH, -11, DATEADD(QUARTER, 1, QuarterStart)) AS Rolling12Start,
        DATEADD(DAY, -1, DATEADD(QUARTER, 2, QuarterStart)) AS Rolling12End
    FROM QuarterlyPeriods
    WHERE DATEADD(QUARTER, 1, QuarterStart) <= @EndDate
)

2. 关联原始数据计算取消率 #

将生成的季度维度表和交易数据关联，按ID、年、季度聚合，计算过去12个月的总销售额、总取消数及取消率：

SELECT
    qp.ID,
    YEAR(qp.QuarterEnd) AS Year,
    DATEPART(QUARTER, qp.QuarterEnd) AS Quarter,
    SUM(t.Sales) AS TotalSales,
    SUM(t.Cancellations) AS TotalCancellations,
    -- 处理销售额为0的情况，避免除以0报错
    CASE WHEN SUM(t.Sales) = 0 THEN 0.0 ELSE CAST(SUM(t.Cancellations) AS FLOAT)/SUM(t.Sales) END AS CancellationRate
FROM QuarterlyPeriods qp
LEFT JOIN YourTransactions t
    ON t.ID = qp.ID
    AND t.Date BETWEEN qp.Rolling12Start AND qp.Rolling12End
GROUP BY qp.ID, YEAR(qp.QuarterEnd), DATEPART(QUARTER, qp.QuarterEnd)
ORDER BY qp.ID, Year, Quarter;

3. 关键优化说明 #

• 动态范围支持：只需修改@StartDate和@EndDate参数，就能覆盖任意时间段的季度，无需手动调整每个时间分支。
• 性能提升：给YourTransactions表的ID和Date字段建立联合索引，可大幅加速时间范围关联查询。
• 边界准确性：用DATEFROMPARTS和DATEADD精确计算季度边界，避免手动计算的日期错误。

替代方案：窗口函数实现（适合连续时间序列） #

如果交易数据的时间序列连续，可先按月聚合，再用窗口函数计算滚动12个月指标，最后按季度提取结果：

DECLARE @StartDate DATE = '2022-01-01', @EndDate DATE = '2023-06-30';

WITH MonthlyAggregates AS (
    SELECT
        ID,
        DATEFROMPARTS(YEAR(Date), MONTH(Date), 1) AS MonthStart,
        SUM(Sales) AS MonthlySales,
        SUM(Cancellations) AS MonthlyCancellations
    FROM YourTransactions
    WHERE Date BETWEEN DATEADD(MONTH, -11, @StartDate) AND @EndDate
    GROUP BY ID, DATEFROMPARTS(YEAR(Date), MONTH(Date), 1)
),
Rolling12Month AS (
    SELECT
        ID,
        MonthStart,
        SUM(MonthlySales) OVER (PARTITION BY ID ORDER BY MonthStart ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS Rolling12Sales,
        SUM(MonthlyCancellations) OVER (PARTITION BY ID ORDER BY MonthStart ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS Rolling12Cancellations
    FROM MonthlyAggregates
)
SELECT
    ID,
    YEAR(MonthStart) AS Year,
    DATEPART(QUARTER, MonthStart) AS Quarter,
    -- 取季度最后一个月的滚动值作为该季度的指标
    LAST_VALUE(Rolling12Sales) OVER (PARTITION BY ID, YEAR(MonthStart), DATEPART(QUARTER, MonthStart) ORDER BY MonthStart) AS TotalSales,
    LAST_VALUE(Rolling12Cancellations) OVER (PARTITION BY ID, YEAR(MonthStart), DATEPART(QUARTER, MonthStart) ORDER BY MonthStart) AS TotalCancellations,
    CASE WHEN LAST_VALUE(Rolling12Sales) OVER (PARTITION BY ID, YEAR(MonthStart), DATEPART(QUARTER, MonthStart) ORDER BY MonthStart) = 0 THEN 0.0
         ELSE CAST(LAST_VALUE(Rolling12Cancellations) OVER (PARTITION BY ID, YEAR(MonthStart), DATEPART(QUARTER, MonthStart) ORDER BY MonthStart) AS FLOAT)
              / LAST_VALUE(Rolling12Sales) OVER (PARTITION BY ID, YEAR(MonthStart), DATEPART(QUARTER, MonthStart) ORDER BY MonthStart)
    END AS CancellationRate
FROM Rolling12Month
WHERE MonthStart <= @EndDate
GROUP BY ID, YEAR(MonthStart), DATEPART(QUARTER, MonthStart), MonthStart, Rolling12Sales, Rolling12Cancellations
HAVING MonthStart = MAX(MonthStart) OVER (PARTITION BY ID, YEAR(MonthStart), DATEPART(QUARTER, MonthStart))
ORDER BY ID, Year, Quarter;

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