Django框架（七）：聚合查询与分组查询、F与Q函数

下面是小凰凰的简介，看下吧！
💗人生态度：珍惜时间，渴望学习，热爱音乐，把握命运，享受生活
💗学习技能：网络 -> 云计算运维 -> python全栈( 当前正在学习中)
💗您的点赞、收藏、关注是对博主创作的最大鼓励，在此谢过！
有相关技能问题可以写在下方评论区，我们一起学习，一起进步。
后期会不断更新python全栈学习笔记，秉着质量博文为原则，写好每一篇博文。

一、聚合查询

什么是SQL的聚合函数？Avg、Sum、Count、Max、Min这些就是聚合函数，在SQL中这些都是小写的！ORM的聚合查询底层sql也就是这些函数！

aggregate()：聚合函数（Avg、Sum等等）作为参数传给这个函数！

# 计算所有图书的平均价格
	>>> from django.db.models import Avg
	# 求book表的所有书籍的平均价格
	>>> Book.objects.all().aggregate(Avg('price'))
	{'price__avg': 34.35}

.all可以省略不写！aggregate()它返回一个字典，指定键名：

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

如果你希望生成不止一个聚合，你可以向aggregate()子句中添加另一个参数。

>>> from django.db.models import Avg, Max, Min
>>> Book.objects.aggregate(Avg('price'), Max('price'), Min('price'))
{'price__avg': 34.35, 'price__max': Decimal('81.20'), 'price__min': Decimal('12.99')}

二、分组查询

annotate()，分组查询关键方法！分组的目的其实就是为了更好的聚合！

1、单表分组查询

# 查询每一个部门名称以及对应的员工数

🌈 emp表:
	id  name age   salary    dep
	1   alex  12   2000     销售部
	2   egon  22   3000     人事部
	3   wen   22   5000     人事部


🌈 sql语句:
	select dep,Count(*) from emp group by dep;

