我有以下数据：
lbid | lbdate | lbtype | lbamount
-----+------------+---------------+---------
1 | 2017-11-01 | Add Plafon | 20
2 | 2017-11-02 | Use Balance | 5
3 | 2017-11-03 | Add Balance | 1
4 | 2017-11-04 | Reduce Plafon | 10
5 | 2017-11-06 | Use Balance | 8
6 | 2017-11-07 | Add Balance | 2
7 | 2017-11-08 | Reduce Plafon | 5
8 | 2017-11-10 | Add Plafon | 10
9 | 2017-11-11 | Use Balance | 1
10 | 2017-11-12 | Reduce Plafon | 5
基本上，我期望的最终结果是这样的：
lbid | lbdate | lbtype | lbamount | sumplafon | sumbalance
-----+------------+---------------+-----------+-----------+-------------
1 | 2017-11-01 | Add Plafon | 20 | 20 | 20
2 | 2017-11-02 | Use Balance | 5 | 20 | 15
3 | 2017-11-03 | Add Balance | 1 | 20 | 16
4 | 2017-11-04 | Reduce Plafon | 10 | 10 | 10
5 | 2017-11-06 | Use Balance | 8 | 10 | 2
6 | 2017-11-07 | Add Balance | 2 | 10 | 4
7 | 2017-11-08 | Reduce Plafon | 5 | 5 | 4
8 | 2017-11-10 | Add Plafon | 10 | 15 | 14
9 | 2017-11-11 | Use Balance | 1 | 15 | 15
10 | 2017-11-12 | Reduce Plafon | 5 | 10 | 10
sumplafon是lbtype为Add Balance（正）和Reduce Balance（负）的所有lbamount的总和。
我已经这样做了。
sum(
case
when "lbtype" = 'Add Plafon' then "lbamount"
when "lbtype" = 'Reduce Plafon' then -1 * "lbamount"
else 0
end
) over (order by "lbdate") sumplafon
而sumballance是lbtype为Add Plafon（positive）、Use Balance（positive）、Use Balance（negative）的所有lbamount的和，但是每次找到lbtype Reduce Plafon，如果sumballance大于sumplafon，sumballance将重置为sumplafon。
例如，lbtype为Reduce Plafon的lbid 4，sumballance为16，大于sumplafon 10，因此sumballance需要重置为其sumplafon为10，然后再次继续sumballance的累加和。
我试过先在cte上和伯爵这样准备小组。
count(
case when "lbtype" = 'Reduce Plafon' then 1 else null end
) over (order by "lbdate") countplafon
然后在第二个cte中，我用第一个cte中的countplafon进行了求和，如下所示：
sum(
case
when "lbtype" = 'Add Plafon' or "lbtype" = 'Add Balance' then "lbamount"
when "lbtype" = 'Use Balance' then -1 * "lbamount"
else 0
end
) over (partition by "countplafon" order by "lbdate") sumbalance
但结果只是从头重置sumballance，因为它使用group by countplafon。
lbid | lbdate | lbtype | lbamount | countplafon |sumplafon | sumbalance
-----+------------+---------------+-----------+-----------+-------------|-----------
1 | 2017-11-01 | Add Plafon | 20 | 0 | 20 | 20
2 | 2017-11-02 | Use Balance | 5 | 0 | 20 | 15
3 | 2017-11-03 | Add Balance | 1 | 0 | 20 | 16
4 | 2017-11-04 | Reduce Plafon | 10 | 1 | 20 | 0
5 | 2017-11-06 | Use Balance | 8 | 1 | 20 | -8
6 | 2017-11-07 | Add Balance | 2 | 1 | 20 | -6
7 | 2017-11-08 | Reduce Plafon | 5 | 2 | 20 | 0
8 | 2017-11-10 | Add Plafon | 10 | 2 | 20 | 10
9 | 2017-11-11 | Use Balance | 1 | 2 | 20 | 9
10 | 2017-11-12 | Reduce Plafon | 5 | 3 | 20 | 0
这是sqlfiddle。
这是sql。
with
cte_runningnumbers1
as (
select
"lbid",
"lbdate",
"lbtype",
"lbamount",
count(
case when "lbtype" = 'Reduce Plafon' then 1 else null end
) over (order by "lbdate") countplafon,
sum(
case
when "lbtype" = 'Add Plafon' then "lbamount"
when "lbtype" = 'Reduce Plafon' then -1 * "lbamount"
else 0
end
) over (order by "lbdate") sumplafon
from "lb"
),
cte_runningnumbers2 as (
select
*,
sum(
case
when "lbtype" = 'Add Plafon' or "lbtype" = 'Add Balance' then "lbamount"
when "lbtype" = 'Use Balance' then -1 * "lbamount"
else 0
end
) over (partition by "countplafon" order by "lbdate") sumbalance
from "cte_runningnumbers1"
)
select *
from cte_runningnumbers2
我一直在关注这个问题，但我仍然不知道如何解决我的问题。
我需要做的最后一步是将它与之前的sumbalance或sumplafon（如果sumbalance大于sumplafon）一起添加，但我不知道怎么做。有人能帮我吗？
最佳答案
创建custom aggregate function.将逻辑置于状态转换函数中：
create or replace function lb_agg_fun(sumbalance numeric, lbtype text, lbamount numeric)
returns numeric language sql as $$
select case
when lbtype in ('Add Plafon', 'Add Balance') then sumbalance + lbamount
when lbtype = 'Use Balance' then sumbalance - lbamount
else case
when lbamount < sumbalance then lbamount
else sumbalance
end
end;
$$;
Create an aggregate:
create aggregate lb_agg(text, numeric) (
sfunc = lb_agg_fun,
stype = numeric,
initcond = 0
);
使用它：
select *, lb_agg(lbtype, lbamount) over (order by lbdate) as sumbalance
from lb;
lbid | lbdate | lbtype | lbamount | sumbalance
------+------------+---------------+----------+------------
1 | 2017-11-01 | Add Plafon | 20 | 20
2 | 2017-11-02 | Use Balance | 5 | 15
3 | 2017-11-03 | Add Balance | 1 | 16
4 | 2017-11-04 | Reduce Plafon | 10 | 10
5 | 2017-11-06 | Use Balance | 8 | 2
6 | 2017-11-07 | Add Balance | 2 | 4
7 | 2017-11-08 | Reduce Plafon | 5 | 4
8 | 2017-11-10 | Add Plafon | 10 | 14
9 | 2017-11-11 | Use Balance | 1 | 13
10 | 2017-11-12 | Reduce Plafon | 5 | 5
(10 rows)