django 聚合aggregate,分组annotate,F,Q查询

一、聚合查询 aggregate( *args,**kwargs)

1.基本介绍

介绍：聚合查询通常情况下都是配合分组一起使用的. 如果你只想使用聚合函数, 但是不想分组, 那么就应该使用aggregate.

使用：直接在objects后面链接.

返回：返回字典格式的数据.返回的字典中：键的名称默认是（属性名称加上__聚合函数名），值是计算出来的聚合值。例如:如果是对price字段求平均值, 那么返回格式是: {'price__avg': 值} ,

和数据库相关模块的方法：
    基本是都在django.db.models里面
    如果上述没有那么应该在django.db里面

2.五种聚合函数

• Avg (Average) : 平均值
• Max (Maximum) : 最大值
• Min (Minimum) : 最小值
• Sum (Summary) : 求和
• Count : 个数

3.aggregate()

• aggregate()是QuerySet 的一个终止子句，意思是说，它返回一个包含一些键值对的字典
• 键的名称是聚合值的标识符，值是计算出来的聚合值
• 键的名称是按照字段和聚合函数的名称自动生成出来的
• 如果你想要为聚合值指定一个名称，可以向聚合子句提供它
• 使用 aggregate() 后，数据类型就变为字典，不能再使用 QuerySet 数据类型的一些 API 了。

from django.db.models import Avg,Max,Min,Sum,Count  # 导入聚合函数

res = models.Book.objects.aggregate(Avg('price')) 
print(res)  # {'price__avg': Decimal('34.204000')}

res = models.Book.objects.aggregate(avg_price=Avg('price'))  # 指定名称
print(res)  # {'avg_price': Decimal('34.204000')}

4.示例

• 求所有数据价钱最高的, 最低的, 总和, 平均价钱

from django.db.models import Max,Min,Sum,Avg
res = models.Book.objects.aggregate(Max('price'),Min('price'),Sum('price'),Avg('price'))
print(res)  # {'price__max': Decimal('88.88'), 'price__min': Decimal('6.66'), 'price__sum': Decimal('171.02'), 'price__avg': Decimal('34.204000')}

二、分组查询 annotate

1.介绍

分组注意事项

• 分组只能拿到分组得依据. 按照什么分组就只能拿到什么组. 其他字段不能直接获取, 需要借助一些方法(聚合函数)

• 提示: 可以指定多个分组, 指定多个分组, 当然就可以获取多个分组之间的分组依据.

  MySQl中设置全局生效得分组严格模式: set global sql_mode='only_full_group_by';

使用步骤：

    第一步: 指定分组的依据
        第一种情况: 默认分组. annotate直接在objects后面链接时, models后面点什么就按照什么分组.
            例子: 按照书分组
            models.Book.objects.annotate(sum_price=Sum)
        第二种情况: 指定分组. annotate跟在values后面, values中指定什么字段就按照什么分组
            例子: 按照书中的价格分组. 
            models.Book.objects.values('price').annotate()
    第二步: 为分组的字段取别名
    第三步: 在annotate后面使用values可以获取分组的依据 和 分组举和的结果

返回：返回QuerySet对象

• 提示: 只要你的orm语句得出的结果还是一个queryset对象, 那么它就可以继续无限制的点queryset对象封装的方法.

特殊情况使用

• 使用Count进行获取分组举和的结果应该是要对主键 或者 唯一字段进行的统计. 所以使用Count进行统计时比如: authors__id 就可以写成 authors

2.分组依据

• values( ) 在 annotate( ) 之前则表示 group by 字段

# 默认分组依据
    如果 annotate() 直接跟在 objects 后面,则表示直接以当前的基表为分组依据
    例 : 按书来分组 : odels.Book.objects.annotate(sum_price=Sum)
# 指定分组依据
    如果 annotate() 跟在 values() 后面,则表示按照values中指定的字段来进行分组
    例 : 按照书中的price进行分组 : models.Book.objects.values('price').annotate()

