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_month | month_total_amount |
|---|---|
| 201911 | 100 |
| 201912 | 100 |
| 202001 | 75 |
| 202002 | 100 |
| 202003 | 95 |
| 202004 | 165 |
| 202005 | 125 |
| 202006 | 75 |
| 202007 | 100 |
| 202008 | 100 |
| 202009 | 315 |
第二步:生成报告期与滚动月的映射关系
提供两种实现路径,可根据数据规模、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
运行结果与聚合样例完全匹配:
| CalendarPeriod | rolling_amount |
|---|---|
| 202004 | 635 |
| 202005 | 660 |
| 202006 | 635 |
| 202007 | 660 |
| 202008 | 660 |
| 202009 | 880 |
内容的提问来源于stack exchange,提问作者tlk27
相关产品推荐
相关产品推荐

