PostgreSQL-запрос курсов конвертации валют
Давайте рассмотрим, что таблица базы данных для конвертации валют выглядит следующим образом:
Если к базе данных запрашивается 1 февраля 2024 г., чтобы получить коэффициент конверсии, где fromCurrency='USD', она должна вернуть
Тот же запрос 15 апреля 2024 г. должен вернуться.
По сути, для всех возможных комбинаций (fromCurrency, toCurrency) в базе данных существует множество записей, и в определенную дату, когда к базе данных запрашивается, только одна из этих многих записей применима в зависимости от даты вступления в силу. Я пытаюсь найти наилучший запрос Postgres для получения записей.
«Лучший возможный запрос Postgres» лучше всего в каком отношении? По каким критериям сравнивать возможные решения?
@aled извини, я виноват, я имел в виду производительность. В таблице очень большой объем данных.
Ответы 3
Попробуйте следующий запрос PostgreSQL, чтобы получить применимые коэффициенты конверсии на основе даты запроса (или даты системы получения):
SELECT
from_currency,
to_currency,
MAX(effective_date) FILTER (WHERE effective_date <= '2023-12-05') AS effective_date,
MAX(conversion_rate) FILTER (WHERE effective_date <= '2023-12-05') AS conversion_rate
FROM conversion_rates
WHERE from_currency = 'USD'
GROUP BY from_currency, to_currency
ORDER BY from_currency, to_currency;
Я сохранил настраиваемые значения effect_date и from_currency, которые вы, возможно, захотите изменить (например, системную дату получения) по мере необходимости.
Моя Скрипка для этой игры.
Это может привести к получению значений из разных строк для effective_date и conversion_rate и, следовательно, к неверным результатам.
Если вы хотите использовать этот подход, вы можете использовать (ARRAY_AGG(ROW(effective_date, conversion_rate) ORDER BY effective_date DESC) FILTER (WHERE effective_date <= '2024-02-01'))[1], как показано в этой обновленной скрипте: dbfiddle.uk/KmjLcXLS. К сожалению, в PostgreSQL нет поддержки MAX(ROW(...)).
Как в jOOQ, так и в собственном PostgreSQL вы можете использовать DISTINCT ON, это предложение, в котором вы можете запросить только первую запись для каждого выражения DISTINCT ON, учитывая предложение ORDER BY:
С SQL:
SELECT DISTINCT ON (fromCurrency, toCurrency)
fromCurrency, toCurrency, effectiveDate, conversionRate
FROM conversions
WHERE effectiveDate <= :currentDate
ORDER BY fromCurrency, toCurrency, effectiveDate DESC
С помощью jOOQ (поскольку вы отметили вопрос jOOQ):
var c = CONVERSIONS.as("c");
ctx.select(c.FROM_CURRENCY, c.TO_CURRENCY, c.EFFECTIVE_DATE, c.CONVERSION_RATE)
.distinctOn(c.FROM_CURRENCY, c.TO_CURRENCY)
.from(c);
.where(c.EFFECTIVE_DATE.le(currentDate))
.orderBy(c.FROM_CURRENCY, c.TO_CURRENCY, c.EFFECTIVE_DATE.desc())
.fetch();
Вот запрос, который может дать вам коэффициент конверсии на определенную системную дату. Если вы не хотите использовать системную дату, вам придется заменить current_date параметром.
WITH currList AS (
SELECT *,
max(effectiveDate) FILTER(WHERE effectiveDate <= CURRENT_DATE) OVER(PARTITION by fromCurrency, toCurrency) as maxEffDate
FROM ConversionRates
)
SELECT *
FROM currList
WHERE effectiveDate = maxEffDate
Пожалуйста, поделитесь вопросом, который у вас есть на данный момент.