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

时间：2022-11-02 06:08:39

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表中数据为：

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

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

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

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 !")

相关文章