ORM聚合函数
2020-06-17 本文已影响0人
小吉头
表关系如下：
author
+----+--------+-----+------------+
| id | name | age | email |
+----+--------+-----+------------+
| 1 | 曹雪芹 | 35 | cxq@qq.com |
| 2 | 吴承恩 | 28 | wce@qq.com |
| 3 | 罗贯中 | 36 | lgz@qq.com |
| 4 | 施耐庵 | 46 | sna@qq.com |
+----+--------+-----+------------+
book
+----+----------+-------+-------+--------+-----------+--------------+----------+
| id | name | pages | price | rating | author_id | publisher_id | remark |
+----+----------+-------+-------+--------+-----------+--------------+----------+
| 1 | 三国演义 | 987 | 108 | 4.8 | 3 | 1 | 三国演义 |
| 2 | 水浒传 | 967 | 107 | 4.83 | 4 | 1 | 水浒传 |
| 3 | 西游记 | 1004 | 105 | 4.85 | 2 | 2 | 西游记 |
| 4 | 红楼梦 | 1007 | 109 | 4.9 | 1 | 2 | 红楼梦 |
+----+----------+-------+-------+--------+-----------+--------------+----------+
book_order
+----+-------+---------+----------------------------+
| id | price | book_id | create_time |
+----+-------+---------+----------------------------+
| 1 | 95 | 1 | 2019-06-17 07:10:02.053000 |
| 2 | 85 | 1 | 2020-06-17 07:10:02.053000 |
| 3 | 88 | 1 | 2020-06-17 07:10:02.053000 |
| 4 | 94 | 2 | 2020-06-17 07:10:02.053000 |
| 5 | 93 | 2 | 2020-06-17 07:10:02.053000 |
+----+-------+---------+----------------------------+
平均值-AVG()
使用aggregate和annotate执行AVG()：
from django.db.models import Avg
from django.db import connection
#查看所有书的平均价格
result = Book.objects.aggregate(Avg("price"))
print(result)
print(connection.queries)
>>>{'price__avg': 97.25}
>>>SELECT AVG(`book`.`price`) AS `price__avg` FROM `book`
#查看每本书的对应所有订单的平均售价
result = Book.objects.annotate(avg = Avg("bookorder__price"))
print(result)
print(connection.queries)
>>><QuerySet [<Book: Book object>, <Book: Book object>, <Book: Book object>, <Book: Book object>]>
>>>SELECT `book`.`id`, `book`.`name`, `book`.`pages`, `book`.`price`, `book`.`rating`, `book`.`author_id`, `book`.`publisher_id`, AVG(`book_order`.`price`) AS `avg` FROM `book` LEFT OUTER JOIN `book_order` ON (`book`.`id` = `book_order`.`book_id`) GROUP BY `book`.`id` ORDER BY NULL LIMIT 21
相同点：
都可以执行聚合函数
不同点：
1、aggregate返回字典，示例中key默认是字段__avg，Book.objects.aggregate(avg = Avg("price"))可修改key，结果是{'avg': 97.25}
2、annotate返回QuerySet对象，使用Book.objects.annotate(avg = Avg("bookorder__price")).values()查看结果，在模型对象属性里增加了平均值属性avg：
<QuerySet [{'publisher_id': 1, 'name': '三国演义', 'avg': 89.33333333333333, 'rating': 4.8, 'author_id': 3, 'pages': 987, 'price': 98.0, 'id': 1},{...},{...}]>
3、从翻译的sql可以看出，aggregate不做分组，annotate使用group by分组。默认会根据分组字段进行排序。
EXPLAIN
SELECT `book`.`id`, `book`.`name`, `book`.`pages`, `book`.`price`, `book`.`rating`, `book`.`author_id`, `book`.`publisher_id`, AVG(`book_order`.`price`) AS `avg` FROM `book` LEFT OUTER JOIN `book_order` ON (`book`.`id` = `book_order`.`book_id`) GROUP BY `book`.`id` ORDER BY NULL LIMIT 21
>>>Extra字段 Using temporary
//去掉ORDER BY NULL查看执行计划
>>>Using temporary; Using filesort
orm翻译的sql使用了ORDER BY NULL禁用排序，提升查找效率。
数量-Count()
from django.db.models import Avg
from django.db import connection
#book表中共有多少个出版社
result = Book.objects.aggregate(nums = Count("publisher_id"))
print(result)
print(connection.queries)
>>>{'nums': 4}
>>>SELECT COUNT(`book`.`publisher_id`) AS `nums` FROM `book`
#book表中共有多少不重复的出版社
result = Book.objects.aggregate(nums = Count("publisher_id",distinct=True))
print(result)
print(connection.queries)
>>>{'nums': 2}
>>>SELECT COUNT(DISTINCT `book`.`publisher_id`) AS `nums` FROM `book`
#统计每本书的销量
books = Book.objects.annotate(nums = Count("bookorder__id")) //bookorder_id可以简写成bookorder
print(books.values())
print(connection.queries)
>>><QuerySet [{'pages': 987, 'publisher_id': 1, 'price': 98.0, 'id': 1, 'name': '三国演义', 'author_id': 3, 'rating': 4.8, 'nums': 3},{...},{...}]>
>>>SELECT `book`.`id`, `book`.`name`, `book`.`pages`, `book`.`price`, `book`.`rating`, `book`.`author_id`, `book`.`publisher_id`, COUNT(`book_order`.`id`) AS `nums` FROM `book` LEFT OUTER JOIN `book_order` ON (`book`.`id` = `book_order`.`book_id`) GROUP BY `book`.`id` ORDER BY NULL LIMIT 21
最大最小值-Max()、Min()
aggregate()和annotate()可以加多个聚合函数
#author年龄最大值和最小值
result = Author.objects.aggregate(max = Max("age"),min = Min("age"))
print(result)
print(connection.queries)
>>>{'max': 46, 'min': 28}
>>>SELECT MAX(`author`.`age`) AS `max`, MIN(`author`.`age`) AS `min` FROM `author`
#获取每本书售卖时的最高价和最低价
result = Book.objects.annotate(max = Max("bookorder__price"),min = Min("bookorder__price"))
print(result.values())
print(connection.queries)
>>><QuerySet [{'publisher_id': 1, 'min': 85.0, 'max': 95.0, 'author_id': 3, 'name': '三国演义', 'id': 1, 'rating': 4.8, 'pages': 987, 'price': 98.0},{...},{...}]>
>>>SELECT `book`.`id`, `book`.`name`, `book`.`pages`, `book`.`price`, `book`.`rating`, `book`.`author_id`, `book`.`publisher_id`, MIN(`book_order`.`price`) AS `min`, MAX(`book_order`.`price`) AS `max` FROM `book` LEFT OUTER JOIN `book_order` ON (`book`.`id` = `book_order`.`book_id`) GROUP BY `book`.`id` ORDER BY NULL LIMIT 21
求和-sum()
aggregate：
#2020年所有图书销售总额
result = BookOrder.objects.filter(create_time__year=2020).aggregate(total = Sum("price"))
print(result)
print(connection.queries)
>>>{'total': 360.0}
>>>SELECT SUM(`book_order`.`price`) AS `total` FROM `book_order` WHERE `book_order`.`create_time` BETWEEN '2019-12-31 16:00:00' AND '2020-12-31 15:59:59.999999'
因为setting里面设置了时区是Asia/Shanghai，migrations时数据库里面保存的其实是UTC时间，所以翻译结果是计算后的utc时间BETWEEN '2019-12-31 16:00:00' AND '2020-12-31 15:59:59.999999,如果要得到和数据库匹配的utc时间，可以修改setting时区为utc
TIME_ZONE = 'UTC'
USE_TZ = True
修改后sql翻译结果：BETWEEN '2020-01-01 00:00:00' AND '2020-12-31 23:59:59.999999'
annotate：
#2020年每本书的销售总额
result = Book.objects.filter(bookorder__create_time__year=2020).annotate(total=Sum("price"))
>>><QuerySet [{'name': '三国演义', 'price': 98.0, 'id': 1, 'author_id': 3, 'total': 196.0, 'publisher_id': 1, 'rating': 4.8, 'pages': 987}, {...},{...}]>
>>>SELECT `book`.`id`, `book`.`name`, `book`.`pages`, `book`.`price`, `book`.`rating`, `book`.`author_id`, `book`.`publisher_id`, SUM(`book`.`price`) AS `total` FROM `book` INNER JOIN `book_order` ON (`book`.`id` = `book_order`.`book_id`) WHERE `book_order`.`create_time` BETWEEN '2020-01-01 00:00:00' AND '2020-12-31 23:59:59.999999' GROUP BY `book`.`id` ORDER BY NULL LIMIT 21