• values( ) 在 annotate( ) 之后则表示取字段
• filter( ) 在 annotate( ) 之前则表示 where 条件
• filter( ) 在 annotate( ) 之后则表示 having 条件

3.示例

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

# 查询出版社id大于1的出版社id，以及出书平均价格
res = models.Book.objects.values('publish_id').filter(publish_id__gt=1).annotate(avg_price=Avg('price')).values('publish_id','avg_price')
print(res)  # <QuerySet [{'publish_id': 2, 'avg_price': Decimal('31.635000')}, {'publish_id': 3, 'avg_price': Decimal('9.435000')}]>

# 查询出版社id大于1的出版社id，以及出书平均价格大于25的
res = models.Book.objects.values('publish_id').filter(publish_id__gt=1).annotate(avg_price=Avg('price')).filter(avg_price__gt=25).values('publish_id','avg_price')
print(res)  # <QuerySet [{'publish_id': 2, 'avg_price': Decimal('31.635000')}]>

# 查询每一个出版社出版的名称和书籍个数(连表)(连表最好以group by的表作为基表)
res = models.Publish.objects.annotate(count_book=Count('book')).values('name','count_book')
print(res)  # <QuerySet [{'name': '沙河尚出版社', 'count_book': 1}, {'name': '北京出版社', 'count_book': 2}, {'name': '狗不理出版社', 'count_book': 2}]>

# 上面以Book作为基表
res = models.Book.objects.values('publish_id').annotate(book_count=Count('nid')).values('publish__name','book_count')
print(res)  # <QuerySet [{'publish__name': '沙河尚出版社', 'book_count': 1}, {'publish__name': '北京出版社', 'book_count': 2}, {'publish__name': '狗不理出版社', 'book_count': 2}]>

# 查询每个作者的名字，以及出版过书籍的最高价格(建议使用分组的表作为基表)
# 多对多如果不以分组的表作为基表, 可能会出现数据问题
res = models.Author.objects.annotate(max_price=Max('book__price')).values('name','max_price')
print(res)  # <QuerySet [{'name': 'shawn', 'max_price': Decimal('88.88')}, {'name': 'chris', 'max_price': Decimal('88.88')}, {'name': '小rub', 'max_price': Decimal('55.50')}, {'name': 'summer', 'max_price': Decimal('12.21')}]>
res = models.Book.objects.values('authors__nid').annotate(max_price=Max('price')).values('authors__name','max_price')
print(res)  # <QuerySet [{'authors__name': 'shawn', 'max_price': Decimal('88.88')}, {'authors__name': 'chris', 'max_price': Decimal('88.88')}, {'authors__name': '小rub', 'max_price': Decimal('55.50')}, {'authors__name': 'summer', 'max_price': Decimal('12.21')}]>

# 查询每一个书籍的名称, 以及对应的作者个数
res = models.Book.objects.annotate(auth_count=Count('authors__nid')).values('title','auth_count')
print(res)  # <QuerySet [{'title': '北京爱情故事', 'auth_count': 2}, {'title': '水浒传', 'auth_count': 2}, {'title': '三国演义', 'auth_count': 1}, {'title': '红楼梦', 'auth_count': 3}, {'title': '包子秘籍', 'auth_count': 2}]>

# 统计价格25元, 作者个数大于1的图书
res = models.Book.objects.filter(price__gt=25).annotate(auth_count=Count('authors__nid')).values('title','auth_count','price')
print(res)  # <QuerySet [{'title': '北京爱情故事', 'price': Decimal('88.88'), 'auth_count': 2}, {'title': '红楼梦', 'price': Decimal('55.50'), 'auth_count': 3}]>

三、F 查询

1.F查询介绍

• 我们之前的例子中对一些字段的过滤和操作都是与一个常量进行比较大小, 如果我们想让字段与字段的值进行比较就无法简单实现, 于是就可以使用 F 查询了
• 作用 : 取出某个字段对应的值

2.使用示例

