Как объединить несколько столбцов с группировкой в Django/Postgres
У меня есть следующие таблицы, которые мне нужно объединить по дате и валюте:
Сделки
.
Описание | Дата | Сумма | Валюта
Курсы валют
Валюта | Дата | Курс
Мне нужно объединить столбцы даты и валюты, умножить курс и сумму и суммировать их, чтобы получить общее значение в разных валютах. Мне также нужно сгруппировать их по календарным месяцам.
Возможно ли это с помощью Django ORM или мне нужно использовать SQL напрямую? Если да, то как это сделать в Postgres?
Предполагая, что Dates
в таблице "Exchange rates"
независимы от dates
в таблице Transactions
, так что для каждой Транзакции, соответствующая "Exchange rates".Date
является последней датой, которая меньше или равна Transactions.Date
, вы можете попробовать это в Postgres :
В Postgres :
SELECT t.Currency
, date_trunc('month', t.Date) AS period_of_time
, sum(t.amount * er.Rate) AS sum_by_currency_by_period_of_time
FROM Transactions AS t
CROSS JOIN LATERAL
( SELECT DISTINCT ON (er.Currency) er.Rate
FROM "Exchange rates" AS er
WHERE er.Currency = t.Currency
AND er.Date <= t.Date
ORDER BY er.Date DESC
) AS er
GROUP BY t.Currency, date_trunc('month', t.Date)
Предполагая, что ваша модель Currency
имеет колонку символов (измените в соответствии с вашими потребностями), вы можете достичь этого с помощью следующих утверждений Django:
from your.models import Transaction, ExchangeRate
from django.db.models.functions import ExtractMonth
from django.db.models import Sum, F, Subquery, OuterRef
rates = ExchangeRate.objects.filter(
currency=OuterRef("currency"), date__lt=OuterRef("date")
).order_by("-date")
Transaction.objects.annotate(
month=ExtractMonth("date"),
rate=Subquery(rates.values("rate")[:1]),
conversion=F("amount") * F("rate"),
).values("currency__symbol", "month").annotate(sum=Sum("conversion")).order_by(
"currency", "month"
)
В результате получится список вида:
{'currency__symbol': '$', 'month': 2, 'sum': 105.0},...
Оператор подзапроса аннотирует последний найденный курс обмена, сравнивая даты. Убедитесь, что каждая транзакция имеет курс обмена (дата курса обмена предшествует дате транзакции).