如何使用postgres窗口功能在会计软件中计算余额

本文介绍了如何使用postgres窗口功能在会计软件中计算余额的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我遇到了与此相同的问题，但我使用的是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

提琴： sqlfiddle/#!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