我有一个需要80秒才能服务的视角。django-debug-toolbar中的SQL选项卡显示我总共有422个查询,其中210个相似。
该视图计算每种商品和truck_type当前年度35周的每周费率(收入/英里数)。
任何关于如何优化我的查询的帮助都将不胜感激。
这里的观点:
def rates_weekly(request):
tenant = request.tenant
loads = Load.objects.all().exclude(load_status='Cancelled').values('billable_amount_after_accessorial', 'total_miles')
from fleetdata.utils import start_week_nr
def calculate_rate(year, week_num, commodity, truck_type):
start_of_week = start_week_nr(year, week_num)
end_of_week = start_of_week + datetime.timedelta(days=7)
relevant_loads = loads.filter(drop_date__gte=start_of_week, drop_date__lt=end_of_week, truck_type=truck_type, commodity=commodity)
revenue = relevant_loads.aggregate(Sum("billable_amount_after_accessorial"))['billable_amount_after_accessorial__sum']
miles = relevant_loads.aggregate(Sum("total_miles"))['total_miles__sum']
if revenue and miles is not None:
rate = revenue / miles
else:
rate = 0
return rate
rates = {}
for week in range(1, CURRENT_WEEK_CUSTOM+6):
rate_ct_reefer = calculate_rate(CURRENT_YEAR, week, 'Reefer', 'CT')
rate_ct_dryvan = calculate_rate(CURRENT_YEAR, week, 'DryVan', 'CT')
rate_ct_flatbed = calculate_rate(CURRENT_YEAR, week, 'Flat Bed', 'CT')
rate_oo_reefer = calculate_rate(CURRENT_YEAR, week, 'Reefer', 'OO')
rate_oo_dryvan = calculate_rate(CURRENT_YEAR, week, 'DryVan', 'OO')
rate_oo_flatbed = calculate_rate(CURRENT_YEAR, week, 'Flat Bed', 'OO')
rates[str(CURRENT_YEAR) + '-' + f"{week:02d}"] = {}
rates[str(CURRENT_YEAR) + '-' + f"{week:02d}"]['rate_ct_reefer'] = rate_ct_reefer
rates[str(CURRENT_YEAR) + '-' + f"{week:02d}"]['rate_ct_dryvan']= rate_ct_dryvan
rates[str(CURRENT_YEAR) + '-' + f"{week:02d}"]['rate_ct_flatbed']= rate_ct_flatbed
rates[str(CURRENT_YEAR) + '-' + f"{week:02d}"]['rate_oo_reefer'] = rate_oo_reefer
rates[str(CURRENT_YEAR) + '-' + f"{week:02d}"]['rate_oo_dryvan']= rate_oo_dryvan
rates[str(CURRENT_YEAR) + '-' + f"{week:02d}"]['rate_oo_flatbed']= rate_oo_flatbed
list_weeks = list(rates.keys())
list_rates = list(rates.values())
list_ct_reefer_rates = [ x['rate_ct_reefer'] for x in list_rates ]
list_ct_dryvan_rates = [ x['rate_ct_dryvan'] for x in list_rates ]
list_ct_flatbed_rates = [ x['rate_ct_flatbed'] for x in list_rates ]
list_oo_reefer_rates = [ x['rate_oo_reefer'] for x in list_rates ]
list_oo_dryvan_rates = [ x['rate_oo_dryvan'] for x in list_rates ]
list_oo_flatbed_rates = [ x['rate_oo_flatbed'] for x in list_rates ]
list_weeks_dt = [ datetime.datetime.strptime(date + '-1', '%Y-%W-%w') for date in list_weeks ]
dict_reefer_dryvan_flatbed = {
'weeks': list_weeks_dt,
'rates_ct_reefer': list_ct_reefer_rates, 'rates_ct_dryvan': list_ct_dryvan_rates, 'rates_ct_flatbed': list_ct_flatbed_rates,
'rates_oo_reefer': list_oo_reefer_rates, 'rates_oo_dryvan': list_oo_dryvan_rates, 'rates_oo_flatbed': list_oo_flatbed_rates
}
fig_ct = px.line(dict_reefer_dryvan_flatbed, x='weeks', y=['rates_ct_reefer', 'rates_ct_dryvan', 'rates_ct_flatbed'])
fig_ct.update_layout(
xaxis_tickformat = '%Y-%W',
xaxis = dict(tickmode = 'linear', dtick = 604800000)
)
fig_ct = fig_ct.to_html()
fig_oo = px.line(dict_reefer_dryvan_flatbed, x='weeks', y=['rates_oo_reefer', 'rates_oo_dryvan', 'rates_oo_flatbed'])
fig_oo.update_layout(
xaxis_tickformat = '%Y-%W',
xaxis = dict(tickmode = 'linear', dtick = 604800000)
)
fig_oo = fig_oo.to_html()
context = {
'tenant': tenant,
'CURRENT_YEAR': CURRENT_YEAR,
'CURRENT_WEEK_CUSTOM': CURRENT_WEEK_CUSTOM,
'rates': rates,
'fig_ct': fig_ct,
'fig_oo': fig_oo,
}
return render(request, template_name='loads/rates-weekly.html', context=context)
字符串
2条答案
按热度按时间o4hqfura1#
有一件事是跳出来的:
字符串
在这里,您正在对同一个查询集执行两个单独的聚合操作,我相信它们将被单独评估。您可以在初始查询集中完成所有这些操作,然后仅依赖于键。这应该是每个迭代减少一个调用。
型
8yparm6h2#
因此,我通过使用.values()对对象进行分组并注解聚合函数来解决多个查询的问题:
字符串