避免循环导致数据库中的往返

Question

我正在使用postgres，我最近遇到我使用的代码有太多的往返。

我所做的基本上是每天从表中获取数据，因为我必须每天查找更改，但执行此工作的整个函数每月调用一次。

我的表格的一个示例

数量

Id | Itemid | Amount | Date
1  |   2    |  50    | 20-5-20

现在，这个表可以随时更新以添加项目，并且我必须每天查看SUM(金额)的总金额。但这里有一个问题，我必须以5%的利率向每天的金额添加利息。所以我不能只调用函数一次，我必须每天查看它的值。

例如，如果我在5月1日添加了50美元的项目，那么当天的利息是5/100*50，我在5月5日添加了另一项价值50美元的项目，现在第5天的利息是5/100*50。

但是在5号之前，利息只有50美元，所以如果我只是简单地使用SUM(Amount)*5/100。这是错误的。

此外，另一个问题是日期存储为时间戳，我需要按时间戳的日期对其进行分组，因为如果我根据时间戳对其进行分组，则会为同一日期创建多个行，这是我希望在求和时避免的。因此，如果有两个条目在相同的日期但不同的时间，理想情况下，查询应该将其总结为一个单独的日期。示例

Amount Table
Date              |    Amount
2020-5-5 20:8:8          100
2020-5-5 7:8:8    |      100

结果应该是

Amount Table
Date              |     Amount
2020-5-5                 200

我当前的代码。

for i in numberofdaysinthemonth:
    amount = amount + session.query(func.sum(Amount.Amount)).filter(Amount.date<current_date).scalar() * 5/100

我想要一个根据日期获取所有这些值的查询，例如

date     | Sum of amount till that date
20-5-20  | 50
20-6-20  | 100

我应该做些什么来避免一个循环运行30次，因为这个函数每个月调用一次。

Answer 1

我应该按天将所有这些数据放在一个表中，并将其汇总为每天的金额总和

这是一个简单的“运行总数”。

select "date",
       sum(amount) over (order by "date") as amount_til_date
from the_table
order by "date";

如果您需要每个项目If的数量

select "date",
       sum(amount) over (partition by itemid order by "date") as amount_til_date
from the_table
order by "date";

如果你还需要计算当天的“复利”，你也可以这样做：

select item_id, 
       "date", 
       sum(amount) over (partition by itemid order by "date") as amount_til_date,
       sum(amount) over (partition by item_id order by "date") * power(1.05, count(*) over (partition by item_id order by "date")) as compound_interest
from the_table
order by "date";

要获取特定月份的数据，请添加WHERE子句：

where "date" >= date '2020-06-01'
  and "date" < date '2020-07-01'

Answer 2

通常，为了避免应用程序和数据库之间的往返，必须使用过程语言在存储代码(存储过程和存储函数)中将应用程序代码从一个应用程序移动到另一个数据库。在像Oracle数据库这样的商业数据库中，这种方法有时被称为“胖数据库”。

PostgreSQL默认的过程性语言是pl/pgsql ，但是您可以使用PostgreSQL扩展来使用Java、Perl、Python和Javascript，这些都需要安装在PostgreSQL中。