想象一下这个简单的模型:
class Expense(models.Model):
price = models.DecimalField(decimal_places=2, max_digits=6)
description = models.CharField(max_length=300)
category = models.CharField(choices=ExpenseCategory.choices, max_length=20)
created_at = models.DateField()
我正在努力获得本年度每category
人每月price
人的平均值.我的总体 idea 是:
sub = (
Expense.objects.filter(created_at__year=date.today().year)
.annotate(month=TruncMonth("created_at"))
.values("month", "category")
.annotate(total=Sum("price"))
.order_by("month")
)
qs = Expense.objects.values("category").annotate(avg=Avg(Subquery(sub.values("total"))))
我基本上是想:
- 缩短
created_at
个月 - 按
category
和month
分组 - 把
prices
加起来 - 每
category
个加prices
个
如果我真的喜欢:
for category in categories:
sub.filter(category=category).aggregate(avg=Avg("total"))