Open gebv opened 6 years ago
Для текущей структуры проекта
Вывод всех изменений с подсчетом суммарного баланса
SELECT
subltree(i.order_id, 1,2) AS user_id,
i.invoice_id,
i.paid,
a.account_type,
bc.change_id,
bc.amount,
bc.balance,
tx.transaction_id,
tx.status,
sum((case when a.account_type IN ('customer', 'system') then bc.amount
else -bc.amount end)) OVER (PARTITION BY subltree(i.order_id, 1,2) ORDER BY bc.change_id) AS bbb
FROM finances.balance_changes as bc
LEFT JOIN finances.transactions as tx ON bc.transaction_id = tx.transaction_id
LEFT JOIN finances.invoices as i ON i.invoice_id = tx.invoice_id
LEFT JOIN finances.accounts as a ON bc.account_id = a.account_id
WHERE a.account_type IN ('customer', 'credit')
ORDER BY bc.created_at DESC;
https://github.com/gebv/acca/issues/7#issuecomment-352605553