聚合在任何类型的ORM中都会引起一些乱七八糟的事情,Django也不例外。虽然在官方文档中已经对ORM中的分组和聚合做了说明,但我还是要从另一个角度来说明如何解决这个问题。

在本文中,我将QuerySets和SQL放在一起。如果SQL令你最舒服,那么这就是适合你的Django分组资料。

准备

为了演示不同的GROUP BY,我将使用Django内置的django.contrib.auth中的模型。

>>> from django.contrib.auth.models import User

Django ORM生成的SQL语句具有很长的别名。为了简洁起见,我将演示一种简化但等效的执行过程。

统计行数

让我们统计一下我们有多少用户:

SELECT
    COUNT(*)
FROM
    auth_user;
User.objects.count()

行的统计查询是如此常见,以至于Django的QuerySet包含了一个函数,与我们接下来将看到的其他QuerySets不同,count返回一个数字。

如何使用aggregate函数

Django还提供了另外两种统计查询方法,首先来看看aggregate

SELECT
    COUNT(id) AS id__count
FROM
    auth_user;
from django.db.models import Count

User.objects.aggregate(Count('id'))

为了使用aggregate,我们导入了Count函数,aggregate以另外一个实现统计查询的表达式为参数,在本例中,我们使用主键id来查询数据库表中的行的数量。

aggregate的结果是一个字典对象:

>>> from django.db.models import Count
>>> User.objects.aggregate(Count('id'))
{"id__count": 891}

键的名称是从字段的名称和查询函数的名称派生的,在本例中,键名是id__count。我们最好不要使用这样的命名方式,而是要自己设定名称:

SELECT
    COUNT(id) as total
FROM
    auth_user;
>>> from django.db.models import Count
>>> User.objects.aggregate(total=Count('id'))
{"total": 891}

aggregate参数的名称,就是返回值字典的键。

如何实现Group By

使用aggregate,我们得到数据表进行聚合查询结果,这很有用,但我们还希望对指定的行应用此操作。

让我们根据用户的活动状态来统计用户数:

SELECT
    is_active,
    COUNT(id) AS total
FROM
    auth_user
GROUP BY
    is_active
(User.objects
.values('is_active')
.annotate(total=Count('id')))

这次使用了annotate函数。我们使用valuesannotate的组合来完成分组查询:

  • values('is_active'):分组依据
  • annotate(total=Count('id')):要查询的内容

顺序很重要:如果在annotate之前调用values失败,不会产生查询结果。

aggregate一样,annotate的参数名称是QuerySet返回值的键,示例中就是total

分组筛选

若要对筛选查询应用聚合功能,可以在查询的任何位置使用filter,例如,仅按员工用户的活动状态对其进行统计:

ELECT
    is_active,
    COUNT(id) AS total
FROM
    auth_user
WHERE
    is_staff = True
GROUP BY
    is_active
(User.objects
.values('is_active')
.filter(is_staff=True)
.annotate(total=Count('id')))

如何进行分组排序

filter类似,要对分组结果排序,可以在查询中使用order_by

SELECT
    is_active,
    COUNT(id) AS total
FROM
    auth_user
GROUP BY
    is_active
ORDER BY
    is_active,
    total
(User.objects
.values('is_active')
.annotate(total=Count('id'))
.order_by('is_staff', 'total'))

请注意:你可以按分组的关键词is_active和聚合的关键词total进行排序。

如何联合聚合查询

要生成同分组的多个聚合查询,请添加多个annotation:

SELECT
    is_active,
    COUNT(id) AS total,
    MAX(date_joined) AS last_joined
FROM
    auth_user
GROUP BY
    is_active
from django.db.models import Max

(User.objects
.values('is_active')
.annotate(
    total=Count('id'),
    last_joined=Max('date_joined'),
))

该查询将得到活动用户和非活动用户的数量,以及用户加入每个组的最后日期。

根据多个字段进行分组

就像执行多个聚合一样,我们可能也希望根据多个字段进行分组。例如,按活动状态和人员状态分组:

SELECT
    is_active,
    is_staff,
    COUNT(id) AS total
FROM
    auth_user
GROUP BY
    is_active,
    is_staff
(User.objects
.values('is_active', 'is_staff')
.annotate(total=Count('id')))

此查询的结果包括is_activeis_staff和每个组中的用户数。

根据表达式分组

分组的另一个常见用例是按表达式分组。例如,统计每年加入的用户数:

SELECT
    EXTRACT('year' FROM date_joined),
    COUNT(id) AS total
FROM
    auth_user
GROUP BY
    EXTRACT('year' FROM date_joined)
(User.objects
.values('date_joined__year')
.annotate(total=Count('id')))

注意,为了从日期开始获取年份,我们在第一次调用values()时使用了特殊表达式<field>__year。查询的结果是一个dict,键的名称将是date_joined__year

有时,内置表达式不够,需要在更复杂的表达式上进行聚合。例如,按注册后登录的用户分组:

