You need to enable JavaScript to run this app. SQL场景下如何实现6个月滚动周期的支付金额统计
优惠活动
大模型
产品
解决方案
定价
更多

SQL场景下如何实现6个月滚动周期的支付金额统计

6个月滚动统计区间SQL实现方案

实现核心逻辑

无需强依赖递归逻辑,固定长度滚动统计可通过「月度支付预聚合 + 报告期-滚动月映射生成」两层结构落地,递归仅作为窗口长度动态调整场景的备选方案。

第一步:预聚合月度支付金额

先将payments表按payment_month维度聚合,消除同一月度内的多条零散支付记录,作为关联基础:

-- 月度支付汇总基础数据集
WITH monthly_pay AS (
    SELECT 
        payment_month,
        SUM(payment_amount) AS month_total_amount
    FROM payments
    GROUP BY payment_month
)

基于样例数据,该聚合结果为:

payment_monthmonth_total_amount
201911100
201912100
20200175
202002100
20200395
202004165
202005125
20200675
202007100
202008100
202009315

第二步:生成报告期与滚动月的映射关系

提供两种实现路径,可根据数据规模、SQL语法兼容性选择:

路径1:非递归实现(生产环境推荐)

固定6个月窗口下,通过日期偏移直接生成映射,无递归栈开销,兼容性、性能更优:

-- 生成每个报告期对应的6个滚动月清单(当前月+前5个月)
WITH period_map AS (
    SELECT 
        c.CalendarPeriod,
        CONVERT(INT, FORMAT(
            DATEADD(MONTH, -offset, c.CalendarDate),
            'yyyyMM'
        )) AS rolling_period
    FROM calendar c
    -- 交叉连接偏移量列表,0代表当前月,1-5代表往前推1-5个月
    CROSS JOIN (VALUES (0),(1),(2),(3),(4),(5)) AS offsets(offset)
)

偏移逻辑直接基于calendar表中存储的月末日期计算,无需处理YYYYMM整数格式的跨年跨月转换问题,和payment_month的存储格式天然对齐。

路径2:递归CTE实现(适合动态窗口场景)

如果后续需要灵活调整滚动窗口长度(如3个月、12个月),可通过递归逻辑实现参数化控制:

WITH RECURSIVE period_map_recursive AS (
    -- 递归锚点:每个报告期初始关联自身(偏移0)
    SELECT 
        CalendarPeriod,
        CalendarPeriod AS rolling_period,
        0 AS month_offset
    FROM calendar
    UNION ALL
    -- 递归分支:每次往前推1个月,直到偏移达到目标窗口长度-1
    SELECT 
        pr.CalendarPeriod,
        CONVERT(INT, FORMAT(
            DATEADD(MONTH, -1, 
                DATEFROMPARTS(LEFT(pr.rolling_period,4), RIGHT(pr.rolling_period,2), 1)
            ),
            'yyyyMM'
        )) AS rolling_period,
        pr.month_offset + 1 AS month_offset
    FROM period_map_recursive pr
    WHERE pr.month_offset < 5 -- 6个月窗口需递归5次
)

第三步:关联输出两类目标结果

结果1:单月粒度明细数据

将映射表与预聚合的月度支付数据关联,得到每个报告期下各滚动月的金额:

WITH monthly_pay AS (
    SELECT 
        payment_month,
        SUM(payment_amount) AS month_total_amount
    FROM payments
    GROUP BY payment_month
),
period_map AS (
    SELECT 
        c.CalendarPeriod,
        CONVERT(INT, FORMAT(DATEADD(MONTH, -offset, c.CalendarDate), 'yyyyMM')) AS rolling_period
    FROM calendar c
    CROSS JOIN (VALUES (0),(1),(2),(3),(4),(5)) AS offsets(offset)
)
SELECT 
    pm.CalendarPeriod,
    pm.rolling_period,
    ISNULL(mp.month_total_amount, 0) AS rolling_amount
FROM period_map pm
LEFT JOIN monthly_pay mp 
    ON pm.rolling_period = mp.payment_month
ORDER BY pm.CalendarPeriod, pm.rolling_period

运行结果与提供的明细样例完全匹配。

结果2:报告期维度滚动总金额

在明细结果基础上按报告期分组求和,得到单个报告期对应的6个月窗口总金额:

WITH monthly_pay AS (
    SELECT 
        payment_month,
        SUM(payment_amount) AS month_total_amount
    FROM payments
    GROUP BY payment_month
),
period_map AS (
    SELECT 
        c.CalendarPeriod,
        CONVERT(INT, FORMAT(DATEADD(MONTH, -offset, c.CalendarDate), 'yyyyMM')) AS rolling_period
    FROM calendar c
    CROSS JOIN (VALUES (0),(1),(2),(3),(4),(5)) AS offsets(offset)
)
SELECT 
    pm.CalendarPeriod,
    SUM(ISNULL(mp.month_total_amount, 0)) AS rolling_amount
FROM period_map pm
LEFT JOIN monthly_pay mp 
    ON pm.rolling_period = mp.payment_month
GROUP BY pm.CalendarPeriod
ORDER BY pm.CalendarPeriod

运行结果与聚合样例完全匹配:

CalendarPeriodrolling_amount
202004635
202005660
202006635
202007660
202008660
202009880

内容的提问来源于stack exchange,提问作者tlk27

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.18 01:15:03