Postgresql:计算每个投资者每天的总投资份额 [英] Postgresql: calculate share of total investment per investor, per day

问题描述

注意:我在这篇文章中使用的是 Y-m-d 日期格式.

所以我有一个包含三个表的数据库:

投资者、投资、daily_stats

我已经在这个 db-fiddle 上设置了它们:

因此，每个投资者在总投资中的份额会根据投资的数量和规模而变化.我们来看一个日期:2021-02-03

在 daily_stats 中，该日期的利润为 339 美元

当日总投资为 25000 美元

John 的份额为 5000 美元(20%)，他的获利百分比为 20.

迈克的份额为 20000 美元(80%)，他的获利百分比为 40.

因此迈克以 339 * 0.8 * 0.4 = 108.48 回家

约翰取 339 * 0.2 * 0.2 = 13.56

现在我想我可以为我想要选择统计数据的时间段设置一个 generate_series 并加入投资/daily_stats 表，但我不知道如何设置它以便投资者的利润被如所示每天计算

解决方案

以下内容可以同时应用于所有用户的任何日期范围.在这个特定的 SQL 中，我计算了 2021-02-01 到 2021-02-05 之间的 user_profit.

另请注意，我认为您对 John 的份额为 $5000 (25%) 的计算是不正确的.25000 美元中的 5000 美元是 20%，而不是 25%.同样的问题也适用于迈克的份额.因此，以下结果与您的预期结果不完全相符.但我相信这个 SQL 是正确的.

SELECT s.date, s.profit, i.user_id, i.amount, i.percent, SUM(i.amount) OVER (PARTITION BY s.date) AS total_inv, ROUND(s.profit * (i.percent/100.0) * i.amount/SUM(i.amount) OVER (PARTITION BY s.date), 2) AS user_profitFROM daily_stats AS像我一样加入投资ON s.date BETWEEN i.start_date AND i.end_date'2021-02-01' 和 '2021-02-05' 之间的日期ORDER BY s.date, i.user_id;+------------+--------+---------+--------+---------+------------+------------+|日期 |利润|用户 ID |金额 |百分比 |total_inv |用户利润|+------------+--------+---------+--------+---------+------------+------------+|2021-02-01 |第248话1 |5000 |20 |25000 |9.92 ||2021-02-01 |第248话2 |20000 |40 |25000 |79.36 ||2021-02-02 |第476话1 |5000 |20 |25000 |19.04 ||2021-02-02 |第476话2 |20000 |40 |25000 |152.32 ||2021-02-03 |第339话1 |5000 |20 |25000 |13.56 ||2021-02-03 |第339话2 |20000 |40 |25000 |108.48 ||2021-02-04 |第464话1 |5000 |20 |25000 |18.56 ||2021-02-04 |第464话2 |20000 |40 |25000 |148.48 ||2021-02-05 |156 |1 |5000 |20 |25000 |6.24 ||2021-02-05 |156 |2 |20000 |40 |25000 |49.92 |+------------+--------+---------+--------+---------+------------+------------+

我也更新了你的小提琴:你的小提琴解决方案

这里的结果也包括一月份的日期，显示了这些日期的不同 total_investment:

+------------+--------+---------+--------+---------+-----------+-------------+|日期 |利润|用户 ID |金额 |百分比 |total_inv |用户利润|+------------+--------+---------+--------+---------+------------+------------+|2021-01-28 |第488话1 |10000 |20 |30000 |32.53 ||2021-01-28 |第488话2 |20000 |40 |30000 |130.13 ||2021-01-29 |480 |1 |10000 |20 |30000 |32.00 ||2021-01-29 |480 |2 |20000 |40 |30000 |128.00 ||2021-01-30 |第332话1 |10000 |20 |30000 |22.13 ||2021-01-30 |第332话2 |20000 |40 |30000 |88.53 ||2021-01-31 |第461话1 |10000 |20 |30000 |30.73 ||2021-01-31 |第461话2 |20000 |40 |30000 |122.93 ||2021-02-01 |第248话1 |5000 |20 |25000 |9.92 ||2021-02-01 |第248话2 |20000 |40 |25000 |79.36 ||2021-02-02 |第476话1 |5000 |20 |25000 |19.04 ||2021-02-02 |第476话2 |20000 |40 |25000 |152.32 ||2021-02-03 |第339话1 |5000 |20 |25000 |13.56 ||2021-02-03 |第339话2 |20000 |40 |25000 |108.48 ||2021-02-04 |第464话1 |5000 |20 |25000 |18.56 ||2021-02-04 |第464话2 |20000 |40 |25000 |148.48 ||2021-02-05 |156 |1 |5000 |20 |25000 |6.24 ||2021-02-05 |156 |2 |20000 |40 |25000 |49.92 |+------------+--------+---------+--------+---------+------------+------------+

Notice: I am using a Y-m-d date format throughout this post.

So I have a database containing three tables:

investors, investments, daily_stats

I've set them up at this db-fiddle: https://www.db-fiddle.com/f/m1DFJKzMs8SuYeVAUYcHU9/6

investors is basically my "users" table. It contains investor details such as email, name etc.

In the investments table, I'm storing data for each of the investors' investments. The start_date is when the investment begins, end_date is when it ends, amount is how much is invested, and percent is the share of the profit that the investor receives on his investment. (the rest being taken as a fee).

And in my daily_stats table, I have the total daily profit on the sum of all the investments that are active for each day.

Now, the tricky part is producing a dataset for each investor which shows them how much daily profit they've made on their individual investment from the data provided. for example:

