python聚合函数详解_75.Python中ORM聚合函数详解：Sum

Sum：某个字段的总和。

1. 求图书的销售总额，示例代码如下：

from django.http import HttpResponse

from django.db import connections

from .models import BookOrder, Book

from django.db.models import Sum

def index(request):

# 1.求图书的销售总额

sum = BookOrder.objects.aggregate(sum=Sum('price'))

print(sum)

# {'sum': 622.0}

print(connection.queries)

# [{'sql': 'SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED', 'time': '0.000'},

# {'sql': 'SELECT SUM(`book_order`.`price`) AS `sum` FROM `book_order`', 'time': '0.000'}]

return HttpResponse("success !")

其中，models.py文件中的模型定义为：

from django.db import models

# 定义图书模型

class Book(models.Model):

name = models.CharField(max_length=100, unique=True)

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'

def __str__(self):

return "(书名：%s,页数：%s,价格：%s,打折：%s,作者：%s,出版社：%s)" % (self.name, self.pages, self.price, self.rating, self.author, self.publisher)

# 定义预定图书的模型

class BookOrder(models.Model):

book = models.ForeignKey('Book', on_delete=models.CASCADE)

price = models.FloatField()

time = models.DateTimeField(auto_now_add=True, null=True)

class Meta:

db_table = 'book_order'

定义完成模型之后，在终端命令行窗口，执行命令：python manage.py makemigrations, 生成迁移脚本文件，python manage.py migrate.将生成的迁移脚本文件映射到数据库中。

mysql数据库book_order表中数据为：

mysql数据库中book表中的数据为：

2.求每一种书的销售总额，示例代码如下：

def index(request):

# 2.求每一本图书的销售总额

# 将每一本书使用annotate()方法进行分组，并且调用Sum()，统计每一本书的销售总额

# 执行anntate()方法对字段进行操作的时候，会为模型添加一个指定名字的字段，默认情况下，添加的字段的名字为：field__聚合函数名，

# 但是同样也可以对字段名字进行指定，比如，以下就指定为sum.

# 注意：添加的这个字段，并没有添加到数据库中如果进行遍历books，打印出每一本书的信息，则信息中只有在models.py中已经定义的属性。而不会有执行annotate()函数添加的字段。

# 但是在进行输出的时候同样可以进行打印出该属性所有的信息

books = Book.objects.annotate(sum=Sum('bookorder__price'))

for book in books:

# 打印出每一本书中，由于执行annotate()方法和聚合函数Sum()方法添加的属性sum信息。

print("%s,%s"%(book.name, book.sum))

# 打印出结果：

# 三国演义,203.0

# 水浒传,215.0

# 红楼梦,204.0

# 西游记,None

# 打印出数据库中book的信息

print(book)

# (书名：三国演义,页数：893,价格：129.0,打折：0.8,作者：罗贯中,47,[email protected],出版社： ->:清华大学出版社)

# (书名：水浒传,页数：983,价格：159.0,打折：0.75,作者：施耐庵,57,[email protected],出版社： ->:吉林大学出版社)

# (书名：红楼梦,页数：1543,价格：199.0,打折：0.85,作者：曹雪芹,42,[email protected],出版社： ->:浙江大学出版社)

# (书名：西游记,页数：1003,价格：159.0,打折：0.75,作者：吴承恩,34,[email protected],出版社： ->:清华大学出版社)

# 打印出django底层所执行的sql语句

print(connection.queries)

# [{'sql': 'SELECT @@SQL_AUTO_IS_NULL', 'time': '0.000'},

# {'sql': 'SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED', 'time': '0.000'},

# {'sql': 'SELECT `book`.`id`, `book`.`name`, `book`.`pages`, `book`.`price`, `book`.`rating`, `book`.`author_id`, `book`.`publisher_id`, SUM(`book_order`.`price`) AS `sum` FROM `book` LEFT OUTER JOIN `book_order` ON (`book`.`id` = `book_order`.`book_id`) GROUP BY `book`.`id` ORDER BY NULL', 'time': '0.000'},

