时间戳

首页 > 解决方案 > 使用日期列作为终止条件的递归 CTE 到 SUM 金额列

sql - 使用日期列作为终止条件的递归 CTE 到 SUM 金额列

问题描述

我有一个包含贷款数据的表格，包括开始日期、贷款期限（贷款将持续多少个月）和每月付款。
我正在尝试创建一个递归 CTE，它将返回过去 12 个月每月支付的付款总和。

CREATE TABLE [dbo].[loan](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [starting_date] [date] NULL,
    [loan_term] [int] NULL,
    [monthly_payment] [decimal](19, 2) NULL
) ON [PRIMARY]
GO

样本数据：

INSERT INTO [dbo].[loan]
    ([starting_date]
    ,[loan_term]
    ,[monthly_payment])
VALUES
    ('8/1/2020', 52, 1),
    ('6/2/2010', 64, 2500),
    ('9/11/2011', 66, 650),
    ('7/4/2011', 36, 600),
    ('11/19/2014', 36, 450),
    ('4/21/2017', 24, 650),
    ('6/25/2009', 68, 7500),
    ('4/3/2016', 72, 12000),
    ('5/2/2013', 12, 330),
    ('7/11/2011', 32, 331.33),
    ('6/11/2013', 36, 200),
    ('7/11/2014', 34, 250),
    ('8/15/2015', 32, 673),
    ('9/15/2017', 31, 8),
    ('11/6/2019', 64, 65),
    ('10/13/2017', 52, 128)
GO

这是我尝试过的：

WITH loanCTE as 
(
-- Anchor Member
SELECT monthly_payment, DATEADD(month, loan_term, starting_date) as loan_end_date, starting_date, starting_date as current_month
    FROM loan

UNION ALL
-- Recursive Member
SELECT monthly_payment, loan_end_date, starting_date, DATEADD(month, 1, current_month)
    FROM loanCTE
    WHERE current_month < loan_end_date -- Terminating condition
)

SELECT  YEAR(current_month) as Year, MONTH(current_month) as Month,  SUM(monthly_payment) as total_montly_payments
    FROM loanCTE
    WHERE Current_Month BETWEEN GETDATE()-365 AND GETDATE()
    GROUP BY YEAR(current_month), MONTH(current_month) 
    ORDER BY YEAR(current_month) DESC, MONTH(current_month) DESC;

它返回这个：

Year    Month   total_montly_payments
2020    8   12001.00
2020    7   12193.00
2020    6   12193.00
2020    5   12193.00
2020    4   12201.00
2020    3   12201.00
2020    2   12201.00
2020    1   12201.00
2019    12  12201.00
2019    11  12201.00
2019    10  12136.00
2019    9   12136.00
2019    8   136.00

我可以说这是错误的，因为 2019-08 的 total_monthly_payments 应该有 12202.00，而 2020-08 的 total_monthly_payments 应该有 12194.00，但我不确定我哪里出错了。似乎我在递归成员中增加当前月份的方式或最终查询的某些内容已关闭，但我目前被卡住了。任何意见是极大的赞赏！

标签： sqlsql-servertsqlrecursioncommon-table-expression

解决方案

我认为您的查询工作正常，因为您正在过滤最新的一年记录，因此首先过滤当前月份（实际上问题是由于 current_month 中的日期值而发生的，我们应该使用月份的第一个日期来获得正确的结果），然后对结果集执行聚合。您可以先合并记录，然后再应用过滤器，如下所示：

;WITH loanCTE as 
(
-- Anchor Member
SELECT monthly_payment, DATEADD(MM, loan_term, starting_date) as loan_end_date, starting_date, starting_date as current_month
    FROM loan
UNION ALL
-- Recursive Member
SELECT monthly_payment, loan_end_date, starting_date, DATEADD(MM, 1, current_month)
    FROM loanCTE
    WHERE current_month < loan_end_date -- Terminating condition
)
,
temp as
(
    SELECT  YEAR(current_month) as [Year], MONTH(current_month) as [Month],  SUM(monthly_payment) as total_montly_payments
    FROM loanCTE
    GROUP BY YEAR(current_month), MONTH(current_month) 
)

SELECT *
FROM temp
WHERE datefromparts(YEAR, MONTH, 1) BETWEEN GETDATE()-365 AND GETDATE()
ORDER BY [Year] DESC, [Month] DESC

此外，对于 201908，总数将为 12136.0，来自以下：

('4/3/2016', 72, 12000), --12000

('9/15/2017', 31, 8), --8

('10/13/2017', 52, 128) --128

请在此处找到 db<>fiddle 。

推荐阅读