Question

鉴于时间框架,我能够计算最低平衡的最佳方法是什么? 在我的数据库中,我有一个交存栏和一份撤销栏和日期栏。

<><>><>>Update<>>>>>

假设1/1,余额为300。 1/2时,共交存300份。 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

Answer 2

Loop through each entry in the database for every day that there was a deposit/withdrawal and store the lowest number in a variable. If the number for the day being checked is lower, replace the variable with that number.

友情链接