如何在 Django ORM 中执行 GROUP BY ... COUNT 或 SUM? [英] How to execute a GROUP BY ... COUNT or SUM in Django ORM?

问题描述

序言:

这是 SO 中经常出现的问题:

This is a question arising often in SO:

• Django 模型分组依据
• Django 等效于计数和分组
• 如何在 django 中查询为 GROUP BY?
• 如何将 ORM 用于等效于 SQL 计数、分组和连接查询?

我已经编写了一个关于 SO 文档的示例，但由于该文档将于 2017 年 8 月 8 日关闭，我将遵循 这个广受赞誉和讨论的元答案 并将我的示例转换为自我回答的帖子.

I have composed an example on SO Documentation but since the Documentation will get shut down on August 8, 2017, I will follow the suggestion of this widely upvoted and discussed meta answer and transform my example to a self-answered post.

当然，我也很乐意看到任何不同的方法！！

Of course, I would be more than happy to see any different approach as well!!

问题:

假设模型:

class Books(models.Model):
    title  = models.CharField()
    author = models.CharField()
    price = models.FloatField()

如何使用 Django ORM 对该模型执行以下查询:

How can I perform the following queries on that model utilizing Django ORM:

• GROUP BY ... COUNT:

SELECT author, COUNT(author) AS count
FROM myapp_books GROUP BY author

GROUP BY ... SUM:

SELECT author,  SUM (price) AS total_price
FROM myapp_books GROUP BY author

推荐答案

我们可以执行一个 GROUP BY ... COUNT 或 GROUP BY ... SUM SQL对 Django ORM 的等效查询，使用 annotate(), values()，django.db.models 的Count 和 Sum 方法分别和可选的 order_by() 方法:

We can perform a GROUP BY ... COUNT or a GROUP BY ... SUM SQL equivalent queries on Django ORM, with the use of annotate(), values(), the django.db.models's Count and Sum methods respectfully and optionally the order_by() method:

• GROUP BY ... COUNT:

 from django.db.models import Count

 result = Books.objects.values('author')
                       .order_by('author')
                       .annotate(count=Count('author'))

现在结果包含一个带有两个键的字典:author 和 count:

Now result contains a dictionary with two keys: author and count:

   author    | count
 ------------|-------
  OneAuthor  |   5
 OtherAuthor |   2
    ...      |  ...

GROUP BY ... SUM:

 from django.db.models import Sum

  result = Books.objects.values('author')
                        .order_by('author')
                        .annotate(total_price=Sum('price'))

现在结果包含一个带有两列的字典:author 和 total_price:

Now result contains a dictionary with two columns: author and total_price:

   author    | total_price
 ------------|-------------
  OneAuthor  |    100.35
 OtherAuthor |     50.00
     ...     |      ...

更新 13/04/2021

正如@dgw 在评论中指出的那样，如果模型使用元选项对行进行排序(例如 ordering)，order_by() 子句对于成功至关重要聚合！

As @dgw points out in the comments, in the case that the model uses a meta option to order rows (ex. ordering), the order_by() clause is paramount for the success of the aggregation!

这篇关于如何在 Django ORM 中执行 GROUP BY ... COUNT 或 SUM?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！