История изменения баланса с общим итогом

[gebv]

Вводная У клиента может быть два счета кредитный и реальный - это две записи в таблице accounts. Не углубляюсь в детали того как хранятся транзакции. Важно то что история изменения баланса хранится в balance_changes. То есть в случае изменения каждого счета будут записи в balacne_changes для соответствующего account

Схема визуально по ссылке ниже https://github.com/gebv/acca/issues/7​

Задача Предложить решение как выводить реальный баланс пользователя. Реальный баланс это разница между реальным и кредитным (бонусным) счетом.

По ссылке ниже данные из таблиц https://github.com/gebv/acca/issues/7#issuecomment-351952307

История баланса для клиента https://github.com/gebv/acca/issues/7#issuecomment-351952589

Мы видим что кредитов начислено в change_id=30 на 1000 и стало 2000 (было до этого 1000 кредитов) Видим так же историю изменения баланса по другому счету того же клиента - было пополнение средств, оплата услуги.

У клиента баланс составляет 2000 кредитных и 12077 реальных денег. В итоге 12077 - 2000 = 10077 баланс клиента.

Надо иметь возможность видеть историю изменения баланса сразу с учетом кредитного и реального счета. В таком виде

https://github.com/gebv/acca/issues/7#issuecomment-351955484

В отклике просьба писать вашу идею решения задачи в общих чертах (без раскрытия деталей) что бы я понял что вы в теме. Общение по задаче предполагается в скайп. Оплата по факту затраченного времени по задаче. Положим 1000 в час.

[gebv]

TABLE finances.accounts;
account_id | customer_id | account_type | balance | updated_at
------------+-----------------------+--------------+--------------+-------------------------------
61 | customer.2.main | customer | 0 | 2017-12-01 07:53:16.580243+00
63 | customer.2.bonus | bonus | 2000 | 2017-12-01 07:53:16.580243+00
58 | system.buffer.bonuses | system | 999999999000 | 2017-12-01 08:02:02.969778+00
62 | customer.1.bonus | bonus | 2000 | 2017-12-01 08:02:02.989237+00
54 | system.buffer.sales1 | system | 58 | 2017-12-01 08:06:12.392435+00
59 | system.buffer.cash | system | 999987865 | 2017-12-01 08:07:58.063316+00
60 | customer.1.main | customer | 12077 | 2017-12-01 08:07:58.096213+00
(7 rows)

TABLE finances.invoices;
invoice_id | order_id | destination_id | source_id | paid | amount | created_at
------------+------------------------------------------------------------+----------------+-----------+------+--------+-------------------------------
20 | customer.1.add_bonuse.1512115322947278000 | 62 | 58 | t | 1000 | 2017-12-01 08:02:02.951493+00
21 | customer.1.finances.60.card_payment.1512115336223029000 | 60 | 59 | t | 100 | 2017-12-01 08:02:16.226776+00
22 | customer.1.finances.60.sberbank_online.1512115538029777000 | 60 | 59 | t | 12023 | 2017-12-01 08:05:38.033957+00
23 | customer.1.orders.9.pay.1512115572346266000 | 54 | 60 | t | 58 | 2017-12-01 08:06:12.349024+00
24 | customer.1.finances.60.noncash.1512115678035479000 | 60 | 59 | t | 12 | 2017-12-01 08:07:58.03908+00
(5 rows)

TABLE finances.transactions;
transaction_id | invoice_id | amount | source | destination | status | created_at | closed_at
----------------+------------+--------+--------+-------------+----------+-------------------------------+-------------------------------
20 | 20 | 1000 | 58 | 62 | accepted | 2017-12-01 08:02:02.962971+00 | 2017-12-01 08:02:02.984469+00
21 | 21 | 100 | 59 | 60 | accepted | 2017-12-01 08:02:16.235761+00 | 2017-12-01 08:02:16.253453+00
22 | 22 | 12023 | 59 | 60 | accepted | 2017-12-01 08:05:38.046837+00 | 2017-12-01 08:05:38.071565+00
23 | 23 | 58 | 60 | 54 | accepted | 2017-12-01 08:06:12.361893+00 | 2017-12-01 08:06:12.385739+00
24 | 24 | 12 | 59 | 60 | accepted | 2017-12-01 08:07:58.053968+00 | 2017-12-01 08:07:58.084857+00
(5 rows)