date	profit
2021-01-01	$3
2021-01-02	$7
2021-01-03	$1
2021-01-04	$5
2021-01-05	$9

For this, my query needs to calculate the investor's share of the total investment for each daily stat, and also apply their investment percentage onto the profit. For example, if you take a look at Mike's investment, you'll see that it intersects the two other investments which belong to John. We can visualise it like this:

So each investors' share of the total investment changes depending on how many investments there are and what their sizes are. Let's take a look at a single date: 2021-02-03

In daily_stats, the profit for that date is $339

the total investment on that date is $25000

John's share is $5000 (20%), and his take profit percentage is 20.

Mike's share is $20000 (80%), and his take profit percentage 40.

Therefore Mike walks home with 339 * 0.8 * 0.4 = 108.48

And John takes 339 * 0.2 * 0.2 = 13.56

Now I'm thinking I can set up a generate_series for the time period that I want to select stats for and JOIN the investments/daily_stats tables but I'm not sure how to set it up such that the investor's profit is being calculated per day as demonstrated

解决方案

The following can be applied to any range of dates, for all users at once. In this specific SQL, I've calculated the user_profit between the dates 2021-02-01 to 2021-02-05.

Note also that I believe your calculation for John's share is $5000 (25%) is incorrect. $5000 of $25000 is 20%, not 25%. The same issue applies to Mike's share. Due to that, the following results do not match your expected results exactly. But I believe this SQL is correct.

SELECT s.date, s.profit
     , i.user_id, i.amount, i.percent
     , SUM(i.amount) OVER (PARTITION BY s.date) AS total_inv
     , ROUND(s.profit * (i.percent / 100.0) * i.amount / SUM(i.amount) OVER (PARTITION BY s.date), 2) AS user_profit
  FROM daily_stats AS s
  JOIN investments AS i
    ON s.date BETWEEN i.start_date AND i.end_date
 WHERE s.date BETWEEN '2021-02-01' AND '2021-02-05'
 ORDER BY s.date, i.user_id
;

+------------+--------+---------+--------+---------+-----------+-------------+
| date       | profit | user_id | amount | percent | total_inv | user_profit |
+------------+--------+---------+--------+---------+-----------+-------------+
| 2021-02-01 |    248 |       1 |   5000 |      20 |     25000 |        9.92 |
| 2021-02-01 |    248 |       2 |  20000 |      40 |     25000 |       79.36 |
| 2021-02-02 |    476 |       1 |   5000 |      20 |     25000 |       19.04 |
| 2021-02-02 |    476 |       2 |  20000 |      40 |     25000 |      152.32 |
| 2021-02-03 |    339 |       1 |   5000 |      20 |     25000 |       13.56 |
| 2021-02-03 |    339 |       2 |  20000 |      40 |     25000 |      108.48 |
| 2021-02-04 |    464 |       1 |   5000 |      20 |     25000 |       18.56 |
| 2021-02-04 |    464 |       2 |  20000 |      40 |     25000 |      148.48 |
| 2021-02-05 |    156 |       1 |   5000 |      20 |     25000 |        6.24 |
| 2021-02-05 |    156 |       2 |  20000 |      40 |     25000 |       49.92 |
+------------+--------+---------+--------+---------+-----------+-------------+

I've updated your fiddle as well: Solution in your fiddle

Here's the result that includes dates in January as well, which shows a different total_investment for those dates:

+------------+--------+---------+--------+---------+-----------+-------------+
| date       | profit | user_id | amount | percent | total_inv | user_profit |
+------------+--------+---------+--------+---------+-----------+-------------+
| 2021-01-28 |    488 |       1 |  10000 |      20 |     30000 |       32.53 |
| 2021-01-28 |    488 |       2 |  20000 |      40 |     30000 |      130.13 |
| 2021-01-29 |    480 |       1 |  10000 |      20 |     30000 |       32.00 |
| 2021-01-29 |    480 |       2 |  20000 |      40 |     30000 |      128.00 |
| 2021-01-30 |    332 |       1 |  10000 |      20 |     30000 |       22.13 |
| 2021-01-30 |    332 |       2 |  20000 |      40 |     30000 |       88.53 |
| 2021-01-31 |    461 |       1 |  10000 |      20 |     30000 |       30.73 |
| 2021-01-31 |    461 |       2 |  20000 |      40 |     30000 |      122.93 |
| 2021-02-01 |    248 |       1 |   5000 |      20 |     25000 |        9.92 |
| 2021-02-01 |    248 |       2 |  20000 |      40 |     25000 |       79.36 |
| 2021-02-02 |    476 |       1 |   5000 |      20 |     25000 |       19.04 |
| 2021-02-02 |    476 |       2 |  20000 |      40 |     25000 |      152.32 |
| 2021-02-03 |    339 |       1 |   5000 |      20 |     25000 |       13.56 |
| 2021-02-03 |    339 |       2 |  20000 |      40 |     25000 |      108.48 |
| 2021-02-04 |    464 |       1 |   5000 |      20 |     25000 |       18.56 |
| 2021-02-04 |    464 |       2 |  20000 |      40 |     25000 |      148.48 |
| 2021-02-05 |    156 |       1 |   5000 |      20 |     25000 |        6.24 |
| 2021-02-05 |    156 |       2 |  20000 |      40 |     25000 |       49.92 |
+------------+--------+---------+--------+---------+-----------+-------------+

这篇关于Postgresql:计算每个投资者每天的总投资份额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！