“KP工作台数据漏斗”的版本间的差异

来自qingwei personal wiki
跳转至： 导航， 搜索
（→‎java脚本）
（→‎计算2级主营类目ID (cateLv2Id) ---！鹊桥接口即将废弃，本函数可能会改）
第87行： 第87行：
 
</source>
 
</source>
  
−
=== 计算2级主营类目ID (cateLv2Id)  ---！鹊桥接口即将废弃，本函数可能会改 ===
+
=== 计算2级主营类目ID (cateLv2Id) ===
 +
==== ---！鹊桥接口即将废弃，本函数可能会改 ====
 
<source lang="java">
 
<source lang="java">
 
import com.alibaba.da.pbserver.client.dubbo.result.PBResult;
 
import com.alibaba.da.pbserver.client.dubbo.result.PBResult;
第120行： 第121行：
  
 
return industryId;
 
return industryId;
 +
</source>
 +
==== 新接口 ====
 +
<source lang="sql">
 +
select
 +
    cateLv2Id as industryId,
 +
    isMainCate
 +
from
 +
    dwpEnDmCompCatelv2FilterD
 +
where
 +
    adminMbrSeq=#{adminMemberSeq}
 +
    and statDate=#{statDate}
 +
order by
 +
    rank limit 1
 
</source>
 
</source>
 
[[category: 阿里]]
 
[[category: 阿里]]
 
[[category: 数据管家]]
 
[[category: 数据管家]]

2018年11月14日 (三) 07:02的版本

描述

aone

aone

RPC接口

接口

需求

接口

external/kpwork/overview

sql

  • terminal_type: PC, TOTAL, WS
SELECT
    stat_date,
    imps_cnt_1d_027,
    clk_cnt_1d_092,
    uv_1d_169,
    fb_cnt_1d_013,
    fst_reply_rate_1m_003,
    terminal_type
FROM
    dwp_en_dm_comp_term_catelv2_eff_d
where
    ( stat_date="2018-10-8"
      or stat_date="2018-10-9" )
    and admin_mbr_seq="14"
    and is_main_cate="Y"
    and cate_lv2_id=100010695

展现的条目

  • （投）--- 未加工（投的钱）
  • （曝）imps_cnt_1d_027  : 最近1天排除联盟的搜索item和p4p商品曝光量
  • （访）uv_1d_169  : 最近1天访客数
  • （点）clk_cnt_1d_092 ： 最近1天排除联盟的搜索item和p4p商品相关点击次数
  • （反）fb_cnt_1d_013  : 最近1天有效MC询盘数
  • （？）fst_reply_rate_1m_003: 最近30天及时回复率

oneService

参数

  • statDate (根据statisticsType校验，返回相应错误) 
  • terminalType
  • statisticsType
  • adminMemberSeq

SQL配置

  • external.kpwork.overview
select
    ${returnFields}    
from
    <if test='statisticsType == "day"'> 
        dwp_en_dm_comp_term_catelv2_eff_d
    </if>  
    <if test='statisticsType == "week"'> 
        dwp_en_dm_comp_term_catelv2_eff_w
    </if>
    <if test='statisticsType == "month"'> 
        dwp_en_dm_comp_term_catelv2_eff_m
    </if>         
where
    ( stat_date = #{statDate} 
       or stat_date = #{prevDate} )
    and admin_mbr_seq = #{adminMemberSeq}
    and terminal_type = #{terminalType}
    and is_main_cate = #{isMainCate}
    and cate_lv2_id = #{cateLv2Id}

计算前一天/周/月

import java.text.SimpleDateFormat;
import org.apache.commons.lang.time.DateUtils;


SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date statDate = new Date(sdf.parse(statDateStr).getTime());
Date prevDate = statDate;
if ("day".equalsIgnoreCase(statisticsType)) {
    prevDate = DateUtils.addDays(statDate, -1);
} else if ("week".equalsIgnoreCase(statisticsType)) {
    prevDate = DateUtils.addWeeks(statDate, -1);
} else if ("month".equalsIgnoreCase(statisticsType)) {
    prevDate = DateUtils.addMonths(statDate, -1);
}

return sdf.format(prevDate);

计算2级主营类目ID (cateLv2Id)

---！鹊桥接口即将废弃，本函数可能会改

import com.alibaba.da.pbserver.client.dubbo.result.PBResult;
import com.alibaba.da.pbserver.client.service.PiebridgeHsfServicesLocator;
import com.alibaba.da.pbserver.server.dubbo.api.IPBHsfService;
import com.alibaba.data.alidataservice.infrastructure.util.ResultChecker;
import org.apache.commons.collections.MapUtils;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;


String PBID_GET_OVERVIEW_MAIN_INDUSTRY = "1BDE5F7A0A7D35266592C87B492946E4";
IPBHsfService pbHsfService = PiebridgeHsfServicesLocator.getPiebridgeService();

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date requestDate = new Date(sdf.parse(statDate).getTime());

Map<String, Object> params = new HashMap<>(2);
params.put("adminMemberSeq", adminMemberSeq);
params.put("statDate", requestDate);

PBResult<Map<String, Object>> mainCatePbResult = pbHsfService.queryForSingle(PBID_GET_OVERVIEW_MAIN_INDUSTRY, params);

Map<String, Object> mainCateResult = ResultChecker.checkAndReturnPbResult(mainCatePbResult);

Long industryId = -1L;
if (MapUtils.isNotEmpty(mainCateResult)) {
    industryId = (Long) mainCateResult.get("industryId");
}

return industryId;

新接口

select
    cateLv2Id as industryId,
    isMainCate 
from
    dwpEnDmCompCatelv2FilterD 
where
    adminMbrSeq=#{adminMemberSeq} 
    and statDate=#{statDate} 
order by
    rank limit 1
取自“http://106.14.10.253:8190/index.php?title=KP工作台数据漏斗&oldid=742”