vlambda博客
学习文章列表

使用SQL语言了解Django ORM中的分组(group by)和聚合(aggregation)查询

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


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

如何在Django中分组

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

使用SQL语言了解Django ORM中的分组(group by)和聚合(aggregation)查询


如何计算行数

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

使用SQL语言了解Django ORM中的分组(group by)和聚合(aggregation)查询

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


如何使用聚合函数

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


我们将从从aggregate开始:

使用SQL语言了解Django ORM中的分组(group by)和聚合(aggregation)查询

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


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

使用SQL语言了解Django ORM中的分组(group by)和聚合(aggregation)查询

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

使用SQL语言了解Django ORM中的分组(group by)和聚合(aggregation)查询


如何分组

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


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

使用SQL语言了解Django ORM中的分组(group by)和聚合(aggregation)查询

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

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

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


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


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


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

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

使用SQL语言了解Django ORM中的分组(group by)和聚合(aggregation)查询


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

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

使用SQL语言了解Django ORM中的分组(group by)和聚合(aggregation)查询

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


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

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

使用SQL语言了解Django ORM中的分组(group by)和聚合(aggregation)查询

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


如何按多个字段进行分组

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

使用SQL语言了解Django ORM中的分组(group by)和聚合(aggregation)查询

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


如何按表达式分组

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

使用SQL语言了解Django ORM中的分组(group by)和聚合(aggregation)查询


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


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

使用SQL语言了解Django ORM中的分组(group by)和聚合(aggregation)查询

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


如何使用条件聚合

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

使用SQL语言了解Django ORM中的分组(group by)和聚合(aggregation)查询

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


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

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

使用SQL语言了解Django ORM中的分组(group by)和聚合(aggregation)查询


如何按distinct分组

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

使用SQL语言了解Django ORM中的分组(group by)和聚合(aggregation)查询

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

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

使用SQL语言了解Django ORM中的分组(group by)和聚合(aggregation)查询

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


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

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

使用SQL语言了解Django ORM中的分组(group by)和聚合(aggregation)查询

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

翻译: 大江狗


相关阅读