一、背景
将出单词推荐给销售使用，将出单词接口提供给java。
业务价值：通过关键词进行引流，提升listing出单；预估50000.
验收人：陆华信


二、需求说明


2.1、提供出单词接口

1、数据来源：出单词库表

2、入参：
系统SKU ：支持多个SKU请求
站点

3、返参：
系统SKU
站点
关键词
关键词类型
市场月搜索量  
市场曝光量   
市场点击量   
市场加购量   
市场购买量   
市场数据来源月份  


注：接口支持批量调用


数据来源参考：
SELECT a.SourceName                     AS '来源渠道',
       c.SourceType                     AS '一级部门',
       c.Department                     AS '部门',
       c.SaleAdminName                  AS '业务人员',
       a.SKU                            AS '系统SKU',
       a.ASIN                           AS 'ASIN',
       e.ProductNameCN                  AS '产品中文名',
       a.SellerSKU                      AS '渠道SKU',
       CASE b.KeyWordSourceType
           WHEN 1 THEN '2'
           WHEN 2 THEN '1'
           WHEN 9 THEN '4'
           WHEN 10 THEN '1'
           WHEN 11 THEN '2'
           WHEN 12 THEN '4'
           WHEN 13 THEN '2'
           WHEN 14 THEN '3'
           WHEN 15 THEN '2'
           WHEN 16 THEN '3'
           WHEN 17 THEN '4'
           ELSE b.KeyWordSourceType END AS '关键词来源优先级',
       CASE b.KeyWordSourceType
           WHEN 1 THEN 'Aba关键词'
           WHEN 2 THEN '广告出单词'
           WHEN 9 THEN 'Listing出单关键词'
           WHEN 10 THEN 'ASIN搜索出单词'
           WHEN 11 THEN 'AbaASIN搜索词机会词'
           WHEN 12 THEN '相似SKU关键词'
           WHEN 13 THEN '同语种拓展词'
           WHEN 14 THEN '广告接口推荐词'
           WHEN 15 THEN '竞品自然关键词'
           WHEN 16 THEN '竞品广告关键词'
           WHEN 17 THEN '竞品标题高频词'
           ELSE b.KeyWordSourceType END AS '关键词来源',
       b.KeyWord                        AS '关键词',
       b.OrderCartAddNum                AS 'SKU当前关键词自然加购数或广告出单数',
       b.OnlineCount                    AS '埋入Listing数',
       b.OrderCount                     AS '埋入中的出单Listing数',
       d.SearchVolume                   AS '市场月搜索量',
       d.Impressions                    AS '市场曝光量',
       d.ClickTotal                     AS '市场点击量',
       d.CartTotal                      AS '市场加购量',
       d.PurchaseTotal                  AS '市场购买量',
       d.ReportDate                     AS '市场数据来源月份'
FROM zhcxkj_AutoPublish_bi.BI_Amazon_Ad_SessionCache AS a
         LEFT JOIN zhcxkj_dw_bi.zhcxkj_amazon_amazonskukeywordstatistics AS b
                   ON a.Country = b.SiteCode AND a.SKU = b.Sku
         LEFT JOIN zhcxkj_AutoPublish_bi.BI_Amazon_Ad_Department AS c
                   ON a.SourceName = c.OrderSourceName AND c.CustomerID = 1
         LEFT JOIN (SELECT SiteCode,
                           KeyWord,
                           SearchVolume,
                           Impressions,
                           ClickTotal,
                           CartTotal,
                           PurchaseTotal,
                           ReportDate,
                           ROW_NUMBER() OVER (PARTITION BY SiteCode,KeyWord ORDER BY ReportDate DESC) AS 'RowNum'
                    FROM zhcxkj_dw_bi.zhcxkj_autochoose_asinkeywordreport) AS d
                   ON a.Country = d.SiteCode AND b.KeyWord = d.KeyWord AND d.RowNum = 1
         LEFT JOIN zhcxkj_dw_bi.rb_product AS e ON c.CustomerID = e.CustomerID AND a.SKU = e.SKU
WHERE b.KeyWordSourceType IN (1, 2, 9, 10, 11, 12, 13, 14, 15, 16, 17)



三、验收标准

接口支持批量查数据

1、接口支持批量查数据。

2、响应时间3秒之内。

业务价值：通过关键词进行引流，提升listing出单；预估50000.