[
6月18日
-
7分钟阅读
[
保存
用SQL和Newton-Raphson方法计算时间加权利息
本教程将使用SQLite和Python。你所需要运行的是本地安装的Python3。到最后，你应该能够运行Python脚本，并在命令行中获得正确的输出。
我想把一个简短的教程放在一起，真正展示一下高级数学如何真正帮助数据分析。如果你不完全理解实现背后的数学，那也没关系。见鬼，我也不是一个专家。
很多年前我父亲给我看了这个，我想写这个，因为我觉得它很酷，而且今天是父亲节。
我们想计算可能有数百万个银行账户和每个账户的数百次交易，鉴于每个账户余额在一年内的所有活动，回报率（利息）是多少。
回报率的天真实现只是需要。
((year_end_balance — beginning_balance) / year_end_balance) * 100
这就是一年中的增长率，但这是有限的。它没有考虑到这一年中影响余额的流入和流出。计算这个公式的简单（嗯，不那么简单）方法是很麻烦的，以至于投资百科全书关于这个问题的文章建议你只用一个在线计算器来计算，而不是试图用手来做。
幸运的是，有一个巧妙的方法，我们可以使用Netwon Raphson方法来实现二次收敛。
首先让我们创建一个Python文件，我们可以用它来做分析。
在CLI上运行。
touch interest_calc.py 并在你喜欢的IDE中打开该文件。
然后我们的第一步是添加夹具数据生成函数。这些片段的直接github链接在本教程中定期提供，如果你想在我们前进时复制和粘贴代码。
get_data 的直接github链接。
为了清晰起见，本教程中只有一个唯一的账户ID =1 ，但我们正在构建的代码将在任何数量的账户ID上工作。然后我们添加特定日期的余额值。我把数据按日期排序，只是为了方便阅读，但我们很快就会发现这并不重要。
现在让我们启动calc_time_weighted_interest ，创建数据库并插入数据，以便我们可以对其进行查询。
首先，我们建立一个连接到内存中的暂存数据库。这意味着数据将在我们的Python脚本执行后蒸发。然后我们创建一个表account_balance ，为account_id,date, 和balance 字段设置列。最后，让我们把我们的假数据插入到表中。虽然这只是函数的前半部分。
现在我们设置一个自定义函数，名称为TIME_WEIGHTED_INTEREST，并给它一个指向其他地方定义的time_weighted_interest 函数的指针。我们将在一分钟后讨论这个问题。
在下一行，我们用游标执行一个查询，使用从get_query() 返回的内容，这就是我们要开始写SQL的地方。我们接下来会讨论这个问题。
游标fetchall()将返回所有的结果。如果结果的长度为零，则引发一个错误。每个结果都会被解压到两个变量account_id 和interest 。我们在夹具数据中只有一个账户ID，所以只需返回第一个利息结果。最后让我们关闭连接。
calc_time_weighted_interest 的直接github链接。
现在我们准备开始编写我们的查询。
开始函数get_query ，让我们开始我们的CTE链。
首先，我们想把之前的余额添加到每个记录中。我们可以用一个Window函数来做这个事情。通过account_id ，对数据进行分区，并在每个分区内按date 的排序顺序查看行。我们可以使用LAG(balance,1) ，将窗口中的前期余额添加到每一行中。
在下一个CTE中，我们将使用prior_balance ，将每个balance 转化为前一个记录的调整差额。在年初的第一个余额的情况下，以前的余额将是NULL，所以我们只需设置adjustment = balance 。否则，我们设置adjustment =balance — prior_balance 。
因此，如果我们以例如
date balance prior_balance2013-01-01 500 NULL2013-04-11 550 500
不，我们最后的结果是
date adjustment2013-01-01 5002013-04-11 50
现在让我们做另一个转换。
在下一个CTE中，我们把日期转换为剩余年份的百分比。这将成为一个介于0.0和1.0之间的值。其中0.0=12月31日，1.0=1月1日。
要做到这一点，我们把一年中的某一天作为一个整数，从365中减去，然后用这个结果除以365。四舍五入到最接近的小数点后2位。
因此，例如，2013-04-11是一年中的第100天。
所以(365-100)/365 = 0.73
我们认为这是一年中剩余的百分比，给定的是一年中的哪一天。
既然如此，为了简单起见，让我们把adjustment 四舍五入到小数点后两位数。
现在按照我们的例子，这个CTE的输出将是。
adjustment perc_year_remaining 500.00 1.00 50.00 0.73
现在让我们做另一个转换。
在这个CTE中，让我们按account id 进行排序，在每个account_id 中按perc_year_remaining 升序排序（年份的时间顺序相反）。因此，举例来说，在每个账户ID中，该年的最后一次调整将出现在第一位，其次是倒数第二位，等等。
作为这个转换的一部分，我们通过管道| ，将perc_year_remaining 和adjustment 连接起来。
|| 是SQLite对字符串连接的语法。
因此，按照我们的例子，我们的行变成了。
perc_adj_pair 0.73|50.001.00|500.00
逆时针排序的输出顺序对下一步CTE很重要。
现在我们GROUP BYaccount_id，并将每一对以管道分隔的(adjustment, perc_year_remaining)串联成该账户ID的一个字符串。每一对都是;分隔的。我们必须使用字符串将数据编码为一个列表，因为不幸的是SQLite没有列表/数组的复合值类型。
结果是每一个account_id ，所有的调整对都以逆时针的排序方式串联在一起。
所以，现在我们对account_id = 1
adjustments0.73|50;1.00|500
现在我们终于可以调用我们的自定义函数来计算一年中所有账户ID的时间加权利息了。
直接的github链接为get_query
在我们运行SQL之前，我们必须实现time_weighted_interest_function
在我们的函数中，我们必须首先将字符串解压为一个(perc_year_remaining, adjustment) 对的列表。然后，我们将这两个数据转换为浮点值。我们可以把investments 这个变量看成是(perc_year_remaining, adjustment) 对的列表。
接下来，我们要计算该账户在年底的余额。我们可以通过加总所有的调整数来实现。由于我们将年初的余额作为调整数，所以这将是可行的。我们也去掉了最后的调整，因为不需要它来收敛真正的时间加权利率。
但我们的功能还没有完成。接下来是多汁的部分。收敛函数。
我们在投资上最多运行25次收敛函数，看看我们是否已经收敛到误差低于<0.0000001 。如果是这样，我们就可以跳出重试循环，并返回x * 100.0，作为利率。这时，我们需要投资按相反的时间顺序排列。
直接的github链接为time_weighted_interest
接下来让我们调用我们的基础函数calc_time_weighted_interest，启动并打印结果。
另外，在脚本的顶部，我们应该添加一些导入。
现在我们准备从CLI上运行Python脚本。
python calc_interest.py
我们应该看到大致是：9.4% ，作为我们夹具测试数据中单一账户的时间加权利率。
提高编码水平
谢谢你成为我们社区的一部分!更多内容在Level Up Coding出版物中。
关注。Twitter,LinkedIn,Newsletter
Level Up正在改变科技招聘的方式 ➡️ 加入我们的人才集体