🌈 ORM分组查询：
	emp.objects.values("dep").annotate(c=Count("id")

# annotate分组，是依据前面value的参数进行分组的，因此上面是依据部门（dep）进行分组的
# annotate的参数是一个个的聚合函数，因为前面说了分组的目的是为了更好的聚合！

有小伙伴会惊呼values参数不是相当于SQL中的select啥啥啥吗？为什么变成了分组的依据了？values虽然被赋予了分组的依据，但是还有select的能力，请看：

# 查询每一个部门的名称以及对应员工的平均薪水      
# 可以看到结果是两个字段(分组的那个字段，要查询的字段)
ret=Emp.objects.values("dep").annotate(avg_salary=Avg("salary"))
print(ret) #
 <QuerySet [{'dep': '教学部', 'avg_salary': 2500.0}, {'dep': '保洁部', 'avg_salary': 3500.0}, {'dep': '保安部', 'avg_salary': 4000.0}]>

练习题
# 查询书籍表每一个出版社id以及对应的书籍个数
	ret=Book.objects.values("publish_id").annotate(c=Count(1))
	print(ret)

# 查询每一个省份的名称以及对应的员工最大年龄
	ret=Emp.objects.values("pro").annotate(max_age=Max("age"))
	print(ret) # <QuerySet [{'pro': '山东省', 'max_age': 123}, {'pro': '河南省', 'max_age': 23}, {'pro': '河北省', 'max_age': 56}]>

2、多表分组查询

# 查询每一个部门名称以及对应的员工数

🌈 emp表:
	id  name age   salary   dep_id
	1   alex  12   2000       1
	2   egon  22   3000       2
	3   wen   22   5000       2

🌈 dep表:
	id   name 
	1    销售部
	2    人事部

🌈 emp－dep关系表:
	id  name age   salary   dep_id   id   name 
	1   alex  12   2000       1      1    销售部
	2   egon  22   3000       2      2    人事部
	3   wen   22   5000       2      2    人事部


🌈 SQL语句:
	select dep.name,Count(*) from emp left join dep on emp.dep_id=dep.id group by dep.id

🌈 ORM分组查询:
	dep.objetcs.values("id").annotate(c=Count("*")).values("name","c")
	# 前面是dep，因此后面查询的name的值也就是dep的name,c是count聚合的结果！

总结 ：跨表分组查询本质就是将关联表join成一张表，再按单表的思路进行分组查询。

其实我这里虽然分成了外键和第三张表的跨表连接，但是其实它们都是一样的！

案例1（通过外键的跨表连接）：
# 查询每一个出版社的名称以及对应的书籍平均价格
	ret=Publish.objects.values("name","email").annotate(avg_price=Avg("book__price"))
	print(ret) # <QuerySet [{'name': '苹果出版社', 'avg_price': 117.0}, {'name': '橙子出版社', 'avg_price': 112.0}, {'name': '西瓜出版社', 'avg_price': 222.0}]>

案例2（多对多，第三张表的跨表连接）
# 查询每一个作者的名字以及出版的书籍的最高价格
	ret=Author.objects.values("pk","name").annotate(max_price=Max("book__price"))
	print(ret)

# 查询每一个书籍的名称以及对应的作者的个数
	ret=Book.objects.values("title").annotate(c=Count("authors"))
	print(ret) # <QuerySet [{'title': 'python', 'authors__count': 2}, {'title': 'linux', 'authors__count': 1}, {'title': 'go', 'authors__count': 1}, {'title': 'java', 'authors__count': 0}]>

上面部分例子有用到了依据多个字段进行分组查询，以values("pk","name")为例，只有当pk主键字段和name两个都相同时，才会被认为是一组！有人会惊呼，这个pk可能相同吗？主键肯定是不一样的啊！

答：pk在单表中的确是不可能一样的！他是unique唯一的！但是我们现在是多表分组查询！
多表分组查询是pk是有可能一样的！请看下图：

nid1是publish表的主键字段！它重复了，publish_id其实和nid1是一样的！多表连接之后，主键就变成了一个普通字段！除非你是OnetoOne关系，那么nid1就不会重复！

3、分组查询补充

ret=Publish.objects.all() # all表示所有字段
print(ret) # <QuerySet [<Publish: 苹果出版社>, <Publish: 橙子出版社>, <Publish: 西瓜出版社>]>   
ret=Publish.objects.all().annotate(avg_price=Avg("book__price"))
print(ret)

下面两种方式是相同的。'方式二是方式一的简写'
1. 方式一
	按哪个字段分组.annotate(分完组后需要统计的的字段).values(取出哪个字段的值)
	# 需求：统计每个出版社出版书籍的平均价格！
	# 那么我们就可以让每个出版社一组，然后联表查平均价格！
	ret=Publish.objects.all().annotate(avg_price=Avg("book__price")).values("name","avg_price")
	print(ret) #<QuerySet [{'name': '苹果出版社', 'avg_price': 117.0}, {'name': '橙子出版社', 'avg_price': 112.0}, {'name': '西瓜出版社', 'avg_price': 222.0}]>

	# annotate前面表示按所有字段进行分组，也就是一条记录一个组
	# 一般用于一对多或多对多中，一对多用于一那方，比如上面的Publish
	# .all().annotate()这样得到的是一个所有记录对象加上自己的相应聚合操作的对象，values表示取值，你想取这些对象的啥，你就取啥

2. 方式二（效果和上面一样的，简写罢了）
ret=Publish.objects.annotate(avg_price=Avg("book__price")).values("name","avg_price")

（1）案例1的三种书写方式

# 查询每一个出版社的名称以及对应的书籍平均价格
    1. 方式一
	    ret=Publish.objects.values("name").annotate(avg_price=Avg("book__price"))
	    print(ret) # <QuerySet [{'name': '苹果出版社', 'avg_price': 117.0}, {'name': '橙子出版社', 'avg_price': 112.0}, {'name': '西瓜出版社', 'avg_price': 222.0}]>
    2. 方式二
	    ret=Publish.objects.all().annotate(avg_price=Avg("book__price")).values("name","avg_price")
	    print(ret) #  <QuerySet [{'name': '苹果出版社', 'avg_price': 117.0}, {'name': '橙子出版社', 'avg_price': 112.0}, {'name': '西瓜出版社', 'avg_price': 222.0}]>
    3. 方式三
	    ret=Publish.objects.annotate(avg_price=Avg("book__price")).values("name","avg_price")
	    print(ret) #  <QuerySet [{'name': '苹果出版社', 'avg_price': 117.0}, {'name': '橙子出版社', 'avg_price': 112.0}, {'name': '西瓜出版社', 'avg_price': 222.0}]>

总结：values是最开始就限制了select取的字段，all方法是你可以先得到一个带有聚合结果的所有对象的集合，你想要啥字段就可以values取啥字段！

（2）案例2的三种书写方式

# 查询每一个作者的名字以及出版的书籍的最高价格方式一：
1. 方式一：
	ret=Author.objects.values("pk","name").annotate(max_price=Max("book__price"))
	print(ret)
2. 方式二：
	ret=Author.objects.annotate(maxprice=Max("book__price")).values("name","maxprice")
	print(ret)

这个案例.all方式我就不写了，简写会就行！

三、F函数

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

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

# 查询评论数大于收藏数的书籍
   from django.db.models import F
   Book.objects.filter(commentNum__gt=F('keepNum'))

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

# 查询评论数大于收藏数2倍的书籍
    Book.objects.filter(commnetNum__lt=F('keepNum')*2)

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

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

四、Q函数

filter() 方法中的逗号连接参数，那都是进行"AND"（且操作） 的，比如filter(age__gt=30,gender='男')：年龄大于30岁且性别为男的过滤出来。 如果你需要两个条件的或、非操作，那么你可以使用Q 函数。

Q 对象可以使用&（且） 、|（或）、~（非） 操作符组合起来。当一个操作符在两个Q 对象上使用时，它产生一个新的Q 对象

from django.db.models import Q
bookList=Book.objects.filter(Q(authors__name="yuan")|Q(authors__name="egon"))
# 过滤出作者姓名为yuan或者作者姓名为egon的书籍对象

等同于下面的SQL WHERE 子句：

WHERE name ="yuan" OR name ="egon"

Q函数支持多个操作符的组合，如下：

Book.objects.filter(Q(authors__name="yuan") & ~Q(publishDate__year=2017)).values_list("title")
# 过滤出作者姓名为yuan且出生日期不是2017年的书籍对象，然后取出每个书籍名放入列表中返回

我们知道filter中的逗号也可以作为且操作，那么也就是说我们的Q函数应该可以和filter中的逗号混用！的确可以混用，只不过Q函数需要往前放，filter中的逗号需要往后放！例如：

bookList=Book.objects.filter(Q(publishDate__year=2016) | Q(publishDate__year=2017),title__icontains="python")

# 我们用逗号连接的title__icontains="python"就必须放在Q函数后面！

Q函数还可以嵌套使用,例如（filter函数我就不写了）：

Q(Q(authors__name="yuan") & ~Q(publishDate__year=2017)) | Q(title__icontains="python")

你可以随便嵌套多少层！实际这样应用的还比较多！