TABLE finances.balance_changes;
change_id | account_id | transaction_id | tx_type | amount | balance | created_at
-----------+------------+----------------+----------+--------+--------------+-------------------------------
29 | 58 | 20 | hold | -1000 | 999999999000 | 2017-12-01 08:02:02.972159+00
30 | 62 | 20 | complete | 1000 | 2000 | 2017-12-01 08:02:02.991401+00
31 | 59 | 21 | hold | -100 | 999999900 | 2017-12-01 08:02:16.243726+00
32 | 60 | 21 | complete | 100 | 100 | 2017-12-01 08:02:16.262139+00
33 | 59 | 22 | hold | -12023 | 999987877 | 2017-12-01 08:05:38.056502+00
34 | 60 | 22 | complete | 12023 | 12123 | 2017-12-01 08:05:38.0826+00
35 | 60 | 23 | hold | -58 | 12065 | 2017-12-01 08:06:12.376742+00
36 | 54 | 23 | complete | 58 | 58 | 2017-12-01 08:06:12.394818+00
37 | 59 | 24 | hold | -12 | 999987865 | 2017-12-01 08:07:58.065814+00
38 | 60 | 24 | complete | 12 | 12077 | 2017-12-01 08:07:58.098867+00
(10 rows)

[gebv]

Выборка истории изменения баланса для конкретного клиента

=> SELECT bc.change_id, order_id, account_type, bc.amount, bc.balance FROM finances.balance_changes as bc
->     LEFT JOIN finances.transactions ON bc.transaction_id = transactions.transaction_id
->   LEFT JOIN finances.invoices ON transactions.invoice_id = invoices.invoice_id
->   LEFT JOIN finances.accounts ON bc.account_id = accounts.account_id
->   WHERE finances.invoices.order_id ~ 'customer.1.*' AND account_type IN ('customer', 'bonus')
->   ORDER BY bc.created_at DESC
->   LIMIT 10;
 change_id |                          order_id                          | account_type | amount | balance
-----------+------------------------------------------------------------+--------------+--------+---------
        38 | customer.1.finances.60.noncash.1512115678035479000         | customer     |     12 |   12077
        35 | customer.1.orders.9.pay.1512115572346266000                | customer     |    -58 |   12065
        34 | customer.1.finances.60.sberbank_online.1512115538029777000 | customer     |  12023 |   12123
        32 | customer.1.finances.60.card_payment.1512115336223029000    | customer     |    100 |     100
        30 | customer.1.add_bonuse.1512115322947278000                  | bonus        |   1000 |    2000
(5 rows)

[gebv]

История изменения общего баланса пользователя

customer.1.add_bonuse.1512115322947278000 | -1000 | -2000
customer.1.finances.60.card_payment.1512115336223029000 | 100 | -1900
customer.1.finances.60.sberbank_online.1512115538029777000 | 12023 | 10123
customer.1.orders.9.pay.1512115572346266000 | -58 | 10065
customer.1.finances.60.noncash.1512115678035479000 | 12 | 10077

[gebv]

Идея, идти по invocices

[gebv]

Решение

http://sqlfiddle.com/#!17/8d5a6/6

     SELECT 
      change_id, order_id, user_id, account_id, account_type, transaction_id, amount, 
      sum((case when account_type IN ('cash', 'bonus') then balance
      else -balance end)) OVER (ORDER BY change_id) AS bbb
     FROM balance_changes
     WHERE user_id = 1000
     ORDER BY created_at DESC
     limit 3;