Django-8 聚合查询与分组查询
聚合查询与分组查询
聚合查询:
aggregate(*args, **kwargs)
#1.聚合 aggregate:返回值是一个字典 from django.db.models import Avg,Max,Min,Count #问题:查询所有书籍的平均价格,最高价格,最低价格 ret = Book.objects.all().aggregate(avg_price=Avg('price'),max_price = Max('price'),min_price=Min('price')) print(ret)
aggregate()是QuerySet 的一个终止子句，意思是说，它返回一个包含一些键值对的字典。键的名称是聚合值的标识符，值是计算出来的聚合值。键的名称是按照字段和聚合函数的名称自动生成出来的。如果你想要为聚合值指定一个名称，可以向聚合子句提供它。
1.单表分组查询
单表查询
\
查询每个部门的的平均薪资
#************************************************聚合与分组查询***************************************************** #----------------------聚合 aggregate:返回值是一个字典-------------------------- from django.db.models import Avg,Max,Min,Count # --------------------分组 annotate 返回的是一个queryset----------------------------- #查询每个部门的平均薪资 #sql:SELECT dep AS 部门, AVG(salary) AS 平均薪资 FROM app01_emp GROUP BY dep; #ORM ret=emp1.objects.values('dep').annotate(平均薪资=Avg('salary')) print(ret)
单表分组查询语法总结:
表单模型.objects.values("group by 分组字段").annotate(聚合函数('聚合字段'))
'''表名.objects.values('被分组的字段').annotate(聚合函数(被聚合字段))
values相当于sql中的select.里面放什么字段,就相当于用什么字段做group by
annotate 和aggregate一样是个聚合函数
'''
总结:
# 总结 跨表的分组查询的模型:
# 每一个后的表模型.objects.values("pk").annotate(聚合函数(关联表__统计字段)).values("表模型的所有字段以及统计字段")
# 每一个后的表模型.objects.annotate(聚合函数(关联表__统计字段)).values("表模型的所有字段以及统计字段")
基于双下划线的多表查询
正向查询按字段
反向查询按表名小写,按表名小写是告诉ORM用来join哪个表
1.正向查询白雪公主这本书的出版社名称
SQL语句:
select app01_publish.name from app01_book inner join app01_publish
on app01_book.publish_id = app01_publish.nid
where app01_book.name='白雪公主';
ORM语句:
ret = book.objects.filter(name='白雪公主').values('publish__name')
注意,这是正向查询,所以这里是publish是指的book表中的publish字段(其实拿到的还是Publish表)
,而不是Publish表
filter(name='白雪公主'):相当于sql中的where语句
values():则相当于select的内容
1.反向查询白雪公主这本书的出版社名称
SQL:
select app01_publish.name from app01_publish inner join app01_book
on app01_publish.nid = app01_book.publish_id
where app01_book.name='白雪公主';
ORM语句:
ret =Publish.objects.filter(book__name='白雪公主').values('name')
filter(book__name='白雪公主'):告诉ORM引擎,inner join book表,然后查询name=白雪公主的书籍
values('name'):拿到和白雪公主这本书籍相对应的出版社的名字
'''
'''
两张有关系的表,A和B,关联字段在A表中.
正向查询:从A表查询B表中的内容,正向查询按 字段
反向查询:从B表查询A表中的内容,反向查询按 表名小写_set.all()
一对多查询:
正向查询:从A表查询B表中的内容,正向查询按 字段
反向查询:从B表查询A表中的内容,反向查询按 表名小写_set.all()
正向查询: book_obj.publish.email:关联属性在book表中是publish,所以查email用publish.email,也就是关联属性(对象)的属性
book(关联对象:publish)------------------------------------>publish
反向查询:book_obj.book_set.all()
publish--------------------------------->book
多对多查询:
正向查询:从A表查询B表中的内容,正向查询按 字段
反向查询:从B表查询A表中的内容,反向查询按 表名小写_set.all()
正向查询(查书籍作者): book_obj.author.all()
book(关联对象:author)-------------------------------------------------------------->author
反向查询(查作者所有书籍):author.book_set.all()
author------------------------------------------------------------>book
一对一查询:
正向查询:从A表查询B表中的内容,正向查询按 字段
反向查询:从B表查询A表中的内容,反向查询按 表名小写
正向查询(查作者详细信息): author.AuthorDetail.tel
author(关联对象:authorDetail)-------------------------------------------->authorDetail
反向查询(查作者所有书籍):auth.name
authorDetail------------------------------------------------------------>author
'''单表分组查询总结: 表单模型.objects.values("group by 分组字段").annotate(聚合函数('聚合字段')) 补充知识: emp1.objects.all() === select * from emp1 emp1.objects.all().values('dep') === select emp1.dep from emp1 ===emp1.objects.values('dep') emp1.objects.all().annotate(Avg('salary')) 分组时包含了主键,这样就是去了分组的意义 分组查询的意义在于把一个字段下多个值进行分组.如果使用了主键进行分组,相当于使用select * form emp1 没有意义 '''
2.跨表分组查询
跨表查询
#1.查询每个出版社出版的书籍数量以及出版社名称 # SQL:SELECT app01_publish.name AS 出版社,COUNT(nid) AS 书籍数量 FROM app01_book INNER JOIN app01_publish ON app01_book.publish_id = app01_publish.nid GROUP BY (publish_id); #ORM: 正向查询 ret=Book.objects.values('publish__name').annotate(书籍数量=Count('publish__name')) print(ret) # <QuerySet [{'publish__name': '人民出版社', '书籍数量': 3}, {'publish__name': '电子工业出版社', '书籍数量': 1}, {'publish__name': '清华大学出版社', '书籍数量': 1}]> ''' 这里需要弄清楚sql语句的执行顺序 注意:第一个publish__name是按照正向查询时book表下的字段publish中的name.第二个publish__name是连表时候按照表名小写加上__字段 ''' # 反向查询: ret = Publish.objects.values('nid').annotate(书籍数量=Count('book__name')).values('name','书籍数量') 这里的values只能显示基表(publish),或者基表下关联表的字段.如果基表没有关联字段,values('book__name')
则会报错.
print(ret) # # <QuerySet [{'name': '人民出版社', '书籍数量': 3}, {'name': '电子工业出版社', '书籍数量': 1}, {'name': '清华大学出版社', '书籍数量': 1}]> #
#2.查询每个作者的名字以及出版过的书籍的最高价格 # SQL:SELECT app01_author.`name`, MAX(price) FROM app01_author INNER JOIN app01_book_author ON app01_author.id = app01_book_author.author_id # # INNER JOIN app01_book ON app01_book.bid = book_id # # GROUP BY author_id ret = Author.objects.values('pk').annotate(price_max=Max('book__price')).values('name','price_max') print(ret) ''' 跨表查询总结: 每个后表模型.objects.values('基表主键 pk').annotate(聚合函数(关联表__查询字段)).values('表模型所有字段','聚合函数字段')
例如:查询每个作者的名字以及出版过的书籍的最高价格 这里每个后面的字符,就是基表,这个基表就是作者表 '''
总结 ：跨表分组查询本质就是将关联表join成一张表，再按单表的思路进行分组查询。
查询练习
1.统计每一个出版社的最便宜的书
'''SQL: SELECT app01_publish.`name` AS Publish_name,app01_book.`name` AS Book_name,MIN(price) AS Min_price FROM app01_publish INNER JOIN app01_book ON app01_publish.nid = app01_book.publish_id GROUP BY app01_publish.nid '''
ORM语句:
ret = Publish.objects.values('pk').annotate(Min_price=Min('book__price')).values_list('name','Min_price') print(ret)
2.统计每本书的作者数量
''' SELECT app01_book.`name`,COUNT(author_id) FROM app01_book INNER JOIN app01_book_author ON app01_book.bid = app01_book_author.book_id GROUP BY app01_book.bid ''' # ORM: ret = Book.objects.values('pk').annotate(c=Count('author__id')).values('name','c') print(ret)
3.
