计算平均值时性能较差

Question

我需要显示报价的平均值。问题是，我必须计算多对多字段组合的平均值。我还得把所有这些都翻过去。

我已经这么做了。问题是它的性能很差，我正在寻找一种方法来解决它。

该模型如下所示：

class Offer(models.Model):
    price = DecimalField(max_digits=10, decimal_places=2)
    quantity = PositiveIntegerField()
    product = ForeignKey(Product)
    qualifiers = ManyToManyField(Qualifier)

计算平均值的相关代码如下：

def get_average(product, qualifiers, users=None):
    offers = Offer.objects.filter(product=product)

    if users is not None:
        offers = offers.filter(user__in=users)

    for qualifier in qualifiers:
        offers = offers.filter(qualifiers=qualifier)

    if not offers.count():
        return None

    offers = offers.aggregate(
        quantity_x_price_sum=Sum(F('quantity') * F('price'), output_field=FloatField()),
        quantity_total=Sum('quantity')
    )

    # Weighted average
    return offers['quantity_x_price_sum'] / offers['quantity_total']


def get_averages(product, limit=20, users=None):
    averages = []

    colors = product.qualifiers.filter(type=1)
    sizes = product.qualifiers.filter(type=2)
    other = product.qualifiers.filter(type=3)

    qualifiers = [colors, sizes, other]
    combinations = itertools.product(*qualifiers)

    for combination in combinations:
        average = get_average(product, combination, users)
        if average is not None:
            averages.append(average)

            if len(averages) == limit:
                return averages

    return averages

主要问题在itertools.product(*限定符)中。可以产生数百种组合。直到len(价格) ==限制，它必须遍历它们中的每一个并执行查询。

任何帮助都将受到欢迎。谢谢。

Answer 1

为什么不直接对查询本身进行平均聚合呢？

来自Django文档：

# Average price across all books.
>>> from django.db.models import Avg
>>> Book.objects.all().aggregate(Avg('price'))
{'price__avg': 34.35}

https://docs.djangoproject.com/en/1.11/topics/db/aggregation/

编辑:有更复杂的方法来查询这个，希望这能有所帮助。不确定它如何处理非数字数据。