使用SQL理解Django中的Group By

聚合在任何类型的ORM中都是混乱的根源，Django也不例外。其文档提供了各种示例和备忘表，演示了如何使用ORM对数据进行分组和聚合，但我决定从另一个角度来处理这个问题。

在本文中，我将查询集和SQL并排放在一起。如果你更熟悉SQL，那么这就是你的Django GROUP BY速查表。

如何在Django中进行分组

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

Django ORM会生成带有长别名的SQL语句。为了简单起见，我将展示一个Django所执行操作的清理后的、但等效的版本。

SQL 日志

要查看Django实际执行SQL的过程，你可以在Django设置中打开SQL日志。

如何计数行

我们来看看我们有多少用户:

计数行是如此常见，以至于Django在QuerySet上为它提供了一个函数。与我们接下来将看到的其他QuerySet不同，count会返回一个数字。

如何使用聚合函数

Django还提供了另外两种计数表中行数的方法。

我们将从aggregate开始:

为了使用aggregate，我们导入了聚合函数Count。该函数接受一个表达式进行计数。在本例中，我们使用主键列id的名称来计数表中的所有行。

聚合NULL

聚合会忽略NULL值。有关聚合如何处理NULL的更多信息，请参见《SQL中12个常见错误和容易错过的优化机会》一文。

aggregate的结果是一个字典:

键的名称派生自字段名称和聚合名称。在本例中，它是id_count。最好不要依赖这种命名约定，而是提供你自己的名称:

要aggregate的参数的名称也就是生成的字典中的键的名称。

如何按字段分组

使用aggregate，我们得到了对整个表应用聚合函数后的结果。这很有用，但通常我们希望对行的分组应用聚合过程。

让我们通过用户的活动状态来对它们进行计数:

这次我们使用了函数annotate。我们使用values和annotate的一个组合去生成一个GROUP BY :

values('is_active'): 对什么进行按字段分组

annotate(total=Count('id')): 对什么进行聚合

顺序很重要:在annotate之前对values的调用失败的话将不会产生聚合结果。

与aggregate一样，要annotate的参数的名称是经过计算的的QuerySet结果中的键。在本例中是total。

如何使用Group By筛选一个QuerySet

要对一个筛选后的查询应用聚合，你可以在查询中的任何位置使用filter。例如，仅根据员工用户的活动状态来对他们进行计数:

如何使用Group By对一个QuerySet进行排序

就像筛选器一样，你可以在查询的任何地方使用order_by来对一个查询集进行排序:

注意，你可以按照Group By键和聚合字段对该查询集进行排序。

如何合并多个聚合

要生成同一个分组的多个聚合，请添加多个注解:

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

如何按多个字段分组

就像执行多个聚合一样，我们可能也想要按照多个字段进行分组。例如，按活动状态和员工状态进行分组:

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

如何按一个表达式分组

GROUP BY的另一个常见用例是按照一个表达式进行分组。例如，计数每年加入的用户数:

请注意，为了获取数据的年份，我们在第一次values()调用中使用了特别的表达式__year。此查询的结果是一个字典，该字典的键名称将是date_joined__year。

有时，内置表达式还不够，你需要聚合一个更复杂的表达式。例如，按注册后已经登录的用户进行分组:

这里的表达式相当复杂。我们首先使用annotate来构建这个表达式，并通过在接下来的values()调用中引用该表达式来将其标记为一个GROUP BY键。从这里开始，一切都是一样的了。

如何使用条件聚合

使用条件聚合，你只能聚合分组的一部分。当你有多个聚合时，条件就派上用场了。例如，通过注册的年份来计数员工和非员工用户:

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

提示

我之前写过关于聚合和筛选器的文章。请参阅我的《处理数据库的9个Django提示》

如何使用Having

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

注解后的字段total上的filter在生成的SQL中添加了一个HAVING子句。

如何按照不同之处分组

对于一些聚合函数，比如COUNT，有时最好是只计数不同的出现特征。例如，每个用户的活动状态有多少个不同的姓氏:

请注意Count调用中distinct=True的使用。

如何使用聚合字段创建表达式

聚合字段通常只是一个更大问题的第一步。例如，不同的姓氏除以用户活动状态的百分比是多少:

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

如何跨关系分组

到目前为止，我们只在单个模型中使用了数据，但是聚合通常是跨关系使用的。比较简单一点的场景是一对一或外键关系。例如，假设我们有一个UserProfile，它与用户是一对一的关系，我们想要根据profile的类型来计数用户数量:

就像GROUP BY表达式一样，在值中使用关系将会根据该关系字段进行分组。请注意，结果中的用户profile类型名称将会是“user_profile_ type”。

如何按多对多关系分组

一个更复杂的关系类型是多对多关系。例如，计数每个用户是多少个分组的成员:

一个用户可以是多个分组的成员。为了计数该用户所属分组的数量，我们在User模型中使用了相关的名称“groups”。如果我们没有显式地设置相关名称(也没有显式地禁用)，Django将会自动以_set的格式生成一个名称。例如,group_set。