Django 中 ORM 的聚合函數(shù)

在介紹 Django 中 ORM 模型的聚合函數(shù)之前，我們先要了解下 MySQL 中常用的聚合函數(shù)。首先同樣是準(zhǔn)備數(shù)據(jù)，使用我i們之前在第 18 小節(jié)中完成的插入 100 條數(shù)據(jù)的代碼，重新執(zhí)行一次：

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

此時，連同上次操作剩余的兩條會員記錄，數(shù)據(jù)庫中總共有 102 條數(shù)據(jù)：

1. MySQL 中的聚合操作

聚合函數(shù)（aggregation function）又稱為組函數(shù)。默認(rèn)情況下聚合函數(shù)會對當(dāng)前所在表當(dāng)做一個組進(jìn)行統(tǒng)計。MySQL5.7 中支持的聚合函數(shù)如下：

在這些聚合函數(shù)中，我們比較常用的有 AVG、COUNT、MAX、MIN、SUM 等。下面重點介紹這幾個聚合函數(shù)：

• AVG()：使用格式如下，函數(shù)返回expr的平均值，DISTINCT 則用于返回 expr 的不同值的平均值。如果沒有匹配的行，AVG() 返回 NULL。

  AVG([DISTINCT] expr)
  

  例如，我們使用 AVG() 函數(shù)計算每個職業(yè)的會員的平均年齡，其 SQL 語句和執(zhí)行結(jié)果如下：

• COUNT()：使用格式如下，返回 SELECT 語句檢索的行中 expr 的非NULL值的計數(shù)。返回結(jié)果是 BIGINT 值。如果沒有匹配的行，count()返回0。

  COUNT(expr)
  

  注意： 我們常用的 COUNT(*)，其返回取回的記錄數(shù)，無論它們是否包含 NULL 值。

  例如，這里我們計算出每個職業(yè)的會員數(shù)，其 SQL 語句和執(zhí)行結(jié)果如下：
  

  count 函數(shù)

• COUNT(DISTINCT …)：使用格式如下，該函數(shù)返回不相同且非 NULL 的 expr 值的行數(shù)。如果沒有匹配的行，則 COUNT(DISTINCT) 返回0。

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

• GROUP_CONCAT()：使用格式如下，這個函數(shù)把來自同一個組的某一列（或者多列）的數(shù)據(jù)連接起來成為一個字符串。如果沒有非 NULL 值，返回 NULL；

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

  例如，這里我們將每個職業(yè)的會員的年齡連接到一起，其 SQL 語句和執(zhí)行結(jié)果如下：

  

  group_concat函數(shù)

• SUM() / MAX() / MIN()：這幾個聚合函數(shù)和 AVG() 函數(shù)用法幾乎一致，計算某列的和/最大值/最小值，也可以使用 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 內(nèi)嵌 ORM 模型的聚合操作

在 Django 中聚合函數(shù)是通過 aggregate 方法實現(xiàn)的，aggregate 方法返回的結(jié)果是一個字典。其支持的聚合函數(shù)如下：

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

注意：第一個是基類，從 Avg 開始，是支持的聚合方法，每個聚合方法的處理對應(yīng)著一個類，而這些類分別繼承自 Aggregate 類。

aggregate 方法的使用也非常簡單，只需要在該方法內(nèi)添加需要執(zhí)行的聚合函數(shù)即可，同時我們還可以打印出聚合函數(shù)執(zhí)行的 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 中保存的是最近執(zhí)行的 SQL 語句，我們在執(zhí)行完 Django 的 ORM 操作后，可以取出最后一次執(zhí)行的 SQL 語句進(jìn)行查看。此外，對于聚合的函數(shù)，如果我們不知道屬性名，則會有默認(rèn)值：字段__聚合函數(shù)名。

>>> 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 中執(zhí)行聚合函數(shù)，我們這里缺少一個 GROUP BY 功能。如果想要對數(shù)據(jù)庫中的記錄先分組然后再進(jìn)行某些聚合操作或排序時，需要使用 annotate 方法來實現(xiàn)。與 aggregate 方法不同的是，annotate 方法返回結(jié)果的不僅僅是含有統(tǒng)計結(jié)果的一個字典，而是包含有新增統(tǒng)計字段的查詢集 (QuerySet)。下面是實現(xiàn)分組聚合的實例操作：

>>> 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 中出現(xiàn)的字段正是需要 GROUP BY 的字段。values 方法中出現(xiàn)多個值，即對多個字段進(jìn)行 GROUP BY；
• annotate 方法的結(jié)果是一個查詢集 (QuerySet)，這樣我們可以繼續(xù)在后面盜用 filter()、order_by() 等方法進(jìn)行進(jìn)一步過濾結(jié)果；
• order_by 方法是對前面的 QuerySet 按某些字段排序，類似于 SQL 中的 ORDER BY 操作。排序字段前面加上 “-” 表示按倒序順序，類似于 DESC 操作

3. 小結(jié)

本小節(jié)中，我們介紹了 MySQL 中的常用的聚合操作，然后在介紹在 Django 中 ORM 模型對應(yīng)的聚合函數(shù)。關(guān)于Django 的內(nèi)置 ORM 模型的介紹到這里就結(jié)束了。接下來將介紹 Django 給我們提供的一個完整的后臺管理系統(tǒng)功能-Admin 模塊。