# {'sql': 'SELECT `author`.`id`, `author`.`name`, `author`.`age`, `author`.`email` FROM `author` WHERE `author`.`id` = 5 LIMIT 21', 'time': '0.016'},

# {'sql': 'SELECT `publisher`.`id`, `publisher`.`name` FROM `publisher` WHERE `publisher`.`id` = 2 LIMIT 21', 'time': '0.000'},

# {'sql': 'SELECT `author`.`id`, `author`.`name`, `author`.`age`, `author`.`email` FROM `author` WHERE `author`.`id` = 1 LIMIT 21', 'time': '0.000'},

# {'sql': 'SELECT `publisher`.`id`, `publisher`.`name` FROM `publisher` WHERE `publisher`.`id` = 1 LIMIT 21', 'time': '0.000'},

# {'sql': 'SELECT `author`.`id`, `author`.`name`, `author`.`age`, `author`.`email` FROM `author` WHERE `author`.`id` = 4 LIMIT 21', 'time': '0.000'},

# {'sql': 'SELECT `publisher`.`id`, `publisher`.`name` FROM `publisher` WHERE `publisher`.`id` = 3 LIMIT 21', 'time': '0.000'},

# {'sql': 'SELECT `author`.`id`, `author`.`name`, `author`.`age`, `author`.`email` FROM `author` WHERE `author`.`id` = 3 LIMIT 21', 'time': '0.000'},

# {'sql': 'SELECT `publisher`.`id`, `publisher`.`name` FROM `publisher` WHERE `publisher`.`id` = 2 LIMIT 21', 'time': '0.000'}]

return HttpResponse("success!)

3.求2020所有图书的销售总额，示例代码如下：

def index(request):

# 3.求2020所有书的销售总额

# 首先为BookOrder模型添加一个新的属性time,必须设置DateTimeField()中的参数null=True。因为bookorder表中已经含有多条数据了。新添加的这个属性，在数据库中字段为NULL.如果不设置null=True的话，默认情况下为False。

book = BookOrder.objects.filter(time__year=2020).aggregate(sum=Sum('price'))

print(book)

# # {'sum': 522.0}

print(connection.queries)

# [{'sql': 'SELECT @@SQL_AUTO_IS_NULL', 'time': '0.000'},

# {'sql': 'SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED', 'time': '0.000'},

# {'sql': "SELECT SUM(`book_order`.`price`) AS `sum` FROM `book_order` WHERE `book_order`.`time` BETWEEN '2020-01-01 00:00:00' AND '2020-12-31 23:59:59.999999'", 'time': '0.000'}]

return HttpResponse('success!')

4.求2020年度每种书的销售总额，示例代码如下：

from django.http import HttpResponse

from djano.db import connection

from django.db.models import Sum

from .models import Book, BookOrder

def index(request):

# 4.求2020年度每种书的销售总额

books = Book.objects.filter(bookorder__time__year=2020).annotate(sum=Sum('bookorder__price'))

for book in books:

print("%s,%s"%(book.name,book.sum))

# 打印出结果：

# 三国演义,203.0

# 水浒传,115.0

# 红楼梦,204.0

print(connection.queries)

# 打印出sql语句

# [{'sql': 'SELECT @@SQL_AUTO_IS_NULL', 'time': '0.000'},

# {'sql': 'SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED', 'time': '0.000'},

# {'sql': "SELECT `book`.`id`, `book`.`name`, `book`.`pages`, `book`.`price`, `book`.`rating`, `book`.`author_id`, `book`.`publisher_id`, SUM(`book_order`.`price`) AS `sum` FROM `book` INNER JOIN `book_order` ON (`book`.`id` = `book_order`.`book_id`) WHERE `book_order`.`time` BETWEEN '2020-01-01 00:00:00' AND '2020-12-31 23:59:59.999999' GROUP BY `book`.`id` ORDER BY NULL", 'time': '0.000'}]

return HttpResponse("success !")

原文：https://www.cnblogs.com/guyan-2020/p/12258714.html