70 多表查询的分组F 聚合 Q 查询

聚合查询和分组查询

聚合

aggregate()是QuerySet 的一个终止子句，意思是说，它返回一个包含一些键值对的字典。键的名称是聚合值的标识符，值是计算出来的聚合值。键的名称是按照字段和聚合函数的名称自动生成出来的。

示例：

>>> models.Book.objects.all().aggregate(Avg("price"))
{'price__avg': 13.233333}

如果你想要为聚合值指定一个名称，可以向聚合子句提供它。

>>> models.Book.objects.aggregate(average_price=Avg('price'))
{'average_price': 13.233333}

如果你希望生成不止一个聚合，你可以向aggregate()子句中添加另一个参数。所以，如果你也想知道所有图书价格的最大值和最小值，可以这样查询：

>>> models.Book.objects.all().aggregate(Avg("price"), Max("price"), Min("price"))
{'price__avg': 13.233333, 'price__max': Decimal('19.90'), 'price__min': Decimal('9.90')}

分组

为调用的QuerySet中每一个对象都生成一个独立的统计值

示例1：统计每一本书的作者个数

>>> book_list = models.Book.objects.all().annotate(author_num=Count("author"))
>>> for obj in book_list:
...     print(obj.author_num)
...
2
1
1

示例2：统计出每个出版社买的最便宜的书的价格

>>> publisher_list = models.Publisher.objects.annotate(min_price=Min("book__price"))
>>> for obj in publisher_list:
...     print(obj.min_price)
...     
9.90
19.90

方法二：

>>> models.Book.objects.values("publisher__name").annotate(min_price=Min("price"))
<QuerySet [{'publisher__name': '沙河出版社', 'min_price': Decimal('9.90')}, {'publisher__name': '人民出版社', 'min_price': Decimal('19.90')}]>

示例3：统计不止一个作者的图书

>>> models.Book.objects.annotate(author_num=Count("author")).filter(author_num__gt=1)
<QuerySet [<Book: 番茄物语>]>

示例4：根据一本图书作者数量的多少对查询集 QuerySet进行排序

>>> models.Book.objects.annotate(author_num=Count("author")).order_by("author_num")
<QuerySet [<Book: 香蕉物语>, <Book: 橘子物语>, <Book: 番茄物语>]>

示例5：查询各个作者出的书的总价格

>>> models.Author.objects.annotate(sum_price=Sum("book__price")).values("name", "sum_price")
<QuerySet [{'name': '小精灵', 'sum_price': Decimal('9.90')}, {'name': '小仙女', 'sum_price': Decimal('29.80')}, {'name': '小魔女', 'sum_price': Decimal('9.90')}]>

F查询和Q查询

F查询

在上面所有的例子中，我们构造的过滤器都只是将字段值与某个常量做比较。如果我们要对两个字段的值做比较，那该怎么做呢？

Django 提供 F() 来做这样的比较。F() 的实例可以在查询中引用字段，来比较同一个 model 实例中两个不同字段的值。

示例1：

查询评论数大于收藏数的书籍

from django.db.models import F
models.Book.objects.filter(commnet_num__lt=F('keep_num'))

Django 支持 F() 对象之间以及 F() 对象和常数之间的加减乘除和取模的操作。

models.Book.objects.filter(commnet_num__lt=F('keep_num')*2)

修改操作也可以使用F函数,比如将每一本书的价格提高30元

models.Book.objects.all().update(price=F("price")+30)

引申：

如果要修改char字段咋办？

如：把所有书名后面加上(第一版)

>>> from django.db.models.functions import Concat
>>> from django.db.models import Value
>>> models.Book.objects.all().update(title=Concat(F("title"), Value("("), Value("第一版"), Value(")")))

Q查询

filter() 等方法中的关键字参数查询都是一起进行“AND” 的。 如果你需要执行更复杂的查询（例如OR语句），你可以使用Q对象。

示例1：

查询作者名是小仙女或小魔女的

models.Book.objects.filter(Q(authors__name="小仙女")|Q(authors__name="小魔女"))

你可以组合& 和|  操作符以及使用括号进行分组来编写任意复杂的Q 对象。同时，Q 对象可以使用~ 操作符取反，这允许组合正常的查询和取反(NOT) 查询。

示例：查询作者名字是小仙女并且不是2018年出版的书的书名。

>>> models.Book.objects.filter(Q(author__name="小仙女") & ~Q(publish_date__year=2018)).values_list("title")
<QuerySet [('番茄物语',)]>

