Django中用SQL实现分组查询

2020-03-02

作者：Haki Benita

翻译：老齐

与本文有关图书：《跟老齐学Python：Django实战（第二版）》

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

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

准备

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

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

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

统计行数

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

SELECT
    COUNT(*)
FROM
    auth_user;

1	User.objects.count()

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

如何使用`aggregate`函数

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

SELECT
    COUNT(id) AS id__count
FROM
    auth_user;

1
2
3

from django.db.models import Count

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

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

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

1
2
3

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

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

SELECT
    COUNT(id) as total
FROM
    auth_user;

1
2
3

>>> 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

1
2
3

(User.objects
.values('is_active')
.annotate(total=Count('id')))

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

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

1
2
3

(User.objects
.values('is_active', 'is_staff')
.annotate(total=Count('id')))

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

根据表达式分组

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

SELECT
    EXTRACT('year' FROM date_joined),
    COUNT(id) AS total
FROM
    auth_user
GROUP BY
    EXTRACT('year' FROM date_joined)

1
2
3

(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

1
2
3

(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

1
2
3

(User.objects
.annotate(memberships=Count('groups'))
.values('id', 'memberships'))

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

原文链接：https://hakibenita.com/django-group-by-sql

关注微信公众号：老齐教室。读深度文章，得精湛技艺，享绚丽人生。

← Python如何实现重载函数 Python3.8新语法：海象运算符 →

赏

使用支付宝打赏

使用微信打赏

若你觉得我的文章对你有帮助，欢迎点击上方按钮对我打赏

关注微信公众号，读文章、听课程，提升技能