Django models 详解之聚合查询（aggregate）与分组查询（annotate）

发表于 | 分类于 | | 阅读次数:
字数统计: 7.6k | 阅读时长 ≈ 0:08

一、测试代码及数据

models.py 代码

1
2
3
4
5
6
7
8
9
10
11
12
13
from django.db import models


class Author(models.Model):
    name = models.CharField(max_length=100)
    age = models.IntegerField()


class Book(models.Model):
    name = models.CharField(max_length=300)
    price = models.DecimalField(max_digits=10, decimal_places=2)
    authors = models.ManyToManyField(Author)
    pubdate = models.DateField()

测试数据

authors：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
[
    {
        "id": 1,
        "name": "路人甲",
        "age": 10
    },
    {
        "id": 2,
        "name": "路人乙",
        "age": 18
    },
    {
        "id": 3,
        "name": "路人丙",
        "age": 28
    },
    {
        "id": 4,
        "name": "路人丁",
        "age": 50
    }
]

books：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
[
    {
        "id": 1,
        "name": "人之初",
        "price": "38.80",
        "pubdate": "2020-12-01",
        "authors": [
            1
        ]
    },
    {
        "id": 2,
        "name": "性本善",
        "price": "28.40",
        "pubdate": "2020-06-01",
        "authors": [
            2
        ]
    },
    {
        "id": 3,
        "name": "性相近",
        "price": "15.20",
        "pubdate": "2019-10-01",
        "authors": [
            3
        ]
    },
    {
        "id": 4,
        "name": "习相远",
        "price": "35.20",
        "pubdate": "2019-07-01",
        "authors": [
            4
        ]
    },
    {
        "id": 5,
        "name": "苟不教",
        "price": "5.20",
        "pubdate": "2018-07-01",
        "authors": [
            1,
            3,
            4
        ]
    },
    {
        "id": 6,
        "name": "性乃迁",
        "price": "55.20",
        "pubdate": "2018-12-01",
        "authors": [
            2,
            3,
            4
        ]
    },
    {
        "id": 7,
        "name": "教之道",
        "price": "33.20",
        "pubdate": "2018-12-23",
        "authors": [
            2,
            3
        ]
    },
    {
        "id": 8,
        "name": "贵以专",
        "price": "27.20",
        "pubdate": "2017-12-23",
        "authors": [
            1,
            4
        ]
    }
]

二、常用聚合操作

获取所有书籍的数量：

1
2
>>> Book.objects.count()
8

获取由路人甲参与著作的所有书籍的数量：

1
2
>>> Book.objects.filter(authors__name__contains='路人甲').count()
3

获取所有书籍的平均价格：

1
2
>>> Book.objects.all().aggregate(Avg('price'))
{'price__avg': Decimal('29.800000')}

获取所有书籍中的最高价格：

1
2
>>> Book.objects.all().aggregate(Max('price'))
{'price__max': Decimal('55.20')}

涉及到一对多或多对多关系的聚合查询

计算每一位作者各自参与写作了多少本书：

1
2
3
4
5
6
7
8
>>> from django.db.models import Count
>>> authors=Author.objects.annotate(num_books=Count('book'))
>>> authors
<QuerySet [<Author: Author object (1)>, <Author: Author object (2)>, <Author: Author object (3)>, <Author: Author object (4)>]>
>>> authors[0].num_books
3
>>> authors.values_list('name', 'num_books')
<QuerySet [('路人甲', 3), ('路人乙', 3), ('路人丙', 4), ('路人丁', 4)]>

即作者包含路人甲的书籍有3本，以此类推。

计算每一位作者各自参与写作的书籍数量，根据书籍出版年份是否在2020年以前分界：

1
2
3
4
5
6
7
8
9
10
>>> from django.db.models import Q
>>> before_2020 = Count('book', filter=Q(book__pubdate__lt='2020-01-01'))
>>> after_2020 = Count('book', filter=Q(book__pubdate__gt='2020-01-01'))
>>> authors = Author.objects.annotate(before_2020=before_2020).annotate(after_2020=after_2020)
>>> authors
<QuerySet [<Author: Author object (1)>, <Author: Author object (2)>, <Author: Author object (3)>, <Author: Author object (4)>]>
>>> authors[0].before_2020
2
>>> authors.values_list('name', 'before_2020', 'after_2020')
<QuerySet [('路人甲', 2, 1), ('路人乙', 2, 1), ('路人丙', 4, 0), ('路人丁', 4, 0)]>

即作者包含路人甲的书籍，2020年以前出版的有2本，2020年以后出版的有1本。以此类推。

获取每一位作者各自参与著作的书籍数量，将输出结果按书籍数量由大到小的顺序排序：