查询函数可以混合使用Q 对象和关键字参数。所有提供给查询函数的参数（关键字参数或Q 对象）都将"AND”在一起。但是，如果出现Q 对象，它必须位于所有关键字参数的前面。

例如：查询出版年份是2017或2018，书名中带物语的所有书。

>>> models.Book.objects.filter(Q(publish_date__year=2018) | Q(publish_date__year=2017), title__icontains="物语")
<QuerySet [<Book: 番茄物语>, <Book: 香蕉物语>, <Book: 橘子物语>]>

1 关于聚合aggregate 

它返回一个包含一些键值对的字典。键的名称是聚合值的标识符，值是计算出来的聚合值。键的名称是按照字段和聚合函数的名称自动生成出来的。

from django.db.models import Avg,Max,Min,Sum

models.Book.objects.aggregate(Avg('price'))
(0.001) SELECT @@SQL_AUTO_IS_NULL; args=None
(0.092) SELECT AVG(`app02_book`.`price`) AS `price__avg` FROM `app02_book`; args=()

{'price__avg': 19.7725}

models.Book.objects.aggregate(ping_jun=Avg('price'))
{'ping_jun': 19.7725}

(0.001) SELECT AVG(`app02_book`.`price`) AS `ping_jun` FROM `app02_book`; args=()

models.Book.objects.aggregate(zuida=Max('price'))
{'zuida': Decimal('29.90')}

(0.093) SELECT MAX(`app02_book`.`price`) AS `zuida` FROM `app02_book`; args=()

odels.Book.objects.aggregate(Max('price'),Avg('price'),Min('price'))
{'price__max': Decimal('29.90'), 'price__avg': 19.7725, 'price__min': Decimal('9.99')}

(0.002) SELECT MAX(`app02_book`.`price`) AS `price__max`, AVG(`app02_book`.`price`) AS `price__avg`, MIN(`app02_book`.`price`) AS `price__min` FROM `app02_book`; args=()

关于 分组 annotate调用的QuerySet中每一个对象都生成一个独立的统计值

from django.db.models import Count
a = models.Author.objects.annotate(sum=Count('books__title'))

for i in a:
print(i.name,i.sum)

(0.062) SELECT `app02_author`.`id`, `app02_author`.`name`, `app02_author`.`detail_id`, COUNT(`app02_book`.`title`) AS `sum` FROM `app02_author` LEFT OUTER JOIN `app02_author_books` ON (`app02_author`.`id` = `app02_author_books`.`author_id`) LEFT OUTER JOIN `app02_book` ON (`app02_author_books`.`book_id` = `app02_book`.`id`) GROUP BY `app02_author`.`id` ORDER BY NULL; args=()
王小一 2
张小二 1
李小三 1
赵小四 0

b = models.Author.objects.annotate(avg_price=Avg('books__price'))

(0.035) SELECT `app02_author`.`id`, `app02_author`.`name`, `app02_author`.`detail_id`, AVG(`app02_book`.`price`) AS `avg_price` FROM `app02_author` LEFT OUTER JOIN `app02_author_books` ON (`app02_author`.`id` = `app02_author_books`.`author_id`) LEFT OUTER JOIN `app02_book` ON (`app02_author_books`.`book_id` = `app02_book`.`id`) GROUP BY `app02_author`.`id` ORDER BY NULL LIMIT 21; args=()
for i in b :
print(i.name,i.acg_price)

(0.002) SELECT `app02_author`.`id`, `app02_author`.`name`, `app02_author`.`detail_id`, AVG(`app02_book`.`price`) AS `avg_price` FROM `app02_author` LEFT OUTER JOIN `app02_author_books` ON (`app02_author`.`id` = `app02_author_books`.`author_id`) LEFT OUTER JOIN `app02_book` ON (`app02_author_books`.`book_id` = `app02_book`.`id`) GROUP BY `app02_author`.`id` ORDER BY NULL; args=()
Traceback (most recent call last):
for i in b :
print(i.name,i.avg_price)

王小一 24.95
张小二 20.0
李小三 29.9
赵小四 None

关于F 和Q 查询
1新建一个app04
D:py文件存放Book>python manage.py startapp app04
2在django 项目settings 进行设置
INSTALLED_APPS = [
'django.contrib.admin',
'django.contrib.auth',
'django.contrib.contenttypes',
'django.contrib.sessions',
'django.contrib.messages',
'django.contrib.staticfiles',
'app02.apps.App02Config',
'app03.apps.App03Config',
'app04.apps.App04Config',
]

