您当然可以使用ORM来完成.您需要将Max
(或SUM
,取决于您的用例)与Q()
表达式过滤器相结合,并在过滤之前将百分比增加注释到queryset.
您可以在一段代码中完成,但我已经将其拆分了,因为获取日期和查询表达式相当长.我还将增加值放在一个单独的变量中,而不是硬编码.
from datetime import datetime, timedelta
from django.db.models import Max, Q
SALES_INCREASE = 1.2
# Get the start dates of this month and last month
this_month = datetime.now().date().replace(day=1)
last_month = (this_month - timedelta(days=15)).replace(day=1)
# Get the maximum sale this month
amount_this_month = Max('monthlyreport__sale',
filter=Q(monthlyreport__date__gte=this_month))
# Get the maximum sale last month, but before this month
amount_last_month = Max('monthlyreport__sale',
filter=Q(monthlyreport__date__gte=last_month) & \
Q(monthlyreport__date__lt=this_month))
Company.objects.annotate(
percentage_increase=amount_this_month/amount_last_month
).filter(percentage_increase__gte=SALES_INCREASE)
Edit-更新答案,如果我们想删除Company
:
# use the same imports and date variables
# Get the maximum sale this month
amount_this_month = Max('sale', filter=Q(date__gte=this_month))
# Get the maximum sale last month, but before this month
amount_last_month = Max('sale', filter=Q(date__gte=last_month) & \
Q(date__lt=this_month))
MonthlyReport.objects.annotate(
percentage_increase=amount_this_month/amount_last_month
).filter(percentage_increase__gte=SALES_INCREASE)