Django ORM聚合查询,分组查询

聚合查询方法名aggregate(),返回的是一个字典,不是queryset,而annotate()返回的是一个queryset,

2个方法区别是aggregate是对一组值进行计算,而annotate更多的是一组值进行group by后再进行计算.

聚合方法需要搭配具体计算的方法一起使用,需要导入avg max min count from django.db.models import Count,Avg,Max,Min

计算书本的平均价格,返回值的key值为 `字段__avg`,

book = models.Book.objects.all().aggregate(Avg("price")) #{'price__avg': Decimal('31.593333')}

也可以自定义key值

book2 = models.Book.objects.all().aggregate(avg_price =Avg("price")) #{'avg_price': Decimal('31.593333')}
Decimal这个值提取出来可以用 to_eng_string() 或者 to_interger()等方法

xxx = {'price__sum': Decimal('320.00')};
print xxx['price__sum'].to_eng_string()

分组查询

ORM中,book.Object.all() 等于sql语句的select * from book,而book.Object.all().values("publish")等于sql语句的select publish from book,
ORM中的values有2种意思,一个是只提取publish字段显示,如果Book.objects.values("publish_id").annotate 后面跟了聚合函数,也会按照该字段进行分组.
所以Book.objects.all().annotate 是一句没有意义的聚合命令,因为all()里面包含了id,每条记录都是不同的,因此无法产生按照特定字段进行聚合.

  比如emp表,需要统计各个部门的人数
    emp:
    id  name    age dep
    1   alex        18  销售部
    2   blex        38  销售部
    3   clex        28  市场部
    4   dlex        48  客服部
    
    sql语句
    select Count(id) from emp group by dep; 通过对dep字段分组,再对每组的id个数进行统计
    单表中的分组查询
    orm中分组是用annotate(),返回值是一个query set 
    使用语法是前面先用value("dep").annotate(Avg("age"))
    value("dep") 等于明确按dep分组,语法等同于group by dep
    例子:
    avg_age = models.Book.objects.values("publish_id").annotate(zidingyi =Avg("price"))  #key值和aggregate一样可以自定义
    # QuerySet[{'publish_id': 1, 'zidingyi': Decimal('38.275000')}, {'publish_id': 2, 'zidingyi': Decimal('18.230000')}]

annotate(聚合函数)
values在前，表示group by字段  #msyql中group by只能查询，当前字段，以及聚合函数的值(不设置严格模式是可以取出来所有的值)
        #注意orm里面可以查询分组的表中的所有的字段的值？？？？
values在后，相当于取字段
filter在前，表示where条件
filter在后，表示having 分组后的筛选条件

##注意
1、单表  只能取分组字段跟聚合函数的字段。
2、多表  可以取分组表中的所有的字段，跟聚合函数的字段。

例句:
1、统计每一个本书作者个数
res=models.Book.objects.annotate(book_count=Count('author__id')).values('name','book_count')

2、统计不止一个作者的图书
res2=models.Books.objects.values('pk').annotate(book_count=Count('author__id')).filter(boo_count__gt=1).values('name','book_count')

3、统计价格大于10元，作者个数个数大于1的图书
res3=models.Books.objects.values('pk').filter('price__gt'=10).annotate(author_count=Count('authors__id')).filter(author_count__gt=1).values('name',author_count)

跨表之组合查询

# 跨表分组查询模板
# 每一个后表模型.objects.values("pk").annotate(聚合函数(关联表__统计字段)).values("表模型中的所有字段或者需要统计的字段")
# 每一个后表模型.objects.annotate(聚合函数(关联表__统计字段)).values("表模型中的所有字段或者需要统计的字段")
# 每一个后表模型.objects.all().annotate(聚合函数(关联表__统计字段)).values("表模型中的所有字段或者需要统计的字段")
上面第二个和第三个效果等同,因为values("pk")只是选择了pk这个字段,同时带有按这个字段group by的效果
但是我们用all()或者省略不写,ORM的语法意思是按照这个对象的所有字段进行group by的意思.

例1:查询出版社的名称以及出版的书本个数
sql语句示例

select my_book_publish.name,Count(my_book_book.title) from my_book_book inner join my_book_publish on my_book_book.publish_id = my_book_publish.pid group by my_book_publish.pid;

ORM语句示例
以publish为基表有三种写法,推荐用第二种,因为pid是唯一的,而有时候如果name会有重复,比如name为人名的时候

    pub = models.Publish.objects.values("pid").annotate(c =Count("book__title"))
    #< QuerySet[{'pid': 1, 'c': 3}, {'pid': 2, 'c': 2}, {'pid': 3, 'c': 1}] >
    # group by pid,然后annotate通过反向查询 表名__字段 对该字段进行聚合计算,但是结果只显示出版社的pid
    pub = models.Publish.objects.values("pid").annotate(c=Count("book__title")).values("name","c")
    #<QuerySet [{'name': '南京出版社', 'c': 3}, {'name': '北京出版社', 'c': 2}, {'name': '浙江出版社', 'c': 1}]>
    # 因为前面语句已经把2张表拼成了一张表,里面除了出版社和book的字段,c 这个计算结果字段也会包含在内,所以我们要显示出版社name和c可以再加个values进行展示
    pub = models.Publish.objects.values("name").annotate(c=Count("book__title"))
    # group by name,然后annotate通过反向查询 表名__字段 对该字段进行聚合计算
    # <QuerySet [{'name': '南京出版社', 'c': 3}, {'name': '北京出版社', 'c': 2}, {'name': '浙江出版社', 'c': 1}]>

例2:查询每一个作者的名字以及他们出版过书籍的最高价格

    sql语句:  select name,MAX(price) from my_book_book inner join my_book_book_author on my_book_book.id = my_book_book_author.book_id inner join my_book_author on my_book_book_author.author_id = my_book_author.aid group by aid
    
    pub = models.Author.objects.values("pk").annotate(c = Max("book__price")).values("name","c")
    #models.Author.objects.values("pk").annotate(c = Max("book__price"))等于是把author ,book author_book三张表join成了一张,后面可以选择要显示的内容
    #<QuerySet [{'name': 'blex', 'c': Decimal('28.23')}, {'name': 'clex', 'c': None}, {'name': 'alex', 'c': Decimal('28.23')}]>

例3: 查询每一个书籍的名称和对应的作者个数

  sql语句:
    #select title,count(aid) as "作者个数" from my_book_book inner join my_book_book_author  on my_book_book.id = book_id inner join my_book_author on my_book_book_author.author_id = my_book_author.aid group by book_id;

    pub = models.Book.objects.values("pk").annotate(c = Count("author__aid")).values("title","c")
    #Count("author__aid")这里的author是字段不是表名,因为book下本来就有author字段,正向查找按字段
    #<QuerySet [{'title': 'python', 'c': 2}, {'title': 'java', 'c': 2}, {'title': 'c++', 'c': 0}, {'title': 'go', 'c': 0}, {'title': 'c#', 'c': 0}, {'title': 'php', 'c': 0}]>
    #我们也可以通过book查其他表下的字段,语法: 字段(表名)__字段
    pub=models.Book.objects.values("pk").annotate(c=Count("author__name")).filter(c__gt=1).values("title","publish__name")