[Django]-Django: Sum by date and then create extra field showing rolling average

The error you are getting is because you are trying to compute the average of an aggregate field (‘sum_for_date’) within the same query. This is not possible with Django’s ORM.

One possible workaround is to use a subquery to calculate the daily sums, and then use another query to calculate the rolling average. Here’s an example using your Sales model:

from django.db.models import Avg, Sum, F, Window
from django.db.models.functions import Lag

# Subquery to calculate daily sums
daily_sums = (
    Sales.objects
    .values('date')
    .annotate(sum_for_date=Sum('value'))
)

# Query to calculate rolling average
rolling_avg = (
    daily_sums
    .annotate(
        rolling_sum=Window(
            expression=Sum('sum_for_date'),
            frame=Window(
                expression=Lag('sum_for_date', offset=6),
            ),
        ),
        rolling_count=Window(
            expression=Sum(1),
            frame=Window(
                expression=Lag('date', offset=6),
            ),
        ),
    )
    .annotate(
        rolling_avg=Window(
            expression=F('rolling_sum') / F('rolling_count'),
            frame=Window(
                expression=Lag('date', offset=6),
            ),
        )
    )
    .order_by('date')
)

This first calculates the daily sums using a subquery, and then uses window functions to calculate the rolling average. The rolling_sum window function calculates the sum of the previous 7 days’ sums, and the rolling_count window function calculates the count of dates in the previous 7 days. Finally, we divide rolling_sum by rolling_count to get the rolling average.

Note that this approach uses Window functions, which are only available in certain databases (e.g. PostgreSQL). If you’re using a different database, you may need to use a different approach to calculate the rolling average.

More information about Window: https://docs.djangoproject.com/en/dev/ref/models/expressions/#window-functions