sql2 写在sql1 上面query_set,统计会出问题
def do_get_ent_list(args):
query_label = dict()
query_ent = {'typed': 1, "region_id": 370211}
query_ent_Q = Q()
if args.get('field'):
query_label['remark'] = args.get('field')
if args.get('industry_code'):
query_label['label'] = args.get('industry_code')
entids = NecLabel.objects.filter(**query_label).values('entid').distinct()
if args.get('keywords_search'):
keywords = args.get('keywords_search').strip()
query_ent_Q.add((Q(entname__icontains=keywords)), Q.AND)
for k, v in args.items():
try:
v = int(v)
except:
v = str(v)
if k == 'regcap_gte' and v:
query_ent['regcap__gte'] = args.get('regcap_gte')
if k == 'regcap_lte' and v:
query_ent['regcap__lte'] = args.get('regcap_lte')
if k == 'empnum_gte' and v:
query_ent['emp__gte'] = args.get('regcap_gte')
if k == 'empnum_lte' and v:
query_ent['emp__lte'] = args.get('empnum_lte')
if k == 'esdate_gte' and v:
query_ent['esdate__gte'] = args.get('esdate_gte')
if k == 'eesdate_lte' and v:
query_ent['esdate__lte'] = args.get('esdate_lte')
if k in ['functional', 'town', 'scale_type']:
query_ent[k] = v
if k == 'ent_type':
query_ent['etype'] = v
if k == 'has_patent':
if v == 1:
query_ent_Q.add((Q(patent_a__gt=0) | Q(patent_b__gt=0) | Q(patent_c__gt=0)), Q.AND)
elif v == 2:
query_ent_Q.add(Q(patent_a=0, patent_b=0, patent_c=0), Q.AND)
if k == 'phase':
if v == 1:
query_ent_Q.add(~Q(phase='0'), Q.AND)
elif v == 2:
query_ent_Q.add(Q(phase='0'), Q.AND)
if k == 'hightech_check':
if v == 1:
query_ent['hightech'] = 1
elif v == 2:
query_ent['hightech'] = 0
if k == 'is_listed':
if v == 1:
query_ent['listed'] = 1
elif v == 2:
query_ent['listed'] = 0
sort_by_dict = {1: 'emp',
2: 'regcap',
3: 'esdate',
4: 'vc_sum_amount',
}
query_set = NecCompany.objects.filter(**query_ent).filter(query_ent_Q).filter(entid__in=entids)
# 放在sql2 sum()下面 query_set 统计会出错,找不到原因
# sql 1
scale_pie_graph, town_line_graph, functional_line_graph = s_t_f_graph(query_set)
# sql 2
patent_pie_graph = query_set.aggregate(实用新型专利=Sum('patent_a'), 外观专利=Sum('patent_b'), 发明专利=Sum('patent_c'),
count=Count(1))
# 排序
sort = int(args.get('sort_by')) if args.get('sort_by') else 1
if args.get('desc_sort') and int(args.get('desc_sort')):
query_set = query_set.order_by('-' + sort_by_dict.get(sort))
else:
query_set = query_set.order_by(sort_by_dict.get(sort))
result = dict()
result['count'] = patent_pie_graph['count']
start = int(args.get('from', 0))
size = int(args.get('size', 2))
annual_growth, total_year_growth = growth_rate(query_set)
result['res_data'] = [data.to_dict() for data in query_set[start:start + size]]
result['from'] = start
result['size'] = size
result['annual_growth'] = annual_growth[1:]
result['total_year_growth'] = total_year_growth[2:]
result['patent_pie_graph'] = [{'name': k, 'value': v} for k, v in patent_pie_graph.items()]
result['scale_pie_graph'] = [{"name": NecCompany.scale_type_map.get(int(scale['alias'])), "value": scale['count']}
for
scale in scale_pie_graph]
result['town_line_graph'] = format_data(town_line_graph)
result['functional_line_graph'] = format_data(functional_line_graph)
# from django.db import connections
# c = connections['el_qingdao']
# print(c.queries)
def format_data(graph):
for i, town in enumerate(graph):
if not town.get('alias'):
graph[-1], graph[i] = graph[i], graph[-1]
return [{"name": town.get("alias"), "value": town.get("count")} for town in graph]
# 规模区域分组统计
def s_t_f_graph(query_set):
'''
# scale_pie_graph = query_set.values('scale_type').annotate(count=Count('*'))
# town_line_graph = query_set.values('town').annotate(count=Count('*'))
# functional_line_graph = query_set.values('functional').annotate(count=Count('*'))
:param query_set:
:return:
'''
scale_pie_graph = []
town_line_graph = []
functional_line_graph = []
scale_type_query = query_set.values('scale_type').annotate(count=Count('*'), alias=F("scale_type"),field=Value("s", output_field=CharField())).values("alias", "count", "field")
town_query = query_set.values('town').annotate(count=Count('*'), alias=F("town"), field=Value("t", output_field=CharField())).values("alias", "count","field")
functional_query = query_set.values('functional').annotate(count=Count('*'), alias=F("functional"),field=Value("f", output_field=CharField())).values("alias", "count", "field")
s_t_f = scale_type_query.union(town_query, functional_query)
for q in s_t_f:
if 's' in q["field"]:
scale_pie_graph.append(q)
elif 't' in q.values():
town_line_graph.append(q)
else:
functional_line_graph.append(q)
return scale_pie_graph, town_line_graph, functional_line_graph
# 近五年新经济企业累计数
def growth_rate(query_set):
total_year_growth = list()
annual_growth = list()
years = [datetime.datetime.now().year - i for i in range(7, 0, -1)]
year_counts = query_set.extra(
select={str(t): f"sum( if(esdate <= \'{str(t)}\', 1, 0))" for t in years}
).values(*[str(t) for t in years])
for t, c in year_counts[0].items():
d = {}
f = {}
t = int(t)
c = int(c)
d['time'] = t
d['count'] = c
if len(total_year_growth) > 0:
# 当前年注册企业
current = c - total_year_growth[-1]['count']
d['growth_rate'] = round((current / total_year_growth[-1]['count']) * 100, 2) if total_year_growth[-1][
'count'] != 0 else 0
f['time'] = t
f['count'] = c - total_year_growth[-1]['count']
if len(annual_growth) > 0:
f['growth_rate'] = round(((current - annual_growth[-1]['count']) / annual_growth[-1]['count']) * 100,
2) if annual_growth[-1]['count'] else 0
annual_growth.append(f)
total_year_growth.append(d)
return annual_growth, total_year_growth
网友评论