как рассчитать балансы в бухгалтерском программном обеспечении, используя оконную функцию postgres

У меня такая же проблема, но я использую Postgres.

Рассчитать баланс с помощью mysql

иметь таблицу, содержащую следующие данные:

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

Теперь мне нужен запрос, который дает мне следующий результат:

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 есть оконная функция, как это сделать с помощью оконных функций postgres?

Спасибо.


postgresql sql database window-functions accounting
person Axil    schedule 04.08.2014    source источник

Ответы (1)


arrow_upward
7
arrow_downward 

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» / «Out», чтобы вам не нужно было заключать их в кавычки. (Это зарезервированные слова)

Если вам нужен только один клиент (customer_id = 2):

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

Если ваш запрос должен охватывать несколько клиентов, и вам нужен текущий баланс, который ПЕРЕЗАПУСКАЕТСЯ с каждым клиентом, вы можете использовать:

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
person Brian DeMilia    schedule 04.08.2014
comment
вау, это было очень быстро! большое спасибо. еще один небольшой вопрос, скажем, есть customer_id, и я хочу запросить и рассчитать балансы только для customer_id == 2, каким будет утверждение? Я не могу заставить это работать: выберите t.*, sum(in-out) over(order by id) as balance where customer_id == 2 from tbl t order by id - person Axil; 04.08.2014
comment
@Axil в sql вы используете один знак равенства, смотрите мое редактирование. Также предложение where идет после предложения from. - person Brian DeMilia; 04.08.2014
comment
Привет Брайан, последний вопрос, если можно, есть ли способ использовать тот же оператор для обновления столбца баланса в таблице. Прямо сейчас баланс является виртуальным столбцом, скажем, в таблице действительно есть столбец баланса, я хочу, чтобы все те значения, которые вы сгенерировали, были обновлены до этой конкретной строки, аналогично тому, как вы ее отобразили. Спасибо - person Axil; 04.08.2014
comment
@Axil Я бы физически не сохранял столбец баланса в таблице, так как это вычисление. Возможно, вы захотите создать представление, которое в основном представляет собой сохраненный оператор sql с этим столбцом. См. postgresql.org/docs/9.2/static/sql-createview.html. . В противном случае вы бы постоянно обновляли таблицу с дополнительными транзакциями. Представление, сделанное с использованием приведенного выше sql, было бы лучше. - person Brian DeMilia; 05.08.2014
comment
@Axil вот пример использования представления. sqlfiddle.com/#!15/058a2/1/0 после создается представление, используя приведенный выше sql, вы можете просто запросить это представление так же, как и любую другую таблицу (по сути, получая то, что хотите, но вам не нужно будет постоянно его обновлять). - person Brian DeMilia; 05.08.2014
comment
Спасибо, я думаю, что согласен с вами, мне трудно принять дизайн хранения остатков. Это просто не казалось элегантным. По какой-то причине мне нужно проверять остатки за каждый финансовый период. Триггеры и хранение просто не кажутся правильными. Я думаю, что один из способов - хранить только начальные остатки, что, вероятно, было бы лучше, чем для каждой отдельной транзакции. Спасибо еще раз. - person Axil; 06.08.2014