业务需求
需求描述
一、背景
目前相关度服务由于压力大已停止，导致现有得标题埋词无词可以埋。故需要将相关度服务控制在有效范围进行重启相关度服务。
业务价值：保证有新得关键词相关度，用于标题埋词提升出单率，预估50W。
验收人：沈伯潜
二、需求描述
2.1、关键词相关度计算优化
操作位置：关键词相关度计算
逻辑：指定SKU 跑相关度，筛选FBM的 在去年11和12月卖的比较好的英语语种SKU进行跑相关度
-- 可通过年月+站点找对应SKU 跑相关度，进行循环跑相关度。
参考语句：
SELECT j.Country,
k.SKU,
COUNT(DISTINCT k.OrderCode) AS 'Orders'
FROM zhcxkj_dw_bi.rb_orderinfo AS j
LEFT JOIN zhcxkj_dw_bi.rb_orderlist k
ON j.CustomerID = k.CustomerID AND j.OrderCode = k.OrderCode
LEFT JOIN zhcxkj_dw_bi.rb_warehouse AS l
ON j.CustomerID = l.CustomerID AND j.WareHouseID = l.ID
LEFT JOIN zhcxkj_AutoPublish_bi.BI_Amazon_Ad_Department AS m
ON j.OrderSourceID = m.OrderSourceID
INNER JOIN zhcxkj_dw_bi.rb_product AS i
ON k.SKU = i.SKU AND i.IsGroup = 0
WHERE j.CustomerID = 1
AND j.DownLoadUpdateTime <> '2000-1-1'
AND j.PayTime BETWEEN '2023-11-01 00:00:00' AND '2023-12-31 00:00:00'
AND j.OrderStatus NOT IN (-1, 2)
AND j.IsStockOrder = 0
AND j.RefundType = 0
AND m.OrderSourceType = 1
AND l.WareHouseType = 1
AND m.SourceType IN ('独立账号', '运营中心')
AND j.Country in ('US','SG','SA','IN','GB','EG','CA','AU','AE')
GROUP BY j.Country,
k.SKU
Having COUNT(DISTINCT k.OrderCode)>1
ORDER BY COUNT(DISTINCT k.OrderCode) DESC
三、风险描述
无
四、验收标准
验收人：沈伯潜
验收标准：相关度可以正常跑起来，便于标题正常埋词。
验收标准
关键词相关度计算服务正常启动
价值描述
业务价值：保证有新得关键词相关度，用于标题埋词提升出单率，预估50W。
1、增加队列表，mysql》sku_queue_keywords_similarity
2、增加服务，Product_Sku_NCron5
将状态Status=0/1，进行相似度处理，生产使用分布式，这个服务仅供测试使用，数据一次别加太多
3、自测，以1377913为例
3.1、队列，AutoChooseCron:Dev:QueueWorker:ListQueue:AmazonKeyWords_N_Similarity_RedisQueue
3.2、造数据
db.getCollection("ExternalNew_Keyword").insert( { UniqueId: "2d16d6546c2c9473f64af1c5f531ef51-US", AddTime: ISODate("2024-11-20T00:00:00Z"), IsPriority: false, Keyword: "boot snake", SiteCode: "US", Status: NumberInt("1"), UpdateTime: ISODate("2024-11-20T00:00:00Z"), } );db.getCollection("ExternalNew_Keyword_Asin_US").insert( { UniqueId: "B0CDRMHM82-2d16d6546c2c9473f64af1c5f531ef51-US", Asin: "B0CDRMHM82", CommentNum: NumberInt("0"), ImgUrl: "https://m.media-amazon.com/images/I/61g+i1ePaSL._AC_UY218_.jpg", Keyword: "boot snake", Page: NumberInt("3"), Price: 15.98, RankList: NumberInt("7"), SiteCode: "US", Star: 0, Status: NumberInt("0"), TaskUniqueId: "2d16d6546c2c9473f64af1c5f531ef51-US", Title: "Nema 17 Stepper Motor 40MM Bipolar 1.5 A 42 Motor 42Ncm (59.47oz.in) 2 Phase 4-Lead Stepper Motor 1.8 Degree with 1m Cable and Connector for DIY CNC 3D Printer, 1.65x1.65x1.57inch", UpdateTime: ISODate("2024-06-13T10:33:21.17Z"), DealRemark: null, DealStatus: NumberInt("2"), DealTime: ISODate("2024-06-13T08:48:43.34Z") } );db.getCollection("ExternalNew_Sku_Asin_US").insert( { UniqueId: "1377913-B0CDRMHM82-US", AddTime: ISODate("2024-06-12T06:27:07.538Z"), Asin: "B0CDRMHM82", Remark: null, ResultStatus: NumberInt("2"), SimilarityStatus: NumberInt("0"), SimilarityTitleAI: 0.69, SiteCode: "US", Sku: "1377913", Status: NumberInt("0"), TypeInfo: { Category: NumberInt("0"), KeyWord: NumberInt("1"), Photo: NumberInt("1"), Product: NumberInt("0") }, UpdateTime: ISODate("2024-07-04T07:30:20.403Z"), AsinTitle: "Mac Book Pro Charger Replacement for Mac Pro 13-Inch 2012-2016 60W 2 T Connector Power Adapter&140W USB C Charger for Mac Book Pro 16 14 inch 2023 2021,MacBook Air 15 13inch 2023 2022,M1/M2/M3" } );3.3、执行完，数据对比
只修改了这两个关键词，是因为有条件，x.UnMatchSkuType == 0 && x.KeywordAsinNum == 0
4、日志查询，EventName :SimilarityAsync-* and ClassName: "Zhcxkj.AutoChoose.Services.Implement.AmazonWords.Product_Sku_N_Service"
5、最后修改队列状态