“首页经营效果中，增加按发布产品拆分指标选项”的版本间的差异

来自qingwei personal wiki
跳转至： 导航， 搜索
（→‎线上问题）
 
（未显示同一用户的30个中间版本）
第1行： 第1行：
 +
== 描述 ==
 +
https://aone.alibaba-inc.com/req/17665713
 
== 新表 ==
 
== 新表 ==
−
* dwp_en_dm_vip_comp_term_region_catelv3_cube_d
+
* dwp_en_dm_vip_comp_term_region_catelv3_cube_d (dwp_en_dm_vip_comp_term_region_cube_d)
−
* dwp_en_dm_vip_catelv3_term_cube_d
+
* dwp_en_dm_vip_catelv3_term_cube_d (dwp_en_dm_vip_cate_term_cube_d)
 +
** 获取具体类目的指标在行业平均效果 （老表不分类目，是店铺的所有类目汇总）
 +
** 指标包括 'comp_fb_uv' , 'comp_ord_amt' , 'shop_pv' , 'comp_ord_cnt' , 'comp_clk_cnt' , 'comp_atm_uv' , 'shop_uv' , 'comp_imps_cnt' , 'comp_fb_cnt'
 
* dwp_en_dm_vip_comp_cate_term_eff_d (dwp_en_dm_vip_comp_term_eff_d)
 
* dwp_en_dm_vip_comp_cate_term_eff_d (dwp_en_dm_vip_comp_term_eff_d)
 +
 
== oneness ==
 
== oneness ==
−
=== 标题文字 ===
+
=== vip.home.getShopSummary ===
 
<source lang="sql">
 
<source lang="sql">
 
select
 
select
 
     ${returnFields}  
 
     ${returnFields}  
 
from
 
from
−
     <if test='statisticsType == "day"'>  
+
     <if test='statisticsType == "day" and seperateByCate == "false"'>  
 
         dwp_en_dm_vip_comp_term_eff_d
 
         dwp_en_dm_vip_comp_term_eff_d
 
     </if>   
 
     </if>   
−
     <if test='statisticsType == "week"'>  
+
     <if test='statisticsType == "week" and seperateByCate == "false"'>  
 
         dwp_en_dm_vip_comp_term_eff_w
 
         dwp_en_dm_vip_comp_term_eff_w
 
     </if>
 
     </if>
−
     <if test='statisticsType == "month"'>  
+
     <if test='statisticsType == "month" and seperateByCate == "false"'>  
 
         dwp_en_dm_vip_comp_term_eff_m
 
         dwp_en_dm_vip_comp_term_eff_m
 +
    </if>
 +
    <if test='statisticsType == "day" and seperateByCate == "true"'>
 +
        dwp_en_dm_vip_comp_cate_term_eff_d
 +
    </if> 
 +
    <if test='statisticsType == "week" and seperateByCate == "true"'>
 +
        dwp_en_dm_vip_comp_cate_term_eff_w
 +
    </if>
 +
    <if test='statisticsType == "month" and seperateByCate == "true"'>
 +
        dwp_en_dm_vip_comp_cate_term_eff_m
 
     </if>
 
     </if>
 
where
 
where
 
     admin_mbr_seq = #{adminMemberSeq}
 
     admin_mbr_seq = #{adminMemberSeq}
 
     and terminal_type = #{terminalType}
 
     and terminal_type = #{terminalType}
 +
    <if test='seperateByCate == "true"'>
 +
        and cate_lv3_id = #{cateId}
 +
    </if>
 
     <if test="statDateList != null">
 
     <if test="statDateList != null">
 
     and  stat_date in
 
     and  stat_date in
第32行： 第49行：
 
</source>
 
</source>
  
 +
== hsf 测试 ==
 +
=== vip/home/getShopSummary ===
 +
<source lang=json>
 +
[
 +
  "vip/home/getShopSummary-new",
 +
  {
 +
    "adminMemberSeq": "14",
 +
    "statDate": "2018-12-04",
 +
    "statisticsType": "day",
 +
    "prevDate": "2018-12-03",
 +
    "cateId": "330",
 +
    "seperateByCate": "true"
 +
  }
 +
]
 +