1
2
3
4
5
>>> authors = Author.objects.annotate(num_books=Count('book')).order_by('-num_books')
>>> authors
<QuerySet [<Author: Author object (3)>, <Author: Author object (4)>, <Author: Author object (1)>, <Author: Author object (2)>]>
>>> authors.values_list('name', 'num_books')
<QuerySet [('路人丙', 4), ('路人丁', 4), ('路人甲', 3), ('路人乙', 3)]>

三、aggregate

在聚合查询中，Django 支持通过 aggregate() 方法从整个 QuerySet 中计算出一个汇总数据。如获取所有书籍的平均价格：

1
2
3
>>> from django.db.models import Avg
>>> Book.objects.all().aggregate(Avg('price'))
{'price__avg': Decimal('29.800000')}

上述语句中的 all() 可以省略。aggregate() 的参数表示我们想要做聚合计算的那一列数据，其中的 'price' 即表示 Book 模型的 price 字段。

aggregate() 对于 QuerySet 来说是一种终止语句，会返回字典形式的键值对作为计算结果。其中的键会根据聚合的字段自动生成，也可以手动指定：

1
2
>>> Book.objects.all().aggregate(average_price=Avg('price'))
{'average_price': Decimal('29.800000')}

如果想要同时完成多个聚合查询操作，可以为 aggregate() 添加多个参数：

1
2
3
>> from django.db.models import Avg, Max, Min
>>> Book.objects.aggregate(Avg('price'), Max('price'), Min('price'))
{'price__avg': Decimal('29.800000'), 'price__max': Decimal('55.20'), 'price__min': Decimal('5.20')}

四、annotate

借助 annotate() 方法，Django 可以从 QuerySet 的每一个对象中计算出对应的独立的汇总数据。比如想获得 Book 模型中每一本书的作者的数量：

1
2
3
4
5
6
7
8
>>> from django.db.models import Count
>>> q = Book.objects.annotate(num_authors=Count('authors'))
>>> q
<QuerySet [<Book: Book object (1)>, <Book: Book object (2)>, <Book: Book object (3)>, <Book: Book object (4)>, <Book: Book object (5)>, <Book: Book object (6)>, <Book: Book object (7)>, <Book: Book object (8)>]>
>>> q[0].num_authors
1
>>> q.values_list('name', 'num_authors')
<QuerySet [('人之初', 1), ('性本善', 1), ('性相近', 1), ('习相远', 1), ('苟不教', 3), ('性乃迁', 3), ('教之道', 2), ('贵以专', 2)]>

不同于 aggregate()，annotate() 对于 QuerySet 来说并不是终止语句，annotate() 方法的输出结果仍是 QuerySet 对象。该对象可以继续执行被 QuerySet 支持的任意操作，如 filter()、order_by() 等，甚至另一个 annotate()。

五、join & aggregate

某些情况下，你想要聚合的字段并不属于当前正在查询的模型，而是属于关联于当前模型的另一个模型。在对这些字段进行聚合查询时，Django 允许使用与 filter() 中相同的用于指定关联字段的双下划线语法。

比如想要获取每一位作者所著书籍的价格区间：

1
2
3
4
>>> from django.db.models import Max, Min
>>> authors = Author.objects.annotate(min_price=Min('book__price'), max_price=Max('book__price'))
>>> authors.values_list('name', 'min_price', 'max_price')
<QuerySet [('路人甲', Decimal('5.20'), Decimal('38.80')), ('路人乙', Decimal('28.40'), Decimal('55.20')), ('路人丙', Decimal('5.20'), Decimal('55.20')), ('路人丁', Decimal('5.20'), Decimal('55.20'))]>

即作者为路人甲的书籍中，最低的价格为 5.20，最高的价格为 38.80。

六、filter() 或 order_by() 应用到 annotate()

如查找所有多人合著（作者数量大于 1）的书籍列表：

1
2
3
4
5
>>> books = Book.objects.annotate(num_authors=Count('authors')).filter(num_authors__gt=1)
>>> books
<QuerySet [<Book: Book object (5)>, <Book: Book object (6)>, <Book: Book object (7)>, <Book: Book object (8)>]>
>>> books.values_list('name', 'num_authors')
<QuerySet [('苟不教', 3), ('性乃迁', 3), ('教之道', 2), ('贵以专', 2)]>

根据作者数量对全部书籍进行排序：

1
2
3
4
5
>>> books = Book.objects.annotate(num_authors=Count('authors')).order_by('num_authors')
>>> books
<QuerySet [<Book: Book object (2)>, <Book: Book object (4)>, <Book: Book object (1)>, <Book: Book object (3)>, <Book: Book object (8)>, <Book: Book object (7)>, <Book: Book object (5)>, <Book: Book object (6)>]>
>>> books.values_list('name', 'num_authors')
<QuerySet [('性本善', 1), ('习相远', 1), ('人之初', 1), ('性相近', 1), ('教之道', 2), ('贵以专', 2), ('苟不教', 3), ('性乃迁', 3)]>

参考资料

Django 官方文档 —— Aggregation