Django：按月查询组 [英] Django: Query Group By Month

问题描述

我目前正在使用：

• django 1.8


• postgre 9.3.13


• Python 2.7

示例。

到目前为止我已经尝试过了。

 ＃对我而言不起作用不要介意，因为我不想使用额外的
 truncate_month = connection.ops.date_trunc_sql（'month'，'day'）
 invoices = Invoice.objects.filter（is_deleted = False，company = company）.extra（{'month'：truncate_month}）。values（'month'）。annotate（Sum（'total'））
 
 ---- 
 #It works但是我认为如果我在范围（1,13）中查询一个大的数据
为current_month，这太慢了：
 Invoice.objects.filter（date__month = current__mont h）.annotate（total = Sum（total））
  

而且这一个，答案似乎很好，但我无法导入TruncMonth模块。

解决方案

要创建一个可以为您提取月份的函数：

 从django.db导入模型
 from django.db .models import Func 
 
 class Month（Func）：
 function ='EXTRACT'
 template ='％（function）s（MONTH from％（expressions）s）'
 output_field = models.IntegerField（）
  

之后，您需要做的只是

1. 注释每一行


2. 使用值（）


3. 使用 Sum（）
$ b $使用总计的总和对每个结果进行注释b

重要：如果您的模型类在元选项中指定了默认排序，则必须添加一个空的 order_by（）子句。这是因为 https：// docs .djangoproject.com / en / 1.9 / topics / db / aggregation /＃interactive-with-default-ordering-or-order-by

当选择输出时，将使用在查询器（或模型中的默认排序中使用）的 order_by（）部分中提到的字段数据，即使它们没有在 values（）调用中另外指定。这些额外的字段用于将喜欢结果组合在一起，他们可以使相同的结果行看起来是分开的。

如果你不确定，你可以添加空的 order_by（）子句，无任何不利影响。

ie

 
 
 summary =（Invoice.objects 
 .annotate （m =月（'date'））
 .values（'m'）
 .annotate（total = Sum（'total'））
 .order_by（））
  

请参阅这里的全部要点： https://gist.github.com/alvingonzales/ff9333e39d221981e5fc4cd6cdafdd17

如果您需要更多信息：

创建自己的Func类的详细信息： htt ps：//docs.djangoproject.com/en/1.8/ref/models/expressions/#func-expressions

values（）子句的详细信息（关于子句顺序与注释相关的注意事项）：
https://docs.djangoproject.com/en/1.9/topics/db/aggregation/#values

将annotate（）和values（）子句应用于查询的顺序很重要。如果values（）子句位于annotate（）之前，则使用values（）子句描述的分组来计算注释。

How to calculate total by month without using extra?

I'm currently using:

• django 1.8
• postgre 9.3.13
• Python 2.7

Example.

What I have tried so far.

#Doesn't work for me but I don't mind because I don't want to use extra
truncate_month = connection.ops.date_trunc_sql('month','day')
invoices = Invoice.objects.filter(is_deleted = False,company = company).extra({'month': truncate_month}).values('month').annotate(Sum('total'))

----
#It works but I think that it's too slow if I query a big set of data
for current_month in range(1,13):
    Invoice.objects.filter(date__month = current__month).annotate(total = Sum("total"))

and also this one, the answer seems great but I can't import the TruncMonth module.

Django: Group by date (day, month, year)

---

P.S. I know that this question is already asked multiple times but I don't see any answer.

Thanks!

---

SOLUTION:

Thanks to @Vin-G's answer.

解决方案

First, you have to make a Function that can extract the month for you:

from django.db import models
from django.db.models import Func

class Month(Func):
    function = 'EXTRACT'
    template = '%(function)s(MONTH from %(expressions)s)'
    output_field = models.IntegerField()

After that all you need to do is

1. annotate each row with the month
2. group the results by the annotated month using values()
3. annotate each result with the aggregated sum of the totals using Sum()

Important: if your model class has a default ordering specified in the meta options, then you will have to add an empty order_by() clause. This is because of https://docs.djangoproject.com/en/1.9/topics/db/aggregation/#interaction-with-default-ordering-or-order-by

Fields that are mentioned in the order_by() part of a queryset (or which are used in the default ordering on a model) are used when selecting the output data, even if they are not otherwise specified in the values() call. These extra fields are used to group "like" results together and they can make otherwise identical result rows appear to be separate.

If you are unsure, you could just add the empty order_by() clause anyway without any adverse effects.

i.e.

from django.db.models import Sum

summary = (Invoice.objects
              .annotate(m=Month('date'))
              .values('m')
              .annotate(total=Sum('total'))
              .order_by())

See the full gist here: https://gist.github.com/alvingonzales/ff9333e39d221981e5fc4cd6cdafdd17

If you need further information:

Details on creating your own Func classes: https://docs.djangoproject.com/en/1.8/ref/models/expressions/#func-expressions

Details on the values() clause, (pay attention to how it interacts with annotate() with respect to order of the clauses): https://docs.djangoproject.com/en/1.9/topics/db/aggregation/#values

the order in which annotate() and values() clauses are applied to a query is significant. If the values() clause precedes the annotate(), the annotation will be computed using the grouping described by the values() clause.

这篇关于Django：按月查询组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！