创建滚动期间 [英] Creating Rolling Periods
问题描述
我想每6个月创建一个滚动期，但我不确定这样做的最佳方法.我猜想这可能必须递归完成?
I am wanting to create a rolling period every 6 months and I am unsure of the best way to do this. I am guessing this might have to been done recursively?
我有一个付款表，其中包含类似于以下内容的数据:
I have a payments table that contains data similar to the following:
CREATE TABLE payments
([id] int, [payment_month] int, [payment_date] datetime, [payment_amount] int)
;
INSERT INTO payments
([id], [payment_month], [payment_date], [payment_amount])
VALUES
(1, 201911, '2019-11-01 00:00:00', 50),
(1, 201912, '2019-12-01 00:00:00', 50),
(1, 202001, '2020-01-01 00:00:00', 50),
(1, 202002, '2020-02-01 00:00:00', 50),
(1, 202003, '2020-03-01 00:00:00', 50),
(1, 202004, '2020-04-01 00:00:00', 50),
(1, 202005, '2020-05-01 00:00:00', 50),
(1, 202006, '2020-06-01 00:00:00', 25),
(1, 202007, '2020-07-01 00:00:00', 50),
(1, 202008, '2020-08-01 00:00:00', 50),
(1, 202009, '2020-09-01 00:00:00', 15),
(2, 201911, '2019-11-01 00:00:00', 50),
(2, 201912, '2019-12-01 00:00:00', 50),
(2, 202001, '2020-01-01 00:00:00', 25),
(2, 202002, '2020-02-01 00:00:00', 50),
(2, 202003, '2020-03-01 00:00:00', 45),
(2, 202004, '2020-04-01 00:00:00', 45),
(2, 202004, '2020-04-10 00:00:00', 20),
(2, 202005, '2020-05-01 00:00:00', 25),
(3, 202004, '2020-04-01 00:00:00', 50),
(3, 202005, '2020-05-01 00:00:00', 50),
(3, 202006, '2020-06-01 00:00:00', 50),
(3, 202007, '2020-07-01 00:00:00', 50),
(3, 202008, '2020-08-01 00:00:00', 50),
(3, 202009, '2020-09-01 00:00:00', 300)
;
我还有一个正在使用的日历表，它返回如下数据:
I also have a calendar table which I am using that returns data like this:
CREATE TABLE calendar
([CalendarPeriod] int, [CalendarDate] datetime)
;
INSERT INTO calendar
([CalendarPeriod], [CalendarDate])
VALUES
(202004, '2020-04-30 00:00:00'),
(202005, '2020-05-31 00:00:00'),
(202006, '2020-06-30 00:00:00'),
(202007, '2020-07-31 00:00:00'),
(202008, '2020-08-31 00:00:00'),
(202009, '2020-09-30 00:00:00')
;
我希望得到一个输出，其中来自 calendar 的每个 CalendarPeriod 与自身和前五个月配对. CalendarPeriod 作为报告期，每个月的滚动期为6个月.我的最终目标是通过 payment_month 将 payment_amounts 加入每个报告期，其中 payment_month 等于报告期内的滚动月份之一.
I am hoping to get an output where each CalendarPeriod from calendar is paired with itself and the 5 preceding months. CalendarPeriod acts as a reporting period and the rolling period for each month is 6 months. My end goal is to join payment_amounts via payment_month to each reporting period where payment_month equals one of the rolling months in a reporting period.
修改:我仅几个月和滚动周期的预期输出将如下表所示.如果需要，我可以通过 CalendarPeriod 操纵表格进行汇总.
My expected output for just months and rolling periods would look like the table below. I can manipulate the table to aggregate by CalendarPeriod if needed.
+----------------+----------------+----------------+
| CalendarPeriod | rolling_period | rolling_amount |
+----------------+----------------+----------------+
| 202004 | 201911 | 100 |
+----------------+----------------+----------------+
| 202004 | 201912 | 100 |
+----------------+----------------+----------------+
| 202004 | 202001 | 75 |
+----------------+----------------+----------------+
| 202004 | 202002 | 100 |
+----------------+----------------+----------------+
| 202004 | 202003 | 95 |
+----------------+----------------+----------------+
| 202004 | 202004 | 165 |
+----------------+----------------+----------------+
| 202005 | 201912 | 100 |
+----------------+----------------+----------------+
| 202005 | 202001 | 75 |
+----------------+----------------+----------------+
| 202005 | 202002 | 100 |
+----------------+----------------+----------------+
| 202005 | 202003 | 95 |
+----------------+----------------+----------------+
| 202005 | 202004 | 165 |
+----------------+----------------+----------------+
| 202005 | 202005 | 125 |
+----------------+----------------+----------------+
| 202006 | 202001 | 75 |
+----------------+----------------+----------------+
| 202006 | 202002 | 100 |
+----------------+----------------+----------------+
| 202006 | 202003 | 95 |
+----------------+----------------+----------------+
| 202006 | 202004 | 165 |
+----------------+----------------+----------------+
| 202006 | 202005 | 125 |
+----------------+----------------+----------------+
| 202006 | 202006 | 75 |
+----------------+----------------+----------------+
| 202007 | 202002 | 100 |
+----------------+----------------+----------------+
| 202007 | 202003 | 95 |
+----------------+----------------+----------------+
| 202007 | 202004 | 165 |
+----------------+----------------+----------------+
| 202007 | 202005 | 125 |
+----------------+----------------+----------------+
| 202007 | 202006 | 75 |
+----------------+----------------+----------------+
| 202007 | 202007 | 100 |
+----------------+----------------+----------------+
| 202008 | 202003 | 95 |
+----------------+----------------+----------------+
| 202008 | 202004 | 165 |
+----------------+----------------+----------------+
| 202008 | 202005 | 125 |
+----------------+----------------+----------------+
| 202008 | 202006 | 75 |
+----------------+----------------+----------------+
| 202008 | 202007 | 100 |
+----------------+----------------+----------------+
| 202008 | 202008 | 100 |
+----------------+----------------+----------------+
| 202009 | 202004 | 165 |
+----------------+----------------+----------------+
| 202009 | 202005 | 125 |
+----------------+----------------+----------------+
| 202009 | 202006 | 75 |
+----------------+----------------+----------------+
| 202009 | 202007 | 100 |
+----------------+----------------+----------------+
| 202009 | 202008 | 100 |
+----------------+----------------+----------------+
| 202009 | 202009 | 315 |
+----------------+----------------+----------------+
-- Alternate:
+----------------+----------------+
| CalendarPeriod | rolling_amount |
+----------------+----------------+
| 202004 | 635 |
+----------------+----------------+
| 202005 | 660 |
+----------------+----------------+
| 202006 | 635 |
+----------------+----------------+
| 202007 | 660 |
+----------------+----------------+
| 202008 | 660 |
+----------------+----------------+
| 202009 | 880 |
+----------------+----------------+
预先感谢您的帮助.
推荐答案
据我了解您的问题，您希望每个时期的最近6个月都可以滚动支付.
As I understand your question, you want a rolling sum of payments for the last 6 months for each period.
我认为您并不需要真正显示的中间数据集来实现此目标.您可以只使用窗口功能:
I don't think that you really need the intermediate dataset that you are showing to achieve this goal. You can just use window functions:
select c.calendarperiod,
sum(p.payment_amount) month_amount,
sum(sum(p.payment_amount)) over(
order by c.calendarperiod
rows between 5 preceding and current row
) rolling_6_month_amount
from calendar c
left join payments p
on p.payment_date >= datefromparts(year(c.calendardate), month(c.calendardate), 1)
and p.payment_date < dateadd(day, 1, c.calendardate)
group by c.calendarperiod
查询从日历开始，并带来每个月的相应付款-我调整了日期间隔，以防万一在该月的最后一天有付款.然后，我们按期间进行汇总.最后，我们可以使用窗口函数回顾过去5个期间并汇总相应的付款.
The query starts from the calendar, and bring the corresponding payments for each month - I adjusted the date interval, in case there are payments within the very last day of the month. Then, we aggregate by period. Finally, we can use window functions to look back over the last 5 periods and sum the corresponding payments.
这篇关于创建滚动期间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
