Django-按组注释加权AVG [英] Django - Annotating Weighted AVG by Group
问题描述
我有下面的模型管理器,可以对SalesRecord进行操作:
I've got the following model manager that operates on a SalesRecord:
def by_variety_and_date(self, start_date, end_date):
return self.model.objects.filter(
date__range=(start_date, end_date)
).values(
"variety"
).annotate(
qty_applied=Sum('qty_applied'),
margin=Avg('margin')
)
我真正想要的是 margin = Avg('margin')
以返回加权平均值,基于 qty_applied
。有没有办法使用Django的注释/汇总查询来做到这一点?我一直在尝试将.aggregate()插入字符串的结尾,但是我仍然希望按照此查询集的描述,每个品种
的平均值。
What I'd really like is for the margin=Avg('margin')
to return a weighted average, based on qty_applied
. Is there a way to do this with Django's annotate/aggregate queries? I've been experimenting with stringing .aggregate() to the end of this, but I still want the average per variety
as described by this queryset.
这种情况下的模型如下:
The model in this case looks like this:
class Sale(models.Model):
margin = models.DecimalField(null=True, blank=True, decimal_places=2, max_digits=12)
qty_applied = models.IntegerField(null=True, blank=True)
variety = models.ForeignKey(Variety, null=True)
totals = Totals()
EDIT
这是我最终的结果。有点古怪,但是可以解决问题。
This is what I finally ended up with. It's a bit wonky, but it does the trick.
def by_variety_and_date(self, start_date, end_date):
return self.model.objects.filter(
date__range=(start_date, end_date)
).values(
"variety"
).annotate(
qty_applied=Sum('qty_applied'),
profit=Sum('profit'),
cogs=Sum('cogs'),
sales=Sum('value'),
margin=Sum(
(F('qty_applied')*F('margin')), output_field=FloatField()
) / Sum(
'qty_applied', output_field=FloatField())
)
我将F对象用作 @WTower 建议将每个对象的 margin
乘以它的 qty_applied
,然后将整个内容包装在<$ c中$ c> Sum 并将其除以其中所有对象的 qty_applied
的 Sum
群组。奇迹般有效!
I used the F objects as @WTower suggested to multiply each object's margin
by it's qty_applied
, then wrapped the whole thing in a Sum
and divided it by the Sum
of the qty_applied
of all objects in the group. Works like a charm!
推荐答案
您可以使用 F()
表达式
You can use the F()
expression
>>> from django.db.models import F, FloatField, Sum
>>> MyModel.objects.all().aggregate(
... w_avg=Sum((F('a')*F('b'))/F('b'), output_field=FloatField()))
有关聚合的更多信息此处。
或者,您可以使用自定义SQL(不建议)或管理器中的自定义方法。
Alternatively, you can use custom SQL (not recommended) or a custom method in the manager.
这篇关于Django-按组注释加权AVG的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!