英文:
How to get running sum within condition by postgres
问题
以下是您要翻译的部分：
I have following table
no status amount
1 2 10000
2 3 10000
3 2 1000
4 2 -11000
I got running some by using following query.
SELECT
main.no
, main.status
, main.amount
, SUM(main.amount) OVER (
ORDER BY
main.no ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
from
table AS main
It returned follows. But I would like to improve my formula by refering to status code.
no status amount running_sum
1 2 10000 10000
2 3 10000 20000
3 2 1000 21000
4 2 -11000 10000
My desired result is following.
I would like to get running sum only in status = 2 after another status code.
no status amount running_sum
1 2 10000 10000
2 3 10000 20000
3 2 1000 11000
4 2 -11000 0
How to add conditioning in my formula?
Are there any good way to achieve this?
Thanks
英文:
I have following table
no status amount
1 2 10000
2 3 10000
3 2 1000
4 2 -11000
I got running some by using following query.
SELECT
main.no
, main.status
, main.amount
, SUM(main.amount) OVER (
ORDER BY
main.no ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
from
table AS main
It returned follows. But I would like to improve my formula by refering to status code.
no status amount running_sum
1 2 10000 10000
2 3 10000 20000
3 2 1000 21000
4 2 -11000 10000
My desired result is following.
I would like to get running sum only in status = 2 after another status code.
no status amount running_sum
1 2 10000 10000
2 3 10000 20000
3 2 1000 11000
4 2 -11000 0
How to add conditioning in my formula ?
Are there any good way to achieve this?
Thanks
答案1
得分: 1
你可以使用以下代码来完成：
使用 `sum(amount) over(partition by status order by no)` 这段代码
选择
主要.no
, 主要.状态
, 主要.金额
, SUM(主要.金额) OVER (
按状态分组
按主要.no排序 行在无限制之前和当前行之间
) as 累计总额
来自
mytable AS 主要
按no排序
[在此处查看演示][1]
英文:
You can do it using sum(amount) over(partition by status order by no)
SELECT
main.no
, main.status
, main.amount
, SUM(main.amount) OVER (
partition by status
ORDER BY
main.no ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) as running_sum
from
mytable AS main
order by no
答案2
得分: 1
根据您的预期输出，在 status = 2 时，您只需从您的累加总和中减去 status <> 2 的情况下的金额的累加总和，尝试以下操作：
select main.no,
main.status,
main.amount,
sum(main.amount) over (order by main.no) -
case when main.status = 2
then sum(case when main.status <> 2 then main.amount else 0 end) over (order by main.no)
else 0
end as running_sum
from table_name as main
英文:
According to your expected output, when status = 2, you just need to subtract the running sum of the amount where status <> 2 from your running sum, try the following:
select main.no,
main.status,
main.amount,
sum(main.amount) over (order by main.no) -
case when main.status = 2
then sum(case when main.status <> 2 then main.amount else 0 end) over (order by main.no)
else 0
end as running_sum
from table_name as main
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论