PostgreSQL服务端编程聚合和分组

在任何类型的ORM中，聚合(aggregation)都是造成混乱的根源，而Django也是如此。该文档提供了各种示例，演示了如何使用Django的ORM对数据进行分组(group)和聚合(aggregation)，但是我决定从另一个角度进行研究。

在本文中，我将QuerySet和SQL并排放置。如果您最喜欢SQL语言，那么这是适合您的Django GROUP BY速查表。

如何在Django中分组

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

如何计算行数

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

对行进行计数非常普遍，以至于Django在QuerySet上就为其包含了一个函数。与其他QuerySet不同，我们接下来将看到它count返回一个数字。

如何使用聚合函数

Django还提供了其它两种方法来对表中的行数。

我们将从从aggregate开始：

为了使用aggregate我们导入了聚合函数Count。在这种情况下，我们使用主键列的名称id来计数表中的所有行。

聚合返回的结果是一个字典，如下所示：

返回字典的默认键名key是id__count。最好不要依赖此命名约定，而是提供自己的名称：

如何分组

使用aggregate我们得到了将聚合函数(比如Count, Max, Sum)应用于整个表后的结果，这很有用，但是通常我们希望将表中的记录分成各个组(group)，然后在对每个组应用聚合函数。

现在让我们根据用户的活动状态分组， 再来统计每个组的人数：

这次我们使用了annotate。为了生产GROUP BY我们使用的组合values和annotate：

• values('is_active')：根据什么分组

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

返回结果是is_active的值以及每种状态的人数。

顺序很重要：在调用values方法之前使用annotate不会以注释形式添加聚合函数结果。

如何在分组聚合前过滤查询集

要将聚合函数应用于过滤后的查询集，可以在查询中的任何位置使用filter。例如，仅统计公司职员(staff)的活动状态计数：

如何在使用分组聚合前对查询集进行排序

像过滤器一样，可在查询语句中的任何位置使用order_by对查询集进行排序：

注意：你可以同时对表中本身的字段(is_active)和聚合后生成的字段(total)进行排序。

如何合并多个聚合函数结果

对同一组数据使用多个聚合函数，请一次添加多个注释：

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

如何按多个字段进行分组

就像执行多个聚合函数一样，我们可能还希望按表中多个字段进行分组。例如，按活动状态和人员状态分组：

该查询的结果包括和is_active，is_staff以及每个组中的用户数。

如何按表达式分组

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

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

有时内置表达式还不够，您需要汇总更复杂的表达式。例如，对注册后已登录过的用户进行分组：

这里的表达相当复杂。我们首先使用annotate来构建表达式，然后通过在以下对的调用中引用该表达式，将其标记为GROUP BY键values()。从这里开始，它是完全一样的。

如何使用条件聚合

使用条件聚合，您只能聚合组的一部分。当您有多个聚合时，条件会派上用场。例如，按签约年份计算职员和编外用户的数量：

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

如何使用Having对聚合结果进行过滤

该HAVING用于过滤聚合函数的结果，例如查找在哪些年份有100个以上的用户注册了：

如何按distinct分组

对于某些聚合函数，比如COUNT，有时希望仅对不同的事件进行计数。例如统计每个用户活动状态有多少个不同的姓氏：

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

聚合字段通常只是一个更大问题的第一步。例如按用户活动状态的唯一姓氏百分比是多少：

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

如何通过各种关系进行分组

到目前为止，我们仅在单个模型中使用了数据，但是聚合通常用于跨关系。更简单的方案是一对一或外键关系。例如，假设我们UserProfile与User之间具有一对一的关系，并且我们想按配置文件类型对用户进行计数：

就像GROUP BY表达式一样，在values中使用关系将按该字段分组。请注意，结果中的用户配置文件类型的名称将为“ user_profile__type”。

如何按多对多关系分组

一种更复杂的关系类型是多对多关系。例如计算每个用户是多少个组的成员：

一个用户可以是多个组的成员。为了计算用户所属的组数，我们在User模型中使用了相关的名称“组” 。如果未显式设置相关名称(related_name)，则Django将自动以format生成名称{related model model}_set。例如，group_set。

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

原作：KhakiBenita

翻译: 大江狗

相关阅读

Django QuerySet查询基础与技巧。有了她，再也不用担心SQL注入了。

Django基础(12): 深夜放干货。QuerySet特性及高级使用技巧，如何减少数据库的访问，节省内存，提升网站性能。

Django 与数据库交互，你需要知道的 9 个技巧

希望能早点了解的Django性能优化技巧

Django基础(24): aggregate和annotate方法使用详解与示例