CODEWITHSUNDEEP
mysqldjangodjango-orm
zubhav
zubhav

Reputation: 1559

Annotating weekly values

I have formed a statement with Django queryset API that gives me weekly averages for some data in my DB.

The data returned is in this format: (6, 4.0), (9, 5.0) ... etc where the first value in each tuple represents the week number and the second value represents an average of data.

However, the weeks are not sequential and are only included if there is data for that week. Notice how the first week is 6 and the second week is 9. I would like to get weeks 6, 7, 8, 9 instead of only returning weeks for which data exists.

Is this possible using Django querysets and annotations?

data = post_queryset.annotate(week=WeekFunc('post__start_time')) \
                    .values_list('week') \
                    .annotate(sum=Sum('goods')) \
                    .order_by('week')

Week is an extension of the Func class

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

post_queryset is a queryset that is passed to this function

Upvotes: 1

Views: 90

Answers (1)

Risadinha
Risadinha

Reputation: 16671

You still haven't posted your Django Model, so here a theoretical answer:

To output rows of data where your base table does not have any entries, requires that you align (:= join) the data against another dataset that contains entries for each resulting row.

Example:

Your model table contains timestamped entries for:

  • week 6
  • week 9

If you want to get data for all weeks, you have to join it agains a dataset that contains timestamps for all weeks. How you create that kind of dataset, I cannot tell you. I suppose that there might be some function or select statement in MySQL that can do that.

all weeks joined to model data:

- week 1 == <no data> => week 1: sum=0
- week 2 == <no data> => week 2: sum=0
- week 3 == <no data> => week 3: sum=0
- week 4 == <no data> => week 4: sum=0
- week 5 == <no data> => week 5: sum=0
- week 6 == 4 goods   => week 6: sum=4
- week 7 == <no data> => week 7: sum=0
- week 8 == <no data> => week 8: sum=0
- week 9 == 5 goods   => week 9: sum=5

Once you know how to create that list of weeks, you can join it against the model data and the join in combination with the SUM() function should then generate entries with 0 value for all the weeks where you don't have data.

It might be worth sitting down and just trying to achieve that in pure SQL in order to understand what has to happen on that layer. Only then can you move on and create a solution in the Django ORM layer - either by just providing raw SQL or by trying to use the annotate and extra function to recreate the pure SQL solution.

Upvotes: 1

Related Questions