django聚合:求和再求平均值 [英] django aggregation: sum then average
问题描述
使用Django的ORM annotate()和/或aggregate():我想基于一个类别字段进行汇总,然后对每个日期的类别值进行平均.我尝试使用两个annotate()语句来执行此操作,但遇到了FieldError.
Using django's ORM annotate() and/or aggregate(): I want to sum up based on one category field and then average over the category values per date. I tried to do it using two annotate() statements but got a FieldError.
我正在这样做:
queryset1 = self.data.values('date', 'category').annotate(sum_for_field=Sum('category'))
哪个输出的ValuesQuerySet对象具有以下内容(因此,每个类别值的总和):
Which outputs a ValuesQuerySet object with things like (so a sum for each value of category):
[{'category': 'apples', 'date': '2015-10-12', sum_for_field=2000},
{'category': 'carrots', 'date': '2015-10-12', sum_for_field=5000},
{'category': 'apples', 'date': '2015-10-13', sum_for_field=3000},
{'category': 'carrots', 'date': '2015-10-13', sum_for_field=6000}, ...
]
然后,我想对每个日期的sum_for_field字段求平均值,以输出如下内容:
I then want to average the sum_for_field field for each date to output something like:
[ {'date': '2015-10-12', avg_final: 3500},
{'date': '2015-10-13', avg_final: 4500}, ...
]
我尝试这样做:
queryset2 = queryset1.values('date', 'sum_for_field')
result = queryset2.annotate(avg_final=Avg('sum_for_field'))
但是我收到了这个FieldError:
But I got this FieldError:
FieldError: FieldError: Cannot compute Avg('sum_for_field'): 'sum_for_field' is an aggregate
推荐答案
许多按组的聚合注释中的按组聚合注释通常是一个复杂的问题,但是Sum
中的 Avg
是一个很复杂的问题更容易的特殊情况.
Aggregate annotation by group from many aggregate annotations by group is generally a complicated question, but Avg
from Sum
is a special much easier case.
表达式Avg('sum_for_field')
评估为Sum('sum_for_field') / Count('category', distinct=True)
,可以通过 Aggregate()表达式. Sum('sum_for_field')
等于Sum('amount')
.
Expression Avg('sum_for_field')
can be evaluated as Sum('sum_for_field') / Count('category', distinct=True)
that can be evaluated by Aggregate() expressions. The Sum('sum_for_field')
equals Sum('amount')
.
解决方案 :(预期名称:模型为Data
,其字段为date
,category
,amount
.)
Solution: (Expected names: The model is Data
that has fields date
, category
, amount
.)
qs = Data.objects.values('date').annotate(
avg_final=Sum('amount') / Count('category', distinct=True)
)
(我确信,即使使用Subquery类,也不需要使用奇怪的extra()方法,也不需要原始SQL,当前的Django 1.11都无法解决非常相似的问题)
(I'm convinced that very similar questions would be without any solution by current Django 1.11, even with Subquery class, without using a strange extra() method and without raw SQL)
这篇关于django聚合:求和再求平均值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!