Рассчитать средний обменный курс за период времени

В Django у меня есть модель, похожая на этот пример:

class Currency(models.Model):
    name = models.CharField(max_length=3, unique=True)
    full_name = models.CharField(max_length=20)


class ExchangeRate(models.Model):
    currency = models.ForeignKey('Currency')
    start_date = models.DateFiled()
    end_date = models.DateField()
    exchange_rate = models.DecimalField(max_digits=12, decimal_places=4)

Давайте упростим это и предположим, что у нас есть только одна валюта и ExchangeRate Стол выглядит так:

+---------------------+-------------------+------------+------------+---------------+
| currency_from__name | currency_to__name | start_date |  end_date  | exchange_rate |
+---------------------+-------------------+------------+------------+---------------+
|        PLN          |        USD        | 2014-03-01 | 2014-08-01 |    3.00000    |
|        PLN          |        USD        | 2014-08-01 | 2014-12-01 |    6.00000    |
+---------------------+-------------------+------------+------------+---------------+

Обратите внимание, что это пример для упрощения математических операций!

В этой таблице плотность данных составляет один раз в месяц, а действительная запись за один месяц, например, когда start_date = 2014.03.01 а также end_date = 2014.04.01, так start_date включительно и end_date является эксклюзивным.

Я хочу рассчитать средний курс обмена за период времени:

2014.06.01; 2012.09.01

Что значит: 2014.06.01 а также 2014.09.01

Когда в Джанго я пишу:

start_date = date(2014, 6, 1)
end_date = date(2014, 9, 1)

ExchangeRate.objects.all().filter(
        (
            Q(start_date__lt=start_date) & 
            Q(end_date__gt=start_date)
        ) | (
            Q(start_date__gte=start_date) & 
            Q(start_date__lt=end_date) & 
            Q(end_date__gt=start_date) 
        )
).annotate(
    currency_from_name = 'currency_from__name', 
    currency_to_name = 'currency_to__name'
).values(  # GROUP BY
    'currency_from_name',
    'currency_to_name'
).aggregate(
    F('currency_from_name'), 
    F('currency_to_name'), 
    Avg('exchange_rate')
)

После этого запроса я получаю значение 4.5000 что с математической точки зрения правильно, но неправильно, когда вам нужно позаботиться о временном диапазоне.
Правильный ответ 4.000,

Я только предложил это решение, чтобы аннотировать дополнительный столбец с помощью этой формулы, а затем вычислять среднее значение из этого столбца:

https://www.codecogs.com/eqnedit.php?latex=\inline&space;Abs&space;\left&space;(&space;\frac{months&space;\left&space;(&space;greater(ER_{start_date}\&space;,\&space; start_date), и пространство, меньше (ER_ {start_date} \ & пространство; \ & пространство; end_date) и пространство, \ вправо и пространство;) и пространство} {месяцев (start_date \ & пространство; \ & пространство; end_date)} и пространство, \ вправо и пространство;) & пространство; * & пространства; ER_ {EXCHANGE_RATE

Куда:

  • Abs функция для абсолютного значения abs()
  • months это функция для расчета месяцев между двумя датами months_between()
  • greater, smaller являются функциями для выбора соответственно большего и меньшего значения из аргументов - greatest() , least()
  • ER означает столбец из ExchangeRate - например F('exchange_rate')

Я использую 9.3 PostgreSQL DB и Django 1.8.4.

Может быть, есть простая функция для этого?
Может я это слишком усложняю?

Источник

3 ответа

Решение

1. months_between():

create function months_of(interval)
 returns int strict immutable language sql as $$
  select extract(years from $1)::int * 12 + extract(month from $1)::int
$$;

create function months_between(date, date)
 returns int strict immutable language sql as $$
   select months_of(age($1, $2))
$$;

2. average_weight():

create function average_weight(numeric, date, date, date, date)
 returns numeric(9,2) strict immutable language sql as $$
   select abs(months_between(GREATEST($2, $4), LEAST($3, $5))/months_between($4, $5))*$1
$$;

3. AverageWeight:

from django.db.models.aggregates import Func
from django.db.models.fields import FloatField

class AverageWeight(Func):
    function = 'average_weight'

    def __init__(self, *expressions):
        super(AverageWeight, self).__init__(*expressions, output_field=FloatField())

По вашему мнению:

