Annotate a queryset with the average date differen

问题:

I searched all over place for an answer to this but couldn't find anything. Perhaps this is just a stupid question or a really tricky one. Here it is:

Let's say my model is this (pseudo django code):

Event
  type = ForeignKey(EventType)
  name = CharField
  date_start = DateField
  date_end = DateField

EventType
  name = CharField

What I want to know is the average duration time for each event type. What I do now is calculate the average duration whenever a new event is created (save method) and have that stored in an average_duration column in EventType. The problem with this approach is that I cannot answer questions like "what was the average duration time for events of type X, during the year Y". So instead of adding more columns to answer questions like these I would prefer to have it done in "real-time".

Can this be done by annotating the queryset? First I would have to get the date differences for each event type, then come up with their average, and then annotate the Event queryset with that average, I assume.

回答1:

Just an update. In Django >= 1.8 it is possible to do:

from django.db.models import F, ExpressionWrapper, fields

duration = ExpressionWrapper(F('date_end') - F('date_start'), output_field=fields.DurationField())

events_with_duration = Event.objects.annotate(duration=duration)

after which you can run queries like:

events_with_duration.filter(duration__gt=timedelta(days=10))

回答2:

I think your best bet is to create an SQL view with the date_end - date_start column, create a django model on this view and then you will be able to query the view and annotate it as you want. I've done this with models similars to yours and maybe I could extract some interesting code for you if you need.

回答3:

You'll need to create a queryset with the extra method to add the date difference to each row

Then use the aggregate method to compute the average for your just added column:

Be careful though, this method is slow and won't scale. Storing the computed value on event_type is imho your best option.