Django models 详解之聚合查询(aggregate)与分组查询(annotate)

models.py 代码

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:

[
{
"id": 1,
"name": "路人甲",
"age": 10
},
{
"id": 2,
"name": "路人乙",
"age": 18
},
{
"id": 3,
"name": "路人丙",
"age": 28
},
{
"id": 4,
"name": "路人丁",
"age": 50
}
]

books:

[
{
"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
]
}
]

二、常用聚合操作

获取所有书籍的数量:

>>> Book.objects.count()
8

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

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

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

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

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

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

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

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

>>> 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年以前分界:

>>> 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本。以此类推。

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

>>> 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
中计算出一个汇总数据。如获取所有书籍的平均价格:

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

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

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

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

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

>> 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
模型中每一本书的作者的数量:

>>> 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()
中相同的用于指定关联字段的双下划线语法。

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

>>> 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)的书籍列表:

>>> 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)]>

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

>>> 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)]>
StarryLand
我还没有学会写个人说明!
上一篇

iPhone 12成为全球最受欢迎的5G手机 市场份额是三星同类竞品的四倍

下一篇

辉瑞新冠疫苗获得欧洲关键评审小组支持 或于周一正式在欧盟获批

你也可能喜欢

评论已经被关闭。

插入图片