aggregate.py

### from Exirel

# Ce que je voudrais
SELECT AVG(last_updated_at - created_at) FROM monapp_item;
 
# Ceci fonctione
Item.objects.extra(select={'delta': 'last_update_at - created_at'})
 
# Mais pas ça
Item.objects.extra(select={'delta': 'last_update_at - created_at'}).aggregate(Avg('delta'))
 
 
# Define New Aggregate Class
 
from django.db.models import Aggregate, Avg
from django.db.models.sql.aggregates import (Aggregate as AggregateSQL, Avg as AvgSQL)
 
class DateTimeDelta(Aggregate):
    name = 'delta'
 
    def add_to_query(self, query, alias, col, source, is_summary):
        aggregate = DateTimeDeltaSQL(col, source=source, is_summary=is_summary,
                                     **self.extra)
        query.aggregates[alias] = aggregate
 
 
class DateTimeDeltaSQL(AggregateSQL):
    def as_sql(self, qn, connection):
        params = []
        if hasattr(self.col, 'as_sql'):
            field_name, params = self.col.as_sql(qn, connection)
        elif isinstance(self.col, (list, tuple)):
            field_name = '.'.join([qn(c) for c in self.col])
        else:
            field_name = self.col
 
        return ('(%s - %s)') % (field_name, self.extra['delta_with']), params
 
 
class AvgDateTimeDelta(Avg):
    def add_to_query(self, query, alias, col, source, is_summary):
        aggregate = AvgDateTimeDeltaSQL(col, source=source, is_summary=is_summary, **self.extra)
        query.aggregates[alias] = aggregate
 
 
class AvgDateTimeDeltaSQL(AvgSQL):
    is_computed = False
 
 
# QuerySet
 
Item.objects.annotate(
    delta=DateTimeDelta('last_updated_at', delta_with='created_at')
).aggregate(
    result=AvgDateTimeDelta('delta')
)
 
 
# Result
 
{'result': datetime.timedelta(1, 107, 34833)}