Django：按月统计多类数据并合并字段不同的查询集

Got it, let's break down how to get those monthly stats you need from your PreData model. We'll cover two approaches—one that's straightforward and easy to debug, and another that's more efficient with a single database query.

First Approach: Separate Queries + Merge Results #

If you prefer clear, distinct steps, start by querying each metric individually, then combine them into a unified dataset.

First, import the necessary tools:

from django.db.models import Count
from django.db.models.functions import TruncMonth

1. Query Each Metric #

• Received Count (grouped by receivedon month):

  received_stats = (
      PreData.objects.filter(receivedon__isnull=False)
      .annotate(month=TruncMonth('receivedon'))
      .values('month')
      .annotate(received_count=Count('id'))
      .order_by('month')
  )
  

• Published Count (grouped by publishedon month):

  published_stats = (
      PreData.objects.filter(publishedon__isnull=False)
      .annotate(month=TruncMonth('publishedon'))
      .values('month')
      .annotate(published_count=Count('id'))
      .order_by('month')
  )
  

• Processing Count (filter by status + group by receivedon month—replace 'processing' with your actual status value):

  processing_stats = (
      PreData.objects.filter(status='processing', receivedon__isnull=False)
      .annotate(month=TruncMonth('receivedon'))
      .values('month')
      .annotate(processing_count=Count('id'))
      .order_by('month')
  )
  

• Rejected Count (replace 'rejected' with your actual status value):

  rejected_stats = (
      PreData.objects.filter(status='rejected', receivedon__isnull=False)
      .annotate(month=TruncMonth('receivedon'))
      .values('month')
      .annotate(rejected_count=Count('id'))
      .order_by('month')
  )
  

2. Merge the Results #

Convert each queryset to a dictionary (using month as the key) then combine all metrics into a single list:

# Convert querysets to dictionaries for easy lookup
received_dict = {item['month']: item['received_count'] for item in received_stats}
published_dict = {item['month']: item['published_count'] for item in published_stats}
processing_dict = {item['month']: item['processing_count'] for item in processing_stats}
rejected_dict = {item['month']: item['rejected_count'] for item in rejected_stats}

# Get all unique months across all metrics
all_months = set(received_dict.keys()).union(published_dict.keys()).union(processing_dict.keys()).union(rejected_dict.keys())

# Build the combined stats list
combined_stats = []
for month in sorted(all_months):
    combined_stats.append({
        'month': month,
        'received_count': received_dict.get(month, 0),
        'published_count': published_dict.get(month, 0),
        'processing_count': processing_dict.get(month, 0),
        'rejected_count': rejected_dict.get(month, 0)
    })

Second Approach: Single Query with Conditional Aggregation #

For better performance (especially with large datasets), use Django's Case and When to calculate all metrics in one go:

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

combined_stats = (
    PreData.objects.filter(receivedon__isnull=False)
    .annotate(month=TruncMonth('receivedon'))
    .values('month')
    .annotate(
        # Received count is total records with non-null receivedon (we already filtered these)
        received_count=Count('id'),
        # Published count: only count records where publishedon is not null
        published_count=Count(
            Case(
                When(publishedon__isnull=False, then='id'),
                output_field=IntegerField()
            )
        ),
        # Processing count: filter by status
        processing_count=Count(
            Case(
                When(status='processing', then='id'),
                output_field=IntegerField()
            )
        ),
        # Rejected count: filter by status
        rejected_count=Count(
            Case(
                When(status='rejected', then='id'),
                output_field=IntegerField()
            )
        )
    )
    .order_by('month')
)

Key Notes: #

• Replace 'processing' and 'rejected' with the actual values stored in your status field (e.g., if you use Chinese like '处理中', swap those in).
• If you need to group processing/rejected counts by publishedon instead of receivedon, just change the TruncMonth argument to 'publishedon' (adjust the filter accordingly too).
• The single-query approach is faster because it hits the database once, while the first approach makes four separate queries.

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