"""
# 作用: 能够帮助你直接获取到表中某个字段对应的数据
# 使用:
    from django.db.models import F
    # 获取到某个字段对应的数据   
        F("字段__条件")
    # 查询字段对应的数据是数字类型可以直接加减运算:
        F('字段__条件') + 500
    # 字符类型需要借助Concat和Value方法
        from django.db.models.functions import Concat
        from django.db.models import Value
        Concat(F('字段__条件'), Value("str"))
        注意: 查询字段对应的数据是字符类型不能直接进行拼接. 否则操作的字段对应的所有数据将变成空白.
"""

from django.db.models import F

# 1. 查询卖出数大于库存数的书籍
res = models.Book.objects.filter(sale__gt=F('stock'))
print(res)  # <QuerySet [<Book: 论语>, <Book: 老子>]>

# 2. 将所有书籍的价格提升500块
res = models.Book.objects.update(price=F('price') + 500)
print(res)  # .update方法返回受影响的行数  7

# 3. 将所有书的名称后面加上爆款两个字
from django.db.models.functions import Concat
from django.db.models import Value
models.Book.objects.update(title=Concat(F('title'), Value('爆款')))
# models.Book.objects.update(title=F('title') + '爆款')  # 这样指定的字段title所有对应的数据全部变成空白.

四、Q查询

1.Q查询介绍

• filter 的字段筛选条件如果指定多个, 默认是and连接多个条件, 如果想要使用or或者not,则需要Q查询
• 作用 : 构造 或 or、与 &、非 ~ 条件

2.示例

"""
# 作用: filter的字段筛选条件指定多个, 默认是and连接. 要实现or或者not需要借助Q查询
# 使用: 
    from django.db.models import Q
    Q(字段__条件=值)
    # 连接条件and的3种情况
        1. filter中指定多个参数逗号隔开: filter(参数1, 参数2)
        2. 查询指定多个逗号隔开: filter(Q(), Q())
        3. 使用&连接符: filter(Q() & Q())
    # 连接条件or
        filter(Q() | Q())
    # 连接条件not
        filter(~Q() | Q()) 
    # Q查询的高阶用法: 让左边指定的变量形式的查询条件可以是字符串
        q = Q()
        q.connecter = 'or'  # 指定连接符. 不指定默认and
        q.children.append(Q('字段__条件', 值))  
        res = models.XXX.objects.filter(q)
"""

from django.db.models import Q
# 1. 查询卖出数大于100 和 价格小于900的书籍 --> 连接条件 and
# res = models.Book.objects.filter(sale__gt=100, price__lt=900)
# res = models.Book.objects.filter(Q(sale__gt=100), Q(price__lt=900))
res = models.Book.objects.filter(Q(sale__gt=100) & Q(price__lt=900))
print(res)  # <QuerySet [<Book: 论语爆款>]>

# 2. 查询卖出数大于100或者价格小于600的书籍 --> 连接条件 or
res = models.Book.objects.filter(Q(sale__gt=100) | Q(price__lt=600))
print(res)  # <QuerySet [<Book: 红龙盟爆款>, <Book: 老子爆款>, <Book: 论语爆款>, <Book: 孟子爆款>, <Book: 老子爆款>, <Book: 三字经爆款>]>

# 3. 查询卖出数不大于100或者价格小于600的书籍 --> 连接条件 not
res = models.Book.objects.filter(~Q(sale__gt=100) | Q(price__lt=600))
print(res)   # <QuerySet [<Book: 夕阳西下爆款>]>

# 4. Q的高阶用法: 能够将查询条件的左边变量的形式变成字符串的形式
q = Q()               # 第一步: 实例化一个q对象
q.connector = 'or'    # 第二步: 定义连接条件
q.children.append(('sale__gt', 100))    # 第三步: 指定字符串形式的查询字段条件, 以及范围100
q.children.append(('price__lt', 600))
res = models.Book.objects.filter(q)     # 第四步: 将q对象传入filter
print(res)