1. 聚合与注解
聚合(aggregate)比较好理解,注解(annotate)真不好理解,这篇示例参考了文章“django中聚合aggregate和annotate GROUP BY的使用方法”提供的模型,以及部分内容。根据参考的文章,注解的理解是:在聚合的基础上使用了GROUP BY语句。
2. 模型代码
from django.db import models class Author(models.Model): name = models.CharField(max_length=100) age = models.IntegerField() class Meta: db_table = 'author' class Publisher(models.Model): name = models.CharField(max_length=300) num_awards = models.IntegerField() class Meta: db_table = 'publisher' 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 Meta: db_table = 'book' class Store(models.Model): name = models.CharField(max_length=300) books = models.ManyToManyField(Book) registered_users = models.PositiveIntegerField() class Meta: db_table = 'store'
3. aggregate
# 需要导入的模块 >>> from django.db.models import Count, Avg, Sum, F, FloatField >>> from django.db import connections >>> from books.models import * #1. 聚合返回的是字典值 统计计数 >>> Publisher.objects.aggregate(num_books=Count('book')) >>> connections['default'].queries SELECT COUNT(`book`.`id`) AS `num_books` FROM `publisher` LEFT OUTER JOIN `book` ON (`publisher`.`id` = `book`.`publisher_id`); 求平均 >>> Book.objects.all().aggregate(Avg('price')) >>> Book.objects.aggregate(Avg('price')) SELECT AVG(`book`.`price`) AS `price__avg` FROM `book`;
求每页价格
>>> Book.objects.aggregate( price_per_page=Sum(F('price')/F('pages'), output_field=FloatField()) )
SELECT
SUM((`book`.`price` / `book`.`pages)) AS `price_per_page`
FROM
`book`;
4. annotate
对数据进行分组后,再聚合,使用annotate
# 导入涉及的模块 from django.db.models import Count, Sum, FloatField from django.db import connections from books.models import * # annotate是分组、聚合的含义,annotate返回的是新的queryset # aggregate则是对全表进行聚合的含义,没有分组的概念,返回的是字典
>>> Book.objects.annotate(Count('authors')) # authors是Book中的属性
SELECT
`book`.`id`, `book`.`name`, `book`.`pages`, `book`.`price`,
`book`.`rating`, `book`.`publisher_id`, `book`.`pubdate`,
COUNT(`book_authors`.`author_id`) AS `authors__count`
FROM
`book`
LEFT OUTER JOIN
`book_authors`
ON
(`book`.`id` = `book_authors`.`book_id`)
GROUP BY
`book`.`id`
ORDER BY
NULL;