暂无
任务描述
需求描述
需求地址：https://alidocs.dingtalk.com/i/nodes/ZX6GRezwJl7BvlK5hqqzEgmwVdqbropQ?utm_scene=team_space
竞品分析，关键词分析板块 监控视图，新增“添加关键词”功能
竞品分析，关键词分析板块 监控视图，新增“添加关键词”功能
验收标准
158Create
测试提示
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
自测截图
{
"success": true,"errCode": null,
"errMessage": null,
"data": {
"finalSyncDate": "2024-10-12",
"competitorsSum": [
{
"parentAsin": "B09G65K144",
"asin": null,
"acSignNum": null,
"top3Num": null,
"top6Num": null,
"top10Num": null,
"top16Num": null,
"firstPageNum": null,
"dateLabel": null,
"dateLabelYea": null
},
{
"parentAsin": "B09PZV54SP",
"asin": null,
"acSignNum": null,
"top3Num": null,
"top6Num": null,
"top10Num": null,
"top16Num": null,
"firstPageNum": null,
"dateLabel": null,
"dateLabelYea": null
},
{
"parentAsin": "B09QRVV8QM",
"asin": null,
"acSignNum": null,
"top3Num": null,
"top6Num": null,
"top10Num": null,
"top16Num": null,
"firstPageNum": null,
"dateLabel": null,
"dateLabelYea": null
},
{
"parentAsin": "B082WXB95Q",
"asin": null,
"acSignNum": null,
"top3Num": null,
"top6Num": null,
"top10Num": null,
"top16Num": null,
"firstPageNum": null,
"dateLabel": null,
"dateLabelYea": null
}
]
}
}
新增类名映射之后
{
"success": true,
"errCode": null,
"errMessage": null,
"data": {
"finalSyncDate": "2024-10-12",
"competitorsSum": [
{
"parentAsin": "B09PZV54SP",
"asin": null,
"acSignNum": 28,
"top3Num": 74,
"top6Num": 201,
"top10Num": 375,
"top16Num": 666,
"firstPageNum": 1990,
"dateLabel": null,
"dateLabelYea": null
},
{
"parentAsin": "B082WXB95Q",
"asin": null,
"acSignNum": 154,
"top3Num": 170,
"top6Num": 426,
"top10Num": 890,
"top16Num": 1507,
"firstPageNum": 3652,
"dateLabel": null,
"dateLabelYea": null
},
{
"parentAsin": "B09QRVV8QM",
"asin": null,
"acSignNum": 0,
"top3Num": 283,
"top6Num": 477,
"top10Num": 693,
"top16Num": 1045,
"firstPageNum": 2548,
"dateLabel": null,
"dateLabelYea": null
},
{
"parentAsin": "B09G65K144",
"asin": null,
"acSignNum": null,
"top3Num": null,
"top6Num": null,
"top10Num": null,
"top16Num": null,
"firstPageNum": null,
"dateLabel": null,
"dateLabelYea": null
}
]
}
}
git