Django grouped annotation percentage with a single query

user_1695437 0
Viewed 289

What I'm trying to do is work out the percentage of amount for each weekday, I know how to do it in two queries, but I'm wondering if there is a way to do it in one?

For example, if I have the model:

class DailyAmount(models.Model):
  date = models.DateField()
  amount = models.DecimalField()

I can get the percentage amounts for each weekday in two queries like so:

total_amount = models.DailyAmount.objects.all().aggregate(total=Sum("amount"))["amount"]
result = models.DailyAmount.objects.all().annotate(
  weekday=ExtractWeekDay("date")
).values("weekday").annotate(
  percentage=ExpressionWrapper(
    Sum("amount") * 100.0 / total_amount,
    output_field=DecimalField()
  )
)

Is there a way to combine them? I've looked into both Subquery and Window, but I can't get either to quite work and I'm sure I'm missing something.

django-queryset django-orm django
1 Answers

Turns out I was reasonably close:

result = models.DailyAmount.objects.all().annotate(
  weekday=ExtractWeekDay("date")
).values("weekday").distinct().annotate(
  amount_total=Window(
    expression=Sum("amount")
  )
).annotate(
  weekday_total=Window(
    expression=Sum("amount"), partition_by=[F("weekday")],
  )
).annotate(
  group_percentage=ExpressionWrapper(
    F("weekday_total") * 100.0 / F("amount_total"),
    output_field=DecimalField(),
  )
)
user_16954370
Related