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

5cg8jx4n 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(334)

我正在使用postgres，最近我发现我使用的代码有太多的往返。
我所做的基本上是每天从表中获取数据，因为我必须每天查找更改，但是完成此任务的整个函数每月调用一次。
我的table的一个例子
数量

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

现在这个表可以在任何时间点更新以添加项目，我必须看到每天的总金额。但有一个问题，我必须按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次，因为函数一个月调用一次。

sql postgresql sqlalchemy

来源：https://stackoverflow.com/questions/62587028/avoiding-roundtrips-in-the-database-caused-by-looping

2条答案

按热度按时间

628mspwn1#

我应该在一个表中获得所有这些数据，并将其汇总为每天的金额总和
这是一个简单的“运行总数”

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

如果您需要每个itemid的金额

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'

赞(0）回复(0）举报 2021-07-24

gdx19jrr2#

一般来说，为了避免应用程序和数据库之间的往返，应用程序代码必须使用过程语言在存储代码（存储过程和存储函数）中从应用程序移动到数据库。这种方法在商业数据库（如oracle数据库）中有时被称为“厚数据库”。
postgresql的默认过程语言是pl/pgsql，但是您可以使用java、perl、python和javascript，使用postgresql扩展，您需要在postgresql中安装这些扩展。

赞(0）回复(0）举报 2021-07-24

我来回答

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

2条答案

相关问题

热门标签

最新问答