postgreCalculate在SQL中的条件运行总数

Question

我想写一个SQL查询，每天计算未付发票的总数，但到目前为止还没有找到解决方案。我的表包括发票金额、创建日期和还款日期(如果尚未收到还款，则为空)。

为了计算未偿还的发票金额，我需要得到所有未偿还金额的总和，即还款日为空或在焦点日之后。下面是一个例子:

tbody> <<tr>

金额	创建日	还款日
100	2-7-2022	4-7-2022
100	2-7-2022	3-7-2022
300	3-7-2022
200	4-7-2022
400	5-7-2022	6-7-2022
100	5-7-2022
100	5-7-2022
300	6-7-2022

第一步是将单个记录中可能发生的两件事拆分为两个单独的行(创建和可选的偿还)。你可以通过"循环"遍历表两次，查看表的创建情况，然后查看表的还款情况:

select
case payment
when 1 then repayment_date
else creation_date
end date,
case payment
when 1 then -amount
else amount
end amount
from (select 0 payment union all select 1 payment) payment
join invoice on payment=0 or repayment_date

然后取其结果，对每个日期的金额进行分组和求和，并使用窗口和来获得每个日期总和的运行总数:

select
date_format(date,'%e-%c-%Y') Date,
sum(sum(amount)) over (order by date) 'Outstanding invoice'
from (
select
case
when payment then repayment_date
else creation_date
end date,
case
when payment then -amount
else amount
end amount
from (select 0 payment union all select 1 payment) payment
join invoice on payment=0 or repayment_date
) activity
group by date

小提琴