如何使用postgres窗口功能在会计软件中计算余额 [英] how to calculate balances in an accounting software using postgres window function

问题描述

我遇到了与此相同的问题，但我使用的是Postgres。

I'ved got a problem same as this but I am using Postgres.

Calculate balance with mysql

有一个包含以下数据的表：

have a table which contains the following data:

ID      In       Out 
1      100.00    0.00   
2       10.00    0.00   
3        0.00   70.00    
4        5.00    0.00    
5        0.00   60.00   
6       20.00    0.00     

现在我需要一个查询，该查询给出以下结果：

Now I need a query which gives me the following result:

ID      In       Out    Balance
1      100.00    0.00   100.00
2       10.00    0.00   110.00
3        0.00   70.00    40.00
4        5.00    0.00    45.00
5        0.00   60.00   -15.00
6       20.00    0.00     5.00

如何最好地处理余额计算。有人告诉我postgres中有window函数，如何使用postgres window函数来完成呢？

How best to handle "balance" calculation. I was told there is window function in postgres, how would this be done using postgres window functions ?

谢谢。

推荐答案

select t.*, sum("In"-"Out") over(order by id) as balance
from tbl t
order by id

提琴： http://sqlfiddle.com/#!15/97dc5/2/0

请考虑更改列名 In / 输出，因此您无需将其放在引号中。 （它们是保留字）

Consider changing your column names "In" / "Out" so that you don't need to put them in quotes. (They are reserved words)

如果您只想要一位客户（customer_id = 2）：

If you wanted only one customer (customer_id = 2):

select t.*, sum("In"-"Out") over(order by id) as balance
from tbl t
where customer_id = 2
order by id

如果您的查询要覆盖多个客户，并且您想要一个与每个客户重新开始的余额，您可以使用：

If your query were to span multiple customers and you wanted a running balance that RESTARTED with each customer, you could use:

select t.*, sum("In"-"Out") over( partition by customer_id
                                  order by customer_id, id ) as balance_by_cust
from tbl t
order by customer_id, id

这篇关于如何使用postgres窗口功能在会计软件中计算余额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！