Django:按日期分组(日,月,年) [英] Django: Group by date (day, month, year)
问题描述
可能重复:
在Django中分组日期
修改:旧标题为
我有一个简单的模型:
pre>
class Order(models.Model):
created = model.DateTimeField(auto_now_add = True)
total = models.IntegerField()#货币值
我想输出一个月的细目:
- 一个月内有多少销售额(
COUNT
) - 值(
SUM
)
我不知道最好的方法攻击是这样的。我看到一些相当可怕的额外选择的查询,但我简单的想法告诉我,我可能会更好的只是迭代数字,从一个任意开始的年/月开始,直到我达到当月,抛出简单查询过滤该月份。更多的数据库工作 - 减少开发人员的压力!
什么是最有意义的?有没有一个很好的方法我可以拉回一个快速的数据表?或者我的肮脏的方法可能是最好的主意?
我正在使用Django 1.3。不确定他们最近是否向 GROUP_BY
添加了一个更好的方式。
p> Django 1.10及以上
Django文档列表 extra
as deprecated不久。 (感谢您指出@seddonym,@ Lucas03)。我打开了门票,这是jarshwah提供的解决方案。
from django.db.models.functions import TruncMonth
Sales.objects
.annotate(month = TruncMonth('timestamp'))#截断到月份并添加到选择列表
.values('month')#Group By month
.annotate(c = Count('id'))#选择分组的计数
。('month','c')#(可能是多余的,没有测试)select month and count
旧版本
从django.db导入连接
从django .db.models import Sum,Count
truncate_date = connection.ops.date_trunc_sql('month','created')
qs = Order.objects.extra({'month':truncate_date})
report = qs.values('month')。annotate(Sum('total'),Count ('pk'))order_by('month')
编辑
- 添加计数
- 添加了django> = 1.10
的信息
Possible Duplicate:
Grouping dates in Django
Edit: Old title was Django: Group sales by month
I've got a simple Model like this:
class Order(models.Model):
created = model.DateTimeField(auto_now_add=True)
total = models.IntegerField() # monetary value
And I want to output a month-by-month breakdown of:
- How many sales there were in a month (
COUNT
) - The combined value (
SUM
)
I'm not sure what the best way to attack this is. I've seen some fairly scary-looking extra-select queries but my simple mind is telling me I might be better off just iterating numbers, starting from an arbitrary start year/month and counting up until I reach the current month, throwing out simple queries filtering for that month. More database work - less developer stress!
What makes most sense to you? Is there a nice way I can pull back a quick table of data? Or is my dirty method probably the best idea?
I'm using Django 1.3. Not sure if they've added a nicer way to GROUP_BY
recently.
Django 1.10 and above
Django documentation lists extra
as deprecated soon. (Thanks for pointing that out @seddonym, @Lucas03). I opened a ticket and this is the solution that jarshwah provided.
from django.db.models.functions import TruncMonth
Sales.objects
.annotate(month=TruncMonth('timestamp')) # Truncate to month and add to select list
.values('month') # Group By month
.annotate(c=Count('id')) # Select the count of the grouping
.values('month', 'c') # (might be redundant, haven't tested) select month and count
Older versions
from django.db import connection
from django.db.models import Sum, Count
truncate_date = connection.ops.date_trunc_sql('month', 'created')
qs = Order.objects.extra({'month':truncate_date})
report = qs.values('month').annotate(Sum('total'), Count('pk')).order_by('month')
Edits
- Added count
- Added information for django >= 1.10
这篇关于Django:按日期分组(日,月,年)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!