聚合查询与分组查询

2.单表下的分组查询

准备工作（详细步骤略,如果需要请参照Django博客其他文章）

• 终端Mysql新建一个数据库，配置setting.py数据库的名称
• 在app01/models.py创建Emp表并进行表迁移(python manage.py makemigrations / python manage.py migrate)
• 在Emp表插入数据。

app01/models.py

from django.db import models

class Emp(models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=32)
    age = models.IntegerField()
    salary = models.DecimalField(max_digits=8,decimal_places=2)
    dep = models.CharField(max_length=32)
    province = models.CharField(max_length=32)

app01/views.py

from django.shortcuts import render,HttpResponse
from app01.models import * # 最好这样引入视图函数，避免models调用重名覆盖！
from django.db.models import Avg,Max,Min,Count


def query(request):
    """跨表查询
        1 基于对象查询
        2 基于双下划线查询
        3 聚合和分组查询
        4 F 与 Q 查询
    """
# ================== 分组查询 ===================
    #           annotate,返回值依然是queryset
    """
    annotate()为调用的QuerySet中每一个对象都生成一个独立的统计值（统计方法用聚合函数）。
    而 aggregate() 是聚合所有的对象？？
    """

    # ======= 单表的分组查询
    """
    app01_emp:
    id  name  age  salary dep    province
    1   alex  12   2000   教学部  四川
    2   egon  22   3000   讲师部  上海
    3   peiqi 22   5000   讲师部  北京

    插入数据
    Emp.objects.create(name="alex", age=12, salary=2000, dep="教学部", province="四川")
    Emp.objects.create(name="egon", age=22, salary=3000, dep="讲师部", province="上海")
    Emp.objects.create(name="peiqi", age=22, salary=5000, dep="讲师部", province="北京")

    查询每一个部门名称以及对应的员工数 sql:
    select Count(id) from app01_emp group by dep;

    如何用ORM 语法进行分组查询:
        单表模型.objects.values("group by的字段").annotate(聚合函数("统计字段"))
        注意：在单表下按照主键进行group by 是没有任何意义的。

    """
    # 示例一：查询每一个部门的名称以及员工的平均薪水
    # select dep,Avg(salary) from app01_emp group by dep;
    # 可以为分组自定义名字avg_salary ；返回值是QuerySet
    res = Emp.objects.values("dep").annotate(avg_salary=Avg('salary'))
    print(res)
    # <QuerySet [{'dep': '教学部', 'avg_salary': 2000.0}, {'dep': '讲师部', 'avg_salary': 4000.0}]>

    # 示例二：查询每一个省份的名称以及对应的员工数
    res = Emp.objects.values("province").annotate(count_number=Count("id"))
    print(res)
    # <QuerySet [{'province': '四川', 'count_number': 1}, {'province': '上海', 'count_number': 1}, {'province': '北京', 'count_number': 1}]>


    # 补充知识点
    ret = Emp.objects.all()
    # select * from app01_emp;

    ret = Emp.objects.all().values('name')
    # 等同于 Emp.objects.values('name')
    # select name from app01_emp;


    return HttpResponse("OK")

---

3.多表下的分组查询

表模型请参考多表操作
数据准备以及路径配置等等请参考 基于对象的跨表查询
其他配置请参考前面几小节的博客

app01/views.py

