Annotate queryset with percentage grouped by date

Question

Suppose I have the following model:

class Order(models.Model):
    category = models.CharField(max_length=100, choices=CATEGORY_CHOICES, default=DEFAULT_CHOICE)
    created_at = models.DateTimeField(auto_now_add=True)

I need to annotate an Order queryset with the percentage of each category grouped by month (based on the created_at field). I managed to write a query to count every Order grouped by month:

orders_per_month = (Order.objects
    .annotate(month=TruncMonth('created_at'))
    .values('month')
    .annotate(count=Count('id'))
    .order_by('month')
    .values('month', 'count')
)

Changing just the last .values() to .values('month', 'category', 'count'), I can get the count grouped by both category and month.

Is it possible to get the percentage of each category grouped by month using Django's ORM? For example, if I had the following data:

MONTH | CATEGORY
Jan   | 'A'
Jan   | 'B'
Feb   | 'A'

I would like to get something similar to this:

[
    (Jan, 'A', 0.5),
    (Jan, 'B', 0.5),
    (Feb, 'A', 1),
]

Thanks in advance.

Did you get this figured out? You should look into the Windows functions. You can use a partition argument to do calculations. (1) window partition over month and count, (2) partition over month and category and count, and (3) divide steps 1 and 2. Let me know if that helps. — ac2001, Dec 10 '19 at 11:35

score 3 · Accepted Answer · answered Feb 18 '20 at 20:03

Using Django's Window functions, as suggested by @ac2001 in the comments, I managed to get what I needed.

Using the example model and supposing I want the percentage of each category grouped by month:

orders_per_month = (Order.objects
    .annotate(month=TruncMonth('created_at'))
    .values('month', 'category')
    .distinct()
    .annotate(month_total=Window(
        expression=Count('id'),
        partition_by=[F('month')],
    ))
    .annotate(month_category=Window(
        expression=Count('id'),
        partition_by=[F('month'), F('category')],
    ))
    .annotate(percentage=ExpressionWrapper(
        F('month_category') * 100.0 / F('month_total'),
        output_field=FloatField()
    ))
    .values('month', 'percentage', 'category')
)

Any suggestions on simplifying this further are welcome.

Annotate queryset with percentage grouped by date

1 Answers1