java - 在给定的时间范围内找到最小余额

Question

在给定时间范围内计算最低余额的最佳方法是什么？在我的数据库中，我有存款列和取款列以及完成日期列。

更新

假设1/1，余额为300。1/2，存入300，总数为600。1/15，取出200，余额为400。1/25，进一步取出300已完成，余额下降到 100。在 1/28，存入 800，余额总计为 900。如果我在 1/31 计算该月的最低余额，我应该得到 100。有什么想法吗？

Answer 1

使用窗口函数建立运行平衡，然后像这样从每个时间间隔中提取最小值:

with cte_transaction_data as
(
    select 300 as deposit, null as withdraw, date '01/02/2010' as transaction_date union all
    select null, 200, date '01/15/2010' union all
    select null, 300, date '01/25/2010' union all
    select 800, null, date '01/28/2010'
)
select
    month,
    min(balance) as minimum_balance
from
    (
        select 
            transaction_date,
            date_trunc('month', transaction_date) as month,
            300 
            + coalesce(sum(deposit) over(order by transaction_date rows between unbounded preceding and current row), 0)
            - coalesce(sum(withdraw) over(order by transaction_date rows between unbounded preceding and current row), 0) as balance
         from cte_transaction_data
    ) as running_balance
group by
    month
order by
    month

结果:

month                   minimum_balance
2010-01-01 00:00:00-06  100