聚合含缺失行：基于前后行分区计算过去6个月交易数平均值

你的核心问题在于原查询使用了基于行数的窗口范围（ROWS BETWEEN 1 following and 6 following），而非基于时间周期的范围。当历史数据不足6个月时，窗口仅包含实际存在的行，AVG函数会自动除以窗口内的实际行数（比如4行），而非强制除以6。此外，原窗口的1 following and 6 following逻辑是取当前行之后的6行，这和你“过去6个月”的需求逻辑完全相反。

要强制按6个月周期计算平均值，关键是补全缺失的月份数据（无交易的月份按0计数），然后基于完整的6个月周期计算总和再除以6（而非依赖AVG的自动计算）。

具体实现代码 #

WITH AllMonths AS (
    -- 生成目标账号的连续月份序列，覆盖所有需要统计的时间范围
    SELECT DATEADD(MONTH, n, (SELECT MIN(EndOfMonth) FROM MonthlyTransCnt WHERE AccountNumber = '0000709510')) AS EndOfMonth
    FROM (
        -- 生成足够多的连续数字，覆盖从最早月份到当前的所有月份
        SELECT TOP (DATEDIFF(MONTH, (SELECT MIN(EndOfMonth) FROM MonthlyTransCnt WHERE AccountNumber = '0000709510'), GETDATE()) + 1)
               n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
        FROM sys.all_columns
    ) AS nums
),
AccountFullMonths AS (
    -- 关联账号与所有连续月份，无交易的月份补0
    SELECT 
        Acc.AccountNumber,
        AM.EndOfMonth,
        ISNULL(MT.cnt, 0) AS MonthlyTransCnt
    FROM AllMonths AM
    CROSS JOIN (SELECT DISTINCT AccountNumber FROM MonthlyTransCnt WHERE AccountNumber = '0000709510') AS Acc
    LEFT JOIN MonthlyTransCnt MT 
        ON MT.AccountNumber = Acc.AccountNumber 
        AND MT.EndOfMonth = AM.EndOfMonth
),
OrderedMonths AS (
    -- 按月份排序，标记行号用于窗口计算
    SELECT 
        AccountNumber,
        EndOfMonth,
        MonthlyTransCnt,
        ROW_NUMBER() OVER (PARTITION BY AccountNumber ORDER BY EndOfMonth) AS RowNum
    FROM AccountFullMonths
)
-- 关联交易表并计算过去6个月的强制平均值
SELECT 
    ST.AccountNumber,
    ST.PrevMonth,
    ST.[Transaction Effective Date],
    ST.[Transaction Amt],
    ST.CurrentMonthTransCnt,
    OM.EndOfMonth,
    -- 计算过去6个月交易数总和后除以6，强制按6个周期计算
    AvgMonthlyTransCntLast6Months = SUM(OM.MonthlyTransCnt) OVER (
        PARTITION BY OM.AccountNumber 
        ORDER BY OM.EndOfMonth 
        ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
    ) / 6.0
FROM EDWAnalytics.ML.TEMP_SymitarTransactionsFinal AS ST
LEFT JOIN OrderedMonths OM 
    ON OM.AccountNumber = ST.AccountNumber 
    AND OM.EndOfMonth = ST.PrevMonth
WHERE ST.AccountNumber = '0000709510';

代码关键点说明 #

1. 生成连续月份：通过AllMonths CTE生成目标账号从最早交易月份到当前的所有连续月份，解决缺失月份的问题。
2. 补全0值：AccountFullMonths CTE将账号与连续月份关联，无交易的月份用ISNULL(MT.cnt, 0)补0，确保每个月份都有计数。
3. 强制按6周期计算：使用SUM(...) OVER (...) / 6.0替代AVG，确保无论窗口内实际有多少行（补全后已覆盖完整周期），都强制除以6得到6个月的平均值。
4. 窗口范围修正：ROWS BETWEEN 5 PRECEDING AND CURRENT ROW表示取当前月份及之前的5个月份，刚好覆盖6个月的周期，符合“过去6个月”的统计逻辑。

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