CODEWITHSUNDEEP
djangopython-3.xdjango-modelsdjango-ormdjango-annotate
Vin
Vin

Reputation: 319

Annotate queryset with percentage grouped by date

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.

Upvotes: 0

Views: 712

Answers (1)

Vin
Vin

Reputation: 319

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.

Upvotes: 3

Related Questions