orm 统计

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