我想做的是计算出每个工作日的amount
百分比,我知道如何在两个查询中做到这一点,但我想知道是否有办法在一个查询中做到这一点?
例如,如果我有模型:
class DailyAmount(models.Model):
date = models.DateField()
amount = models.DecimalField()
我可以在两个查询中获取每个工作日的百分比金额,如下所示:
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()
)
)
有没有办法将它们结合起来?我已经研究了Subquery
和Window
,但我都无法正常工作,我确定我错过了一些东西。
事实证明我相当接近:
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(),
)
)