在Django中如何计算平均数？

Question

我有以下Category和Product模型：

class Category(models.Model):
    name = models.CharField(max_length=20)

class Product(models.Model):
    category = models.ForeignKey(Category, on_delete=models.CASCADE)
    name = models.CharField(max_length=50)
    price = models.DecimalField(decimal_places=2, max_digits=5)

问题:

1. 如何对Product模型中的所有价格求平均?

2. 如何按类别对Product模型中的所有价格进行平均?

- yaboinav

Answer 1

您可以在Django查询中使用聚合函数和平均值。

假设您的模型Product的字段名为price：

from django.db.models import Avg

Product.objects.aggregate(Avg('price'))
Product.objects.aggregate(Avg('price'))

- Yuri Malheiros

---

这个计算的是总体平均值，但是我需要每个成员的平均值，你明白我的意思吗？ - yaboinav

是的！例如，Aragorn有34胜27负。Jack有2胜18负。我需要每个成员的平均胜率。 - yaboinav

我认为使用 .values 更容易获得胜利和失败，并手动进行计算，这是简单的数学。 - Yuri Malheiros

Answer 2

for category in Category.objects.all():
    stat = Product.objects.filter(category_id=category.id).aggregate(Avg('price'))['price__avg']`

Django聚合

- Alexandr Ovdienko

Answer 3

你只需要使用 values()、aggregate() 和函数 Avg() 和 F() 即可。

假设您有以下这样的 Model:

class Member(models.Model):
    nickname = models.CharField(max_length=16, unique=True)

class MatchResult(models.Model):
    member = models.ForeignKey('Member')
    wins = models.IntegerField()
    losses = models.IntegerField()

现在，您可以使用这个语法（查看__双下划线符号）来聚合它们：

MatchResult.objects.values(
    'username_id', 
    'username__nickname', 
    'wins',
    'losses'
).aggregate(
    user_id=F('username_id'),
    nickname=F('username__nickname'),
    avg_wins=Avg('wins'),
    avg_losses=Avg('losses')
)

请参考文档。

提示：注释和筛选子句的顺序很重要。

- WBAR

Answer 4

您可以使用 aggregate() 和 annotate() 与 Avg() 一起对 Product 模型中的价格按类别进行平均，如下所示。*您需要在使用 annotate() 时使用 order_by('pk')，否则值将按降序打印：

from django.db.models import Avg

# Average the prices in "Product" model
print(
    "<All products>",
    Product.objects.aggregate(Avg('price'))
) 

print()

# Average the prices in "Product" model category by category
for obj in Category.objects.all(): 
    print(
        obj.id, obj.name,
        Product.objects.filter(category=obj)
                       .aggregate(Avg('price'))
    )

print()

# Average the prices in "Product" model category by category
for obj in Category.objects.all(): 
    print(
        obj.id, obj.name, 
        obj.product_set.aggregate(Avg('price'))
    )

print()

# Average the prices in "Product" model category by category
qs = Category.objects.annotate(Avg('product__price')).order_by('pk')
for obj in qs:
    print(obj.id, obj.name, obj.product__price__avg)

然后，在控制台上输出以下内容：

<All products> {'price__avg': Decimal('20.1578947368421053')}

1 Fruits {'price__avg': Decimal('13.5714285714285714')}
2 Vegetable {'price__avg': Decimal('23.0000000000000000')}
3 Meat {'price__avg': Decimal('24.2500000000000000')}
4 Fish {'price__avg': Decimal('25.3333333333333333')}

1 Fruits {'price__avg': Decimal('13.5714285714285714')}
2 Vegetable {'price__avg': Decimal('23.0000000000000000')}
3 Meat {'price__avg': Decimal('24.2500000000000000')}
4 Fish {'price__avg': Decimal('25.3333333333333333')}

1 Fruits 13.5714285714285714
2 Vegetable 23.0000000000000000
3 Meat 24.2500000000000000
4 Fish 25.3333333333333333

而且，您可以将默认键price__avg和product__price__avg更改为priceAvg，以便于下面所示的价格列：

from django.db.models import Avg

# Average the prices in "Product" model
print(
    "<All products>",
    Product.objects.aggregate(priceAvg=Avg('price'))
)                             # ↑ Here

print()

# Average the prices in "Product" model category by category
for obj in Category.objects.all(): 
    print(
        obj.id, obj.name,
        Product.objects.filter(category=obj)
                       .aggregate(priceAvg=Avg('price'))
    )                             # ↑ Here

print()

# Average the prices in "Product" model category by category
for obj in Category.objects.all(): 
    print(
        obj.id, obj.name, 
        obj.product_set.aggregate(priceAvg=Avg('price'))
    )                             # ↑ Here

print()

# Average the prices in "Product" model category by category
qs = Category.objects.annotate(priceAvg=Avg('product__price')).order_by('pk')
for obj in qs:                 # ↑ Here
    print(obj.id, obj.name, obj.priceAvg)
                                # ↑ Here

然后，将默认密钥更改如下：

<All products> {'priceAvg': Decimal('20.1578947368421053')}

1 Fruits {'priceAvg': Decimal('13.5714285714285714')}
2 Vegetable {'priceAvg': Decimal('23.0000000000000000')}
3 Meat {'priceAvg': Decimal('24.2500000000000000')}
4 Fish {'priceAvg': Decimal('25.3333333333333333')}

1 Fruits {'priceAvg': Decimal('13.5714285714285714')}
2 Vegetable {'priceAvg': Decimal('23.0000000000000000')}
3 Meat {'priceAvg': Decimal('24.2500000000000000')}
4 Fish {'priceAvg': Decimal('25.3333333333333333')}

1 Fruits 13.5714285714285714
2 Vegetable 23.0000000000000000
3 Meat 24.2500000000000000
4 Fish 25.3333333333333333

- Kai - Kazuya Ito