У меня есть две таблицы - Account (банковский счёт) и Transaction.
Account:
CREATE TABLE "Account"
(
id SERIAL
PRIMARY KEY,
name TEXT NOT NULL,
"clientId" INTEGER NOT NULL
REFERENCES "Client"
ON UPDATE CASCADE ON DELETE RESTRICT,
currency "Currency" NOT NULL,
);
Transaction:
CREATE TABLE "Transaction"
(
id SERIAL
PRIMARY KEY,
type "TransactionType" NOT NULL,
"fromAccountId" INTEGER
REFERENCES "Account"
ON UPDATE CASCADE ON DELETE SET NULL,
"fromAmount" DOUBLE PRECISION,
"toAccountId" INTEGER
REFERENCES "Account"
ON UPDATE CASCADE ON DELETE SET NULL,
"toAmount" DOUBLE PRECISION,
status "TransactionStatus" NOT NULL
);
fromAccountId - ID Аккаунта, с которого сняты деньги
toAccountId - ID Аккаунта, на который придут деньги
fromAmount - Количество денег, которые будут сняты с
fromAccountId
toAmount - Количество денег, которые будут положены на
fromAccountId
Среди всех типов транзакций, есть тип
EXCHANGE, означающий перевод из одного валютного счёта, в другой.
Передо мной стоит задача составить статистику балансов пользователей по некоторым периодам. Сделать это можно только перебрав и сложив все транзакции за этот период и сложив все соответствующие значения.
Для теста я написал SQL запрос, проверяющий, что значения для exchange транзакций считаются правильно (колонки
ingoing_exchange и
outgoing_exchange)
SELECT a.id AS account_id,
a.currency,
sum(coalesce(t."toAmount", 0)) - sum(coalesce(t."fromAmount", 0)) AS balance,
ingoing_exchange_transactions."toAmount" AS ingoing_exchange,
outgoing_exchange_transactions."fromAmount" AS outgoing_exchange,
date_part('month', date_trunc('month', t."createdAt")) AS date,
date_part('year', date_trunc('year', t."createdAt")) AS year
FROM "Transaction" t
INNER JOIN "Account" a ON (t."fromAccountId" = a.id OR t."toAccountId" = a.id)
/* JOIN Exchange транзакций */
LEFT JOIN LATERAL (SELECT "toAccountId", "toAmount"
FROM "Transaction"
WHERE "type" = 'EXCHANGE'
AND (t.status = 'COMPLETE' OR t.status = 'PENDING')) ingoing_exchange_transactions
ON a.id = ingoing_exchange_transactions."toAccountId"
LEFT JOIN LATERAL (SELECT "fromAccountId", "fromAmount"
FROM "Transaction"
WHERE "type" = 'EXCHANGE'
AND (t.status = 'COMPLETE' OR t.status = 'PENDING')) outgoing_exchange_transactions
ON a.id = outgoing_exchange_transactions."fromAccountId"
WHERE t.type <> 'EXCHANGE'
AND (t.status = 'COMPLETE' OR t.status = 'PENDING')
GROUP BY account_id, a.currency, ingoing_exchange, outgoing_exchange, date, year
ORDER BY date, year;
И данный код действительно всё правильно считает. Если зайти в таблицу транзакций и взглянуть, например, на транзакции для
toAmount = 208, то там действительно будут эти два числа
Однако такой результат нас не устраивает, нужно посчитать суммарный
ingoing_exchange и
outgoing_exchange для каждого
account_id, и затем сложить и вычесть соответствующие значения с колонкой code>balance.
Но если пока не складывать ничего с балансом, а просто просуммировать, чтобы проверить что сумма верна, то Postgres возвращает суммы, в 10 раз больше действительности
SELECT a.id AS account_id,
a.currency,
sum(coalesce(t."toAmount", 0)) - sum(coalesce(t."fromAmount", 0)) AS balance,
sum(ingoing_exchange_transactions."toAmount") AS ingoing_exchange,
sum(outgoing_exchange_transactions."fromAmount") AS outgoing_exchange,
date_part('month', date_trunc('month', t."createdAt")) AS date,
date_part('year', date_trunc('year', t."createdAt")) AS year
FROM "Transaction" t
INNER JOIN "Account" a ON (t."fromAccountId" = a.id OR t."toAccountId" = a.id)
LEFT JOIN LATERAL (SELECT "toAccountId", "toAmount"
FROM "Transaction"
WHERE "type" = 'EXCHANGE'
AND (t.status = 'COMPLETE' OR t.status = 'PENDING')) ingoing_exchange_transactions
ON a.id = ingoing_exchange_transactions."toAccountId"
LEFT JOIN LATERAL (SELECT "fromAccountId", "fromAmount"
FROM "Transaction"
WHERE "type" = 'EXCHANGE'
AND (t.status = 'COMPLETE' OR t.status = 'PENDING')) outgoing_exchange_transactions
ON a.id = outgoing_exchange_transactions."fromAccountId"
WHERE t.type <> 'EXCHANGE'
AND (t.status = 'COMPLETE' OR t.status = 'PENDING')
GROUP BY account_id, a.currency, date, year
ORDER BY date, year;
Теперь в колонке
ingoing_exchange явно не лежит значение, равное
48835+77750. Из-за чего так происходит? И ещё: можно ли как-то улучшить данный запрос?
Средний
тут и во втором таком блоке точно нет дублей? 