聚合查询, 分组查询,F&Q查询,orm的优化

关于Django中关于ORM更多查询表达式,详情见中文文档

 聚合函数

在MySQL中我们知道聚合函数有下面这几种？

avg():返回指定组中的平均值

count():返回指定组中项目的总数量　　

max():返回指定组中的数据最大值

min():返回指定组中的数据最小值

sum():返回指定组中的数据和

在Django中我们需要使用聚合函数就要放在aggregate函数里来计算。

aggregate(*args,**kwargs) :仅仅是一个聚合,并没有groupby 分组功能

通过对QuerySet进行计算，返回一个聚合值的字典。aggregate()中每一个参数都指定一个包含在字典中的返回值。即在查询集上生成聚合。

from django.db.models import Avg,Min,Sum,Max    #首先导入模块,注意大写

#从整个查询集生成统计值。比如，你想要计算所有在售书的平均价钱。Django的查询语法提供了一种方式描述所有图书的集合。

Book.objects.all().aggregate(Avg('price'))      
# {'price__avg': 34.35}
 
# aggregate()子句的参数描述了我们想要计算的聚合值，在这个例子中，是Book模型中price字段的平均值
 
# aggregate()是QuerySet 的一个终止子句，意思是说，它返回一个包含一些键值对的字典。键的名称是聚合值的标识符，值是计算出来的聚合值。键的名称是按照字段和聚合函数的名称自动生成出来的。
如果你想要为聚合值指定一个名称，可以向聚合子句提供它:
Book.objects.aggregate(average_price=Avg('price'))      
# {'average_price': 34.35}

# 如果你也想知道所有图书价格的最大值和最小值，可以这样查询：
Book.objects.aggregate(Avg('price'), Max('price'), Min('price'))
# {'price__avg': 34.35, 'price__max': Decimal('81.20'), 'price__min': Decimal('12.99')}

通过上面的介绍，我们可以知道，aggregate的逻辑比较简单，应用场景比较窄，如果你想要对数据进行分组（GROUP BY）后再聚合的操作，则需要使用annotate来实现。

分组聚合函数

annotate(*args,**kwargs) :   拥有分组和聚合的功能。它是和values合作实现分组聚合，values后边跟分组依据

　可以通过计算查询结果中每一个对象所关联的对象集合，从而得出总计值(也可以是平均值或总和)，即为查询集的每一项生成聚合。最终得到的是特殊的Queryset,这个结果只包含value中的字段和聚合函数

以前我们用aggregate只能查询alex出的书总聚合     

 

查询各个作者出的书的总价格,这里就涉及到分组了，values后边跟分组条件是authors__name ,其实这里就像groupby authors_name 一样,用values进行分组然后再聚合

 

查询各个出版社最便宜的书价是多少，annotate可以起别名

annotate正向查询

 上面那个例子是正向查询，还是上面那个句子我们来反向查询一下也就是用出版社来查询，这时候values后面就不是跟的分组条件，这时候分组条件就变成了出版社的ID了：