</source>
 +
* 比较老版少了4个字段  (dwp_en_dm_vip_comp_cate_term_eff_d)
 +
** statDate {1} fbUv {4} orderAmount {4} shopPv {4} orderCount {4} searchClicks {4} shopUv {4} tmUv {4} searchImps {4} fbPv
 +
** statDate {1} fbUv {4} searchClicks {4} tmUv {4} searchImps {4} fbPv {4}
 +
** 少的字段： orderAmount  shopPv  orderCount  shopUv
 +
 +
=== vip/home/getShopTrends ===
 +
<source lang="json">
 +
[
 +
  "vip/home/getShopTrends-new",
 +
  {
 +
    "adminMemberSeq": "14",
 +
    "beginDate": "2018-12-03",
 +
    "endDate": "2018-12-04",
 +
    "statisticsType": "day",
 +
    "cateId": "145",
 +
    "seperateByCate": "true"
 +
  }
 +
]
 +
</source>
 +
 +
=== vip/home/getShopRegionAnalysis ===
 +
<source lang="json">
 +
[
 +
  "vip/home/getShopRegionAnalysis-new",
 +
  {
 +
    "adminMemberSeq": "14",
 +
    "statDate": "2018-12-04",
 +
    "statisticsType": "day",
 +
    "cateId": "1521",
 +
    "seperateByCate": "true"
 +
  }
 +
]
 +
 +
 +
[
 +
  "vip/home/getShopRegionAnalysis-new",
 +
  {
 +
    "adminMemberSeq": "14",
 +
    "statDate": "2018-12-08",
 +
    "statisticsType": "week",
 +
    "cateId": "1521",
 +
    "seperateByCate": "true"
 +
  }
 +
]
 +
</source>
 +
== oneness查询 ==
 +
=== 行业平均(数据有问题！！！) ===
 +
<source lang="sql">
 +
select
 +
    stat_date as statDate,
 +
    round(cate_lv3_top10_avg,
 +
    1) as cateAvg,
 +
    target_type as indicatorType,
 +
    round(cate_lv3_top10_avg) as cateTop10Avg
 +
from
 +
    dwp_en_dm_vip_catelv3_term_cube_m
 +
where
 +
    cate_lv3_id = '127726013'
 +
    and stat_date >= '2018-11-01'
 +
    and '2018-12-01' >= stat_date
 +
    and terminal_type = 'TOTAL'
 +
    and statistic_type = 'all'
 +
    and target_type in (
 +
        'comp_fb_uv' , 'adm_click_cnt' , 'comp_clk_cnt' , 'comp_atm_uv' , 'comp_imps_cnt' , 'comp_fb_cnt' , 'adm_imps_cnt'
 +
    )
 +
</source>
 +
=== 卡片数据与趋势数据最后一天对应不上（添加选择框后）===
 +
<source lang="sql">
 +
# 为 空
 +
select
 +
    stat_date as statDate,
 +
    fb_uv_1d_013 as fbUv,
 +
    clk_cnt_1d_092 as searchClicks,
 +
    fb_uv_1d_014 as tmUv,
 +
    imps_cnt_1d_027 as searchImps,
 +
    fb_cnt_1d_013 as fbPv
 +
from
 +
    dwp_en_dm_vip_comp_cate_term_eff_d
 +
where
 +
    admin_mbr_seq = '200042360'
 +
    and terminal_type = 'TOTAL'
 +
    and cate_lv3_id = '127820002'
 +
    and stat_date = '2018-12-23'
 +
    and statistic_type = 'all'
 +
</source>
 +
== 线上问题 ==
 +
=== 类目数据 ===
 +
<source lang="sql">
 +
SELECT
 +
    cate_lv2_id, cate_lv2_desc, cate_lv3_id, cate_lv3_desc
 +
