Django:按日期分组(日、月、年) [英] Django: Group by date (day, month, year)

问题描述

我有一个像这样的简单模型:

I've got a simple Model like this:

class Order(models.Model):
    created = model.DateTimeField(auto_now_add=True)
    total = models.IntegerField() # monetary value

我想按月输出:

• 一个月有多少销售额(COUNT)
• 组合值 (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?

我使用的是 Django 1.3.不确定他们最近是否为 GROUP_BY 添加了更好的方法.

I'm using Django 1.3. Not sure if they've added a nicer way to GROUP_BY recently.

推荐答案

Django 1.10 及更高版本

Django 文档将 extra 列为即将弃用.(感谢您指出 @seddonym、@Lucas03).我打开了一张 ticket，这是 jarshwah 提供的解决方案.

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
from django.db.models import Count

Sales.objects
    .annotate(month=TruncMonth('created'))  # 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 

旧版本

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')

编辑

• 添加数量
• 为 django >= 1.10 添加了信息

这篇关于Django:按日期分组(日、月、年)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！