深圳智汇创想科技有限公司

【海量词】

相关信息

1. 第一步获取最新日期的sql:
select max(collection_date) from t_amazon_competitors_keyword_massive_ranking

2.

通过父asin找到这个asin 关联的类目，
select category from t_amazon_competitors_category_relation where country_code='' and parent_asin in(xx) and is_deleted=0

3. 
执行统计sql
 
 select
            ka.parent_asin,
            ka.country_code,
            count(case when msr.natural_ranking <= 3 then 1 else null end) as top3Num,
            count(case when msr.natural_ranking <= 6 then 1 else null end) as top6Num,
            count(case when msr.natural_ranking <= 10 then 1 else null end) as top10Num,
            count(case when msr.natural_ranking <= 16 then 1 else null end) as top16Num,
            count(case when msr.natural_ranking <= 48 then 1 else null end) as firstPageNum,
            count(case when msr.ac_sign is not null and msr.ac_sign !='' then 1 else null end) as acSignNum
        from t_amazon_competitors_keyword kw
        join t_amazon_competitors_keyword_massive_ranking msr on kw.id = msr.keyword_id
        left join t_amazon_competitors_keyword_asin ka on ka.asin = msr.asin and ka.country_code = msr.country_code
        WHERE
           
                 ka.parent_asin in('B0BW2VGT6F','B0D2XZ8FSW','B0D4CT3YFY')                                 
                and ka.country_code = 'US' and  kw.category IN('第二步的类名')
           
                and msr.collection_date ='第一步的日期'
            
            and ka.keyword_type = 2
 
        group by
        ka.parent_asin,
        ka.country_code

【验证过程】

本迭代只加了第2个步骤

新建数据到对应的表，能查到数据

【关键词】