competitor_product_analysis/handle/pull_data.sql

WITH all_company_data as (
SELECT  company_id,new_cid,base,name,name_en,name_alias,history_names,legal_entities,reg_number,company_org_type,reg_location,estiblish_time,from_time,to_time,business_scope,reg_institute,approved_time,reg_status,reg_capital,org_number,org_approved_institute,parent_company_id,company_type,credit_code,score,cate_first_code,cate_second_code,cate_third_code,lat,lng,province_code,city_code,county_code,reg_capital_amount,reg_capital_currency,actual_capital_amount,actual_capital_currency,reg_status_std,social_security_staff_num,cancel_date,cancel_reason,revoke_date,revoke_reason,emails,phones,wechat_public_num,logo,crawled_time,create_time,update_time,deleted,lawyer_num,lawfirm_intro,competent_unit,certificate_validity,tel_info,ds
FROM    (
            SELECT  *
                    ,ROW_NUMBER() OVER(PARTITION BY company_id ORDER BY ds DESC ) AS num
            FROM    (
                        SELECT  company_id,new_cid,base,name,name_en,name_alias,history_names,legal_entities,reg_number,company_org_type,reg_location,estiblish_time,from_time,to_time,business_scope,reg_institute,approved_time,reg_status,reg_capital,org_number,org_approved_institute,parent_company_id,company_type,credit_code,score,cate_first_code,cate_second_code,cate_third_code,lat,lng,province_code,city_code,county_code,reg_capital_amount,reg_capital_currency,actual_capital_amount,actual_capital_currency,reg_status_std,social_security_staff_num,cancel_date,cancel_reason,revoke_date,revoke_reason,emails,phones,wechat_public_num,logo,crawled_time,create_time,update_time,deleted,lawyer_num,lawfirm_intro,competent_unit,certificate_validity,tel_info,ds
                        FROM    winhc_ng.ads_company_v9
                        WHERE   ds = 20220111
                        UNION ALL
                        SELECT  company_id,new_cid,base,name,name_en,name_alias,history_names,legal_entities,reg_number,company_org_type,reg_location,estiblish_time,from_time,to_time,business_scope,reg_institute,approved_time,reg_status,reg_capital,org_number,org_approved_institute,parent_company_id,company_type,credit_code,score,cate_first_code,cate_second_code,cate_third_code,lat,lng,province_code,city_code,county_code,reg_capital_amount,reg_capital_currency,actual_capital_amount,actual_capital_currency,reg_status_std,social_security_staff_num,cancel_date,cancel_reason,revoke_date,revoke_reason,emails,phones,wechat_public_num,logo,crawled_time,create_time,update_time,deleted,lawyer_num,lawfirm_intro,competent_unit,certificate_validity,tel_info,ds
                        FROM    winhc_ng.inc_ads_company_v9
                        WHERE   ds > 20220111
                    ) AS all_t1
        ) AS all_t2
WHERE   all_t2.num = 1 and reg_status_std NOT LIKE '%销%'  and (name LIKE '%公司%' or name LIKE '%合伙%')
)

INSERT OVERWRITE TABLE out_winhc_data_analysis_pull_data PARTITION(ds='{latest_ds}')

SELECT company_id,company_name,company_org_type,province_code,city_code,county_code,org_number,credit_code,reg_number,cate_first_code,cate_second_code,cate_third_code from (
SELECT tt1.*,tt2.company_rank from (
SELECT company_id,name as company_name,company_org_type,province_code,city_code,county_code,org_number,credit_code,reg_number,cate_first_code,cate_second_code,cate_third_code,ds from all_company_data as t1
LEFT ANTI join (
select * from out_winhc_data_analysis_pull_data where ds < '{latest_ds}'
) as t2
on t1.company_id = t2.company_id
) as tt1
left join (
    SELECT company_id,final_value as company_rank from  calc_company_rank_out where ds = '{company_rank_latest_ds}'
) as tt2
on tt1.company_id =tt2.company_id
)
ORDER by company_rank desc
LIMIT {limit_num};