ExchangeRate.objects.all().filter(
        (
            Q(start_date__lt=start_date) & 
            Q(end_date__gt=start_date)
        ) | (
            Q(start_date__gte=start_date) & 
            Q(start_date__lt=end_date) & 
            Q(end_date__gt=start_date) 
        )
).annotate(
    currency_from_name = 'currency_from__name', 
    currency_to_name = 'currency_to__name',
    weight_exchange = AverageWeight(
        F('exchange_rate'),
        start_date,
        end_date,
        F('start_date'),
        F('end_date'),
    )
).values(  # GROUP BY
    'currency_from_name',
    'currency_to_name'
).aggregate(
    F('currency_from_name'), 
    F('currency_to_name'), 
    Avg('weight_exchange')
)
Источник

Проблема с вашим приложением заключается в том, как вы решаете хранить курсы валют. Итак, чтобы ответить на ваш вопрос: да, вы слишком усложняете это.

"Математика" говорит вам, что средний обменный курс составляет 4,5, потому что

(3 + 6) /2 == 4.5

Независимо от того, какую дату начала или окончания вы выберете, система получит одно и то же значение.

Чтобы устранить первопричину, давайте попробуем другой подход. (для простоты я оставлю внешние ключи и другие детали, не относящиеся к получению среднего значения в пределах определенного диапазона дат, вы можете добавить их позже)

с этой моделью:

class ExchangeRate(models.Model):
    currency1 = models.CharField(max_length=3)
    currency2 = models.CharField(max_length=3)
    start_date = models.DateField()
    exchange_rate = models.DecimalField(max_digits=12, decimal_places=4)

и эти данные:

INSERT INTO exchange_rate_exchangerate(currency1, currency2, start_date, exchange_rate) VALUES ('PLN', 'USD', '2014-03-01', 3);
INSERT INTO exchange_rate_exchangerate(currency1, currency2, start_date, exchange_rate) VALUES ('PLN', 'USD', '2014-04-01', 3);
INSERT INTO exchange_rate_exchangerate(currency1, currency2, start_date, exchange_rate) VALUES ('PLN', 'USD', '2014-05-01', 3);
INSERT INTO exchange_rate_exchangerate(currency1, currency2, start_date, exchange_rate) VALUES ('PLN', 'USD', '2014-06-01', 3);
INSERT INTO exchange_rate_exchangerate(currency1, currency2, start_date, exchange_rate) VALUES ('PLN', 'USD', '2014-07-01', 3);
INSERT INTO exchange_rate_exchangerate(currency1, currency2, start_date, exchange_rate) VALUES ('PLN', 'USD', '2014-08-01', 6);
INSERT INTO exchange_rate_exchangerate(currency1, currency2, start_date, exchange_rate) VALUES ('PLN', 'USD', '2014-09-01', 6);
INSERT INTO exchange_rate_exchangerate(currency1, currency2, start_date, exchange_rate) VALUES ('PLN', 'USD', '2014-10-01', 6);
INSERT INTO exchange_rate_exchangerate(currency1, currency2, start_date, exchange_rate) VALUES ('PLN', 'USD', '2014-11-01', 6);

мы можем выполнить этот запрос:

from django.db.models import Avg
from datetime import date

first_date = date(2014, 6, 1)
last_date = date(2014, 9, 1)
er.models.ExchangeRate.objects.filter(
    start_date__gte = first_date,
    start_date__lt = last_date

).aggregate(Avg('exchange_rate'))

Чтобы получить этот вывод:

{'exchange_rate__avg': 4.0}
Источник

Вы должны думать об этом как о средневзвешенном значении, поэтому вам нужно рассчитать вес каждой строки и затем сложить все вместе.

Я не знаю достаточно Django, чтобы помочь вам там, но в SQL это было бы (я не могу проверить это сейчас, но я думаю, что это дает правильную идею):

SELECT SUM((LEAST(end_date, @end_date) - GREATEST(start_date, @start_date)) * exchange_rate) / (@end_date - @start_date) AS weighted_avg
FROM 
  ExchangeRate
WHERE
  (start_date, end_date) OVERLAPS (@start_date, @end_date)

При этом используется оператор OVERLAPS, чтобы увидеть, перекрываются ли периоды. Я не уверен, есть ли ошибка в расчете веса на 1, но думаю, что это следует учитывать в определении входной переменной (@end_date = @end_date - 1)

Источник
Другие вопросы по тегам