如何在Django中基于共同键合并两个QuerySet并整合统计字段？

需求与现状 #

需要统计某赛事中各球队的主客场比赛数据（胜平负、进球失球、积分），期望每个球队对应一条包含所有主客场字段的数据。

当前通过分别查询主、客场数据得到两个QuerySet，再通过循环合并字典实现需求，但希望用更高效的Django ORM方式替代循环。

模型定义 #

class Match(models.Model):
    competition = models.ForeignKey(Competition, related_name='matches', on_delete=models.CASCADE)
    gameweek = models.PositiveSmallIntegerField(blank=True, null=True)
    home_team = models.ForeignKey(Team, related_name='home_matches', on_delete=models.CASCADE)
    away_team = models.ForeignKey(Team, related_name='away_matches', on_delete=models.CASCADE)
    home_score = models.PositiveSmallIntegerField(default=0)
    away_score = models.PositiveSmallIntegerField(default=0)

    STATUS = Choices(
        (1, 'not_started', 'Not Started'),
        (2, 'half_time', 'Half Time'),
        (3, 'full_time', 'Full Time'),
        (9, 'postponed', 'Postponed')
    )
    status = models.PositiveSmallIntegerField(choices=STATUS, default=STATUS.not_started)

原实现方式 #

先分别查询主、客场数据：

qs_home = Match.objects.filter(competition=competition) \
    .values(name=F("home_team__name")) \
    .annotate(home_win=Sum(Case(When(home_score__gt=F('away_score'), then=1)))) \
    .annotate(home_draw=Sum(Case(When(home_score=F('away_score'), then=1)))) \
    .annotate(home_lose=Sum(Case(When(home_score__lt=F('away_score'), then=1)))) \
    .annotate(home_goal=Sum('home_score')) \
    .annotate(home_conceded=Sum('away_score')) \
    .annotate(home_point=Sum(Case(
        When(home_score__gt=F('away_score'), then=3),
        When(home_score=F('away_score'), then=1)
    ))) \
    .order_by('home_team__name')

qs_away = Match.objects.filter(competition=competition) \
    .values(name=F("away_team__name")) \
    .annotate(away_win=Sum(Case(When(away_score__gt=F('home_score'), then=1)))) \
    .annotate(away_draw=Sum(Case(When(away_score=F('home_score'), then=1)))) \
    .annotate(away_lose=Sum(Case(When(away_score__lt=F('home_score'), then=1)))) \
    .annotate(away_goal=Sum('away_score')) \
    .annotate(away_conceded=Sum('home_score')) \
    .annotate(away_point=Sum(Case(
        When(away_score__gt=F('home_score'), then=3),
        When(away_score=F('home_score'), then=1)
    ))) \
    .order_by('away_team__name')

再通过循环合并两个QuerySet：

for qs in qs_home:
    away_dict = next((item for item in qs_away if item['name'] == qs['name']), None)
    qs['away_win'] = away_dict['away_win']
    qs['away_draw'] = away_dict['away_draw']
    qs['away_lose'] = away_dict['away_lose']
    qs['away_goal'] = away_dict['away_goal']
    qs['away_conceded'] = away_dict['away_conceded']
    qs['away_point'] = away_dict['away_point']

优化ORM方案 #

直接从Team模型出发，利用反向关联的home_matches和away_matches，一次性聚合所有主客场统计数据，仅需一次数据库查询：

from django.db.models import F, Sum, Case, When, Q
import django.db.models as models

teams_stats = Team.objects.filter(
    # 筛选参与当前赛事的球队（包含仅主/仅客场比赛的球队）
    Q(home_matches__competition=competition) | Q(away_matches__competition=competition)
).distinct().annotate(
    # 主场统计字段
    home_win=Sum(Case(
        When(home_matches__home_score__gt=F('home_matches__away_score'), then=1),
        default=0,
        output_field=models.IntegerField()
    )),
    home_draw=Sum(Case(
        When(home_matches__home_score=F('home_matches__away_score'), then=1),
        default=0,
        output_field=models.IntegerField()
    )),
    home_lose=Sum(Case(
        When(home_matches__home_score__lt=F('home_matches__away_score'), then=1),
        default=0,
        output_field=models.IntegerField()
    )),
    home_goal=Sum('home_matches__home_score'),
    home_conceded=Sum('home_matches__away_score'),
    home_point=Sum(Case(
        When(home_matches__home_score__gt=F('home_matches__away_score'), then=3),
        When(home_matches__home_score=F('home_matches__away_score'), then=1),
        default=0,
        output_field=models.IntegerField()
    )),
    # 客场统计字段
    away_win=Sum(Case(
        When(away_matches__away_score__gt=F('away_matches__home_score'), then=1),
        default=0,
        output_field=models.IntegerField()
    )),
    away_draw=Sum(Case(
        When(away_matches__away_score=F('away_matches__home_score'), then=1),
        default=0,
        output_field=models.IntegerField()
    )),
    away_lose=Sum(Case(
        When(away_matches__away_score__lt=F('away_matches__home_score'), then=1),
        default=0,
        output_field=models.IntegerField()
    )),
    away_goal=Sum('away_matches__away_score'),
    away_conceded=Sum('away_matches__home_score'),
    away_point=Sum(Case(
        When(away_matches__away_score__gt=F('away_matches__home_score'), then=3),
        When(away_matches__away_score=F('away_matches__home_score'), then=1),
        default=0,
        output_field=models.IntegerField()
    ))
).values(
    'name',
    'home_win', 'home_draw', 'home_lose',
    'home_goal', 'home_conceded', 'home_point',
    'away_win', 'away_draw', 'away_lose',
    'away_goal', 'away_conceded', 'away_point'
).order_by('name')

方案优势 #

• 性能更优：仅执行一次数据库查询，避免两次查询加内存循环的开销
• 逻辑清晰：直接从球队维度聚合数据，无需额外合并操作
• 覆盖全面：通过Q对象筛选出所有参与赛事的球队，包括仅主/仅客场比赛的队伍

内容的提问来源于stack exchange，提问作者Andi Fathul Mukminin