CODEWITHSUNDEEP
pythondjangopostgresql
bdoubleu
bdoubleu

Reputation: 6127

Django summing values over past 10 weeks for each week

Given a date how can you sum the total of the week that date falls in and each of the 10 weeks previous?

For each day in the last 30 this can be accomplished by:

import datetime as dt
from django.db.models import Sum

last_30 = dt.date.today() - dt.timedelta(days=30)
order_data = Order.objects.filter(
    date__gt=last_30).extra(
    select={'day': 'date(date)'}.values('day').annotate(
    total=Sum('total')
)

print(order_data)

<OrderQuerySet [{'day': datetime.date(2018, 8, 28), 'total': Decimal('50000.00'), 
                {'day': datetime.date(2018, 8, 29), 'total': Decimal('84000.00'), 
                '...(remaining elements truncated)...']>

I guess they could also somehow be grouped into weeks after the fact by looping through order_data but I was wondering if there's another way.

Using postgresql if that makes a difference.

Upvotes: 1

Views: 462

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 477666

You can use Django's ExtractWeek [Django-doc] to obtain the week number of a date. So we can first annotate the queryset, and then pass the correct values, like:

from django.db.models.functions import ExtractWeek

qs = Order.objects.filter(date__gt=last_70).annotate(
    week=ExtractWeek('date')
).values('week').annotate(
    week_total=Sum('total')
).order_by('week')

This will produce something like:

<QuerySet [
    { 'week': 14, 'week_total': 1425.0 }
    { 'week': 15, 'week_total': 1302.0 }
    { 'week': 17, 'week_total': 1993.0 }
]>

Note that

  1. the .order_by is important, since otherwise the values will not "fold";
  2. if there are no rows for a specific week, these will not be in the queryset, so it might require some post-processing, to introduce zeros for that;
  3. as per source code, it uses Monday as the first day of the week;
  4. since you filter the dataset, the earliest week, will only sum over a subset of that week (the days that are not "too old"), this thus will result in the fact that that sum of that week will not match with all the days that week.

Upvotes: 2

Related Questions