Q:

Django queryset : Calculate monthly average

I have a sales model and i want to calculate the (Number of transactions)/(num of days) when grouped by month, week, year.
class SaleItem(models.Model):
    id = models.UUIDField(default=uuid4, primary_key=True)
    bill = models.ForeignKey()
    item = models.ForeignKey('item')
    quantity = models.PositiveSmallIntegerField()
    price = models.DecimalField(max_digits=13, decimal_places=3, default=0)

So if sales is grouped by month then this becomes (# transcations/# days in that month) for each month. Now if the sales if grouped by year this becomes (# transcations/# days in that year)
Currently i can get the number of transactions
aggregate = 'month' # parameter
# get number of transactions
SaleItem.objects.annotate(date=Trunc('bill__date', aggregate)).values('date').annotate(sales=Count('bill', distinct=True))

But how can i divide each count by the number of days in that group?

A:

Doing it in SQL is possible (and not even that difficult). Getting the number of days in a month is RDBMS-specific though, and there is no generic Django database function to shield you from the various SQL implementations.  
Django makes it very easy to wrap your own functions around SQL functions. For instance, for SQLite, you can define
class DaysInMonth(Func):
    output_field = IntegerField()
    def as_sqlite(self, compiler, connection):
        return super().as_sql(
            compiler, 
            connection,
            function='strftime',
            template='''
            %(function)s("%%%%d", 
            %(expressions)s, 
            "start of month", 
            "+1 month", 
            "-1 day")
            ''',
        )

Then you can use DaysInMonth() to divide your count by the number of days:
qs = (
    SaleItem.objects
        .annotate(date=Trunc('bill__date', aggregate))
        .values('date')
        .annotate(
            sales = Count('bill', distinct=True),
            sales_per_day = F('sales') / DaysInMonth('date')
        )
)

If a rounded-down integer is not sufficient and you need a decimal result, this is another hoop to jump through:
sales_per_day=ExpressionWrapper(
    Cast('sales', FloatField()) / DaysInMonth(F('date')), 
    DecimalField()
)

If, heaven forbid, you want to round in the database rather than in your template, you need another custom function:
class Round(Func):
  function = 'ROUND'
  output_field = FloatField()
  arity = 2

sales_per_day=Round(
    Cast('sales', FloatField()) / DaysInMonth(F('date')), 
    2 # decimal precision
)

So Django is really flexible, but as Willem said doing it in Python would save you some pain without losing significant performance (if any at all).

