聚合函数 取名： field + __ + 聚合函数名字 ，如：price__avg；可传关键字参数修改名字：avg=Avg("price")；
aggregate：不会返回一个 QuerySet 对象，而是返回一个字典，key为聚合函数的名字，value为聚合函数执行结果；
annotate：返回一个 QuerySet 对象；
相同点：
- 都可执行聚合函数；可在任何的‘QuerySet’对象上调用，因此只要返回了‘QuerySet’对象，即可进行链式调用，如 index5 中获取年度销售总额，可先过滤年份再求聚合函数；
不同点：
- aggregate 返回一个字典，字典中存储聚合函数执行的结果；而 annotate 返回一个 QuerySet对象 ，并在查找的模型上添加一个聚合函数的属性；
- aggregate 不会做分组，而 annotate 会使用 Group by 字句进行分组，只有调用该字句才能对每一条数据求聚合函数的值；
- 数据库中的内容：
-
models.py文件：
from django.db import models class Author(models.Model):
'''作者模型'''
name = models.CharField(max_length=100)
age = models.IntegerField()
email = models.EmailField() class Meta:
db_table = 'author' class Publisher(models.Model):
'''出版社模型'''
name = models.CharField(max_length=300) class Meta:
db_table = 'publisher' class Book(models.Model):
'''图书模型'''
name = models.CharField(max_length=300)
pages = models.IntegerField()
price = models.FloatField()
rating = models.FloatField()
author = models.ForeignKey(Author,on_delete=models.CASCADE)
publisher = models.ForeignKey(Publisher,on_delete=models.CASCADE) class Meta:
db_table = 'book' class Bookorder(models.Model):
'''图示订单模型'''
book = models.ForeignKey('Book',on_delete=models.CASCADE)
price = models.FloatField()
create_time = models.DateTimeField(auto_now_add=True,null=True) class Meta:
db_table = 'book_order'
-
views.py文件：
from django.shortcuts import render
from django.http import HttpResponse
from .models import Author,Publisher,Book,Bookorder
from django.db.models import Avg,Count,Max,Min,Sum
from django.db import connection def index(request):
# 获取所有图书定价的平均价（使用aggregate）；Avg：平均值；
result = Book.objects.aggregate(Avg('price'))
print(result)
print(connection.queries)
return HttpResponse('index')
# >>>{'price__avg': 97.25}
# >>>{'price__avg': 97.25}
# {'sql': 'SELECT AVG(`book`.`price`) AS `price__avg` FROM `book`'； def index2(requset):
# 获取每一本销售的平均价格（使用annotate）；
books = Book.objects.annotate(avg=Avg('bookorder__price'))
for book in books:
print("%s:%s" % (book.name,book.avg))
print(connection.queries)
return HttpResponse('index2')
#三国演义:89.33333333333333
# 水浒传:93.5
# 西游记:None
# 红楼梦:None
# `book_order` ON (`book`.`id` = `book_order`.`book_id`) GROUP BY `book`.`id` ORDER BY NULL'； def index3(request):
# book 表中总共有多少本书；
# Count：求某个数据的的个数；
result = Book.objects.aggregate(nums=Count('id'))
# author 表中总共有多少个不同的邮箱；
# 使用‘distinct=True’剔除重复的值；
results = Author.objects.aggregate(book_num=Count('email',distinct=True)) # 统计每本书的销量
books = Book.objects.annotate(book_nums=Count('bookorder'))
for book in books:
print("%s:%s" % (book.name,book.book_nums))
print(books)
print(connection.queries)
return HttpResponse("index3")
# 三国演义:3
# 水浒传:2
# 西游记:0
# 红楼梦:0 def index4(request):
result = Author.objects.aggregate(max=Max('age'),min=Min('age'))
# 每本图书售卖前的最大及最小价格；
books = Book.objects.annotate(max=Max("bookorder__price"),min=Min("bookorder__price"))
for book in books:
print("%s:%s:%s" % (book.name,book.max,book.min))
print(connection.queries)
return HttpResponse("index4") # 三国演义:95.0:85.0
# 水浒传:94.0:93.0
# 西游记:None:None
# 红楼梦:None:None def index5(request):
# 求所有图书的销售总额；
result = Book.objects.aggregate(total=Sum("price"))
print(result)
print(connection.queries)
# {'total': 389.0}
# {'sql': 'SELECT SUM(`book`.`price`) AS `total` FROM `book`', 'time': '0.001'}； # 求每一本图书的销售总额；
books = Book.objects.annotate(total=Sum('bookorder__price'))
for book in books:
print("%s:%s" % (book.name,book.total))
print(connection.queries)
# 三国演义: 268.0
# 水浒传: 187.0
# 西游记: None
# 红楼梦: None # 求2018年度的销售总额；
result = Bookorder.objects.filter(create_time__year=2019).aggregate(total=Sum("price"))
# 求2018年度每一本图书的销售总额；
books = Book.objects.filter(bookorder__create_time__year=2019).annotate(total=Sum("bookorder__price"))
for book in books:
print("%s:%s" % (book.name,book.total) )
print(connection.queries)
# 三国演义: 268.0
# 水浒传: 187.0
# `book_order`.`create_time` BETWEEN '2019-01-01 00:00:00' AND '2019-12-31 23:59:59.999999' GROUP BY `book`.`id`
-
F表达式：动态获取某个字段上的值，不会真正到数据库中查询数据，只起一个标识的作用；
def index6(requset):
# 给每本书售价增加10元；update;
Book.objects.update(price=F('price')+10)
print(connection.queries[-1])
# 'UPDATE `book` SET `price` = (`book`.`price` + 10)'; # Author中name与email相同的内容；
authors = Author.objects.filter(name=F('emile'))
for author in authors:
print("%s:%s" % (author.name,author.email))
return HttpResponse('index6') -
Q表达式：包裹查询条件，可在条件间进行多种操作：与 & 、或 | 、非 ~ 等查询操作；
def index7(requset):
# 1、获取价格大于100，评分大于4.5的图书；
# books = Book.objects.filter(price__gte=100,rating=4.5)
books = Book.objects.filter(Q(price__gte=100)&Q(rating__gte=4.5))
for book in books:
print("%s:%s:%s" % (book.name,book.price,book.rating)) --- # 2、获取价格低于100，或者评分低于4.5分的图书；
books = Book.objects.filter(Q(price__lt=100)|Q(rating__lt=4.5))
for book in books:
print("%s:%s:%s" % (book.name,book.price,book.rating)) --- # 3、获取价格大于100，并且图书名字不包含“传”字的图书；
books = Book.objects.filter(Q(price__gte=100)&~Q(name__icontains='传'))
for book in books:
print("%s:%s:%s" % (book.name,book.price,book.rating))
return HttpResponse('index7')