[TOC]
聚合 Django 数据库抽象 API 描述了使用 Django queries 来增删查改单个对象的方法。 然而,有时候你要获取的值需要根据一组对象聚合后才能得到。
模型示例: 作者,出版社,书,商店。
from django.db import models class Author(models.Model): name = models.CharField(max_length=100) age = models.IntegerField() class Publisher(models.Model): name = models.CharField(max_length=300) class Book(models.Model): name = models.CharField(max_length=300) pages = models.IntegerField() price = models.DecimalField(max_digits=10, decimal_places=2) rating = models.FloatField() authors = models.ManyToManyField(Author) publisher = models.ForeignKey(Publisher, on_delete=models.CASCADE) pubdate = models.DateField() class Store(models.Model): name = models.CharField(max_length=300) books = models.ManyToManyField(Book) 常见的聚合查询 expressions output_field filter Avg Count Max Min Sum Variance: 返回给定表达式中数据的方差 StdDev: 返回给定表达式中数据的标准差 # Total number of books. >>> Book.objects.count() 2452 # Total number of books with publisher=BaloneyPress >>> Book.objects.filter(publisher__name='BaloneyPress').count() 73 # Average price across all books. >>> from django.db.models import Avg >>> Book.objects.all().aggregate(Avg('price')) {'price__avg': 34.35} # Max price across all books. >>> from django.db.models import Max >>> Book.objects.all().aggregate(Max('price')) {'price__max': Decimal('81.20')} # Difference between the highest priced book and the average price of all books. >>> from django.db.models import FloatField >>> Book.objects.aggregate( ... price_diff=Max('price', output_field=FloatField()) - Avg('price')) {'price_diff': 46.85} # All the following queries involve traversing the Book<->Publisher # foreign key relationship backwards. # Each publisher, each with a count of books as a "num_books" attribute. >>> from django.db.models import Count >>> pubs = Publisher.objects.annotate(num_books=Count('book')) >>> pubs <QuerySet [<Publisher: BaloneyPress>, <Publisher: SalamiPress>, ...]> >>> pubs[0].num_books 73 # Each publisher, with a separate count of books with a rating above and below 5 >>> from django.db.models import Q >>> above_5 = Count('book', filter=Q(book__rating__gt=5)) >>> below_5 = Count('book', filter=Q(book__rating__lte=5)) >>> pubs = Publisher.objects.annotate(below_5=below_5).annotate(above_5=above_5) >>> pubs[0].above_5 23 >>> pubs[0].below_5 12 # The top 5 publishers, in order by number of books. >>> pubs = Publisher.objects.annotate(num_books=Count('book')).order_by('-num_books')[:5] >>> pubs[0].num_books 1323 aggregate Django 提供了两种生成聚合的方法。第一种方法是从整个 QuerySet 生成汇总值。比如你想要计算所有在售书的平均价格。Django 的查询语法提供了一种用来描述所有图书集合的方法:
...