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

来自qingwei personal wiki
跳转至： 导航， 搜索
（→‎SQL配置）
（→‎曝访反点）
 
（未显示同一用户的53个中间版本）
第1行： 第1行：
 
== 描述 ==
 
== 描述 ==
 +
=== aone ===
 
[https://aone.alibaba-inc.com/project/705770/task/17744903?spm=a2o8d.corp_prod_req_detail.0.0.616754d5Gx7pVa aone]
 
[https://aone.alibaba-inc.com/project/705770/task/17744903?spm=a2o8d.corp_prod_req_detail.0.0.616754d5Gx7pVa aone]
−
== 接口 ==
+
=== RPC接口 ===
 +
[http://oneness2.alibaba-inc.com/businessFlow?moduleId=145&interfaceId=2649&group=alidata 接口]
 +
== 表 ==
 +
=== 曝访反点 ===
 +
<pre>
 +
dwp_en_dm_comp_term_catelv2_eff_d
 +
dwp_en_dm_comp_term_catelv2_eff_w
 +
dwp_en_dm_comp_term_catelv2_eff_m
 +
</pre>
 +
 
 +
== 需求==
 +
=== 接口 ===
 
external/kpwork/overview
 
external/kpwork/overview
−
== oneService ==
+
=== sql ===
 +
* terminal_type: PC, TOTAL, WS
 
<source lang="sql">
 
<source lang="sql">
 
SELECT
 
SELECT
第11行： 第24行：
 
     uv_1d_169,
 
     uv_1d_169,
 
     fb_cnt_1d_013,
 
     fb_cnt_1d_013,
−
     fst_reply_rate_1m_003
+
     fst_reply_rate_1m_003,
 +
    terminal_type
 
FROM
 
FROM
 
     dwp_en_dm_comp_term_catelv2_eff_d
 
     dwp_en_dm_comp_term_catelv2_eff_d
 
where
 
where
−
     stat_date="2018-10-8"
+
     ( stat_date="2018-10-8"
−
    and statistic_type="ALL"
+
      or stat_date="2018-10-9" )
 
     and admin_mbr_seq="14"
 
     and admin_mbr_seq="14"
−
    and terminal_type="PC"
 
 
     and is_main_cate="Y"
 
     and is_main_cate="Y"
 
     and cate_lv2_id=100010695
 
     and cate_lv2_id=100010695
 
</source>
 
</source>
 +
 +
=== 展现的条目 ===
 +
*  （投）adm_amt_1d (admAmt)  ：最近1天财务消耗数 （财务总投入）
 +
*  （曝）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配置 ===
 
=== SQL配置 ===
−
external.kpwork.overview
+
* external.kpwork.overview
 
<source lang="sql">
 
<source lang="sql">
 
select
 
select
第38行： 第67行：
 
     </if>         
 
     </if>         
 
where
 
where
−
     stat_date= #{statDate}
+
     ( stat_date = #{statDate}  
−
     and admin_mbr_seq= #{adminMemberSeq}
+
      or stat_date = #{prevDate} )
−
     and statistic_type= #{region}
+
     and admin_mbr_seq = #{adminMemberSeq}
−
     and terminal_type= #{terminalType}
+
     and terminal_type = #{terminalType}
−
     and is_main_cate= #{isMainCate}
+
    and is_main_cate = #{isMainCate}
−
     and cate_lv2_id= #{cateLv2Id}
+
    and cate_lv2_id = #{cateLv2Id}
 +
</source>
 +
=== 计算前一天/周/月 ===
 +
<source lang="java">
 +
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);
 +
</source>
 +
 
 +
=== 计算2级主营类目ID (cateLv2Id)  ===
 +
====  ---！鹊桥接口即将废弃，本函数可能会改 ====
 +
<source lang="java">
 +
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;
 +
</source>
 +
==== 新接口 ====
 +
http://oneservice.alibaba-inc.com/v2/view_service_unit.htm?spm=a1zad.8169555.0.0.616bfe14ofZa7f&logicTableId=1022295&sqlId=1001517
 +
<source lang="sql">
 +
select
 +
    cateLv2Id as industryId,
 +
    isMainCate
 +
from
 +
    dwpEnDmCompCatelv2FilterD
 +
where
 +
    adminMbrSeq=#{adminMemberSeq}
 +
    and statDate=#{statDate}
 +
order by
 +
    rank limit 1
 +
</source>
 +
* oneness
 +
<source lang="sql">
 +
select
 +
    ${returnFields}
 +
from
 +
    dwpEnDmCompCatelv2FilterD
 +
where
 +
    adminMbrSeq=#{adminMemberSeq}  
 +
     and statDate=#{statDate}
 +
order by
 +
    rank limit 1
 +
</source>
 +
== 测试 ==
 +
=== 天 ===
 +
* hsf
 +
<source lang="json">
 +
[
 +
  "external/kpwork/overview",
 +
  {
 +
    "adminMemberSeq": "14",
 +
    "statDate": "2018-10-14",
 +
    "statisticsType": "day"
 +
  }
 +
]
 +
</source>
 +
=== 周 ===
 +
* oneService
 +
<source lang="sql">
 +
select
 +
    stat_date as statDate,
 +
    clk_cnt_1d_092 as searchClicks,
 +
    imps_cnt_1d_027 as searchImps,
 +
    fb_cnt_1d_013 as mcCnt,
 +
    uv_1d_169 as visitUv,
 +
    terminal_type as terminalType,
 +
    fst_reply_rate_1m_003 as replyRate 
 +
from
 +
    dwp_en_dm_comp_term_catelv2_eff_w 
 +
where
 +
    (
 +
        stat_date = '2018-11-10'         
 +
        or stat_date = '2018-11-03'     
 +
    )     
 +
    and admin_mbr_seq = 14     
 +
    and is_main_cate = 'Y'     
 +
    and cate_lv2_id = 100010695
 +
order by
 +
    stat_date desc
 +
</source>
 +
*hsf
 +
<source lang="json">
 +
[
 +
  "external/kpwork/overview",
 +
  {
 +
    "adminMemberSeq": "14",
 +
    "statDate": "2018-11-10",
 +
    "statisticsType": "week"
 +
  }
 +
]
 +
</source>
 +
=== 月 ===
 +
* oneService
 +
<source lang="sql">
 +
select
 +
    stat_date as statDate,
 +
    clk_cnt_cm_092 as searchClicks,
 +
    fb_cnt_cm_013 as mcCnt,
 +
    imps_cnt_cm_027 as searchImps,
 +
    uv_cm_169 as visitUv,
 +
    terminal_type as terminalType,
 +
    fst_reply_rate_1m_003 as replyRate
 +
from
 +
    dwp_en_dm_comp_term_catelv2_eff_m
 +
where
 +
    (
 +
        stat_date = '2018-11-01'
 +
        or stat_date = '2018-10-01'
 +
    )
 +
    and admin_mbr_seq = 14
 +
     and is_main_cate = 'Y'
 +
    and cate_lv2_id = 100010695
 +
</source>
 +
* hsf
 +
<source lang="json">
 +
[
 +
  "external/kpwork/overview",
 +
  {
 +
    "adminMemberSeq": "14",
 +
    "statDate": "2018-11-01",
 +
    "statisticsType": "month"
 +
  }
 +
]
 +
</source>
 +
 
 +
== alidataservice 包装接口测试 ==
 +
=== 一些信息 ===
 +
*[https://aone.alibaba-inc.com/task/17744903 aone]
 +
*[http://hsf.alibaba-inc.com/hsfops/testpage/testMethod.htm?envType=pre&service=com.alibaba.data.alidataservice.api.core.service.AlidataService:1.0.0&method=queryForSingle~java.lang.String;java.util.List%3Cjava.lang.String%3E;java.util.Map%3Cjava.lang.String,%20java.lang.Object%3E;com.alibaba.data.alidataservice.api.common.dto.ClientInfo HSF test]
 +
 
 +
=== 天 ===
 +
* hsf
 +
<source lang="json">
 +
[
 +
  "external/kpwork/overview",
 +
  [
 +
    ""
 +
  ],
 +
  {
 +
    "admin_mbr_seq": 14,
 +
    "adminMemberSeq": "14",
 +
    "statDate": "2018-2-10",
 +
    "statisticsType": "day"
 +
  },
 +
  {
 +
    "appInfo": "kpwork",
 +
    "appSecret": "O7QjaM7Orq",
 +
    "class": "com.alibaba.data.alidataservice.api.common.dto.ClientInfo"
 +
  }
 +
]
 +
</source>
 +
== 投 ==
 +
=== TDDL PRECTRL ===
 +
<source lang="xml">
 +
<entry key="dwp_en_dm_comp_maincatelv2_effer_w" value-ref="dwp_en_dm_comp_maincatelv2_effer_w_bean" />
 +
<entry key="dwp_en_dm_comp_maincatelv2_effer_m" value-ref="dwp_en_dm_comp_maincatelv2_effer_m_bean" />
 +
 
 +
 
 +
    <bean id="dwp_en_dm_comp_maincatelv2_effer_w_bean" class="com.taobao.tddl.interact.rule.TableRule">
 +
        <property name="extraPackages">
 +
            <list>
 +
                <value>com.alibaba.icbu.da.tddl.util.B2bIcbuDaPartitionMethod</value>
 +
            </list>
 +
        </property>
 +
        <property name="dbNamePattern" value="SC_ALIDATA_{0000}_GROUP"/>
 +
        <property name="dbRuleArray">
 +
            <value>B2bIcbuDaPartitionMethod.partitionNumber(#admin_mbr_seq,1,16#,16)</value>
 +
        </property>
 +
        <property name="tbNamePattern" value="dwp_en_dm_comp_maincatelv2_effer_w_{0000}"/>
 +
        <property name="tbRuleArray">
 +
            <value>B2bIcbuDaPartitionMethod.partitionWeek(#stat_date,7_date,30#,30)</value>
 +
        </property>
 +
        <property name="allowFullTableScan" value="true"/>
 +
    </bean>
 +
 
 +
    <bean id="dwp_en_dm_comp_maincatelv2_effer_m_bean" class="com.taobao.tddl.interact.rule.TableRule">
 +
        <property name="extraPackages">
 +
            <list>
 +
                <value>com.alibaba.icbu.da.tddl.util.B2bIcbuDaPartitionMethod</value>
 +
            </list>
 +
        </property>
 +
        <property name="dbNamePattern" value="SC_ALIDATA_{0000}_GROUP"/>
 +
        <property name="dbRuleArray">
 +
            <value>B2bIcbuDaPartitionMethod.partitionNumber(#admin_mbr_seq,1,16#,16)</value>
 +
        </property>
 +
        <property name="tbNamePattern" value="dwp_en_dm_comp_maincatelv2_effer_m_{0000}"/>
 +
        <property name="tbRuleArray">
 +
            <value>B2bIcbuDaPartitionMethod.partitionMonth(#stat_date,1_month,8#,8)</value>
 +
        </property>
 +
        <property name="allowFullTableScan" value="true"/>
 +
    </bean>
 +
</source>
 +
* US
 +
<source lang="xml">
 +
<entry key="dwp_en_dm_comp_maincatelv2_effer_w" value-ref="dwp_en_dm_comp_maincatelv2_effer_w_bean" />
 +
<entry key="dwp_en_dm_comp_maincatelv2_effer_m" value-ref="dwp_en_dm_comp_maincatelv2_effer_m_bean" />
 +
 
 +
    <bean id="dwp_en_dm_comp_maincatelv2_effer_w_bean" class="com.taobao.tddl.interact.rule.TableRule">
 +
        <property name="extraPackages">
 +
            <list>
 +
                <value>com.alibaba.icbu.da.tddl.util.B2bIcbuDaPartitionMethod</value>
 +
            </list>
 +
        </property>
 +
        <property name="dbNamePattern" value="SC_ALIDATA_US_{0000}_GROUP"/>
 +
        <property name="dbRuleArray">
 +
            <value>B2bIcbuDaPartitionMethod.partitionNumber(#admin_mbr_seq,1,16#,16)</value>
 +
        </property>
 +
        <property name="tbNamePattern" value="dwp_en_dm_comp_maincatelv2_effer_w_{0000}"/>
 +
        <property name="tbRuleArray">
 +
            <value>B2bIcbuDaPartitionMethod.partitionWeek(#stat_date,7_date,30#,30)</value>
 +
        </property>
 +
        <property name="allowFullTableScan" value="true"/>
 +
    </bean>
 +
 
 +
    <bean id="dwp_en_dm_comp_maincatelv2_effer_m_bean" class="com.taobao.tddl.interact.rule.TableRule">
 +
        <property name="extraPackages">
 +
            <list>
 +
                <value>com.alibaba.icbu.da.tddl.util.B2bIcbuDaPartitionMethod</value>
 +
            </list>
 +
        </property>
 +
        <property name="dbNamePattern" value="SC_ALIDATA_US_{0000}_GROUP"/>
 +
        <property name="dbRuleArray">
 +
            <value>B2bIcbuDaPartitionMethod.partitionNumber(#admin_mbr_seq,1,16#,16)</value>
 +
        </property>
 +
        <property name="tbNamePattern" value="dwp_en_dm_comp_maincatelv2_effer_m_{0000}"/>
 +
        <property name="tbRuleArray">
 +
            <value>B2bIcbuDaPartitionMethod.partitionMonth(#stat_date,1_month,8#,8)</value>
 +
        </property>
 +
        <property name="allowFullTableScan" value="true"/>
 +
    </bean>
 +
</source>
 +
=== SQL ===
 +
<source lang="sql">
 +
select
 +
    ${returnFields}   
 +
from
 +
    <if test='statisticsType == "day"'>
 +
        dwp_en_dm_comp_maincatelv2_eff_d
 +
    </if> 
 +
    <if test='statisticsType == "week"'>
 +
        dwp_en_dm_comp_maincatelv2_eff_w
 +
    </if>
 +
    <if test='statisticsType == "month"'>
 +
        dwp_en_dm_comp_maincatelv2_eff_m
 +
    </if>       
 +
where
 +
    ( stat_date = #{statDate}
 +
      or stat_date = #{prevDate} )
 +
     and admin_mbr_seq = #{adminMemberSeq}
 
</source>
 
</source>
 
[[category: 阿里]]
 
[[category: 阿里]]
 
[[category: 数据管家]]
 
[[category: 数据管家]]

2018年11月28日 (三) 05:01的最新版本

描述

aone

aone

RPC接口

接口

表

曝访反点

dwp_en_dm_comp_term_catelv2_eff_d
dwp_en_dm_comp_term_catelv2_eff_w
dwp_en_dm_comp_term_catelv2_eff_m

需求

接口

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

展现的条目

  • （投）adm_amt_1d (admAmt) ：最近1天财务消耗数 （财务总投入）
  • （曝）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;

新接口

http://oneservice.alibaba-inc.com/v2/view_service_unit.htm?spm=a1zad.8169555.0.0.616bfe14ofZa7f&logicTableId=1022295&sqlId=1001517 

select
    cateLv2Id as industryId,
    isMainCate 
from
    dwpEnDmCompCatelv2FilterD 
where
    adminMbrSeq=#{adminMemberSeq} 
    and statDate=#{statDate} 
order by
    rank limit 1
  • oneness
select
    ${returnFields}
from
    dwpEnDmCompCatelv2FilterD 
where
    adminMbrSeq=#{adminMemberSeq} 
    and statDate=#{statDate} 
order by
    rank limit 1

测试

天

  • hsf
[
  "external/kpwork/overview",
  {
    "adminMemberSeq": "14",
    "statDate": "2018-10-14",
    "statisticsType": "day"
  }
]

周

  • oneService
select
    stat_date as statDate,
    clk_cnt_1d_092 as searchClicks,
    imps_cnt_1d_027 as searchImps,
    fb_cnt_1d_013 as mcCnt,
    uv_1d_169 as visitUv,
    terminal_type as terminalType,
    fst_reply_rate_1m_003 as replyRate  
from
    dwp_en_dm_comp_term_catelv2_eff_w  
where
    (
        stat_date = '2018-11-10'          
        or stat_date = '2018-11-03'      
    )      
    and admin_mbr_seq = 14      
    and is_main_cate = 'Y'      
    and cate_lv2_id = 100010695 
order by
    stat_date desc
  • hsf
[
  "external/kpwork/overview",
  {
    "adminMemberSeq": "14",
    "statDate": "2018-11-10",
    "statisticsType": "week"
  }
]

月

  • oneService
select
    stat_date as statDate,
    clk_cnt_cm_092 as searchClicks,
    fb_cnt_cm_013 as mcCnt,
    imps_cnt_cm_027 as searchImps,
    uv_cm_169 as visitUv,
    terminal_type as terminalType,
    fst_reply_rate_1m_003 as replyRate 
from
    dwp_en_dm_comp_term_catelv2_eff_m 
where
    (
        stat_date = '2018-11-01' 
        or stat_date = '2018-10-01' 
    ) 
    and admin_mbr_seq = 14 
    and is_main_cate = 'Y' 
    and cate_lv2_id = 100010695
  • hsf
[
  "external/kpwork/overview",
  {
    "adminMemberSeq": "14",
    "statDate": "2018-11-01",
    "statisticsType": "month"
  }
]

alidataservice 包装接口测试

一些信息

天

  • hsf
[
  "external/kpwork/overview",
  [
    ""
  ],
  {
    "admin_mbr_seq": 14,
    "adminMemberSeq": "14",
    "statDate": "2018-2-10",
    "statisticsType": "day"
  },
  {
    "appInfo": "kpwork",
    "appSecret": "O7QjaM7Orq",
    "class": "com.alibaba.data.alidataservice.api.common.dto.ClientInfo"
  }
]

投

TDDL PRECTRL

<entry key="dwp_en_dm_comp_maincatelv2_effer_w" value-ref="dwp_en_dm_comp_maincatelv2_effer_w_bean" />
<entry key="dwp_en_dm_comp_maincatelv2_effer_m" value-ref="dwp_en_dm_comp_maincatelv2_effer_m_bean" />


    <bean id="dwp_en_dm_comp_maincatelv2_effer_w_bean" class="com.taobao.tddl.interact.rule.TableRule">
        <property name="extraPackages">
            <list>
                <value>com.alibaba.icbu.da.tddl.util.B2bIcbuDaPartitionMethod</value>
            </list>
        </property>
        <property name="dbNamePattern" value="SC_ALIDATA_{0000}_GROUP"/>
        <property name="dbRuleArray">
            <value>B2bIcbuDaPartitionMethod.partitionNumber(#admin_mbr_seq,1,16#,16)</value>
        </property>
        <property name="tbNamePattern" value="dwp_en_dm_comp_maincatelv2_effer_w_{0000}"/>
        <property name="tbRuleArray">
            <value>B2bIcbuDaPartitionMethod.partitionWeek(#stat_date,7_date,30#,30)</value>
        </property>
        <property name="allowFullTableScan" value="true"/>
    </bean>

    <bean id="dwp_en_dm_comp_maincatelv2_effer_m_bean" class="com.taobao.tddl.interact.rule.TableRule">
        <property name="extraPackages">
            <list>
                <value>com.alibaba.icbu.da.tddl.util.B2bIcbuDaPartitionMethod</value>
            </list>
        </property>
        <property name="dbNamePattern" value="SC_ALIDATA_{0000}_GROUP"/>
        <property name="dbRuleArray">
            <value>B2bIcbuDaPartitionMethod.partitionNumber(#admin_mbr_seq,1,16#,16)</value>
        </property>
        <property name="tbNamePattern" value="dwp_en_dm_comp_maincatelv2_effer_m_{0000}"/>
        <property name="tbRuleArray">
            <value>B2bIcbuDaPartitionMethod.partitionMonth(#stat_date,1_month,8#,8)</value>
        </property>
        <property name="allowFullTableScan" value="true"/>
    </bean>
  • US
<entry key="dwp_en_dm_comp_maincatelv2_effer_w" value-ref="dwp_en_dm_comp_maincatelv2_effer_w_bean" />
<entry key="dwp_en_dm_comp_maincatelv2_effer_m" value-ref="dwp_en_dm_comp_maincatelv2_effer_m_bean" />

    <bean id="dwp_en_dm_comp_maincatelv2_effer_w_bean" class="com.taobao.tddl.interact.rule.TableRule">
        <property name="extraPackages">
            <list>
                <value>com.alibaba.icbu.da.tddl.util.B2bIcbuDaPartitionMethod</value>
            </list>
        </property>
        <property name="dbNamePattern" value="SC_ALIDATA_US_{0000}_GROUP"/>
        <property name="dbRuleArray">
            <value>B2bIcbuDaPartitionMethod.partitionNumber(#admin_mbr_seq,1,16#,16)</value>
        </property>
        <property name="tbNamePattern" value="dwp_en_dm_comp_maincatelv2_effer_w_{0000}"/>
        <property name="tbRuleArray">
            <value>B2bIcbuDaPartitionMethod.partitionWeek(#stat_date,7_date,30#,30)</value>
        </property>
        <property name="allowFullTableScan" value="true"/>
    </bean>

    <bean id="dwp_en_dm_comp_maincatelv2_effer_m_bean" class="com.taobao.tddl.interact.rule.TableRule">
        <property name="extraPackages">
            <list>
                <value>com.alibaba.icbu.da.tddl.util.B2bIcbuDaPartitionMethod</value>
            </list>
        </property>
        <property name="dbNamePattern" value="SC_ALIDATA_US_{0000}_GROUP"/>
        <property name="dbRuleArray">
            <value>B2bIcbuDaPartitionMethod.partitionNumber(#admin_mbr_seq,1,16#,16)</value>
        </property>
        <property name="tbNamePattern" value="dwp_en_dm_comp_maincatelv2_effer_m_{0000}"/>
        <property name="tbRuleArray">
            <value>B2bIcbuDaPartitionMethod.partitionMonth(#stat_date,1_month,8#,8)</value>
        </property>
        <property name="allowFullTableScan" value="true"/>
    </bean>

SQL

select
    ${returnFields}    
from
    <if test='statisticsType == "day"'> 
        dwp_en_dm_comp_maincatelv2_eff_d
    </if>  
    <if test='statisticsType == "week"'> 
        dwp_en_dm_comp_maincatelv2_eff_w
    </if>
    <if test='statisticsType == "month"'> 
        dwp_en_dm_comp_maincatelv2_eff_m
    </if>         
where
    ( stat_date = #{statDate} 
       or stat_date = #{prevDate} )
    and admin_mbr_seq = #{adminMemberSeq}
取自“http://106.14.10.253:8190/index.php?title=KP工作台数据漏斗&oldid=796”