对数据库的修改 对app04 进行登记 和执行

关于 同字段F 的查询
在上面所有的例子中，我们构造的过滤器都只是将字段值与某个常量做比较。如果我们要对两个字段的值做比较，那该怎么做呢？

Django 提供 F() 来做这样的比较。F() 的实例可以在查询中引用字段，来比较同一个 model 实例中两个不同字段的值

from app04 import models
from django.db.models import F
models.Goods.objects.filter(keep_num__gt=F('buy_num'))
(0.001) SELECT @@SQL_AUTO_IS_NULL; args=None
(0.001) SELECT VERSION(); args=None
<QuerySet [<Goods: 大萝卜>]>
(0.001) SELECT `app04_goods`.`id`, `app04_goods`.`name`, `app04_goods`.`keep_num`, `app04_goods`.`buy_num` FROM `app04_goods` WHERE `app04_goods`.`keep_num` > (`app04_goods`.`buy_num`) LIMIT 21; args=()
(0.001) SELECT @@SQL_AUTO_IS_NULL; args=None
(0.000) SELECT VERSION(); args=None
(0.001) SELECT `app04_goods`.`id`, `app04_goods`.`name`, `app04_goods`.`keep_num`, `app04_goods`.`buy_num` FROM `app04_goods` WHERE `app04_goods`.`keep_num` > (`app04_goods`.`buy_num`) LIMIT 21; args=()

models.Goods.objects.filter(keep_num__gt=F('buy_num')*2)
<QuerySet [<Goods: 大萝卜>]>
(0.076) SELECT `app04_goods`.`id`, `app04_goods`.`name`, `app04_goods`.`keep_num`, `app04_goods`.`buy_num` FROM `app04_goods` WHERE `app04_goods`.`keep_num` > ((`app04_goods`.`buy_num` * 2)) LIMIT 21; args=(2,)
(0.001) SELECT `app04_goods`.`id`, `app04_goods`.`name`, `app04_goods`.`keep_num`, `app04_goods`.`buy_num` FROM `app04_goods` WHERE `app04_goods`.`keep_num` > ((`app04_goods`.`buy_num` * 2)) LIMIT 21; args=(2,)

models.Goods.objects.all().update(buy_num=F('buy_num')+1000)
(0.084) UPDATE `app04_goods` SET `buy_num` = (`app04_goods`.`buy_num` + 1000); args=(1000,)
4

拼接 字符串
from django.db.models import Value
from django.db.models.functions import Concat

models.Goods.objects.all().update(name=Concat(Value('特价'),F(name)))

支持多个参数
models.Goods.objects.all().update(name=Concat(Value('('),Value('特价'),Value(')'),F('name')))

关于Q查询（就是or| 和and &） 联表查询 跨表字段前面需要加上杠杠

filter() 等方法中的关键字参数查询都是一起进行“AND” 的。 如果你需要执行更复杂的查询（例如OR语句），你可以使用Q对象。

或者 or 的意思

models.Author.objects.filter(Q(name='王小一')|Q(name='张小二'))
(0.063) SELECT `app02_author`.`id`, `app02_author`.`name`, `app02_author`.`detail_id` FROM `app02_author` WHERE (`app02_author`.`name` = '王小一' OR `app02_author`.`name` = '张小二') LIMIT 21; args=('王小一', '张小二')
<QuerySet [<Author: 王小一>, <Author: 张小二>]>

并且的意思

models.Book.objects.filter(Q(author__name='王小一')&Q(publish_day__year='2017'))
(0.002) SELECT `app02_book`.`id`, `app02_book`.`title`, `app02_book`.`price`, `app02_book`.`publish_day`, `app02_book`.`publisher_id`, `app02_book`.`memo` FROM `app02_book` INNER JOIN `app02_author_books` ON (`app02_book`.`id` = `app02_author_books`.`book_id`) INNER JOIN `app02_author` ON (`app02_author_books`.`author_id` = `app02_author`.`id`) WHERE (`app02_author`.`name` = '王小一' AND `app02_book`.`publish_day` BETWEEN '2017-01-01' AND '2017-12-31') LIMIT 21; args=('王小一', '2017-01-01', '2017-12-31')
<QuerySet [<Book: 芥末物语>]>