增加產品列表增加數據統計及分段功能

描述

https://aone.alibaba-inc.com/req/18338586

表

• 老版： dwp_en_dm_prod_terminal_eff_d
• 新版： dwp_en_dm_vip_prod_eff_d

分段功能字段

字段	前端key	數據庫key （天表）	數據庫key （周表）	數據庫key （月表）	類型
曝光	minViews: 20 maxViews: 100	imps_cnt_1d_027	imps_cnt_1w_027	imps_cnt_cm_027	BIGINT(20)
點擊	minClicks: 2 maxClicks: 100	clk_cnt_1d_092	clk_cnt_1w_092	clk_cnt_cm_092	BIGINT(20)
點擊率	minClkRate: 22 maxClkRate: 33	clk_rate_1d_068	clk_rate_1w_068	clk_rate_cm_068	DECIMAL(20,4)
反饋	minInquiries: 12 maxInquiries: 24	fb_cnt_1d_013	fb_cnt_1w_013	fb_cnt_cm_013	BIGINT(20)

分段功能需要做的事

1. 修改sql [vip.product.getEffectiveProductsTotal]
2. 修改業務流： vip/product/getEffectiveProductsTotal， 增加4*2個參數 http://oneness2.alibaba-inc.com/businessFlow?moduleId=114&interfaceId=1943&group=alidata
3. 修改業務流： vip/product/getEffectiveProducts

數據統計需要做的事

vip/product/getEffectiveProductsTotal

statistics: {total: 5, clkRate: "0.00", inquiries: 13, crtOrd: 1, views: 0, clicks: 0}

sql過長，參照

http://oneness2.alibaba-inc.com/businessFlow?moduleId=157&interfaceId=2877&group=alidata

數據統計hsf測試

[
  "vip/product/getEffectiveProductsTotal-new",
  {
    "statDate": "2018-12-01",
    "adminMemberSeq": "200042360",
    "statisticsType": "month",
    "region": "all",
    "minViews": "1",
    "maxViews":"11",
    "minClicks": "1",
    "maxClicks": "22",
    "minClkRate": "0.0",
    "maxClkRate": "50",
    "minInquiries": "1",
    "maxInquiries": "22"
  }
]

產品列表hsf測試(有問題)

[
  "vip/product/getEffectiveProducts-new",
  {
    "statDate": "2018-12-01",
    "adminMemberSeq": "200042360",
    "statisticsType": "month",
    "region": "all",
    "minViews": "1",
    "maxViews":"11",
    "minClkRate": "0",
    "maxClkRate": "22"
  }
]

測試問題分析

select
    lst_name as lastName,
    uv_cm_169 as sumProdVisitorCnt,
    imps_cnt_cm_027 as sumProdShowNum,
    is_intlbw as isIntlbw,
    is_kw_prod as isKwSearch,
    prod_name as subject,
    is_std_prod as isStdProd,
    uv_cm_610 as cmp,
    clk_cnt_cm_092 as sumProdClickNum,
    fb_cnt_cm_013 as sumProdFbNum,
    is_p4p_prod as isP4pProd,
    crt_ord_cnt as crtOrd,
    fst_name as firstName,
    clk_rate_cm_068 as sumProdClickRate,
    vst_fb_rate_cm_006 as sumProdFbRate,
    keyword_effect as keywordEffect,
    uv_cm_612 as fav,
    uv_cm_611 as share,
    is_show_win as isShowcase,
    prod_id as id 
from
    dwp_en_dm_vip_prod_eff_m 
where
    stat_date = '2018-12-01' 
    and admin_mbr_seq = '200042360' 
    and statistic_type = 'all' 
    and imps_cnt_cm_027 >= 1 
    and 1 >= imps_cnt_cm_027 
    and clk_rate_cm_068 >= 2.0 
    and 2.0 >= clk_rate_cm_068 
order by
    imps_cnt_cm_027 asc,
    prod_id limit 0,
    30