Django ORM:获取每个类别的月平均价格

Question

想象一下这个简单的模型:

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()

我试图得到price在当年每个category的月平均值。我一般的想法是这样做:

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"))))

我基本上是想:

1. 截断created_at的月份
2. category和month分组
3. prices
4. 每个category的prices聚合

如果我这样做就可以了:

for category in categories:
sub.filter(category=category).aggregate(avg=Avg("total"))

Answer 1

您的查询可以比您想象的更简单。您当前尝试的解决方案是:

1. 截断created_at以获得月份
2. category和month分组
prices
3. 取每个类别的平均值

这样做的问题是取聚合的聚合。让我们反过来想想你的问题(我们将在这里做一些数学运算)。您想要一个类别的每月价格的平均值，如果我们只考虑一个类别并且每月价格是一个数组M[12]，那么我们可以表示为:

(M[0] + M[1] + ... + M[11]) / 12

M中的每个值都可以认为是与月份匹配的prices的求和。如果我们考虑P[12][]是一个包含每个月价格的二维数组，我们可以将上面的公式重写为:

(Sum(P[0]) + Sum(P[1] + ... + Sum(P[12])) / 12

进一步考虑，它只是一年中所有价格的总和除以12!这意味着您的查询可以简单地写成:

from django.db.models import ExpressionWrapper, FloatField, Sum, Value

qs = Expense.objects.filter(
created_at__year=date.today().year
).values("category").annotate(
avg=ExpressionWrapper(
Sum("price") / Value(12), output_field=FloatField()
)
)

注意除以12意味着我们假设我们有全年的数据，这可能不是今年的数据，所以我们应该除以适当的月数。我们可能还想过滤到前一个月，以防我们没有明显地进入当前月份。

Answer 2

模型管理器中的函数可能是一个很好的解决方案。在你的models.py中添加一个模型。在Expense类之前调用ExpenseManager的管理器类:

class ExpenseManager(models.Manager):
def summarise_categories(self):
return (
super(ExpenseManager, self)
.get_queryset()
.filter(created_at__year=date.today().year)
.annotate(month=TruncMonth("created_at"))
.values("month", "category")
.annotate(total=Sum("price")/Count("created_at__month", distinct=True))
)

那么在你的Expense类做:

class Expense(models.Model):
...

objects = ExpenseManager()

要获得结果的查询集聚合，您可以简单地在视图中的模型对象上调用模型管理器方法:

summarised_categories = Expense.objects.summarise_categories()

这种方法将所有的工作推到数据库，返回一个类别字典和它们今年到目前为止的平均每月价格。