FROM
 +
    dwp_en_dm_vip_comp_cate_filter_d
 +
WHERE
 +
    stat_date='2019-01-07'
 +
    AND admin_mbr_seq=230098990
 +
</source>
 +
=== 卡片数据 ===
 +
<source lang="sql">
 +
# 用户supeall 两个3级类目：127726081,  5904002 数据一样
 +
select
 +
    stat_date as statDate,
 +
    fb_uv_1d_013 as fbUv,
 +
    adm_click_cnt_1d_003 as p4pClickCnt,
 +
    clk_cnt_1d_092 as searchClicks,
 +
    fb_uv_1d_014 as tmUv,
 +
    imps_cnt_1d_027 as searchImps,
 +
    fb_cnt_1d_013 as fbPv,
 +
    adm_imps_cnt_1d_001 as p4pExposureCnt
 +
from
 +
    dwp_en_dm_vip_comp_cate_term_eff_d
 +
where
 +
    admin_mbr_seq = '230098990'
 +
    and terminal_type = 'TOTAL'
 +
    and cate_lv3_id = '5904002'
 +
    and stat_date in (
 +
        '2019-01-07'  , '2018-12-31'
 +
    )
 +
    and statistic_type = 'os'
 +
</source>
 
[[category: 阿里]]
 
[[category: 阿里]]
 
[[category: 数据管家]]
 
[[category: 数据管家]]

2019年1月9日 (三) 07:14的最新版本

描述

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

新表

  • dwp_en_dm_vip_comp_term_region_catelv3_cube_d (dwp_en_dm_vip_comp_term_region_cube_d)
  • dwp_en_dm_vip_catelv3_term_cube_d (dwp_en_dm_vip_cate_term_cube_d)
    • 获取具体类目的指标在行业平均效果 （老表不分类目，是店铺的所有类目汇总）
    • 指标包括 'comp_fb_uv' , 'comp_ord_amt' , 'shop_pv' , 'comp_ord_cnt' , 'comp_clk_cnt' , 'comp_atm_uv' , 'shop_uv' , 'comp_imps_cnt' , 'comp_fb_cnt'
  • dwp_en_dm_vip_comp_cate_term_eff_d (dwp_en_dm_vip_comp_term_eff_d)

oneness

vip.home.getShopSummary

select
    ${returnFields} 
from
    <if test='statisticsType == "day" and seperateByCate == "false"'> 
        dwp_en_dm_vip_comp_term_eff_d
    </if>  
    <if test='statisticsType == "week" and seperateByCate == "false"'> 
        dwp_en_dm_vip_comp_term_eff_w
    </if>
    <if test='statisticsType == "month" and seperateByCate == "false"'> 
        dwp_en_dm_vip_comp_term_eff_m
    </if>
    <if test='statisticsType == "day" and seperateByCate == "true"'> 
        dwp_en_dm_vip_comp_cate_term_eff_d
    </if>  
    <if test='statisticsType == "week" and seperateByCate == "true"'> 
        dwp_en_dm_vip_comp_cate_term_eff_w
    </if>
    <if test='statisticsType == "month" and seperateByCate == "true"'> 
        dwp_en_dm_vip_comp_cate_term_eff_m
    </if>
where
    admin_mbr_seq = #{adminMemberSeq}
    and terminal_type = #{terminalType}
    <if test='seperateByCate == "true"'>
        and cate_lv3_id = #{cateId}
    </if>
     <if test="statDateList != null">
     and  stat_date in
     <foreach collection="statDateList" item="item" open="(" separator="," close=")">  
           #{item}  
     </foreach>
     </if>
     <if test="region!= null">
          and  statistic_type = #{region}  
     </if>

hsf 测试

vip/home/getShopSummary