publisher.objects.annotate(a=Max(book__price)).values("a)

我们可以看到反向查询也是只能得到分组条件和聚合函数的值，其他的值都不能得到,会报错

比如我们用反向查询获取各个出版社最便宜的书价是多少,书的名字

# publisher.objects.annotate(a=Max(book__price)).values("a，”book__name")

你会得到这个结果，因为：sqlmdoe = only_full_group_by

django.db.utils.InternalError: (1055, "Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 
'text1.mytext.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by")

这是orm🔐解决不了的问题

我们需要写原生sql,

思路

1.关闭sqlmdoe = only_full_group_by

2.写原生SQL先对价格进行排序（order_by）生成一个新表，然后再对这个新表进行分组

 

注意当你限定条件(filter)去聚合时,不能用annotate 函数,应该用aggregate 函数,因为限定条件并不是分组 例如:

 例题:

#查询每个出版社出版了几本书
  data =models.Publish.objects.values().annotate(Count("book__id")) 得到的函数也是一个Queryset
  print((data))

　结果:

　

 F&Q查询

F( )查询

一个 F()对象代表了一个model的字段值或注释列。 使用它就可以直接参考model的field和执行数据库操作而不用再把它们（model field）查询出来放到python内存中

F( ) ---- 专门取对象中某列数据的操作

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

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

示例1：

查询评论数大于收藏数的书籍

from django.db.models import F
models.Book.objects.filter(commnet_num__lt=F('keep_num'))

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

models.Book.objects.filter(commnet_num__lt=F('keep_num')*2)

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

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

引申：

如果要修改char字段咋办？

如：把所有书名后面加上(第一版)

>>> from django.db.models.functions import Concat
>>> from django.db.models import Value
>>> models.Book.objects.all().update(title=Concat(F("title"), Value("("), Value("第一版"), Value(")")))

F()表达式的效率上的优点主要体现在

• 直接通过数据库操作而不是Python
• 减少数据库查询次数

Q( )查询

Q( ) ---- 用于对对象的复杂查询

filter() 等方法中的关键字参数查询都是一起进行“AND” 的。 如果你需要执行更复杂的查询（例如OR语句），你可以使用Q对象。

Q() 对象和F 对象类似，把一个SQL表达式封装在Python 对象中，这个对象可以用于数据库相关的操作。

通常，Q()对象使得定义查询条件然后重用成为可能。 它允许使用|（OR）和&（AND）和~（not）操作构建复杂的数据库查询；否则在特定情况下，在QuerySets使用不了OR。

示例1：

查询作者名是小仙女或小魔女的

   from django.db.models import Q

models.Book.objects.filter(Q(authors__name="小仙女")|Q(authors__name="小魔女"))

你可以组合& 和|  操作符以及使用括号进行分组来编写任意复杂的Q 对象。同时，Q 对象可以使用~ 操作符取反，这允许组合正常的查询和取反(NOT) 查询。

示例：查询作者名字是小仙女并且不是2018年出版的书的书名。

>>> models.Book.objects.filter(Q(author__name="小仙女") & ~Q(publish_date__year=2018)).values_list("title")
<QuerySet [('番茄物语',)]>

查询函数可以混合使用Q 对象和关键字参数。所有提供给查询函数的参数（关键字参数或Q 对象）都将"AND”在一起。但是，如果出现Q 对象，它必须位于所有关键字参数的前面。

例如：查询出版年份是2017或2018，书名中带物语的所有书。

>>> models.Book.objects.filter(Q(publish_date__year=2018) | Q(publish_date__year=2017), title__icontains="物语")
<QuerySet [<Book: 番茄物语>, <Book: 香蕉物语>, <Book: 橘子物语>]>

 Q的高级应用:

Q在stark组件中的应用,主要用在搜索框中,得到or的关系

 def get_search_condition(self):
        """
        获得搜索框条件的函数
        :return: 返回一个包含搜索条件的Q对象
        """
        from django.db.models import Q
        search_condition = Q()  # 定义一个Q对象
        search_condition.connector = "or"  # 描述或的关系,默认是and的关系
        if self.search_filed:  # 如果默认搜索框中有内容
            keyword = self.request.GET.get("q") #q 是搜索框中的name,通过它来找到搜索框中的值
            if keyword:
                for fileds in self.search_filed:
                    search_condition.children.append((fileds + "__contains", keyword), )  # 两个值要放在元组中

search_condition = self.get_search_condition()  # 得到搜索框的Q对象
        get_filter_condition = self.get_filter_condition()  # 得到过滤条件Q对象
        queryset = self.model.objects.filter(search_condition).filter(get_filter_condition)  # 在搜索框的基础上再进行过滤框的查询,查询到对应的数据

 

orm的优化

select_related

减少数据库的查询次数，仅用于正向查询，

def select_related(self, *fields)
    性能相关：表之间进行join连表操作，一次性获取关联的数据。

    总结：
    1. select_related主要针一对一和多对一关系进行优化。主要
    2. select_related使用SQL的JOIN语句进行优化，通过减少SQL查询的次数来进行优化、提高性能。
　　3.仅用于正向查询

举例子:

查所有的书对应的出版社的名字

def index(request):
   data=models.Book.objects.all()
   for i in data:
       print(i.publishs.name)
   return HttpResponse('查询完毕')

 

结果:

我们可以看到进行了三次查询

(0.002) SELECT `app01_publish`.`id`, `app01_publish`.`name` FROM `app01_publish` WHERE `app01_publish`.`id` = 1; args=(1,)
(0.000) SELECT `app01_publish`.`id`, `app01_publish`.`name` FROM `app01_publish` WHERE `app01_publish`.`id` = 1; args=(1,)
(0.000) SELECT `app01_publish`.`id`, `app01_publish`.`name` FROM `app01_publish` WHERE `app01_publish`.`id` = 2; args=(2,)
[02/Sep/2018 12:23:10] "GET /app01/index/ HTTP/1.1" 200 12
北京出版社
北京出版社
山东出版社

分析:

这种写法符合逻辑，但是在性能上却是十分低下，原因在于，虽然我们使用all()获得了查询集data，然后使用for遍历data

(求值)，只进行了一次数据库查询，但是在for循环体中print(i.publishs.name)会再次触发查询，前面讲到了，Django的外键关系也是惰性的，因此获取BooK对象的时候并没有去获取相应的Publish对象，
而是在真正使用的时候触发查询，也就是在打印publishs.name的时候，这时候会触发一次数据库查询去查找Book对应的Publish，而for在查询集data上一共循环了k次，因此一共导致了k+1次数据库查询。

 

 

 

我们用select_relate来查询下

def index(request):
   data=models.Book.objects.all().select_related('publishs')#select_related后边跟关联的字段
   for i in data:
       print(i.publishs.name)

   return HttpResponse('查询完毕')

 

结果:

只查询了一次

北京出版社

北京出版社

山东出版社
`app01_book`.`publishs_id`, `app01_publish`.`id`, `app01_publish`.`name` FROM `app01_book` INNER JOIN `app01_publish` ON (`app01_book`.`publishs_id` = 
`app01_publish`.`id`); args=()

prefetch_related 

 

def prefetch_related(self, *lookups)
    性能相关：多表连表操作时速度会慢，使用其执行多次SQL查询在Python代码中实现连表操作。

    总结：
    1. 对于多对多字段（ManyToManyField）和一对多字段(主要用在一对多的反向查询中)，可以使用prefetch_related()来进行优化。
    2. prefetch_related()的优化方式是分别查询每个表，然后用Python处理他们之间的关系。

 

与select_related类似，prefetch_related也可以大幅提高查询效率，但是prefetch_related的方式跟select_related大不一样。select_reateld是通过创建一条包含SQL join操作的SELECT语句来一次性获得所有相关对象的信息。因此，select_related需要从同一个数据库中获得相关对象。但是，为了避免由于join操作带来的较大的查询集结果，select_related被限制在了单值关系——外键关系或一对一关系。

另一方面，prefetch_related为每一个关系使用了单独的查询，并在Python层面进行’join’操作，因此该操作允许多对多关系以及反向关系，而这是select_related无法做到的。我们这次使用prefetch_related执行查询

 

举例子:

查每本书对应的作者

def index(request):
   data=models.Book.objects.all().prefetch_related('authors')
   for i in data:
       print(i.authors.all())

   return HttpResponse('查询完毕')

 

结果:

(0.000) SELECT `app01_book`.`id`, `app01_book`.`title`, `app01_book`.`publishs_id` FROM `app01_book`; args=()

(0.000) SELECT (`app01_book_authors`.`book_id`) AS `_prefetch_related_val_book_id`, `app01_author`.`id`, `app01_author`.`name`, `app01_author`
.`details_id` FROM `app01_author` INNER JOIN `app01_book_authors` ON (`app01_author`.`id` = `app01_book_authors`.`author_id`) WHERE `app01_book_authors`
.`book_id` IN (1, 2, 3); args=(1, 2, 3)

<QuerySet [<Author: 韩信>, <Author: 李白>]>
<QuerySet [<Author: 李白>]>
<QuerySet [<Author: 韩信>]>

不使用 prefetch_related

def index(request):
   data=models.Book.objects.all()
   for i in data:
       print(i.authors.all())
   return HttpResponse('查询完毕')

 

结果:

 

<QuerySet [<Author: 韩信>, <Author: 李白>]>
(0.000) SELECT `app01_book`.`id`, `app01_book`.`title`, `app01_book`.`publishs_id` FROM `app01_book`; args=()
(0.000) SELECT VERSION(); args=None
(0.000) SELECT `app01_author`.`id`, `app01_author`.`name`, `app01_author`.`details_id` FROM `app01_author` INNER JOIN `app01_book_authors` ON (`app01_author`.`id` = `app01_book_authors`.`author_id`) WHERE `app01_book_authors`.`book_id` = 1 LIMIT 21; args=(1,)
<QuerySet [<Author: 李白>]>
(0.001) SELECT `app01_author`.`id`, `app01_author`.`name`, `app01_author`.`details_id` FROM `app01_author` INNER JOIN `app01_book_authors` ON (`app01_author`.`id` = `app01_book_authors`.`author_id`) WHERE `app01_book_authors`.`book_id` = 2 LIMIT 21; args=(2,)
<QuerySet [<Author: 韩信>]>
(0.001) SELECT `app01_author`.`id`, `app01_author`.`name`, `app01_author`.`details_id` FROM `app01_author` INNER JOIN `app01_book_authors` ON (`app01_author`.`id` = `app01_book_authors`.`author_id`) WHERE `app01_book_authors`.`book_id` = 3 LIMIT 21; args=(3,)