如何用Django ORM查询各竞赛的题目作答统计数据？

首先需要修正原模型中的两处拼写错误，否则会导致ORM查询失败：

• ContestQuestions中的quetions字段名应为questions
• UserResponse中的on_deleted参数应为on_delete

修正后的完整模型 #

from django.db import models
from django.contrib.auth.models import User

class Contest(models.Model):
    name = models.CharField(max_length=255)
    date = models.CharField(max_length=50)
    is_active = models.BooleanField(default=False)

class Question(models.Model):
    title = models.CharField(max_length=255)
    created_at = models.DateTimeField(auto_now_add=True)

class Answer(models.Model):
    question = models.ForeignKey(Question, on_delete=models.CASCADE, related_name='answers')
    answer = models.CharField(max_length=255)
    is_true = models.BooleanField(default=False)

class ContestQuestions(models.Model):
    contest = models.ForeignKey(Contest, on_delete=models.CASCADE, related_name='contest_questions')
    questions = models.ForeignKey(Question, on_delete=models.CASCADE, related_name='contest_question_links')

class UserResponse(models.Model):
    user = models.ForeignKey(User, on_delete=models.CASCADE, related_name='responses')
    response = models.ForeignKey(Answer, on_delete=models.CASCADE, related_name='user_responses')

1. 获取单竞赛的题目基础统计 #

针对指定竞赛（例如contest_id=1），查询每道题的总作答人数、答对人数：

from django.db.models import Count, F, Q

contest_question_stats = Contest.objects.filter(id=1).annotate(
    questions=F('contest_questions__questions')
).values(
    'id', 'name', 'questions__id', 'questions__title'
).annotate(
    # 统计去重的总作答用户数
    total_respondents=Count('contest_questions__questions__answers__user_responses__user', distinct=True),
    # 统计选择正确答案的去重用户数
    correct_respondents=Count(
        'contest_questions__questions__answers__user_responses__user',
        filter=Q(contest_questions__questions__answers__is_true=True),
        distinct=True
    )
).order_by('questions__id')

返回的数据结构示例：

{
    'id': 1,
    'name': 'Python基础竞赛',
    'questions__id': 101,
    'questions__title': 'Python中列表的append方法返回值是什么？',
    'total_respondents': 150,
    'correct_respondents': 90
}

2. 获取答对人数最多的前5道题 #

基于基础统计结果，按答对人数倒序取前5：

top_correct_questions = contest_question_stats.order_by('-correct_respondents')[:5]

3. 获取答错人数最多的前5道题 #

先计算答错人数（总作答人数 - 答对人数），再按答错人数倒序取前5：

top_incorrect_questions = contest_question_stats.annotate(
    incorrect_respondents=F('total_respondents') - F('correct_respondents')
).order_by('-incorrect_respondents')[:5]

4. 批量获取所有竞赛的统计数据 #

如果需要一次性查询所有竞赛的统计，可通过预加载优化性能：

all_contest_stats = Contest.objects.annotate(
    total_questions=Count('contest_questions__questions', distinct=True)
).prefetch_related(
    models.Prefetch(
        'contest_questions',
        queryset=ContestQuestions.objects.annotate(
            total_respondents=Count('questions__answers__user_responses__user', distinct=True),
            correct_respondents=Count(
                'questions__answers__user_responses__user',
                filter=Q(questions__answers__is_true=True),
                distinct=True
            )
        ).select_related('questions')
    )
)

• 使用distinct=True确保统计的是唯一用户数，避免同一用户多次作答重复计数
• 通过Q()过滤器精准筛选出选择正确答案的用户行为
• 利用F()表达式实现字段间的算术运算，直接在数据库层面计算答错人数
• 批量查询时使用prefetch_related和select_related减少数据库请求次数，优化查询性能

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