我有一个自定义SQL调用,它向模板返回的结果与我直接对数据库运行同一查询时得到的结果不同(1行对2行)
查询-从Django调试工具栏复制:
SELECT ((Sum(new_recruit_interviews) / Sum(opportunities_offered)) * 100) as avg_recruit, ((Sum(inspections) / Sum(presentations)) * 100) as avg_inspect, ((Sum(contracts_signed) / Sum(roof_approvals)) * 100) as avg_contracts, ((Sum(adjusters) / Sum(contracts_signed)) * 100) as avg_adjusters, ((Sum(roof_approvals) / Sum(adjusters)) *100) as roof_approval_avg, ((Sum(roof_turned_in) / Sum(adjusters)) * 100) as roof_jobs_avg, Sum(roof_turned_in) as roof_jobs_total,
((Sum(siding_approvals) / Sum(adjusters)) *100) as siding_approval_avg, ((Sum(siding_turned_in) / Sum(adjusters)) * 100) as siding_jobs_avg, Sum(siding_turned_in) as siding_jobs_total, ((Sum(gutter_approvals) / Sum(adjusters)) *100) as gutter_approval_avg, ((Sum(gutter_turned_in) / Sum(adjusters)) * 100) as gutter_jobs_avg, Sum(gutter_turned_in) as gutter_jobs_total,
((Sum(window_approvals) / Sum(adjusters)) *100) as window_approval_avg, ((Sum(window_turned_in) / Sum(adjusters)) * 100) as window_jobs_avg, Sum(window_turned_in) as window_jobs_total,
(Sum(roof_turned_in) + Sum(siding_turned_in) + Sum(gutter_turned_in) + Sum(window_turned_in)) as total_jobs,
(((Sum(collections_jobs_new) + Sum(collections_jobs_previous)) / (Sum(roof_turned_in) + Sum(siding_turned_in) + Sum(gutter_turned_in) + Sum(window_turned_in))) * 100) as total_collections,
sales_report_salesmen.location_id as detail_id, business_unit_location.title as title
FROM sales_report_salesmen
Inner Join auth_user ON sales_report_salesmen.user_id = auth_user.id
Inner Join business_unit_location ON sales_report_salesmen.location_id = business_unit_location.id
GROUP BY location_id
运行上述查询的直接查询的结果:
INSERT INTO `` (`avg_recruit`, `avg_inspect`, `avg_contracts`, `avg_adjusters`, `roof_approval_avg`, `roof_jobs_avg`, `roof_jobs_total`, `siding_approval_avg`, `siding_jobs_avg`, `siding_jobs_total`, `gutter_approval_avg`, `gutter_jobs_avg`, `gutter_jobs_total`, `window_approval_avg`, `window_jobs_avg`, `window_jobs_total`, `total_jobs`, `total_collections`, `detail_id`, `title`)
VALUES (95.3968, 92.8178, 106.9622, 90.2928, 103.5420, 103.5670, 4152, 100.2494, 106.8845, 4285, 120.1297, 86.2559, 3458, 92.9658, 106.1611, 4256, 16151, 4.281469, 12, 'St Paul, MN');
VALUES (90.2982, 73.3723, 97.8474, 104.5433, 97.7585, 86.1848, 1884, 109.9268, 109.3321, 2390, 81.0156, 96.4318, 2108, 91.7200, 123.8792, 2708, 9090, 4.531573, 13, 'Denver, CO');
模板结果:
{'roof_jobs_total': Decimal('4152'), 'gutter_jobs_total': Decimal('3458'), 'avg_adjusters': Decimal('90.2928'), 'title': u'St Paul, MN', 'window_approval_avg': Decimal('92.9658'), 'total_collections': Decimal('4.281469'), 'gutter_approval_avg': Decimal('120.1297'), 'avg_recruit': Decimal('95.3968'), 'siding_approval_avg': Decimal('100.2494'), 'window_jobs_total': Decimal('4256'), 'detail_id': 12L, 'siding_jobs_avg': Decimal('106.8845'), 'avg_inspect': Decimal('92.8178'), 'roof_approval_avg': Decimal('103.5420'), 'roof_jobs_avg': Decimal('103.5670'), 'total_jobs': Decimal('16151'), 'window_jobs_avg': Decimal('106.1611'), 'avg_contracts': Decimal('106.9622'), 'gutter_jobs_avg': Decimal('86.2559'), 'siding_jobs_total': Decimal('4285')}
返回的行与通过控制台查询返回的第一行相同,因此我认为只要将结果传递给。。。
(是的,有点难看,一直在玩)
def sql_grouped(table, fields, group_by=False, where=False):
from django.db import connection
query = 'SELECT %s FROM %s' % (fields, table)
if where:
query = query + ' WHERE %s' % (where)
if group_by:
query = query + ' GROUP BY %s' % (group_by)
cursor = connection.cursor()
cursor.execute(query)
desc = cursor.description
data = [dict(zip([col[0] for col in desc], row)) for row in cursor.fetchall()]
return data[0]
任何反馈都是非常感谢的-我一直在修补这个问题,因为我意识到我可以跳过几个步骤,直接在SQL中生成平均值,而不是后期处理
