Django技术问题：如何实现平均值的平均值注解？

模型定义 #

我有Puzzle和Session模型，用户在会话中游玩谜题并评分，结果通过Play模型记录：

class Play(models.Model):
    puzzle = models.ForeignKey(
            'puzzles.Puzzle', on_delete=models.CASCADE, related_name='plays')
    rating = models.IntegerField(default=-1)
    session = models.ForeignKey(Session, blank=True,
                                null=True, on_delete=models.SET_NULL, related_name='plays')

同一谜题可在同一会话中被多次游玩。

需求 #

我需要给Session.objects添加注解rating_avg_from_session_plays，计算逻辑分为两步：

• 对会话中每个游玩的谜题，计算该谜题的平均评分（avg_pzl_rating）
• 对每个会话，计算所有谜题平均评分的平均值，得到会话的最终平均评分

错误的实现代码 #

我尝试用Subquery和group_by实现，但结果不符合预期——只取了第一个评分的平均值（测试会话得到1.0，正确值应为2.1527777777777777），代码如下：

rating_average_by_puzzle_from_session_plays = Subquery(Play.objects.filter(session_id=OuterRef(
                'id')).values('puzzle_id').order_by('puzzle_id').annotate(
                puzzle_rating_average=Avg('rating')).values('puzzle_rating_average'))
    
Session.objects.annotate(rating_avg_from_session_plays=Avg(rating_average_by_puzzle_from_session_plays))

单个Session对象的正确实现 #

对单个Session对象执行以下代码能得到正确结果：

session_object.plays.values('puzzle_id').order_by('puzzle_id').annotate(
           puzzle_rating_average=Avg('rating')).aggregate(
           rating_average=Avg('puzzle_rating_average'))['rating_average'] or 0

生成的SQL语句 #

上述错误代码生成的SQL如下：

SELECT "games_session"."id", "games_session"."datetime", "games_session"."rating_average", 
"games_session"."user_id", AVG((SELECT AVG(U0."rating") AS "puzzle_rating_average"             
FROM "games_play" U0 WHERE U0."session_id" = "games_session"."id" 
GROUP BY U0."puzzle_id")) AS "rating_avg_from_session_plays" FROM "games_session" 
GROUP BY "games_session"."id", "games_session"."datetime", "games_session"."rating_average", 
"games_session"."user_id"

测试数据 #

测试会话包含31次游玩，涉及12个谜题，数据如下：

[{"model": "games.play","pk": 207,"fields": {"puzzle": 104,"rating": 1,"session": 2}},{"model": "games.play","pk": 208,"fields": {"puzzle": 104,"rating": 1,"session": 2}},{"model": "games.play","pk": 209,"fields": {"puzzle": 104,"rating": 1,"session": 2}},{"model": "games.play","pk": 210,"fields": {"puzzle": 104,"rating": 1,"session": 2}},{"model": "games.play","pk": 211,"fields": {"puzzle": 104,"rating": 1,"session": 2}},{"model": "games.play","pk": 212,"fields": {"puzzle": 104,"rating": 1,"session": 2}},{"model": "games.play","pk": 213,"fields": {"puzzle": 105,"rating": 1,"session": 2}},{"model": "games.play","pk": 214,"fields": {"puzzle": 105,"rating": 1,"session": 2}},{"model": "games.play","pk": 215,"fields": {"puzzle": 106,"rating": 2,"session": 2}},{"model": "games.play","pk": 218,"fields": {"puzzle": 107,"rating": 3,"session": 2}},{"model": "games.play","pk": 220,"fields": {"puzzle": 109,"rating": 3,"session": 2}},{"model": "games.play","pk": 224,"fields": {"puzzle": 113,"rating": 1,"session": 2}},{"model": "games.play","pk": 225,"fields": {"puzzle": 114,"rating": 1,"session": 2}},{"model": "games.play","pk": 232,"fields": {"puzzle": 108,"rating": 2,"session": 2}},{"model": "games.play","pk": 233,"fields": {"puzzle": 110,"rating": 3,"session": 2}},{"model": "games.play","pk": 246,"fields": {"puzzle": 111,"rating": 3,"session": 2}},{"model": "games.play","pk": 247,"fields": {"puzzle": 112,"rating": 2,"session": 2}},{"model": "games.play","pk": 248,"fields": {"puzzle": 115,"rating": 2,"session": 2}},{"model": "games.play","pk": 510,"fields": {"puzzle": 104,"rating": 1,"session": 2}},{"model": "games.play","pk": 511,"fields": {"puzzle": 105,"rating": 2,"session": 2}},{"model": "games.play","pk": 512,"fields": {"puzzle": 106,"rating": 2,"session": 2}},{"model": "games.play","pk": 513,"fields": {"puzzle": 107,"rating": 3,"session": 2}},{"model": "games.play","pk": 514,"fields": {"puzzle": 108,"rating": 3,"session": 2}},{"model": "games.play","pk": 515,"fields": {"puzzle": 109,"rating": 3,"session": 2}},{"model": "games.play","pk": 516,"fields": {"puzzle": 110,"rating": 3,"session": 2}},{"model": "games.play","pk": 517,"fields": {"puzzle": 111,"rating": 3,"session": 2}},{"model": "games.play","pk": 518,"fields": {"puzzle": 112,"rating": 3,"session": 2}},{"model": "games.play","pk": 519,"fields": {"puzzle": 113,"rating": 2,"session": 2}},{"model": "games.play","pk": 520,"fields": {"puzzle": 114,"rating": 1,"session": 2}},{"model": "games.play","pk": 521,"fields": {"puzzle": 115,"rating": 2,"session": 2}},{"model": "games.play","pk": 522,"fields": {"puzzle": 115,"rating": 2,"session": 2}},{"model": "games.session","pk": 2,"fields": {"datetime": "2020-10-28T20:26:52.023Z","rating_average": "2.2"}}]

请问我的代码哪里出错了？

内容的提问来源于stack exchange，提问作者Skratt