django聚合查询与分组查询

目录

• django聚合查询与分组查询
  • 例题
    • 聚合、分组查询
    • Count( )例子
    • order_by()
    • values()
    • annotate() 和 values() 的顺序
  • 分组加聚合

django聚合查询与分组查询

例题

聚合、分组查询

# 所有书的平均价格
avg_query = Book.objects.all().aggregate(Avg("price"))
print(avg_query)  # {'price__avg': Decimal('93.6888888888888889')}

# 作者jason写的书的最大价格
max_query = Book.objects.filter(authors__name="jason").aggregate(Max("price"))
print(max_query)  # {'price__max': Decimal('151.00')}

# 所有书的价格的最大值和最小值的差值
query = Book.objects.aggregate(
  price_diff=Max('price', output_field=IntegerField()) - Min('price', output_field=IntegerField())
)
print(query)  # {'price_diff': 551}  output_field参数可以指定返回的字段类型

# 以出版社分组，第一个出版社下出版了多少种书
query = Publish.objects.annotate(num_books=Count('book'))
print(query)  # <QuerySet [<Publish: 东方出版社>, <Publish: 南方出版社>, <Publish: 西方出版社>, <Publish: 北方出版社>]>
print(query[0].num_books)  # 3

# 以出版社分组，第一个出版社下出版书的最低价格
query = Publish.objects.annotate(min_price=Min('book__price'))
print(query[0].min_price)  # 11.00

# 只有一个作者的书(生成的新字段是可以当作过滤条件的，甚至可以用双下划线)
query = Book.objects.annotate(num_authors=Count('authors')).exclude(num_authors__gt=1)
print(query)  # <QuerySet [<Book: python开发>, <Book: 西游记>]>

# 查询每个作者出的书的总价格
query = Author.objects.annotate(sum_price=Sum('book__price'))
print(query[0], query[0].sum_price)  # jason 195.00

# 以出版社分组，查出评级>3的书的数量和评级<=3的数量
'''
        Count这里的用法，仅仅是在分组查询的时候多加过滤条件，
        这里above_3就是计算书的数量，什么书？评级大于3的书的数量，
        并且必须结合Q一起使用， above_3其实就只是一个参数当作筛选条件，
        如果在使用多个聚合时，并且聚合的字段是来自不同的表，那么Count
        最好加上distinct=True，来确保数据的正确，因为底层使用的是join
        而不是子查询，所以可能会出现重复的数据
'''
above_3 = Count('book', filter=Q(book__rating__gt=3))
below_3 = Count('book', filter=Q(book__rating__lte=3))
# 这里其实就是按出版社分组，查出两个字段，相当于annotate两个连用
query = Publish.objects.annotate(below_3=below_3, above_3=above_3)
print(query, query[0].above_3, query[0].below_3)  # <QuerySet [<Publish: 东方出版社>, <Publish: 南方出版社>, <Publish: 西方出版社>, <Publish: 北方出版社>]> 1 2
# 可以这么写
query = Publish.objects.annotate(
  above_3=Count('book', filter=Q(book__rating__gt=3)),
  below_3=Count('book', filter=Q(book__rating__lte=3))
)
# 也可以按下面的方式写，只是要分成两个步骤
query1 = Publish.objects.filter(book__rating__gt=3).annotate(num_books=Count('book'))
print(query1, query1[0].num_books)  # 每个出版社评级大于3的书的数量
query2 = Publish.objects.filter(book__rating__lte=3).annotate(num_books=Count('book'))
print(query2, query2[0].num_books)  # 每个出版社评级小于等于3的书的数量

# 查出3种类型的卡的数量
# 方法一
res = Card.objects.aggregate(
            card_type_1=Count('pk', filter=Q(card_type=1)),
            card_type_2=Count('pk', filter=Q(card_type=2)),
            card_type_100=Count('pk', filter=Q(card_type=100)),
        )
{'card_type_1': 2, 'card_type_2': 7, 'card_type_100': 1}
# 方法二
res = Card.objects.values("card_type").annotate(num=Count("pk")).values("card_type", 'num')
<QuerySet [{'card_type': 1, 'num': 2}, {'card_type': 2, 'num': 7}, {'card_type': 100, 'num': 1}]>