[
  "vip/home/getShopSummary-new",
  {
    "adminMemberSeq": "14",
    "statDate": "2018-12-04",
    "statisticsType": "day",
    "prevDate": "2018-12-03",
    "cateId": "330",
    "seperateByCate": "true"
  }
]
  • 比较老版少了4个字段 (dwp_en_dm_vip_comp_cate_term_eff_d)
    • statDate {1} fbUv {4} orderAmount {4} shopPv {4} orderCount {4} searchClicks {4} shopUv {4} tmUv {4} searchImps {4} fbPv
    • statDate {1} fbUv {4} searchClicks {4} tmUv {4} searchImps {4} fbPv {4}
    • 少的字段： orderAmount shopPv orderCount shopUv

vip/home/getShopTrends

[
  "vip/home/getShopTrends-new",
  {
    "adminMemberSeq": "14",
    "beginDate": "2018-12-03",
    "endDate": "2018-12-04",
    "statisticsType": "day",
    "cateId": "145",
    "seperateByCate": "true"
  }
]

vip/home/getShopRegionAnalysis

[
  "vip/home/getShopRegionAnalysis-new",
  {
    "adminMemberSeq": "14",
    "statDate": "2018-12-04",
    "statisticsType": "day",
    "cateId": "1521",
    "seperateByCate": "true"
  }
]


[
  "vip/home/getShopRegionAnalysis-new",
  {
    "adminMemberSeq": "14",
    "statDate": "2018-12-08",
    "statisticsType": "week",
    "cateId": "1521",
    "seperateByCate": "true"
  }
]

oneness查询

行业平均(数据有问题！！！)

select
    stat_date as statDate,
    round(cate_lv3_top10_avg,
    1) as cateAvg,
    target_type as indicatorType,
    round(cate_lv3_top10_avg) as cateTop10Avg 
from
    dwp_en_dm_vip_catelv3_term_cube_m 
where
    cate_lv3_id = '127726013' 
    and stat_date >= '2018-11-01' 
    and '2018-12-01' >= stat_date 
    and terminal_type = 'TOTAL' 
    and statistic_type = 'all' 
    and target_type in (
        'comp_fb_uv' , 'adm_click_cnt' , 'comp_clk_cnt' , 'comp_atm_uv' , 'comp_imps_cnt' , 'comp_fb_cnt' , 'adm_imps_cnt' 
    )

卡片数据与趋势数据最后一天对应不上（添加选择框后）

# 为 空
select
    stat_date as statDate,
    fb_uv_1d_013 as fbUv,
    clk_cnt_1d_092 as searchClicks,
    fb_uv_1d_014 as tmUv,
    imps_cnt_1d_027 as searchImps,
    fb_cnt_1d_013 as fbPv 
from
    dwp_en_dm_vip_comp_cate_term_eff_d 
where
    admin_mbr_seq = '200042360' 
    and terminal_type = 'TOTAL' 
    and cate_lv3_id = '127820002' 
    and stat_date = '2018-12-23'
    and statistic_type = 'all'

线上问题

类目数据

SELECT
    cate_lv2_id, cate_lv2_desc, cate_lv3_id, cate_lv3_desc
FROM
    dwp_en_dm_vip_comp_cate_filter_d 
WHERE
    stat_date='2019-01-07' 
    AND admin_mbr_seq=230098990

卡片数据

# 用户supeall 两个3级类目：127726081,  5904002 数据一样
select
    stat_date as statDate,
    fb_uv_1d_013 as fbUv,
    adm_click_cnt_1d_003 as p4pClickCnt,
    clk_cnt_1d_092 as searchClicks,
    fb_uv_1d_014 as tmUv,
    imps_cnt_1d_027 as searchImps,
    fb_cnt_1d_013 as fbPv,
    adm_imps_cnt_1d_001 as p4pExposureCnt 
from
    dwp_en_dm_vip_comp_cate_term_eff_d 
where
    admin_mbr_seq = '230098990' 
    and terminal_type = 'TOTAL' 
    and cate_lv3_id = '5904002' 
    and stat_date in (
        '2019-01-07'  , '2018-12-31' 
    ) 
    and statistic_type = 'os'
取自“http://106.14.10.253:8190/index.php?title=首页经营效果中，增加按发布产品拆分指标选项&oldid=959”