Grouping by Week and Disabling Missing Weeks

Question

In my Django model, I have a very simple model, which represents a single event event (for example, the appearance of a server):

class EventOccurrence: event = models.ForeignKey(Event) time = models.DateTimeField() 

My ultimate goal is to create a table or graph that shows how many times an event has occurred in the past n weeks.

So my question has two parts:

• How can I group_by week field time ?
• How can I group_by out the result of this group_by to add a null value for all the remaining weeks?

For example, for the second part, I would like to convert the result as follows:

 | week | count | | week | count | | 2 | 3 | | 2 | 3 | | 3 | 5 | —— becomes —> | 3 | 5 | | 5 | 1 | | 4 | 0 | | 5 | 1 | 

What is the best way to do this in Django? General Python solutions are also in order.

Answer 1

Django DateField , as well as datetime do not support the week attribute. To get everything in one request, you need to do:

 from django.db import connection cursor = connection.cursor() cursor.execute(" SELECT WEEK(`time`) AS 'week', COUNT(*) AS 'count' FROM %s GROUP BY WEEK(`time`) ORDER BY WEEK(`time`)" % EventOccurrence._meta.db_table, []) data = [] results = cursor.fetchall() for i, row in enumerate(results[:-1]): data.append(row) week = row[0] + 1 next_week = results[i+1][0] while week < next_week: data.append( (week, 0) ) week += 1 data.append( results[-1] ) print data 

Answer 2

After digging the django request api doc, I did not find a way to make the request through the django ORM system. Cursor is a workaround if your database brand is MySQL:

 from django.db import connection, transaction cursor = connection.cursor() cursor.execute(""" select week(time) as `week`, count(*) as `count` from EventOccurrence group by week(time) order by 1;""") myData = dictfetchall(cursor) 

This, in my opinion, is the best solution for increasing productivity. But note that this will not miss a week.

EDITED An independent database brand solution using python (less performance)

If you are looking for brand-independent database code, then you should take dates every day and collect them through python. If this is your code, the code might look like this:

 #get all weeks: import datetime weeks = set() d7 = datetime.timedelta( days = 7) iterDay = datetime.date(2012,1,1) while iterDay <= datetime.now(): weeks.add( iterDay.isocalendar()[1] ) iterDay += d7 #get all events allEvents = EventOccurrence.objects.value_list( 'time', flat=True ) #aggregate event by week result = dict() for w in weeks: result.setdefault( w ,0) for e in allEvents: result[ e.isocalendar()[1] ] += 1 

(Disclaimer: not verified)

Answer 3

Since I have to query multiple tables by attaching them, I use db view to solve these requirements.

 CREATE VIEW my_view AS SELECT *, // <-- other fields goes here YEAR(time_field) as year, WEEK(time_field) as week FROM my_table; 

and model:

 from django.db import models class MyView(models.Model): # other fields goes here year = models.IntegerField() week = models.IntegerField() class Meta: managed = False db_table = 'my_view' def query(): rows = MyView.objects.filter(week__range=[2, 5]) # to handle the rows 

after getting the lines from this db view, use the @danihp path to fill 0 for the “hole” of weeks / months.

NOTE: this is only checked on the MySQL backend, I'm not sure if this is normal for MS SQL Server or another.