使用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
应用程序中的模型。
如何计算行数
让我们计算一下我们有多少用户:
对行进行计数非常普遍,以至于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
翻译: 大江狗
相关阅读