我在你的问题中注意到两件事:
-
结果按月排序。
-
购买总额可以是
blank
或
null
.
基于这些,我将提出这种方法:
你可以得到一个月的总数,你只需要处理
total_pushase
为空(作为旁注,没有任何意义
Purchase
哪里
total_purchase
为空,至少必须为0)。
阅读有关
Django Conditional expressions
了解更多信息
When
和
Case
.
# Annotate the filtered objects with the correct value (null) is equivalent
# to 0 for this requirement.
result = Purchase.objects.filter(date__gte=start_date, date__lt=end_date).annotate(
real_total = Case(
When(total_purchase__isnull=True, then=0),
default=F('total_purchase')
)
)
# Then if you want to know the total for a specific month, use Sum.
month_partial_total = result.filter(
date__month=selected_month
).aggregate(
partial_total=Sum('real_total')
)['partial_total']
您可以在函数中使用它来实现您想要的结果:
import calendar
import collections
import dateutil
def totals(start_date, end_date):
"""
start_date and end_date are datetime.date objects.
"""
results = collections.OrderedDict() # Remember order things are added.
result = Purchase.objects.filter(date__gte=start_date, date__lt=end_date).annotate(
real_total = Case(
When(total_purchase__isnull=True, then=0),
default=F('total_purchase')
)
)
date_cursor = start_date
month_partial_total = 0
while date_cursor < end_date:
# The while statement implicitly orders results (it goes from start to end).
month_partial_total += result.filter(date__month=date_cursor.month).aggregate(
partial_total=Sum('real_total')
)['partial_total']
results[date_cursor.month] = month_partial_total
# Uncomment following line if you want result contains the month names
# instead the month's integer values.
# result[calendar.month_name[month_number]] = month_partial_total
date_cursor += dateutil.relativedelta.relativedelta(months=1)
return results
因为Django 1.11可以解决这个问题
SubQueries
但我从未在同一个模型上使用过它进行子查询。
