How to get a moving average (or max) in time period using Django ORM

I am trying to work out if it's possible/practical to use the Django ORM to get the highest value in an arbitrary timebox out of the database.

Imagine a restaurant orders ingredients every day, we might have a simple model that looks like:

class Order(models.Model):
    date = models.DateField()
    ingredient = models.CharField()
    quantity = models.IntegerField()

Then I am able to get the sum quantities ordered each week:

Order.objects.filter(date__gte=start_date, date__lt=end_date)
    .annotate(date=TruncWeek("date"))
    .values("ingredient", "date")
    .annotate(total=Sum("quantity"))
    .order_by("ingredient")

But now I want to figure out the maximum quanity of each ingredient that has been ordered in any consecutive 7 (or X number of) days, across the filtered date range.

Is it possible to do this in the ORM?

You can find the maximum quantity of each ingredient ordered in any consecutive 7-day window using several approaches, either with Window functions (Postgres db) or a combination of list comprehension and ORM queries.

  1. I believe using Window func it will be something like this
from django.db.models import Max, Window
from django.db.models.functions import ExtractDay
from django.db.models.expressions import ValueRange

peak_orders = (
    Order.objects
    .filter(date__gte=start_date, date__lt=end_date)
    .annotate(
        peak_7day=Window(
            expression=Max('quantity'),
            partition_by=['ingredient'],
            order_by=ExtractDay('date').asc(),
            frame=ValueRange(start=-6, end=0)
        )
    )
    .values('ingredient', 'peak_7day')
    .distinct()
)
  1. using ORM with list comprehension
from datetime import timedelta
from django.db.models import Sum

results = []
unique_dates = Order.objects.dates('date', 'day')
ingredients = Order.objects.values_list('ingredient', flat=True).distinct()

for ing in ingredients:
    current_max = 0
    for date in unique_dates:
        window_sum = (
            Order.objects
            .filter(
                ingredient=ing,
                date__gte=date,
                date__lt=date + timedelta(days=7)
            )
            .aggregate(total=Sum('quantity'))['total'] or 0
        )
        current_max = max(current_max, window_sum)
    
    results.append({
        'ingredient': ing,
        'peak_7day': current_max
    })
Вернуться на верх