CODEWITHSUNDEEP
pythondjangoorm
Xezeqeel
Xezeqeel

Reputation: 11

Django - aggregate queryset by week of year

I have model looking like this:

class TestData(models.Model):
    name = models.CharField(max_length=255)
    one_value = models.IntegerField()
    second_value = models.IntegerField()
    create_at = models.DateTimeField()

Is any way to easy generate queryset of summed values for every week of year? Django 1.9, Python 3

Upvotes: 0

Views: 1765

Answers (2)

Vladimir Danilov
Vladimir Danilov

Reputation: 2388

from django.db.models import Func, F, Sum

class Week(Func):
    def as_mysql(self, compiler, connection):
        self.function = 'WEEK'
        return super().as_sql(compiler, connection)

data = (TestData.objects
        .filter(create_at__year=year)
        .annotate(week=Week('create_at', output_field=IntegerField()))
        .values('week')
        .annotate(Sum('one_value')))

Or Sum(F('one_value') + F('second_value')), depending on what sum you want to get.

Upvotes: 2

zubhav
zubhav

Reputation: 1559

This worked for me. Follows on from Vladimir Danilov's solution.

class WeekFunc(Func):
        """
            SQL function call for grouping by WEEK
        """
        def as_mysql(self, compiler, connection):
            self.function = 'WEEK'
            return super(Week, self).as_sql(compiler, connection)


data = queryset.annotate(week=Week('start_time')) \
        .values_list('week') \
        .annotate(some_avg=Avg('field_1')) \
        .order_by('week')

Upvotes: 1

Related Questions