过滤聚合后的数据：条件聚合，可以实现分类统计

Conditional aggregation¶

What if we want to find out how many clients there are for each account_type? We can use the filter argument of aggregate functions to achieve this:

>>> # Create some more Clients first so we can have something to count
>>> Client.objects.create(
...     name='Jean Grey',
...     account_type=Client.REGULAR,
...     registered_on=date.today())
>>> Client.objects.create(
...     name='James Bond',
...     account_type=Client.PLATINUM,
...     registered_on=date.today())
>>> Client.objects.create(
...     name='Jane Porter',
...     account_type=Client.PLATINUM,
...     registered_on=date.today())
>>> # Get counts for each value of account_type
>>> from django.db.models import Count
>>> Client.objects.aggregate(
...     regular=Count('pk', filter=Q(account_type=Client.REGULAR)),
...     gold=Count('pk', filter=Q(account_type=Client.GOLD)),
...     platinum=Count('pk', filter=Q(account_type=Client.PLATINUM)),
... )
{'regular': 2, 'gold': 1, 'platinum': 3}

This aggregate produces a query with the SQL 2003 FILTER WHERE syntax on databases that support it:

SELECT count('id') FILTER (WHERE account_type=1) as regular,
       count('id') FILTER (WHERE account_type=2) as gold,
       count('id') FILTER (WHERE account_type=3) as platinum
FROM clients;

On other databases, this is emulated using a CASE statement:

SELECT count(CASE WHEN account_type=1 THEN id ELSE null) as regular,
       count(CASE WHEN account_type=2 THEN id ELSE null) as gold,
       count(CASE WHEN account_type=3 THEN id ELSE null) as platinum
FROM clients;

The two SQL statements are functionally equivalent but the more explicit FILTER may perform better.