Django中Distinct命令及四类账户统计场景的ORM查询实现

Hey there! Let's break down how distinct() works in Django's ORM first, then jump into those four account counting scenarios you need.

First, a quick primer: distinct() removes duplicate rows from your queryset. By default, it checks all fields to determine if a row is unique, but you can also pass specific field names to narrow down what counts as a duplicate (heads up: this field-specific distinct() is a PostgreSQL-only feature—other databases like MySQL don't support it).

Now let's get to your use cases:

1. Count child accounts (account_child_id) #

There are two ways to interpret this, so I'll cover both:

• If you want the total number of child account entries (including duplicates if the same child account appears multiple times):

  total_child_account_entries = Account.objects.count()
  

• If you want the number of unique child accounts (only count each account_child_id once):

  unique_child_accounts = Account.objects.values('account_child_id').distinct().count()
  

The values('account_child_id') tells Django to only consider that field when checking for duplicates, then distinct() removes repeats, and count() gives you the final number.

2. Count accounts where stage='Won' #

This is a filtered count—simple, but again, two flavors depending on whether you need duplicates:

• Count all 'Won' entries (even if the same account is marked 'Won' multiple times):

  total_won_entries = Account.objects.filter(stage='Won').count()
  

• Count unique accounts that have a 'Won' stage (each account only once, even if they have multiple 'Won' entries):

  unique_won_accounts = Account.objects.filter(stage='Won').values('account_id').distinct().count()
  

Replace account_id with your actual unique identifier field for accounts if it's named differently.

3. Count distinct accounts where potential='HP' #

Here we want to count each unique account exactly once, even if they show up multiple times with potential='HP':

• Cross-database compatible version (works with MySQL, PostgreSQL, etc.):

  hp_potential_unique_accounts = Account.objects.filter(potential='HP').values('account_id').distinct().count()
  

• PostgreSQL-only shortcut (cleaner syntax):

  hp_potential_unique_accounts = Account.objects.filter(potential='HP').distinct('account_id').count()
  

The PostgreSQL version lets you pass the field directly to distinct() instead of using values(), which is a bit more readable.

4. Count distinct accounts where pipeline='HP' #

This is identical to scenario 3, just filtering on pipeline instead of potential:

• Cross-database compatible:

  hp_pipeline_unique_accounts = Account.objects.filter(pipeline='HP').values('account_id').distinct().count()
  

• PostgreSQL-only:

  hp_pipeline_unique_accounts = Account.objects.filter(pipeline='HP').distinct('account_id').count()
  

Again, make sure account_id is the correct unique field for your Account model.

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