python - Django 使用 prefetch_related() 聚合 Avg vs for 循环

Question

我有一个 Review 模型，它与 Rating 模型是一对一的关系。用户可以根据六个不同的标准（清洁度、沟通、签到、准确性、位置和价值）给出评级，这些标准被定义为评级模型中的字段。

class Rating(models.Model):
    cleanliness     = models.PositiveIntegerField()
    communication   = models.PositiveIntegerField()
    check_in        = models.PositiveIntegerField()
    accuracy        = models.PositiveIntegerField()
    location        = models.PositiveIntegerField()
    value           = models.PositiveIntegerField()

class Review(models.Model):
    room       = models.ForeignKey('room.Room', on_delete=models.SET_NULL, null=True)
    host       = models.ForeignKey('user.User', on_delete=models.CASCADE, related_name='host_reviews')
    guest      = models.ForeignKey('user.User', on_delete=models.CASCADE, related_name='guest_reviews')
    rating     = models.OneToOneField('Rating', on_delete=models.SET_NULL, null=True)
    content    = models.CharField(max_length=2000)

我正在考虑一种计算总体评分的方法，这将是评分模型中每列的平均值。一种方法是使用 Django 的 aggregate() 函数，另一种方法是预取所有评论并循环遍历每条评论以手动计算总体评分。例如，

for room in Room.objects.all()
    ratings_dict = Review.objects.filter(room=room)\
            .aggregate(*[Avg(field) for field in ['rating__cleanliness', 'rating__communication', \
               'rating__check_in', 'rating__accuracy', 'rating__location', 'rating__value']])
    ratings_sum = 0
    for key in ratings_dict.keys():
        ratings_sum += ratings_dict[key] if ratings_dict[key] else 0

或者，简单地循环，

rooms = Room.objects.prefetch_related('review_set')
for room in rooms:
    reviews   = room.review_set.all()
    ratings   = 0
    for review in reviews:
        ratings += (review.rating.cleanliness + review.rating.communication + review.rating.check_in + 
                        review.rating.accuracy + review.rating.location+ review.rating.value)/6

哪种方式在时间复杂度方面更有效并导致更少的数据库调用？

aggregate(Avg('field_name')) 每次函数调用是否在数据库级别生成一个 Avg 查询？

在调用 room.review_set.all() 时，首先使用 prefetch_related() 调用所有房间是否有助于减少查询次数？

标签： pythondjangoperformanceaggregateprefetch