如何关联（而非合并）多个Django QuerySet生成统计报表？

先修正下你模型里的几个小错误：

• STATUS_CHOICES 里的 ('PE') 格式不对，得写成二元组 ('PE', 'PENDING')
• modles.Model 拼写错了，应该是 models.Model
• ref_no = models.CharField(max_length=) 必须指定长度，比如 max_length=20

修正后的模型代码：

STATUS_CHOICES = (
    ('CL', 'CLOSED'),
    ('FR', 'FORWARDED'),
    ('PE', 'PENDING'),
)

class References(models.Model):
    ref_no = models.CharField(max_length=20)
    date_received = models.DateField()
    status = models.CharField(max_length=2, choices=STATUS_CHOICES)

最优方案：一次查询搞定所有统计 #

你完全不用写4个QuerySet再费劲关联，用Django的Case和When可以在单条查询里按年月分组，同时算出总数量和各状态的计数，性能比多次查询高多了：

from django.db.models import Count, Case, When, IntegerField

stats = References.objects.values('date_received__year', 'date_received__month') \
    .annotate(
        total=Count('ref_no'),
        closed=Count(Case(
            When(status__iexact='CL', then=1),
            output_field=IntegerField()
        )),
        forwarded=Count(Case(
            When(status__iexact='FR', then=1),
            output_field=IntegerField()
        )),
        pending=Count(Case(
            When(status__iexact='PE', then=1),
            output_field=IntegerField()
        ))
    ) \
    .order_by('date_received__year', 'date_received__month')

结果格式 #

执行后stats里的每个元素都是字典，示例如下：

{
    'date_received__year': 2024,
    'date_received__month': 5,
    'total': 15,
    'closed': 6,
    'forwarded': 4,
    'pending': 5
}

如果一定要合并你原来的QuerySet（不推荐） #

要是你坚持要用自己写的4个QuerySet合并，得把每个QuerySet的结果转成字典，用(年,月)当键来整合，但这种方法要查4次数据库，效率很低。代码示例：

# 把各QuerySet转成(年,月)为键的字典
total_dict = {(item['date_received__year'], item['date_received__month']): item['dcount'] for item in q_total}
closed_dict = {(item['date_received__year'], item['date_received__month']): item['dcount'] for item in q_closed}
forwarded_dict = {(item['date_received__year'], item['date_received__month']): item['dcount'] for item in q_forwarded}
pending_dict = {(item['date_received__year'], item['date_received__month']): item['dcount'] for item in q_pending}

# 收集所有出现过的年月组合
all_years_months = set(total_dict.keys()).union(closed_dict.keys()).union(forwarded_dict.keys()).union(pending_dict.keys())

# 生成最终报表数据
report = []
for year, month in sorted(all_years_months):
    report.append({
        'year': year,
        'month': month,
        'total': total_dict.get((year, month), 0),
        'closed': closed_dict.get((year, month), 0),
        'forwarded': forwarded_dict.get((year, month), 0),
        'pending': pending_dict.get((year, month), 0)
    })

还是推荐用第一种单QuerySet的方法，简洁又高效。

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