测试4
测试大礼包
爬虫实战特训课

Django 中 ORM 的聚合函数

在介绍 Django 中 ORM 模型的聚合函数之前，我们先要了解下 MySQL 中常用的聚合函数。首先同样是准备数据，使用我i们之前在第 18 小节中完成的插入 100 条数据的代码，重新执行一次：

(django-manual) [root@server test]# python insert_records.py 
批量插入完成

此时，连同上次操作剩余的两条会员记录，数据库中总共有 102 条数据：

图片描述

数据库记录

1. MySQL 中的聚合操作

聚合函数（aggregation function）又称为组函数。默认情况下聚合函数会对当前所在表当做一个组进行统计。MySQL5.7 中支持的聚合函数如下：
图片描述

聚合函数

在这些聚合函数中，我们比较常用的有 AVG、COUNT、MAX、MIN、SUM 等。下面重点介绍这几个聚合函数：

  • AVG()：使用格式如下，函数返回expr的平均值，DISTINCT 则用于返回 expr 的不同值的平均值。如果没有匹配的行，AVG() 返回 NULL。

    AVG([DISTINCT] expr)

    例如，我们使用 AVG() 函数计算每个职业的会员的平均年龄，其 SQL 语句和执行结果如下：

图片描述

avg 聚合函数

  • COUNT()：使用格式如下，返回 SELECT 语句检索的行中 expr 的非NULL值的计数。返回结果是 BIGINT 值。如果没有匹配的行，count()返回0。

    COUNT(expr)

    注意： 我们常用的 COUNT(*)，其返回取回的记录数，无论它们是否包含 NULL 值。

    例如，这里我们计算出每个职业的会员数，其 SQL 语句和执行结果如下：
    图片描述

    count 函数

  • COUNT(DISTINCT …)：使用格式如下，该函数返回不相同且非 NULL 的 expr 值的行数。如果没有匹配的行，则 COUNT(DISTINCT) 返回0。

    COUNT(DISTINCT expr,[expr...])

  • GROUP_CONCAT()：使用格式如下，这个函数把来自同一个组的某一列（或者多列）的数据连接起来成为一个字符串。如果没有非 NULL 值，返回 NULL；

    GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

    例如，这里我们将每个职业的会员的年龄连接到一起，其 SQL 语句和执行结果如下：

    图片描述

    group_concat函数

  • SUM() / MAX() / MIN()：这几个聚合函数和 AVG() 函数用法几乎一致，计算某列的和/最大值/最小值，也可以使用 GROUP BY 分组计算：

    SELECT occupation, SUM(age) FROM member WHERE 1=1 GROUP BY occupation;
SELECT occupation, MAX(age) FROM member WHERE 1=1 GROUP BY occupation;
SELECT occupation, MIN(age) FROM member WHERE 1=1 GROUP BY occupation;

2. Django 内嵌 ORM 模型的聚合操作

在 Django 中聚合函数是通过 aggregate 方法实现的，aggregate 方法返回的结果是一个字典。其支持的聚合函数如下：

# 源码位置 django/db/models/aggregates.py
...
__all__ = [
    'Aggregate', 'Avg', 'Count', 'Max', 'Min', 'StdDev', 'Sum', 'Variance',
]
...

注意：第一个是基类，从 Avg 开始，是支持的聚合方法，每个聚合方法的处理对应着一个类，而这些类分别继承自 Aggregate 类。

aggregate 方法的使用也非常简单，只需要在该方法内添加需要执行的聚合函数即可，同时我们还可以打印出聚合函数执行的 SQL 语句，具体操作如下：

(django-manual) [root@server first_django_app]# python manage.py shell
Python 3.8.1 (default, Dec 24 2019, 17:04:00) 
[GCC 4.8.5 20150623 (Red Hat 4.8.5-39)] on linux
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> from hello_app.models import Member
>>> from django.db.models import Avg, Max, Min, Sum
>>> from django.db import connection
>>> Member.objects.all().aggregate(avg_age=Avg('age'),sum_age=Sum('age'), max_age=Max('age'), min_age=Min('age'))
{'avg_age': 29.392156862745097, 'sum_age': 2998.0, 'max_age': '40', 'min_age': '20'}
>>> print(connection.queries[-1]['sql'])
SELECT AVG(`member`.`age`) AS `avg_age`, SUM(`member`.`age`) AS `sum_age`, MAX(`member`.`age`) AS `max_age`, MIN(`member`.`age`) AS `min_age` FROM `member`

注意：connection.queries 中保存的是最近执行的 SQL 语句，我们在执行完 Django 的 ORM 操作后，可以取出最后一次执行的 SQL 语句进行查看。此外，对于聚合的函数，如果我们不知道属性名，则会有默认值：字段__聚合函数名。

>>> from django.db.models import Count
>>> Member.objects.all().aggregate(Count('age', distinct=True))
{'age__count': 21}
>>> print(connection.queries[-1]['sql'])
SELECT COUNT(DISTINCT `member`.`age`) AS `age__count` FROM `member`

相比前面在 MySQL 中执行聚合函数，我们这里缺少一个 GROUP BY 功能。如果想要对数据库中的记录先分组然后再进行某些聚合操作或排序时，需要使用 annotate 方法来实现。与 aggregate 方法不同的是，annotate 方法返回结果的不仅仅是含有统计结果的一个字典，而是包含有新增统计字段的查询集 (QuerySet)。下面是实现分组聚合的实例操作：

>>> from django.db.models import Count, Avg, Sum, Max, Min
>>> Member.objects.values('occupation').annotate(count=Count('age')).order_by('-count')
<QuerySet [{'occupation': 'security', 'count': 15}, {'occupation': 'ui', 'count': 15}, {'occupation': 'product', 'count': 14}, {'occupation': 'leader', 'count': 14}, {'occupation': 'ops', 'count': 14}, {'occupation': 'web', 'count': 12}, {'occupation': 'teacher', 'count': 8}, {'occupation': 'server', 'count': 8}, {'occupation': 'java', 'count': 1}, {'occupation': 'c/c++', 'count': 1}]>
>>> print(connection.queries[-1]['sql'])
SELECT `member`.`occupation`, COUNT(`member`.`age`) AS `count` FROM `member` GROUP BY `member`.`occupation` ORDER BY `count` DESC  LIMIT 21

注意：上面的操作有如下说明：

  • annotate 方法前面的 values 中出现的字段正是需要 GROUP BY 的字段。values 方法中出现多个值，即对多个字段进行 GROUP BY；
  • annotate 方法的结果是一个查询集 (QuerySet)，这样我们可以继续在后面盗用 filter()、order_by() 等方法进行进一步过滤结果；
  • order_by 方法是对前面的 QuerySet 按某些字段排序，类似于 SQL 中的 ORDER BY 操作。排序字段前面加上 “-” 表示按倒序顺序，类似于 DESC 操作

3. 小结

本小节中，我们介绍了 MySQL 中的常用的聚合操作，然后在介绍在 Django 中 ORM 模型对应的聚合函数。关于Django 的内置 ORM 模型的介绍到这里就结束了。接下来将介绍 Django 给我们提供的一个完整的后台管理系统功能-Admin 模块。