# 以出版社分组，查出书的数量，并且按数量排序，取前三名出版社的数据
query = Publish.objects.annotate(num_book=Count('book')).order_by('-num_book')[:3]
print(query, query[0].num_book)  # <QuerySet [<Publish: 东方出版社>, <Publish: 西方出版社>, <Publish: 南方出版社>]> 3

Count( )例子

# 每个 account_type 有多少个客户
Client.objects.create(
...     name='Jean Grey',
...     account_type=Client.REGULAR,
...     registered_on=date.today())
>>> Client.objects.create(
...     name='James Bond',
...     account_type=Client.PLATINUM,
...     registered_on=date.today())
>>> Client.objects.create(
...     name='Jane Porter',
...     account_type=Client.PLATINUM,
...     registered_on=date.today())
>>> # Get counts for each value of account_type
>>> from django.db.models import Count
>>> Client.objects.aggregate(
  			# 多个regular字段及结果，计算account_type=regular的数量
...     regular=Count('pk', filter=Q(account_type=Client.REGULAR)),
  			# 多个gold字段及结果， 计算account_type=gold的数量
...     gold=Count('pk', filter=Q(account_type=Client.GOLD)),
  			# 多个platinum字段及结果， 计算account_type=platinum的数量
...     platinum=Count('pk', filter=Q(account_type=Client.PLATINUM)),
... )
{'regular': 2, 'gold': 1, 'platinum': 3}

order_by()

注解可以当做基本排序来使用。当你定义了一个 order_by() 子句，你提供的聚合可以引用任何定义为查询中 annotate() 子句的一部分的别名。

比如，通过书籍的作者数量来对书籍的 QuerySet 排序，你可以使用下面的查询：

>>> Book.objects.annotate(num_authors=Count('authors')).order_by('num_authors')

values()

通常，注解值会添加到每个对象上，即一个被注解的 QuerySet 将会为初始 QuerySet 的每个对象返回一个结果集。然而，当使用 values() 子句来对结果集进行约束时，生成注解值的方法会稍有不同。不是在原始 QuerySet 中对每个对象添加注解并返回，而是根据定义在 values() 子句中的字段组合先对结果进行分组，再对每个单独的分组进行注解，这个注解值是根据分组中所有的对象计算得到的。

下面是一个关于作者的查询例子，查询每个作者所著书的平均评分：

>>> Author.objects.annotate(average_rating=Avg('book__rating'))

这段代码返回的是数据库中的所有作者及其所著书的平均评分。

但是如果你使用 values() 子句，结果会稍有不同：

>>> Author.objects.values('name').annotate(average_rating=Avg('book__rating'))

在这个例子中，作者会按名字分组，所以你只能得到不重名的作者分组的注解值。这意味着如果你有两个作者同名，那么他们原本各自的查询结果将被合并到同一个结果中；两个作者的所有评分都将被计算为一个平均分。

annotate() 和 values() 的顺序

和使用 filter() 一样，作用于某个查询的 annotate() 和 values() 子句的顺序非常重要。如果 values() 子句在 annotate() 之前，就会根据 values() 子句产生的分组来计算注解。

然而如果 annotate() 子句在 values() 之前，就会根据整个查询集生成注解。这种情况下，values() 子句只能限制输出的字段。

举个例子，如果我们颠倒上个例子中 values() 和 annotate() 的顺序：

>>> Author.objects.annotate(average_rating=Avg('book__rating')).values('name', 'average_rating')

这段代码将为每个作者添加一个唯一注解，但只有作者姓名和 average_rating 注解会返回在输出结果中。

你应该也会注意 average_rating 已经明确包含在返回的值列表中。这是必需的，因为 values() 和 annotate() 子句的顺序。

如果 values() 子句在 annotate() 子句之前，任何注解将自动添加在结果集中。然而，如果 values() 子句应用在 annotate() 子句之后，则需要显式包含聚合列。

分组加聚合

你也可以在注解结果上生成聚合。当你定义 aggregate() 子句时，你提供的聚合可以引用任何定义在查询中 annotate() 子句的别名。

比如，如果你想计算每本书的平均作者数，首先使用作者数注解书籍集合，然后引用注解字段聚合作者数：

>>> from django.db.models import Avg, Count
>>> Book.objects.annotate(num_authors=Count('authors')).aggregate(Avg('num_authors'))
{'num_authors__avg': 1.66}