Django:按日期(天、月、年)分组

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.3。不确定他们最近是否为GROUP_BY添加了更好的方法。

5 个解决方案

#1

158

Django 1.10 and above

Django 1.10及以上

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.

Django文档列出了不赞成的额外文档。(谢谢你指出@seddonym， @Lucas03)。我开了一张票，这是jarshwah提供的解决方案。

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 >的信息= 1.10

#2

Just a small addition to @tback answer: It didn't work for me with Django 1.10.6 and postgres. I added order_by() at the end to fix it.

@tback的回答只是一个小小的补充:对于Django 1.10.6和postgres来说，它不起作用。我在末尾添加了order_by()来修复它。

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
    .order_by()

#3

Another approach is to use ExtractMonth. I ran into trouble using TruncMonth due to only one datetime year value being returned. For example, only the months in 2009 were being returned. ExtractMonth fixed this problem perfectly and can be used like below:

另一种方法是使用提取月。我在使用TruncMonth时遇到了问题，因为只返回一个datetime year值。例如，2009年只有几个月被归还。ExtractMonth可以很好地解决这个问题，如下所示:

from django.db.models.functions import ExtractMonth
Sales.objects
    .annotate(month=ExtractMonth('timestamp')) 
    .values('month')                          
    .annotate(count=Count('id'))                  
    .values('month', 'count')

#4

Here's my dirty method. It is dirty.

这是我的肮脏的方法。它是脏的。

import datetime, decimal
from django.db.models import Count, Sum
from account.models import Order
d = []

# arbitrary starting dates
year = 2011
month = 12

cyear = datetime.date.today().year
cmonth = datetime.date.today().month

while year <= cyear:
    while (year < cyear and month <= 12) or (year == cyear and month <= cmonth):
        sales = Order.objects.filter(created__year=year, created__month=month).aggregate(Count('total'), Sum('total'))
        d.append({
            'year': year,
            'month': month,
            'sales': sales['total__count'] or 0,
            'value': decimal.Decimal(sales['total__sum'] or 0),
        })
        month += 1
    month = 1
    year += 1

There may well be a better way of looping years/months but that's not really what I care about :)

也许有更好的方法来循环年/月，但这并不是我真正关心的:

#5

-2

By month:

月:

 Order.objects.filter().extra({'month':"Extract(month from created)"}).values_list('month').annotate(Count('id'))

By Year:

年:

 Order.objects.filter().extra({'year':"Extract(year from created)"}).values_list('year').annotate(Count('id'))

By day:

白天:

 Order.objects.filter().extra({'day':"Extract(day from created)"}).values_list('day').annotate(Count('id'))

Don't forget to import Count

不要忘记导入计数

from django.db.models import *

For django < 1.10

django < 1.10

#1

158