postgresql Django：按月查询组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37851053/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Django: Query Group By Month
提问by aldesabido
How to calculate total by month without using extra?
如何在不使用额外费用的情况下按月计算总数？
I'm currently using:
我目前正在使用：
- django 1.8
- postgre 9.3.13
- Python 2.7
- Django 1.8
- postgre 9.3.13
- 蟒蛇 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.
还有这个，答案看起来不错，但我无法导入 TruncMonth 模块。
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.
PS我知道这个问题已经被问过多次，但我没有看到任何答案。
Thanks!
谢谢！
SOLUTION:
解决方案：
Thanks to @Vin-G's answer.
感谢@Vin-G 的回答。
回答by Vin-G
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
之后你需要做的就是
- annotate each row with the month
- group the results by the annotated month using
values() - annotate each result with the aggregated sum of the totals using
Sum()
- 用月份注释每一行
- 使用带注释的月份对结果进行分组
values() - 使用总计的汇总总和注释每个结果
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
重要提示：如果您的模型类在元选项中指定了默认排序，则您必须添加一个空order_by()子句。这是因为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 thevalues()call. These extra fields are used to group “like” results together and they can make otherwise identical result rows appear to be separate.
order_by()选择输出数据时会使用查询集部分中提到的字段（或在模型的默认排序中使用的字段），即使它们没有在values()调用中以其他方式指定。这些额外的字段用于将“相似”的结果分组在一起，它们可以使其他相同的结果行看起来是分开的。
If you are unsure, you could just add the empty order_by()clause anyway without any adverse effects.
如果您不确定，您可以添加空order_by()子句，而不会产生任何不利影响。
i.e.
IE
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
在此处查看完整要点：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
有关创建自己的 Func 类的详细信息：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
values() 子句的详细信息，（注意它如何与 annotate() 就子句的顺序进行交互）：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.
annotate() 和 values() 子句应用于查询的顺序很重要。如果 values() 子句在 annotate() 之前，则将使用 values() 子句描述的分组来计算注释。
回答by schwobaseggl
itertools.groupbyis the performant option in Python and can be utilized with a single db query:
itertools.groupby是 Python 中的高性能选项，可以与单个 db 查询一起使用：
from itertools import groupby
invoices = Invoice.objects.only('date', 'total').order_by('date')
month_totals = {
k: sum(x.total for x in g)
for k, g in groupby(invoices, key=lambda i: i.date.month)
}
month_totals
# {1: 100, 3: 100, 4: 500, 7: 500}
I am not aware of a pure django ORM solution. The date__monthfilter is very limited and cannot be used in values, order_by, etc.
我不知道纯 django ORM 解决方案。该date__month过滤器是非常有限的，并且不能被使用在values，order_by等
回答by thosimo
result = (
invoices.objects
.all()
.values_list('created_at__year', 'created_at__month')
.annotate(Sum('total'))
.order_by('created_at__year', 'created_at__month')
)
回答by Papouche Guinslyzinho
I don't know if my solution is faster than your. You should profile it. Nonetheless I only query the db once instead of 12 times.
我不知道我的解决方案是否比你的更快。您应该对其进行概要分析。尽管如此，我只查询数据库一次而不是 12 次。
#utils.py
from django.db.models import Count, Sum
def get_total_per_month_value():
"""
Return the total of sales per month
ReturnType: [Dict]
{'December': 3400, 'February': 224, 'January': 792}
"""
result= {}
db_result = Sale.objects.values('price','created')
for i in db_result:
month = str(i.get('created').strftime("%B"))
if month in result.keys():
result[month] = result[month] + i.get('price')
else:
result[month] = i.get('price')
return result
#models.py
class Sale(models.Model):
price = models.PositiveSmallIntegerField()
created = models.DateTimeField(_(u'Published'), default="2001-02-24")
#views.py
from .utils import get_total_per_month_value
# ...
result = get_total_per_month_value()
test.py
测试文件
#
import pytest
from mixer.backend.django import mixer
#Don't try to write in the database
pytestmark = pytest.mark.django_db
def test_get_total_per_month():
from .utils import get_total_per_month_value
selected_date = ['01','02','03','01','01']
#2016-01-12 == YYYY-MM-DD
for i in selected_date:
mixer.blend('myapp.Sale', created="2016-"+i+"-12")
values = get_total_per_month_value() #return a dict
months = values.keys()
assert 'January' in months, 'Should include January'
assert 'February' in months, 'Should include February'
assert len(months) == 3, 'Should aggregate the months'
回答by shacker
Don't forget that Django querysets provide a native datetimesmanager, which lets you easily pull all of the days/weeks/months/years out of any queryset for models with a datetime field. So if the Invoicemodel above has a createddatetime field, and you want totals for each month in your queryset, you can just do:
不要忘记 Django 查询集提供了一个本机datetimesmanager，它可以让您轻松地从具有日期时间字段的模型的任何查询集中提取所有天/周/月/年。因此，如果Invoice上面的模型有一个created日期时间字段，并且您想要查询集中每个月的总数，您可以这样做：
invoices = Invoice.objects.all()
months = invoices.datetimes("created", kind="month")
for month in months:
month_invs = invoices.filter(created__month=month.month)
month_total = month_invs.aggregate(total=Sum("otherfield")).get("total")
print(f"Month: {month}, Total: {month_total}")
No external functions or deps needed.
不需要外部函数或 deps。
