PostgreSQL服务端编程聚合和分组

举报
xcc-2022 发表于 2022/07/04 21:06:47 2022/07/04
【摘要】 在任何类型的ORM中,聚合(aggregation)都是造成混乱的根源,而Django也是如此。该文档提供了各种示例,演示了如何使用Django的ORM对数据进行分组(group)和聚合(aggregation),但是我决定从另一个角度进行研究。在本文中,我将QuerySet和SQL并排放置。如果您最喜欢SQL语言,那么这是适合您的Django GROUP BY速查表。如何在Django中分...

在任何类型的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我们使用的组合valuesannotate

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

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

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

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

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

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

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

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

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

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

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

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

如何按多个字段进行分组

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

该查询的结果包括和is_activeis_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方法使用详解与示例

【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

举报
请填写举报理由
0/200