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,312587329@qq.com,出版社: ->:清华大学出版社)
# (书名:水浒传,页数:983,价格:159.0,打折:0.75,作者:施耐庵,57,1924572@qq.com,出版社: ->:吉林大学出版社)
# (书名:红楼梦,页数:1543,价格:199.0,打折:0.85,作者:曹雪芹,42,123521472@qq.com,出版社: ->:浙江大学出版社)
# (书名:西游记,页数:1003,价格:159.0,打折:0.75,作者:吴承恩,34,193452272@qq.com,出版社: ->:清华大学出版社)

    # 打印出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