Grapeoff
@Grapeoff
В чём концепция...?

Почему SUM возвращает значения, гораздо большие реальных?

У меня есть две таблицы - 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, то там действительно будут эти два числа

66068f5c309dd770001227.png
Однако такой результат нас не устраивает, нужно посчитать суммарный 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;

660690ac10226866288939.png

Теперь в колонке ingoing_exchange явно не лежит значение, равное 48835+77750. Из-за чего так происходит? И ещё: можно ли как-то улучшить данный запрос?
  • Вопрос задан
  • 840 просмотров
Пригласить эксперта
Ответы на вопрос 1
Fragster
@Fragster
помогло? отметь решением!
Весь вопрос не читал, но для повторяющихся значений при джоине происходит умножение строк, условно для двух таблиц:
А, А1
А, А2
и
А, Б1
А, Б2
при джоине по первой колонке на выходе получим
А, А1, Б1
А, А2, Б1
А, А1, Б2
А, А2, Б2
и sum по какому-то числу даст кратно бОльший результат.

Решением может быть переход на union all или предварительная очистка от дублей (если возможна), например предварительная аггрегация перед джоином. Вариант с union предпочтительнее, потому что работает чаще быстрее и для восприятия будет скорее всего проще.
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы