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

问题描述

>

Django Models Group By

Django等同于计数和分组

如何在django中查询GROUP BY？

如何使用ORM作为SQL计数，组和连接查询的等价物？

我已经编写了SO文档的示例，但由于文档将于2017年8月8日关闭，因此我会遵循这个广泛的upvoted和讨论的元答案，并将我的例子转变为一个自我回复的帖子。

当然，我会很高兴看到任何不同的方法！

---

问题：

假设模型：

  class Books（models.Model）：
 title = models.CharField（）
 author = models.CharField（）
 price = models.FloatField（）
  

如何在使用Django ORM的模型上执行以下查询： b
$ b

• GROUP BY ... COUNT ：

    SELECT author，COUNT（author） AS count 
   FROM myapp_books GROUP BY author 
    




• GROUP BY ... SUM ：

    SELECT作者，SUM（价格）AS total_price 
   FROM myapp_books GROUP BY作者
    

解决方案

我们可以执行 GROUP BY ... COUNT 或 GROUP BY ... SUM code> Django ORM上的SQL等效查询，使用 annotate（） ， values（） ， django.db.models 的 Count 和 Sum 方法尊敬和可选地 order_by（） 方法：
$ b

• GROUP BY ... COUNT：

    result = Books.objects.values（'author'）
   .order_by（'author'）
   .annotate（count = Count（'author'））
    

  
  
  现在结果包含一个字典有两个键： author 和 count ：
  
  pre> author | count
------------ | -------
OneAuthor | 5
OtherAuthor | 2
... | ...





• GROUP BY ... SUM：

    result = Books.objects.values（'author'）
   .order_by（'author'）
   .annotate（total_price = Sum（'price'））
    

  
  
  现在结果包含字典有两列： author 和 total_price ：
  
  pre> author | total_price
------------ | -------------
OneAuthor | 100.35
OtherAuthor | 50.00
... | ...


Prologue:

This is a question arising often in SO:

• Django Models Group By
• Django equivalent for count and group by
• How to query as GROUP BY in django?
• How to use the ORM for the equivalent of a SQL count, group and join query?

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!!

---

Question:

Assume the model:

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

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
  

解决方案

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:

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

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

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

• GROUP BY ... SUM:

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

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

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

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