from django.shortcuts import render,HttpResponse
from app01.models import * # 最好这样引入视图函数，避免models调用重名覆盖！
from django.db.models import Avg,Max,Min,Count
def query(request):
    """跨表查询
        1 基于对象查询
        2 基于双下划线查询
        3 聚合和分组查询
        4 F 与 Q 查询
    """
   # ================= 多表(跨表)分组查询 ================================
    """
    思路：先join然后group py
    跨表分组查询的实用模型
        1:被分组字段所在的基表模型.objects.values("pk").annotate(聚合函数('关联表__统计字段')).value("取其他字段"...)
    其他玩法:被分组字段所在的基表模型.objects.annotate(聚合函数('关联表__统计字段')).value("取其他字段"...)
                                  -----.all().-----
    """
    # 使用之前的orm_multi数据库中的数据

    # 单表：查询每一个出版社出版的书籍个数
    res = Book.objects.values("publish_id").annotate(Count('nid'))
    print(res)

    # ===示例一：查询每一个出版社的名称以及出版社的书籍个数
    """原生SQL
    select     app01_publish.name,Count(app01_book.publish_id)
    from       app01_book
    inner join app01_publish
    on         app01_book.publish_id = app01_publish.nid
    group by   app01_publish.name;
    """
    # ORM实现跨表分组查询 (这里的values完成了select 和 join的功能)
    res = Book.objects.values("publish__name").annotate(Count("publish_id"))
    # 或者(好几没有写SQL，之前把Count搞错了，聚合函数是统计个数，不需被统计字段的值是一样的，因为它统计的是整个该对象下的该字段的个数)
    res = Publish.objects.values("name").annotate(c_count=Count("book__title"))
    print(res)
    # <QuerySet [{'publish__name': '成都中医药大学出版社', 'publish_id__count': 2}, {'publish__name': '电子科技大学出版社', 'publish_id__count': 2}]>

    """有个小问题： 跨表分组查询之后可以取到任意字段
    对于两个多对一关联表分组统计过后，如果想显示任意一张表的其他字段，也是可以的！()非严格模式，但是注意：这样操作
    对于一对多查询，显示与 分组条件 在同一张表的其他字段是有意义的
    但是如果显示 多关系 字段 与 聚合结果，使用跨表分组查询是多此一举的
    如下：这样是多此一举"""
    res = Publish.objects.values("nid").annotate(c_count=Count("book__title")).values("c_count","book__title")
    print(res)

    # ===示例二：查询每一个作者的名字以及出版过的书籍的最高价格
    """SQL
    select      app01_author.name,Max(app01_book.price)
    from        app01_book
    inner join  app01_book_authors
    on          app01_book.nid = app01_book_authors.book_id
    inner join  app01_author
    on          app01_author.nid = app01_book_authors.author_id
    group by    app01_author.nid;

    """
    # 方式一
    res = Book.objects.values("authors__nid").annotate(max_price=Max("price")).values("authors__name",'max_price')
    print(res)
    """
    <QuerySet [{'authors__name': 'alex', 'max_price': Decimal('999.99')},
               {'authors__name': 'egon', 'max_price': Decimal('999.99')},
               {'authors__name': 'peiqi', 'max_price': Decimal('888.88')},
               {'authors__name': None, 'max_price': Decimal('222.55')}]>

    """
    # 方式二
    res = Author.objects.values("nid").annotate(max_price=Max("book__price")).values("name","max_price")
    print(res)
    """
    <QuerySet [ {'name': 'alex', 'max_price': Decimal('999.99')},
                {'name': 'egon', 'max_price': Decimal('999.99')},
                {'name': 'peiqi', 'max_price': Decimal('888.88')},
                {'name': 'yuan', 'max_price': None}]>
    """

    # === 示例三：查询每个书籍的名称以及对应的作者个数
    res = Book.objects.values("title").annotate(count_author=Count("authors__nid"))
    print(res)
    """{'name': 'peiqi', 'max_price': Decimal('888.88')}, {'name': 'yuan', 'max_price': None}]>
<QuerySet [  {'title': 'Python', 'count_author': 2},
             {'title': 'JavaScript', 'count_author': 0},
             {'title': 'Vue', 'count_author': 1},
             {'title': 'Java', 'count_author': 1}]>
    原生SQL：
        select app01_book.title,Count(app01_author.nid)
        from        app01_book
        inner join  app01_book_authors
        on          app01_book.nid = app01_book_authors.book_id
        inner join  app01_author
        on          app01_author.nid = app01_book_authors.author_id
        group by    app01_book.title
        ;
    """

    # ======跨表分组查询的另一种玩儿法，可以根据多个字段组合进行分组
    # 示例
    res = Publish.objects.annotate(c_count=Count("book__title")).values("c_count","book__title")

    # ===== 小练习
    # 统计每一本以Py开头的书籍的作者的个数。
    res = Book.objects.filter(title__startswith="Py").annotate(count_author=Count("authors__nid")).values("title",'count_author')
    print(res)
    # <QuerySet [{'title': 'Python', 'count_author': 2}]>

    # 统计不止一个作者的书籍名称
    res = Book.objects.values("pk").annotate(count_book=Count("authors__name")).filter(count_book__gt=1).values("title")
    print(res) # <QuerySet [{'title': 'Python'}]>
    """原生SQL
    SELECT            `app01_book`.`title`
    FROM              `app01_book`
    LEFT OUTER JOIN   `app01_book_authors`
    ON               (`app01_book`.`nid` = `app01_book_authors`.`book_id`)
    LEFT OUTER JOIN   `app01_author`
    ON               (`app01_book_authors`.`author_id` = `app01_author`.`nid`)
    GROUP BY          `app01_book`.`nid`
    HAVING COUNT     (`app01_author`.`name`) > 1
    ORDER BY NULL
    LIMIT 21;
    """

    return HttpResponse("OK")

