美文网首页
orm 统计

orm 统计

作者: 高峥 | 来源:发表于2021-11-26 19:11 被阅读0次
    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
    
    

    相关文章

      网友评论

          本文标题:orm 统计

          本文链接:https://www.haomeiwen.com/subject/wnrxxrtx.html