SELECT
  last_login > date_joined AS logged_since_joined,
  COUNT(id) AS total
FROM
  auth_user
GROUP BY
  last_login > date_joined
from django.db.models import (
    ExpressionWrapper,
    Q, F, BooleanField,
)

(User.objects
.annotate(
    logged_since_joined=ExpressionWrapper(
        Q(last_login__gt=F('date_joined')),
        output_field=BooleanField(),
    )
)
.values('logged_since_joined')
.annotate(total=Count('id'))
.values('logged_since_joined', 'total')

这里的表达式相当复杂。我们首先使用annotate构建表达式,然后在下面对values()的调用中通过引用表达式将其标记为按该关键词分组。后面的代码就跟前述一样了。

根据条件聚合

根据条件,只能对组的一部分进行聚合。当有多个聚合时,条件就很有用了。例如,按注册年份统计员工用户和非员工用户的数量:

SELECT
    EXTRACT('year' FROM date_joined),

    COUNT(id) FILTER (
        WHERE is_staff = True
    ) AS staff_users,

    COUNT(id) FILTER (
        WHERE is_staff = False
    ) AS non_staff_users

FROM
    auth_user
GROUP BY
    EXTRACT('year' FROM date_joined)
from django.db.models import F, Q

(User.objects
.values('date_joined__year')
.annotate(
    staff_users=(
        Count('id', filter=Q(is_staff=True))
    ),
    non_staff_users=(
        Count('id', filter=Q(is_staff=False))
    ),
))

上面的SQL来自PostgreSQL,它和SQLite是目前唯一支持FILTER语法快捷方式(正式名称为“选择性聚合”)的数据库。对于其他数据库,ORM将使用CASE ... WHEN来代替。

如何使用Having

HAVING子句用于筛选聚合函数的结果。例如,查找超过100多个用户加入的年份:

SELECT
    is_active,
    COUNT(id) AS total
FROM
    auth_user
GROUP BY
    is_active
HAVING
    COUNT(id) > 100
(User.objects
.annotate(year_joined=F('date_joined__year'))
.values('is_active')
.annotate(total=Count('id'))
.filter(total__gt=100))

annotate中的total查询结果进行过滤,即后面的filter,它与SQL中的HAVING子句等效。

根据Distinct分组

对于某些聚合函数(如“COUNT”),有时只需要计算不同的出现次数。例如,每一种活动状态中的用户有多少不同的姓氏:

SELECT
    is_active,
    COUNT(id) AS total,
    COUNT(DISTINCT last_name) AS unique_names
FROM
    auth_user
GROUP BY
    is_active
(User.objects
.values('is_active')
.annotate(
    total=Count('id'),
    unique_names=Count('last_name', distinct=True),
))

注意在Count的参数中使用了distinct=True

使用聚合字段创建表达式

聚合字段通常只是解决较大问题的第一步。例如,按用户活动状态列出的唯一姓氏的百分比是多少:

SELECT
    is_active,
    COUNT(id) AS total,
    COUNT(DISTINCT last_name) AS unique_names,
    (COUNT(DISTINCT last_name)::float
        / COUNT(id)::float) AS pct_unique_names
FROM
    auth_user
GROUP BY
    is_active
from django.db.models import FloatField
from django.db.models.functions import Cast

(User.objects
.values('is_active')
.annotate(
    total=Count('id'),
    unique_names=Count('last_name', distinct=True),
)
.annotate(pct_unique_names=(
    Cast('unique_names', FloatField())
    / Cast('total', FloatField())
))

第一个annotate()定义聚合字段。第二个annotate()使用聚合函数构造表达式。

跨表分组

到目前为止,我们只是在一个模型中进行各种数据查询操作,但聚合也能在不同模型(即不同数据库表)之间实现,比较简单的情况是一对一或外键关系。例如,假设我们有一个与用户一一对应的User profile模型,并且我们希望按配置文件的类型统计用户:

SELECT
    p.type,
    COUNT(u.id) AS total
FROM
    auth_user u
    JOIN user_profile p ON u.id = p.user_id
GROUP BY
    p.type
(User.objects
.values('user_profile__type')
.annotate(total=Count('id')))')))

就像分组表达式一样,在values中使用关联表,并按该该字段分组。请注意:表示关联数据库包的名称将是user_profile__type

根据多对多关系分组

更复杂的还是多对多的关系。例如,计算每个用户参与了多少个小组:

SELECT
    u.id,
    COUNT(ug.group_id) AS memberships
FROM
    auth_user
    LEFT OUTER JOIN auth_user_groups ug ON (
        u.id = ug.user_id
    )
GROUP BY
    u.id
(User.objects
.annotate(memberships=Count('groups'))
.values('id', 'memberships'))

用户可以是多个组的成员,要统计用户所属的组数,我们在User模型中使用了相关名称groups。如果未显式设置相关名称(且未显式禁用),Django将自动生成格式为{related model model}_set的名称。例如group_set