---

4.F查询与Q查询

F查询

• Django 提供 F() 来做这样的比较。F() 的实例可以在查询中引用字段，来比较同一个 model 实例中两个不同字段的值。
• Django 支持 F() 对象之间以及 F() 对象和常数之间的加减乘除和取模的操作。
• 修改操作也可以使用F函数,比如将每一本书的价格提高1元：

Q查询

• Q 对象可以使用& 和| 操作符组合起来。当一个操作符在两个Q 对象上使用时，它产生一个新的Q 对象
• Q 对象可以使用~ 操作符取反，这允许组合正常的查询和取反(NOT) 查询
• 查询函数可以混合使用Q 对象和关键字参数。所有提供给查询函数的参数（关键字参数或Q 对象）都将"AND”在一起。但是，如果出现Q 对象，它必须位于所有关键字参数的前面。

app01/views.py

from django.shortcuts import render,HttpResponse
from app01.models import * # 最好这样引入视图函数，避免models调用重名覆盖！
from django.db.models import Avg,Max,Min,Count
def query(request):
    # === F查询与Q查询 ===
    # 为Book表出插入两个字段comment_num  read_num (最好在终端中添加)

    # 场景：比如要查询一个字段的值大于另一个字段值，查询Book 表comment_num > read_num 记录
    # 荒唐的写法：Book.objects.filter(comment_num__gt=read_num) 不支持！

    # F查询 将字段当成一个变量，可以查询
    from django.db.models import F
    res = Book.objects.filter(comment_num__gt=F("read_num")).values("title",'comment_num','read_num')
    print(res)

    # 场景：把所有的书籍价格都提升1块钱
    Book.objects.all().update(price=F("price") + 1)


    # Q查询 将字段当成一个条件，可以嵌套
    from django.db.models import Q
    # 查询 title为Python 且 publish_id 为5 的nid
    res = Book.objects.filter(title="Python", publish_id=5).values('nid')
    res = Book.objects.filter(Q(title="Python") & Q(publish_id=5)).values('nid')

    # 查询 title为Python 或者 publish_id 为5 的nid
    res = Book.objects.filter(Q(title="Python") | Q(publish_id=5)).values('nid')

    # 查询 title不等于 Python的书籍 。~ 非操作
    res = Book.objects.filter(~Q(title="Python")).values('title')
    print(res)

    # 如果想过滤多个条件，要吧Q语句放在最前面
    res = Book.objects.filter(~Q(title="Python"),price__gt=200).values('title')


    return HttpResponse("OK")