[toc]

项目资源

指标口径、数据表关系

指标口径、数据表关系梳理

来源表数据模型

ITSY_DWA.DWA_V_D_CUS_QK_USERS_INFO [全客]用户信息宽表(日)
ITSY_DWD.DWD_D_EVT_QK_STRATEGY_INFO--策略信息明细表
DIM.DIM_D_PUB_QK_POLICY_RELATION--政策关系表
ITSY_DWA.DWA_V_D_CUS_QK_TOUCH_USER_INFO_L7 [全客]用户信息表(触达类T-7)(日)
ITSY_DWD.DWD_D_EVT_QK_PLAN_CHANNEL [全客]触点信息明细(日)

会议纪要-20220322

  1. 数仓:在用户信息宽表中增加字段source_type,区分SMS(来源于触达表)
    原有触达拉链表拆分为触达用户表(T-1日表,按日更新)+短信拉链表(T-1拉链近60天数据,一个day_id是包含近60天的数据)
  2. 经分:触达需要回刷的触达类指标,口径变更,账期增加字段day_id、month_id,缩小数据范围
  3. 经分:经分侧拿用户信息宽表非短信部分+短信拉链表形成新表,以便出T-8指标
  4. 经分:由于短信拉链表中不包含user_id,剔重需按照“用户手机号码+策略+触点+发送时间+接触时间”:
    group by 用户手机号码+策略+触点,order by 发送时间+接触时间,降序后取1条
  5. 经分需注意:月指标需要每天更新

风险:方案变更可能导致延期

行云脚本开发

由于来源表的宽表月账期也是按日刷新,所以在CUBE层可以将日表和月表获取指标的过程合并,然后在导数到应用集群时再拆分开。

CUBE层开发

建表语句

  • ITSY_CUBE.DM_KPI_STRATEGY_RPT_MID
CREATE
    TABLE
        ITSY_CUBE.DM_KPI_STRATEGY_RPT_MID(
            DATE_ID VARCHAR(8) COMMENT '日账期' ,
            PROV_ID VARCHAR(3) COMMENT '策略创建省分' ,
            AREA_ID VARCHAR(20) COMMENT '策略创建地市' ,
            STRATEGY_ID VARCHAR(100) COMMENT '策略ID' ,
            MARKET_DELINEATE_USER NUMBER COMMENT '营销圈定用户' ,
            FILTER_USER NUMBER COMMENT '过滤用户' ,
            FILTER_LATER_USER NUMBER COMMENT '过滤后用户' ,
            UNKNOWN_IS_REACH_USER NUMBER COMMENT '未知是否触达用户触点未闭环部分' ,
            UNKNOWN_IS_REACH_USER_B NUMBER COMMENT '未知是否触达用户触点闭环部分(当前的不确定性指标)' ,
            REACH_CB_NOT_ORDER_USER NUMBER COMMENT '触达成功cB未订购用户' ,
            REACH_CB_ORDER_USER NUMBER COMMENT '触达成功cB订购用户' ,
            UNKNOWN_CB_ORDER_USER NUMBER COMMENT '未知是否触达cB订购用户' ,
            REACH_CB_ORDER_SUCCESS_USER NUMBER COMMENT '触达成功cB订购成功用户' ,
            REACH_CB_ORDER_FAIL_USER NUMBER COMMENT '触达成功cB订购失败用户' ,
            REACH_CB_ORDER_IN_USER NUMBER COMMENT '触达成功cB订购中用户' ,
            UNKNOWN_CB_ORDER_SUCCESS_USER NUMBER COMMENT '未知是否触达cB订购成功用户' ,
            UNKNOWN_CB_ORDER_FAIL_USER NUMBER COMMENT '未知是否触达cB订购失败用户' ,
            UNKNOWN_CB_ORDER_IN_USER NUMBER COMMENT '未知是否触达cB订购中用户',    
            MARKET_DELINEATE_USER_LJ NUMBER COMMENT '营销圈定用户累计',
            FILTER_USER_LJ NUMBER COMMENT '过滤用户累计',
            FILTER_LATER_USER_LJ NUMBER COMMENT '过滤后用户累计',
            UNKNOWN_IS_REACH_USER_LJ NUMBER COMMENT '未知是否触达用户触点未闭环部分累计',
            UNKNOWN_IS_REACH_USER_B_LJ NUMBER COMMENT '未知是否触达用户触点闭环部分(当前的不确定性指标)累计',
            REACH_CB_NOT_ORDER_USER_LJ NUMBER COMMENT '触达成功cB未订购用户累计',
            REACH_CB_ORDER_USER_LJ NUMBER COMMENT '触达成功cB订购用户累计',
            UNKNOWN_CB_ORDER_USER_LJ NUMBER COMMENT '未知是否触达cB订购用户累计',
            REACH_CB_ORDER_SUCCESS_USER_LJ NUMBER COMMENT '触达成功cB订购成功用户累计',
            REACH_CB_ORDER_FAIL_USER_LJ NUMBER COMMENT '触达成功cB订购失败用户累计',
            REACH_CB_ORDER_IN_USER_LJ NUMBER COMMENT '触达成功cB订购中用户累计',
            UNKNOWN_CB_ORDER_SUCCESS_USER_LJ NUMBER COMMENT '未知是否触达cB订购成功用户累计',
            UNKNOWN_CB_ORDER_FAIL_USER_LJ NUMBER COMMENT '未知是否触达cB订购失败用户累计',
            UNKNOWN_CB_ORDER_IN_USER_LJ NUMBER COMMENT '未知是否触达cB订购中用户累计'
        ) PARTITIONED BY(
            DATE_ID ,
            PROV_ID
        )
;
  • ITSY_CUBE.DM_KPI_STRATEGY_RPT
CREATE
    TABLE
        ITSY_CUBE.DM_KPI_STRATEGY_RPT(
            DATE_ID VARCHAR(8) COMMENT '日账期' ,
            PROV_ID VARCHAR(3) COMMENT '策略创建省分' ,
            AREA_ID VARCHAR(10) COMMENT '策略创建地市' ,
            STRATEGY_ID VARCHAR(100) COMMENT '策略ID' ,
            WHOLE_NET_USER NUMBER COMMENT '全网用户' ,
            NOT_DELINEATE_USER NUMBER COMMENT '非圈定用户' ,
            MARKET_DELINEATE_USER NUMBER COMMENT '营销圈定用户' ,
            FILTER_USER NUMBER COMMENT '过滤用户' ,
            FILTER_LATER_USER NUMBER COMMENT '过滤后用户' ,
            UNKNOWN_IS_REACH_USER NUMBER COMMENT '未知是否触达用户触点未闭环部分' ,
            REACH_CB_NOT_ORDER_USER NUMBER COMMENT '触达成功cB未订购用户' ,
            REACH_CB_ORDER_USER NUMBER COMMENT '触达成功cB订购用户' ,
            UNKNOWN_CB_NOT_ORDER_USER NUMBER COMMENT '未知是否触达cB未订购用户' ,
            UNKNOWN_CB_ORDER_USER NUMBER COMMENT '未知是否触达cB订购用户' ,
            REACH_CB_ORDER_SUCCESS_USER NUMBER COMMENT '触达成功cB订购成功用户' ,
            REACH_CB_ORDER_FAIL_USER NUMBER COMMENT '触达成功cB订购失败用户' ,
            REACH_CB_ORDER_IN_USER NUMBER COMMENT '触达成功cB订购中用户' ,
            UNKNOWN_CB_ORDER_SUCCESS_USER NUMBER COMMENT '未知是否触达cB订购成功用户' ,
            UNKNOWN_CB_ORDER_FAIL_USER NUMBER COMMENT '未知是否触达cB订购失败用户' ,
            UNKNOWN_CB_ORDER_IN_USER NUMBER COMMENT '未知是否触达cB订购中用户' ,
            CB_ORDER_USER NUMBER COMMENT 'cB订购用户' ,
            CB_ORDER_SUCCESS_USER NUMBER COMMENT 'cB订购成功用户',
            WHOLE_NET_USER_LJ NUMBER COMMENT '全网用户累计' ,
            NOT_DELINEATE_USER_LJ NUMBER COMMENT '非圈定用户累计' ,
            MARKET_DELINEATE_USER_LJ NUMBER COMMENT '营销圈定用户累计' ,
            FILTER_USER_LJ NUMBER COMMENT '过滤用户累计' ,
            FILTER_LATER_USER_LJ NUMBER COMMENT '过滤后用户累计' ,
            UNKNOWN_IS_REACH_USER_LJ NUMBER COMMENT '未知是否触达用户触点未闭环部分累计' ,
            REACH_CB_NOT_ORDER_USER_LJ NUMBER COMMENT '触达成功cB未订购用户累计' ,
            REACH_CB_ORDER_USER_LJ NUMBER COMMENT '触达成功cB订购用户累计' ,
            UNKNOWN_CB_NOT_ORDER_USER_LJ NUMBER COMMENT '未知是否触达cB未订购用户累计' ,
            UNKNOWN_CB_ORDER_USER_LJ NUMBER COMMENT '未知是否触达cB订购用户累计' ,
            REACH_CB_ORDER_SUCCESS_USER_LJ NUMBER COMMENT '触达成功cB订购成功用户累计' ,
            REACH_CB_ORDER_FAIL_USER_LJ NUMBER COMMENT '触达成功cB订购失败用户累计' ,
            REACH_CB_ORDER_IN_USER_LJ NUMBER COMMENT '触达成功cB订购中用户累计' ,
            UNKNOWN_CB_ORDER_SUCCESS_USER_LJ NUMBER COMMENT '未知是否触达cB订购成功用户累计' ,
            UNKNOWN_CB_ORDER_FAIL_USER_LJ NUMBER COMMENT '未知是否触达cB订购失败用户累计' ,
            UNKNOWN_CB_ORDER_IN_USER_LJ NUMBER COMMENT '未知是否触达cB订购中用户累计' ,
            CB_ORDER_USER_LJ NUMBER COMMENT 'cB订购用户累计' ,
            CB_ORDER_SUCCESS_USER_LJ NUMBER COMMENT 'cB订购成功用户累计'
        ) PARTITIONED BY(
            DATE_ID ,
            PROV_ID
        )
;
  • ITSY_CUBE.DM_KPI_STRATEGY_CH_MID
CREATE
    TABLE
        ITSY_CUBE.DM_KPI_STRATEGY_CH_MID(
            DATE_ID VARCHAR(8) COMMENT '日账期' ,
            PROV_ID VARCHAR(3) COMMENT '策略创建省分' ,
            AREA_ID VARCHAR(10) COMMENT '策略创建地市' ,
            STRATEGY_ID VARCHAR(100) COMMENT '策略ID' ,
            --日指标
            REACH_FAIL_USER NUMBER COMMENT '触点失败用户',
            REACH_SUCCESS_USER NUMBER COMMENT '触点成功用户',
            UNKNOWN_IS_REACH_USER NUMBER COMMENT '未知是否触达用户触点闭环部分',
            --月指标
            REACH_FAIL_USER_LJ NUMBER COMMENT '触点失败用户累计',
            REACH_SUCCESS_USER_LJ NUMBER COMMENT '触点成功用户累计',
            UNKNOWN_IS_REACH_USER_LJ NUMBER COMMENT '未知是否触达用户触点闭环部分累计'
        ) PARTITIONED BY(
            DATE_ID ,
            PROV_ID
        )
;
  • ITSY_CUBE.DM_KPI_STRATEGY_CH
CREATE
    TABLE
        ITSY_CUBE.DM_KPI_STRATEGY_CH(
            DATE_ID VARCHAR(8) COMMENT '日账期' ,
            PROV_ID VARCHAR(3) COMMENT '策略创建省分' ,
            AREA_ID VARCHAR(10) COMMENT '策略创建地市' ,
            STRATEGY_ID VARCHAR(100) COMMENT '策略ID' ,
            --日指标
            REACH_FAIL_USER NUMBER COMMENT '触点失败用户',
            REACH_SUCCESS_USER NUMBER COMMENT '触点成功用户',
            UNKNOWN_IS_REACH_USER NUMBER COMMENT '未知是否触达用户触点闭环部分',
            --月指标
            REACH_FAIL_USER_LJ NUMBER COMMENT '触点失败用户累计',
            REACH_SUCCESS_USER_LJ NUMBER COMMENT '触点成功用户累计',
            UNKNOWN_IS_REACH_USER_LJ NUMBER COMMENT '未知是否触达用户触点闭环部分累计'
        ) PARTITIONED BY(
            DATE_ID
        )
;

加工脚本

  • ITSY_CUBE.DM_KPI_STRATEGY_RPT_MID
--INSERT INTO DM_KPI_STRATEGY_RPT_MID PARTITION ON (DATE_ID='20220301',PROV_ID='010')
SELECT 
    MONTH_ID, --账期
    CREATE_PROV_ID, --策略创建省份
    CREATE_AREA_ID, --策略创建地市
    STRATEGY_ID, --策略ID
    SUM(MARKET_DELINEATE_USER),--营销圈定用户
    SUM(FILTER_USER),--过滤用户
    SUM(FILTER_LATER_USER),--过滤后用户
    SUM(UNKNOWN_IS_REACH_USER),--未知是否触达用户触点未闭环部分
    SUM(UNKNOWN_IS_REACH_USER_B),--未知是否触达用户触点闭环部分
    SUM(REACH_CB_NOT_ORDER_USER),--触达成功cB未订购用户
    SUM(REACH_CB_ORDER_USER),--触达成功cB订购用户
    SUM(UNKNOWN_CB_ORDER_USER),--未知是否触达cB订购用户
    SUM(REACH_CB_ORDER_SUCCESS_USER),--触达成功cB订购成功用户
    SUM(REACH_CB_ORDER_FAIL_USER),--触达成功cB订购失败用户
    SUM(REACH_CB_ORDER_IN_USER),--触达成功cB订购中用户
    SUM(UNKNOWN_CB_ORDER_SUCCESS_USER),--未知是否触达cB订购成功用户
    SUM(UNKNOWN_CB_ORDER_FAIL_USER),--未知是否触达cB订购失败用户
    SUM(UNKNOWN_CB_ORDER_IN_USER),--未知是否触达cB订购中用户
    SUM(MARKET_DELINEATE_USER_LJ),--营销圈定用户月累计
    SUM(FILTER_USER_LJ),--过滤用户月累计
    SUM(FILTER_LATER_USER_LJ),--过滤后用户月累计
    SUM(UNKNOWN_IS_REACH_USER_LJ),--未知是否触达用户触点未闭环部分月累计
    SUM(UNKNOWN_IS_REACH_USER_B_LJ),--未知是否触达用户触点闭环部分月累计
    SUM(REACH_CB_NOT_ORDER_USER_LJ),--触达成功cB未订购用户月累计
    SUM(REACH_CB_ORDER_USER_LJ),--触达成功cB订购用户月累计
    SUM(UNKNOWN_CB_ORDER_USER_LJ),--未知是否触达cB订购用户月累计
    SUM(REACH_CB_ORDER_SUCCESS_USER_LJ),--触达成功cB订购成功用户月累计
    SUM(REACH_CB_ORDER_FAIL_USER_LJ),--触达成功cB订购失败用户月累计
    SUM(REACH_CB_ORDER_IN_USER_LJ),--触达成功cB订购中用户月累计
    SUM(UNKNOWN_CB_ORDER_SUCCESS_USER_LJ),--未知是否触达cB订购成功用户月累计
    SUM(UNKNOWN_CB_ORDER_FAIL_USER_LJ),--未知是否触达cB订购失败用户月累计
    SUM(UNKNOWN_CB_ORDER_IN_USER_LJ)--未知是否触达cB订购中用户月累计
FROM (
    SELECT 
        MONTH_ID, --账期
        CREATE_PROV_ID, --策略创建省份
        CREATE_AREA_ID, --策略创建地市
        PARENT_STRATEGY_ID STRATEGY_ID, --策略ID
        COUNT(DISTINCT(CASE WHEN END_DATE >= '20220301' 
            THEN USER_ID END)) MARKET_DELINEATE_USER, --营销圈定用户
        COUNT(DISTINCT(CASE WHEN END_DATE >= '20220301' AND LOOP_RESULT IN ('0','1') AND PUSH_CHANNEL_RESULT = '0' 
            THEN USER_ID END)) FILTER_USER, --过滤用户
        COUNT(DISTINCT(CASE WHEN END_DATE >= '20220301' AND (LOOP_RESULT IN ('0','1') AND PUSH_CHANNEL_RESULT = '0') OR LOOP_RESULT IN ('2','3') 
            THEN USER_ID END)) FILTER_LATER_USER, --过滤后用户
        COUNT(DISTINCT(CASE WHEN INPUT_DATE = '20220301' AND LOOP_RESULT IN ('2','3') 
            THEN USER_ID END)) UNKNOWN_IS_REACH_USER, --未知是否触达用户触点未闭环部分    
        COUNT(DISTINCT(CASE WHEN CONTACT_TIME = '20220301' AND LOOP_RESULT IN ('2','3') 
            THEN USER_ID END)) UNKNOWN_IS_REACH_USER_B, --未知是否触达用户触点闭环部分(当前的不确定性指标)
        COUNT(DISTINCT(CASE WHEN ACCEPT_DATE = '20220301' AND LOOP_RESULT IN ('0','1') AND PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT = '1' AND SUBSCRIBE_STATE IS NULL 
            THEN USER_ID END)) REACH_CB_NOT_ORDER_USER, --触达成功CB未订购用户
        COUNT(DISTINCT(CASE WHEN ACCEPT_DATE = '20220301' AND  LOOP_RESULT IN ('0','1') AND PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT = '1' AND SUBSCRIBE_STATE IS NOT NULL 
            THEN USER_ID END)) REACH_CB_ORDER_USER, --触达成功CB订购用户
        COUNT(DISTINCT(CASE WHEN ACCEPT_DATE = '20220301' AND ((LOOP_RESULT IN ('0','1') AND PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT IS NULL) OR LOOP_RESULT IN ('2','3')) AND SUBSCRIBE_STATE IS NOT NULL 
            THEN USER_ID END)) UNKNOWN_CB_ORDER_USER, --未知是否触达CB订购用户
        COUNT(DISTINCT(CASE WHEN ACCEPT_DATE = '20220301' AND LOOP_RESULT IN ('0','1') AND PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT = '1' AND SUBSCRIBE_STATE = '9' 
            THEN USER_ID END)) REACH_CB_ORDER_SUCCESS_USER, --触达成功CB订购成功用户
        COUNT(DISTINCT(CASE WHEN ACCEPT_DATE = '20220301' AND LOOP_RESULT IN ('0','1') AND PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT = '1' AND SUBSCRIBE_STATE = '3' 
            THEN USER_ID END)) REACH_CB_ORDER_FAIL_USER, --触达成功CB订购失败用户
        COUNT(DISTINCT(CASE WHEN ACCEPT_DATE = '20220301' AND LOOP_RESULT IN ('0','1') AND PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT = '1' AND SUBSCRIBE_STATE IN ('6','5','2','1','B','0') 
            THEN USER_ID END)) REACH_CB_ORDER_IN_USER, --触达成功CB订购中用户
        COUNT(DISTINCT(CASE WHEN ACCEPT_DATE = '20220301' AND ((LOOP_RESULT IN ('0','1') AND PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT IS NULL) OR LOOP_RESULT IN ('2','3')) AND SUBSCRIBE_STATE = '9' 
            THEN USER_ID END)) UNKNOWN_CB_ORDER_SUCCESS_USER, --未知是否触达CB订购成功用户
        COUNT(DISTINCT(CASE WHEN ACCEPT_DATE = '20220301' AND ((LOOP_RESULT IN ('0','1') AND PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT IS NULL) OR LOOP_RESULT IN ('2','3')) AND SUBSCRIBE_STATE = '3' 
            THEN USER_ID END)) UNKNOWN_CB_ORDER_FAIL_USER, --未知是否触达CB订购失败用户
        COUNT(DISTINCT(CASE WHEN ACCEPT_DATE = '20220301' AND ((LOOP_RESULT IN ('0','1') AND PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT IS NULL) OR LOOP_RESULT IN ('2','3')) AND SUBSCRIBE_STATE IN ('6','5','2','1','B','0') 
            THEN USER_ID END)) UNKNOWN_CB_ORDER_IN_USER, --未知是否触达CB订购中用户
        COUNT(DISTINCT(USER_ID))MARKET_DELINEATE_USER_LJ,--营销圈定用户月累计
        COUNT(DISTINCT (CASE WHEN LOOP_RESULT IN ('0','1') AND PUSH_CHANNEL_RESULT = '0'
            THEN USER_ID END))FILTER_USER_LJ,--过滤用户月累计
        COUNT(DISTINCT (CASE WHEN (LOOP_RESULT IN ('0','1') AND PUSH_CHANNEL_RESULT = '0') OR LOOP_RESULT IN ('2','3')
            THEN USER_ID END))FILTER_LATER_USER_LJ,--过滤后用户月累计
        COUNT(DISTINCT (CASE WHEN LOOP_RESULT IN ('2','3') AND SUBSTRING(INPUT_DATE, 1, 6) = '20220301'
            THEN USER_ID END))UNKNOWN_IS_REACH_USER_LJ,--未知是否触达用户触点未闭环部分月累计
        COUNT(DISTINCT (CASE WHEN LOOP_RESULT IN ('0','1') AND PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT  IS NULL  AND SUBSTRING(contact_time, 1, 6) = '20220301'
            THEN USER_ID END))UNKNOWN_IS_REACH_USER_B_LJ,--未知是否触达用户触点闭环部分月累计
        COUNT(DISTINCT (CASE WHEN LOOP_RESULT IN ('1','2') AND PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT = '1' AND SUBSCRIBE_STATE IS NULL AND SUBSTRING(ACCEPT_DATE, 1, 6) = '20220301'
            THEN USER_ID END))REACH_CB_NOT_ORDER_USER_LJ,--触达成功CB未订购用户月累计
        COUNT(DISTINCT (CASE WHEN LOOP_RESULT IN ('1','2') AND PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT = '1' AND SUBSCRIBE_STATE IS NOT NULL AND SUBSTRING(ACCEPT_DATE, 1, 6) = '20220301'
            THEN USER_ID END))REACH_CB_ORDER_USER_LJ,--触达成功CB订购用户月累计
        COUNT(DISTINCT (CASE WHEN ((LOOP_RESULT IN ('1','0') AND PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT IS NULL) OR LOOP_RESULT IN ('2','3')) AND SUBSCRIBE_STATE IS NOT NULL AND SUBSTRING(ACCEPT_DATE, 1, 6) = '20220301'
            THEN USER_ID END))UNKNOWN_CB_ORDER_USER_LJ,--未知是否触达CB订购用户月累计
        COUNT(DISTINCT (CASE WHEN LOOP_RESULT IN ('0','1') AND PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT = '1' AND SUBSCRIBE_STATE IN ('9') AND   SUBSTRING(ACCEPT_DATE, 1, 6) = '20220301'
            THEN USER_ID END ))REACH_CB_ORDER_SUCCESS_USER_LJ,--触达成功CB订购成功用户月累计
        COUNT(DISTINCT (CASE WHEN LOOP_RESULT IN ('0','1') AND PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT = '1' AND SUBSCRIBE_STATE IN ('3') AND SUBSTRING(ACCEPT_DATE, 1, 6) = '20220301'
            THEN USER_ID END ))REACH_CB_ORDER_FAIL_USER_LJ,--触达成功CB订购失败用户月累计
        COUNT(DISTINCT (CASE WHEN LOOP_RESULT IN ('0','1') AND PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT = '1' AND SUBSCRIBE_STATE IN ('6','5','2','1','B','0') AND SUBSTRING(ACCEPT_DATE, 1, 6) = '20220301'
            THEN USER_ID END ))REACH_CB_ORDER_IN_USER_LJ,--触达成功CB订购中用户月累计
        COUNT(DISTINCT(CASE WHEN ((LOOP_RESULT IN ('0','1')AND PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT = '1')OR LOOP_RESULT IN ('2','3'))AND SUBSCRIBE_STATE IN ('9') AND SUBSTRING(ACCEPT_DATE, 1, 6) = '20220301'
            THEN USER_ID END ))UNKNOWN_CB_ORDER_SUCCESS_USER_LJ,--未知是否触达CB订购成功用户月累计
        COUNT(DISTINCT(CASE WHEN ((LOOP_RESULT IN ('0','1')AND PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT = '1')OR LOOP_RESULT IN ('2','3'))AND SUBSCRIBE_STATE IN ('3') AND SUBSTRING(ACCEPT_DATE, 1, 6) = '20220301'
            THEN USER_ID END))UNKNOWN_CB_ORDER_FAIL_USER_LJ,--未知是否触达CB订购失败用户月累计
        COUNT(DISTINCT(CASE WHEN ((LOOP_RESULT IN ('0','1')AND PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT = '1')OR LOOP_RESULT IN ('2','3'))AND SUBSCRIBE_STATE IN ('6','5','2','1','B','0') AND SUBSTRING(ACCEPT_DATE, 1, 6) = '20220301'
        THEN USER_ID END))UNKNOWN_CB_ORDER_IN_USER_LJ--未知是否触达CB订购中用户月累计
    FROM ITSY_DWA.DWA_V_D_CUS_QK_USERS_INFO --全客用户信息宽表
    WHERE MONTH_ID = '20220301' 
        AND CREATE_PROV_ID = '010'
    GROUP BY 
        MONTH_ID, --账期
        CREATE_PROV_ID , --省份
        CREATE_AREA_ID , --地市
        PARENT_STRATEGY_ID --策略ID
)

GROUP BY 
    MONTH_ID, --账期
    CREATE_PROV_ID, --策略创建省份
    CREATE_AREA_ID, --策略创建地市
    STRATEGY_ID  --策略ID
    
    
UNION ALL 

--省份汇总


SELECT 
    MONTH_ID, --账期
    CREATE_PROV_ID, --策略创建省份
    '-1', --策略创建地市
    STRATEGY_ID, --策略ID
    SUM(MARKET_DELINEATE_USER),--营销圈定用户
    SUM(FILTER_USER),--过滤用户
    SUM(FILTER_LATER_USER),--过滤后用户
    SUM(UNKNOWN_IS_REACH_USER),--未知是否触达用户触点未闭环部分
    SUM(UNKNOWN_IS_REACH_USER_B),--未知是否触达用户触点闭环部分
    SUM(REACH_CB_NOT_ORDER_USER),--触达成功cB未订购用户
    SUM(REACH_CB_ORDER_USER),--触达成功cB订购用户
    SUM(UNKNOWN_CB_ORDER_USER),--未知是否触达cB订购用户
    SUM(REACH_CB_ORDER_SUCCESS_USER),--触达成功cB订购成功用户
    SUM(REACH_CB_ORDER_FAIL_USER),--触达成功cB订购失败用户
    SUM(REACH_CB_ORDER_IN_USER),--触达成功cB订购中用户
    SUM(UNKNOWN_CB_ORDER_SUCCESS_USER),--未知是否触达cB订购成功用户
    SUM(UNKNOWN_CB_ORDER_FAIL_USER),--未知是否触达cB订购失败用户
    SUM(UNKNOWN_CB_ORDER_IN_USER),--未知是否触达cB订购中用户
    SUM(MARKET_DELINEATE_USER_LJ),--营销圈定用户月累计
    SUM(FILTER_USER_LJ),--过滤用户月累计
    SUM(FILTER_LATER_USER_LJ),--过滤后用户月累计
    SUM(UNKNOWN_IS_REACH_USER_LJ),--未知是否触达用户触点未闭环部分月累计
    SUM(UNKNOWN_IS_REACH_USER_B_LJ),--未知是否触达用户触点闭环部分月累计
    SUM(REACH_CB_NOT_ORDER_USER_LJ),--触达成功cB未订购用户月累计
    SUM(REACH_CB_ORDER_USER_LJ),--触达成功cB订购用户月累计
    SUM(UNKNOWN_CB_ORDER_USER_LJ),--未知是否触达cB订购用户月累计
    SUM(REACH_CB_ORDER_SUCCESS_USER_LJ),--触达成功cB订购成功用户月累计
    SUM(REACH_CB_ORDER_FAIL_USER_LJ),--触达成功cB订购失败用户月累计
    SUM(REACH_CB_ORDER_IN_USER_LJ),--触达成功cB订购中用户月累计
    SUM(UNKNOWN_CB_ORDER_SUCCESS_USER_LJ),--未知是否触达cB订购成功用户月累计
    SUM(UNKNOWN_CB_ORDER_FAIL_USER_LJ),--未知是否触达cB订购失败用户月累计
    SUM(UNKNOWN_CB_ORDER_IN_USER_LJ)--未知是否触达cB订购中用户月累计
FROM (
    SELECT 
        MONTH_ID, --账期
        CREATE_PROV_ID, --策略创建省份
        '-1', --策略创建地市
        PARENT_STRATEGY_ID STRATEGY_ID, --策略ID
        COUNT(DISTINCT(CASE WHEN END_DATE >= '20220301' 
            THEN USER_ID END)) MARKET_DELINEATE_USER, --营销圈定用户
        COUNT(DISTINCT(CASE WHEN END_DATE >= '20220301' AND LOOP_RESULT IN ('0','1') AND PUSH_CHANNEL_RESULT = '0' 
            THEN USER_ID END)) FILTER_USER, --过滤用户
        COUNT(DISTINCT(CASE WHEN END_DATE >= '20220301' AND (LOOP_RESULT IN ('0','1') AND PUSH_CHANNEL_RESULT = '0') OR LOOP_RESULT IN ('2','3') 
            THEN USER_ID END)) FILTER_LATER_USER, --过滤后用户
        COUNT(DISTINCT(CASE WHEN INPUT_DATE = '20220301' AND LOOP_RESULT IN ('2','3') 
            THEN USER_ID END)) UNKNOWN_IS_REACH_USER, --未知是否触达用户触点未闭环部分    
        COUNT(DISTINCT(CASE WHEN CONTACT_TIME = '20220301' AND LOOP_RESULT IN ('2','3') 
            THEN USER_ID END)) UNKNOWN_IS_REACH_USER_B, --未知是否触达用户触点闭环部分(当前的不确定性指标)
        COUNT(DISTINCT(CASE WHEN ACCEPT_DATE = '20220301' AND LOOP_RESULT IN ('0','1') AND PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT = '1' AND SUBSCRIBE_STATE IS NULL 
            THEN USER_ID END)) REACH_CB_NOT_ORDER_USER, --触达成功CB未订购用户
        COUNT(DISTINCT(CASE WHEN ACCEPT_DATE = '20220301' AND  LOOP_RESULT IN ('0','1') AND PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT = '1' AND SUBSCRIBE_STATE IS NOT NULL 
            THEN USER_ID END)) REACH_CB_ORDER_USER, --触达成功CB订购用户
        COUNT(DISTINCT(CASE WHEN ACCEPT_DATE = '20220301' AND ((LOOP_RESULT IN ('0','1') AND PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT IS NULL) OR LOOP_RESULT IN ('2','3')) AND SUBSCRIBE_STATE IS NOT NULL 
            THEN USER_ID END)) UNKNOWN_CB_ORDER_USER, --未知是否触达CB订购用户
        COUNT(DISTINCT(CASE WHEN ACCEPT_DATE = '20220301' AND LOOP_RESULT IN ('0','1') AND PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT = '1' AND SUBSCRIBE_STATE = '9' 
            THEN USER_ID END)) REACH_CB_ORDER_SUCCESS_USER, --触达成功CB订购成功用户
        COUNT(DISTINCT(CASE WHEN ACCEPT_DATE = '20220301' AND LOOP_RESULT IN ('0','1') AND PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT = '1' AND SUBSCRIBE_STATE = '3' 
            THEN USER_ID END)) REACH_CB_ORDER_FAIL_USER, --触达成功CB订购失败用户
        COUNT(DISTINCT(CASE WHEN ACCEPT_DATE = '20220301' AND LOOP_RESULT IN ('0','1') AND PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT = '1' AND SUBSCRIBE_STATE IN ('6','5','2','1','B','0') 
            THEN USER_ID END)) REACH_CB_ORDER_IN_USER, --触达成功CB订购中用户
        COUNT(DISTINCT(CASE WHEN ACCEPT_DATE = '20220301' AND ((LOOP_RESULT IN ('0','1') AND PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT IS NULL) OR LOOP_RESULT IN ('2','3')) AND SUBSCRIBE_STATE = '9' 
            THEN USER_ID END)) UNKNOWN_CB_ORDER_SUCCESS_USER, --未知是否触达CB订购成功用户
        COUNT(DISTINCT(CASE WHEN ACCEPT_DATE = '20220301' AND ((LOOP_RESULT IN ('0','1') AND PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT IS NULL) OR LOOP_RESULT IN ('2','3')) AND SUBSCRIBE_STATE = '3' 
            THEN USER_ID END)) UNKNOWN_CB_ORDER_FAIL_USER, --未知是否触达CB订购失败用户
        COUNT(DISTINCT(CASE WHEN ACCEPT_DATE = '20220301' AND ((LOOP_RESULT IN ('0','1') AND PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT IS NULL) OR LOOP_RESULT IN ('2','3')) AND SUBSCRIBE_STATE IN ('6','5','2','1','B','0') 
            THEN USER_ID END)) UNKNOWN_CB_ORDER_IN_USER, --未知是否触达CB订购中用户
        COUNT(DISTINCT(USER_ID))MARKET_DELINEATE_USER_LJ,--营销圈定用户月累计
        COUNT(DISTINCT (CASE WHEN LOOP_RESULT IN ('0','1') AND PUSH_CHANNEL_RESULT = '0'
            THEN USER_ID END))FILTER_USER_LJ,--过滤用户月累计
        COUNT(DISTINCT (CASE WHEN (LOOP_RESULT IN ('0','1') AND PUSH_CHANNEL_RESULT = '0') OR LOOP_RESULT IN ('2','3')
            THEN USER_ID END))FILTER_LATER_USER_LJ,--过滤后用户月累计
        COUNT(DISTINCT (CASE WHEN LOOP_RESULT IN ('2','3') AND SUBSTRING(INPUT_DATE, 1, 6) = '20220301'
            THEN USER_ID END))UNKNOWN_IS_REACH_USER_LJ,--未知是否触达用户触点未闭环部分月累计
        COUNT(DISTINCT (CASE WHEN LOOP_RESULT IN ('0','1') AND PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT  IS NULL  AND SUBSTRING(contact_time, 1, 6) = '20220301'
            THEN USER_ID END))UNKNOWN_IS_REACH_USER_B_LJ,--未知是否触达用户触点闭环部分月累计
        COUNT(DISTINCT (CASE WHEN LOOP_RESULT IN ('1','2') AND PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT = '1' AND SUBSCRIBE_STATE IS NULL AND SUBSTRING(ACCEPT_DATE, 1, 6) = '20220301'
            THEN USER_ID END))REACH_CB_NOT_ORDER_USER_LJ,--触达成功CB未订购用户月累计
        COUNT(DISTINCT (CASE WHEN LOOP_RESULT IN ('1','2') AND PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT = '1' AND SUBSCRIBE_STATE IS NOT NULL AND SUBSTRING(ACCEPT_DATE, 1, 6) = '20220301'
            THEN USER_ID END))REACH_CB_ORDER_USER_LJ,--触达成功CB订购用户月累计
        COUNT(DISTINCT (CASE WHEN ((LOOP_RESULT IN ('1','0') AND PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT IS NULL) OR LOOP_RESULT IN ('2','3')) AND SUBSCRIBE_STATE IS NOT NULL AND SUBSTRING(ACCEPT_DATE, 1, 6) = '20220301'
            THEN USER_ID END))UNKNOWN_CB_ORDER_USER_LJ,--未知是否触达CB订购用户月累计
        COUNT(DISTINCT (CASE WHEN LOOP_RESULT IN ('0','1') AND PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT = '1' AND SUBSCRIBE_STATE IN ('9') AND   SUBSTRING(ACCEPT_DATE, 1, 6) = '20220301'
            THEN USER_ID END ))REACH_CB_ORDER_SUCCESS_USER_LJ,--触达成功CB订购成功用户月累计
        COUNT(DISTINCT (CASE WHEN LOOP_RESULT IN ('0','1') AND PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT = '1' AND SUBSCRIBE_STATE IN ('3') AND SUBSTRING(ACCEPT_DATE, 1, 6) = '20220301'
            THEN USER_ID END ))REACH_CB_ORDER_FAIL_USER_LJ,--触达成功CB订购失败用户月累计
        COUNT(DISTINCT (CASE WHEN LOOP_RESULT IN ('0','1') AND PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT = '1' AND SUBSCRIBE_STATE IN ('6','5','2','1','B','0') AND SUBSTRING(ACCEPT_DATE, 1, 6) = '20220301'
            THEN USER_ID END ))REACH_CB_ORDER_IN_USER_LJ,--触达成功CB订购中用户月累计
        COUNT(DISTINCT(CASE WHEN ((LOOP_RESULT IN ('0','1')AND PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT = '1')OR LOOP_RESULT IN ('2','3'))AND SUBSCRIBE_STATE IN ('9') AND SUBSTRING(ACCEPT_DATE, 1, 6) = '20220301'
            THEN USER_ID END ))UNKNOWN_CB_ORDER_SUCCESS_USER_LJ,--未知是否触达CB订购成功用户月累计
        COUNT(DISTINCT(CASE WHEN ((LOOP_RESULT IN ('0','1')AND PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT = '1')OR LOOP_RESULT IN ('2','3'))AND SUBSCRIBE_STATE IN ('3') AND SUBSTRING(ACCEPT_DATE, 1, 6) = '20220301'
            THEN USER_ID END))UNKNOWN_CB_ORDER_FAIL_USER_LJ,--未知是否触达CB订购失败用户月累计
        COUNT(DISTINCT(CASE WHEN ((LOOP_RESULT IN ('0','1')AND PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT = '1')OR LOOP_RESULT IN ('2','3'))AND SUBSCRIBE_STATE IN ('6','5','2','1','B','0') AND SUBSTRING(ACCEPT_DATE, 1, 6) = '20220301'
        THEN USER_ID END))UNKNOWN_CB_ORDER_IN_USER_LJ--未知是否触达CB订购中用户月累计
    FROM ITSY_DWA.DWA_V_D_CUS_QK_USERS_INFO --全客用户信息宽表
    WHERE MONTH_ID = '20220301' 
        AND CREATE_PROV_ID = '010'
    GROUP BY 
        MONTH_ID, --账期
        CREATE_PROV_ID , --省份
        PARENT_STRATEGY_ID --策略ID
)

GROUP BY 
    MONTH_ID, --账期
    CREATE_PROV_ID, --策略创建省份
    STRATEGY_ID  --策略ID
  • ITSY_CUBE.DM_KPI_STRATEGY_RPT
--INSERT INTO DM_KPI_STRATEGY_RPT_D PARTITION ON (DATE_ID='''||V_DATE||''',PROV_ID='''||V_PROV||''')

--地市指标及省份汇总指标

SELECT 
    T0.DATE_ID, --日账期
    T0.PROV_ID, --省份
    T0.AREA_ID, --地市
    T0.STRATEGY_ID, --策略ID
    --日表部分指标
    SUM(T1.USER_NUM), --全网用户
    SUM(T1.USER_NUM) - SUM(T0.MARKET_DELINEATE_USER), --非圈定用户
    SUM(T0.MARKET_DELINEATE_USER), --营销圈定用户
    SUM(T0.FILTER_USER), --过滤用户
    SUM(T0.FILTER_LATER_USER), --过滤后用户
    SUM(T0.UNKNOWN_IS_REACH_USER), --未知是否触达用户触点未闭环部分
    SUM(T0.REACH_CB_NOT_ORDER_USER), --触达成功CB未订购用户
    SUM(T0.REACH_CB_ORDER_USER), --触达成功CB订购用户
    SUM(T0.UNKNOWN_IS_REACH_USER) + SUM(T0.UNKNOWN_IS_REACH_USER_B) - SUM(T0.UNKNOWN_CB_ORDER_USER), --未知是否触达CB未订购用户
    SUM(T0.UNKNOWN_CB_ORDER_USER), --未知是否触达CB订购用户
    SUM(T0.REACH_CB_ORDER_SUCCESS_USER), --触达成功CB订购成功用户
    SUM(T0.REACH_CB_ORDER_FAIL_USER), --触达成功CB订购失败用户
    SUM(T0.REACH_CB_ORDER_IN_USER), --触达成功CB订购中用户
    SUM(T0.UNKNOWN_CB_ORDER_SUCCESS_USER), --未知是否触达CB订购成功用户
    SUM(T0.UNKNOWN_CB_ORDER_FAIL_USER), --未知是否触达CB订购失败用户
    SUM(T0.UNKNOWN_CB_ORDER_IN_USER), --未知是否触达CB订购中用户
    SUM(T0.UNKNOWN_CB_ORDER_USER) + SUM(T0.REACH_CB_ORDER_USER), --CB订购用户
    SUM(T0.REACH_CB_ORDER_SUCCESS_USER) + SUM(T0.UNKNOWN_CB_ORDER_SUCCESS_USER), --CB订购成功用户
    --月表部分指标
    SUM(T1.USER_NUM_LJ), --全网用户累计
    SUM(T1.USER_NUM_LJ) - SUM(T0.MARKET_DELINEATE_USER_LJ), --非圈定用户累计
    SUM(T0.MARKET_DELINEATE_USER_LJ), --营销圈定用户累计
    SUM(T0.FILTER_USER_LJ), --过滤用户累计
    SUM(T0.FILTER_LATER_USER_LJ), --过滤后用户累计
    SUM(T0.UNKNOWN_IS_REACH_USER_LJ), --未知是否触达用户触点未闭环部分累计
    SUM(T0.REACH_CB_NOT_ORDER_USER_LJ), --触达成功CB未订购用户累计
    SUM(T0.REACH_CB_ORDER_USER_LJ), --触达成功CB订购用户累计
    SUM(T0.UNKNOWN_IS_REACH_USER_LJ) + SUM(T0.UNKNOWN_IS_REACH_USER_B_LJ) - SUM(T0.UNKNOWN_CB_ORDER_USER_LJ), --未知是否触达CB未订购用户累计
    SUM(T0.UNKNOWN_CB_ORDER_USER_LJ), --未知是否触达CB订购用户累计
    SUM(T0.REACH_CB_ORDER_SUCCESS_USER_LJ), --触达成功CB订购成功用户累计
    SUM(T0.REACH_CB_ORDER_FAIL_USER_LJ), --触达成功CB订购失败用户累计
    SUM(T0.REACH_CB_ORDER_IN_USER_LJ), --触达成功CB订购中用户累计
    SUM(T0.UNKNOWN_CB_ORDER_SUCCESS_USER_LJ), --未知是否触达CB订购成功用户累计
    SUM(T0.UNKNOWN_CB_ORDER_FAIL_USER_LJ), --未知是否触达CB订购失败用户累计
    SUM(T0.UNKNOWN_CB_ORDER_IN_USER_LJ), --未知是否触达CB订购中用户累计
    SUM(T0.UNKNOWN_CB_ORDER_USER_LJ) + SUM(T0.REACH_CB_ORDER_USER_LJ), --CB订购用户累计
    SUM(T0.REACH_CB_ORDER_SUCCESS_USER_LJ) + SUM(T0.UNKNOWN_CB_ORDER_SUCCESS_USER_LJ) --CB订购成功用户累计
    
FROM (
    SELECT *
    FROM ITSY_CUBE.DM_KPI_STRATEGY_RPT_MID --全客策略维度日表中间表
    WHERE DATE_ID = '''||V_DATE||'''
        AND PROV_ID = '''||V_PROV||'''
) T0 LEFT JOIN (
    SELECT 
        DATE_ID,
        PROV_ID,
        AREA_ID,
        USER_NUM, --全网用户数
        USER_NUM_LJ --全网用户数累计
    FROM ITSY_CUBE.DM_C_D_CUS_MB_BASE_REPORT  --经分表
    WHERE DATE_ID = '''||V_DATE||'''
        AND PROV_ID = '''||V_PROV||'''
) T1 ON T0.DATE_ID = T1.DATE_ID AND T0.PROV_ID = T1.PROV_ID AND T0.AREA_ID = T1.AREA_ID

GROUP BY 
    T0.DATE_ID, --日账期
    T0.PROV_ID, --省份
    T0.AREA_ID, --地市
    T0.STRATEGY_ID --策略ID
    
UNION ALL 

--全国汇总

SELECT 
    T0.DATE_ID, --日账期
    ''111'', --省份
    ''-1'', --地市
    T0.STRATEGY_ID, --策略ID
    --日表部分指标
    SUM(T1.USER_NUM), --全网用户
    SUM(T1.USER_NUM) - SUM(T0.MARKET_DELINEATE_USER), --非圈定用户
    SUM(T0.MARKET_DELINEATE_USER), --营销圈定用户
    SUM(T0.FILTER_USER), --过滤用户
    SUM(T0.FILTER_LATER_USER), --过滤后用户
    SUM(T0.UNKNOWN_IS_REACH_USER), --未知是否触达用户触点未闭环部分
    SUM(T0.REACH_CB_NOT_ORDER_USER), --触达成功CB未订购用户
    SUM(T0.REACH_CB_ORDER_USER), --触达成功CB订购用户
    SUM(T0.UNKNOWN_IS_REACH_USER) + SUM(T0.UNKNOWN_IS_REACH_USER_B) - SUM(T0.UNKNOWN_CB_ORDER_USER), --未知是否触达CB未订购用户
    SUM(T0.UNKNOWN_CB_ORDER_USER), --未知是否触达CB订购用户
    SUM(T0.REACH_CB_ORDER_SUCCESS_USER), --触达成功CB订购成功用户
    SUM(T0.REACH_CB_ORDER_FAIL_USER), --触达成功CB订购失败用户
    SUM(T0.REACH_CB_ORDER_IN_USER), --触达成功CB订购中用户
    SUM(T0.UNKNOWN_CB_ORDER_SUCCESS_USER), --未知是否触达CB订购成功用户
    SUM(T0.UNKNOWN_CB_ORDER_FAIL_USER), --未知是否触达CB订购失败用户
    SUM(T0.UNKNOWN_CB_ORDER_IN_USER), --未知是否触达CB订购中用户
    SUM(T0.UNKNOWN_CB_ORDER_USER) + SUM(T0.REACH_CB_ORDER_USER), --CB订购用户
    SUM(T0.REACH_CB_ORDER_SUCCESS_USER) + SUM(T0.UNKNOWN_CB_ORDER_SUCCESS_USER), --CB订购成功用户
    -- 月表部分指标
    SUM(T1.USER_NUM_LJ), --全网用户累计
    SUM(T1.USER_NUM_LJ) - SUM(T0.MARKET_DELINEATE_USER_LJ), --非圈定用户累计
    SUM(T0.MARKET_DELINEATE_USER_LJ), --营销圈定用户累计
    SUM(T0.FILTER_USER_LJ), --过滤用户累计
    SUM(T0.FILTER_LATER_USER_LJ), --过滤后用户累计
    SUM(T0.UNKNOWN_IS_REACH_USER_LJ), --未知是否触达用户触点未闭环部分累计
    SUM(T0.REACH_CB_NOT_ORDER_USER_LJ), --触达成功CB未订购用户累计
    SUM(T0.REACH_CB_ORDER_USER_LJ), --触达成功CB订购用户累计
    SUM(T0.UNKNOWN_IS_REACH_USER_LJ) + SUM(T0.UNKNOWN_IS_REACH_USER_B_LJ) - SUM(T0.UNKNOWN_CB_ORDER_USER_LJ), --未知是否触达CB未订购用户累计
    SUM(T0.UNKNOWN_CB_ORDER_USER_LJ), --未知是否触达CB订购用户累计
    SUM(T0.REACH_CB_ORDER_SUCCESS_USER_LJ), --触达成功CB订购成功用户累计
    SUM(T0.REACH_CB_ORDER_FAIL_USER_LJ), --触达成功CB订购失败用户累计
    SUM(T0.REACH_CB_ORDER_IN_USER_LJ), --触达成功CB订购中用户累计
    SUM(T0.UNKNOWN_CB_ORDER_SUCCESS_USER_LJ), --未知是否触达CB订购成功用户累计
    SUM(T0.UNKNOWN_CB_ORDER_FAIL_USER_LJ), --未知是否触达CB订购失败用户累计
    SUM(T0.UNKNOWN_CB_ORDER_IN_USER_LJ), --未知是否触达CB订购中用户累计
    SUM(T0.UNKNOWN_CB_ORDER_USER_LJ) + SUM(T0.REACH_CB_ORDER_USER_LJ), --CB订购用户累计
    SUM(T0.REACH_CB_ORDER_SUCCESS_USER_LJ) + SUM(T0.UNKNOWN_CB_ORDER_SUCCESS_USER_LJ) --CB订购成功用户累计
    
FROM (
    SELECT *
    FROM ITSY_CUBE.DM_KPI_STRATEGY_RPT_MID --全客策略维度日表中间表
    WHERE DATE_ID = '''||V_DATE||'''
        AND AREA_ID = ''-1''
) T0 LEFT JOIN (
    SELECT 
        DATE_ID,
        PROV_ID,
        AREA_ID,
        USER_NUM, --全网用户数
        USER_NUM_LJ --全网用户数累计
    FROM ITSY_CUBE.DM_C_D_CUS_MB_BASE_REPORT  --经分表
    WHERE DATE_ID = '''||V_DATE||'''
        AND AREA_ID = ''-1''
) T1 ON T0.DATE_ID = T1.DATE_ID AND T0.PROV_ID = T1.PROV_ID AND T0.AREA_ID = T1.AREA_ID

GROUP BY 
    T0.DATE_ID, --日账期
    T0.STRATEGY_ID
  • ITSY_CUBE.DM_KPI_STRATEGY_CH_MID
INSERT INTO ITSY_CUBE.DM_KPI_STRATEGY_CH_MID PARTITION ON (DATE_ID='20220301',PROV_ID='010')
--地市指标
SELECT 
    MONTH_ID,
    PROV_ID,
    AREA_ID,
    PARENT_STRATEGY_ID,
    COUNT(DISTINCT CASE WHEN LOOP_RESULT IN('0','1') AND PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT = '1' AND CONTACT_TIME= '20220301'  THEN USER_ID END),--触达成功用户(当前的不确定的指标)
    COUNT(DISTINCT CASE WHEN PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT = '0' AND CONTACT_TIME= '20220301'  THEN USER_ID END),--触达失败用户(当前的不确定的指标)
    COUNT(DISTINCT CASE WHEN LOOP_RESULT IN('0','1') AND PUSH_CHANNEL_RESULT = '1' AND SUBSCRIBE_STATE IS NULL AND CONTACT_TIME= '20220301' THEN USER_ID END),--未知是否触达用户 --触点闭环部分(当前的不确定的指标)
    COUNT(DISTINCT CASE WHEN LOOP_RESULT IN('0','1') AND PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT = '1' AND SUBSTR(CONTACT_TIME,1,6)= '20220301'  THEN USER_ID END),--触达成功用户(当前的不确定的指标)
    COUNT(DISTINCT CASE WHEN PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT = '0' AND SUBSTR(CONTACT_TIME,1,6)= '202203'  THEN USER_ID END),--触达失败用户(当前的不确定的指标)
    COUNT(DISTINCT CASE WHEN LOOP_RESULT IN('0','1') AND PUSH_CHANNEL_RESULT = '1' AND SUBSCRIBE_STATE IS NULL AND SUBSTR(CONTACT_TIME,1,6)= '202203' THEN USER_ID END)--未知是否触达用户 --触点闭环部分(当前的不确定的指标)
FROM (
    SELECT * FROM ITSY_DWA.DWA_V_D_CUS_QK_USERS_INFO 
    WHERE MONTH_ID = '20220301'
        AND PROV_ID = '010'
)
GROUP BY 
    MONTH_ID,
    PROV_ID,
    AREA_ID,
    PARENT_STRATEGY_ID
    
UNION ALL 
 
--省份汇总
 
SELECT 
    MONTH_ID,
    PROV_ID,
    '-1',
    PARENT_STRATEGY_ID,
    COUNT(DISTINCT CASE WHEN LOOP_RESULT IN('0','1') AND PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT = '1' AND CONTACT_TIME= '20220301'  THEN USER_ID END),--触达成功用户(当前的不确定的指标)
    COUNT(DISTINCT CASE WHEN PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT = '0' AND CONTACT_TIME= '20220301'  THEN USER_ID END),--触达失败用户(当前的不确定的指标)
    COUNT(DISTINCT CASE WHEN LOOP_RESULT IN('0','1') AND PUSH_CHANNEL_RESULT = '1' AND SUBSCRIBE_STATE IS NULL AND CONTACT_TIME= '20220301' THEN USER_ID END),--未知是否触达用户 --触点闭环部分(当前的不确定的指标)
    COUNT(DISTINCT CASE WHEN LOOP_RESULT IN('0','1') AND PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT = '1' AND SUBSTR(CONTACT_TIME,1,6)= '20220301'  THEN USER_ID END),--触达成功用户(当前的不确定的指标)
    COUNT(DISTINCT CASE WHEN PUSH_CHANNEL_RESULT = '1' AND CONTACT_RESULT = '0' AND SUBSTR(CONTACT_TIME,1,6)= '202203'  THEN USER_ID END),--触达失败用户(当前的不确定的指标)
    COUNT(DISTINCT CASE WHEN LOOP_RESULT IN('0','1') AND PUSH_CHANNEL_RESULT = '1' AND SUBSCRIBE_STATE IS NULL AND SUBSTR(CONTACT_TIME,1,6)= '202203' THEN USER_ID END)--未知是否触达用户 --触点闭环部分(当前的不确定的指标)
FROM (
    SELECT * FROM ITSY_DWA.DWA_V_D_CUS_QK_USERS_INFO 
    WHERE MONTH_ID = '20220301'
        AND PROV_ID = '010'
)
GROUP BY 
    MONTH_ID,
    PROV_ID,
    PARENT_STRATEGY_ID

INSERT INTO ITSY_CUBE.DM_KPI_STRATEGY_CH_MID PARTITION ON (DATE_ID='20220222',PROV_ID='010')
 
--短信部分
SELECT
    DATE_ID,
    PROV_ID,
    AREA_ID,
    STRATEGY_ID,
    SUM(REACH_FAIL_USER), --触达失败用户
    SUM(REACH_SUCCESS_USER), --触达成功用户
    SUM(UNKNOWN_IS_REACH_USER), --未知是否触达用户触点闭环部分
    SUM(REACH_FAIL_USER_LJ), --触达失败用户累计
    SUM(REACH_SUCCESS_USER_LJ), --触达成功用户累计
    SUM(UNKNOWN_IS_REACH_USER_LJ) --未知是否触达用户触点闭环部分累计
FROM (
 
 
    SELECT 
        DATE_ID, --日账期
        PROV_ID, --策略归属省分
        AREA_ID, --策略归属地市
        STRATEGY_ID, --策略ID
        SUM(CASE WHEN CONTACT_DATE = '20220301' AND IS_CONTACT_SUCCESS = '0' THEN 1 ELSE 0 END) REACH_FAIL_USER, --触达失败用户
        SUM(CASE WHEN CONTACT_DATE = '20220301' AND IS_CONTACT_SUCCESS = '1' THEN 1 ELSE 0 END) REACH_SUCCESS_USER, --触达成功用户
        SUM(CASE WHEN SEND_DATE = '20220222' AND IS_CONTACT_SUCCESS IS NULL THEN 1 ELSE 0 END) UNKNOWN_IS_REACH_USER, --未知是否触达用户触点闭环部分
        SUM(CASE WHEN SUBSTRING(CONTACT_DATE,1,6) = '202202' AND IS_CONTACT_SUCCESS = '0' THEN 1 ELSE 0 END) REACH_FAIL_USER_LJ, --触达失败用户累计    
        SUM(CASE WHEN SUBSTRING(CONTACT_DATE,1,6) = '202202' AND IS_CONTACT_SUCCESS = '1' THEN 1 ELSE 0 END) REACH_SUCCESS_USER_LJ, --触达成功用户累计
        SUM(CASE WHEN SUBSTRING(SEND_DATE,1,6) ='202202' AND IS_CONTACT_SUCCESS IS NULL THEN 1 ELSE 0 END) UNKNOWN_IS_REACH_USER_LJ --未知是否触达用户触点闭环部分累计
    FROM (
        SELECT 
            DATE_ID,
            MONTH_ID,
            DAY_ID,
            CREATE_PROV_ID PROV_ID,
            CREATE_AREA_ID AREA_ID,
            IS_CONTACT_SUCCESS,
            SEND_DATE,
            CONTACT_DATE,
            STRATEGY_ID
        FROM 
        (
            SELECT 
                DATE_ID,
                MONTH_ID,
                DAY_ID,
                CREATE_PROV_ID,
                CREATE_AREA_ID,
                IS_CONTACT_SUCCESS,
                SEND_DATE,
                CONTACT_DATE,
                SUBSTRB(STRATEGY_ID,1,REGEXP_INSTR(STRATEGY_ID,'-')-1) STRATEGY_ID, --策略ID截取"-"之前部分获取父策略ID
                ROW_NUMBER() OVER(PARTITION BY STRATEGY_ID,CHANNEL_CODE,DEVICE_NUMBER ORDER BY SEND_DATE DESC) RN --根据策略ID,触点编码,用户号码分组打标
            FROM ITSY_DWA.DWA_V_D_CUS_QK_TOUCH_USER_INFO_L7  --触达类拉链表T-7
            WHERE DATE_ID = '20220222'
                AND CREATE_PROV_ID = '010'
                AND SOURCE_TYPE = 'SMS'
        ) T0 
        WHERE RN = '1'
    )
 
    GROUP BY 
        DATE_ID, --日账期
        PROV_ID, --策略归属省分
        AREA_ID , --策略归属地市
        STRATEGY_ID  --策略ID
)
 
GROUP BY 
    DATE_ID,
    PROV_ID,
    AREA_ID,
    STRATEGY_ID
    
UNION ALL 
 
--短信部分省份汇总
 
SELECT
    DATE_ID,
    PROV_ID,
    '-1',
    STRATEGY_ID,
    SUM(REACH_FAIL_USER), --触达失败用户
    SUM(REACH_SUCCESS_USER), --触达成功用户
    SUM(UNKNOWN_IS_REACH_USER), --未知是否触达用户触点闭环部分
    SUM(REACH_FAIL_USER_LJ), --触达失败用户累计
    SUM(REACH_SUCCESS_USER_LJ), --触达成功用户累计
    SUM(UNKNOWN_IS_REACH_USER_LJ) --未知是否触达用户触点闭环部分累计
FROM (
 
 
    SELECT 
        DATE_ID, --日账期
        PROV_ID, --策略归属省分
        AREA_ID, --策略归属地市
        STRATEGY_ID, --策略ID
        SUM(CASE WHEN CONTACT_DATE = '20220301' AND IS_CONTACT_SUCCESS = '0' THEN 1 ELSE 0 END) REACH_FAIL_USER, --触达失败用户
        SUM(CASE WHEN CONTACT_DATE = '20220301' AND IS_CONTACT_SUCCESS = '1' THEN 1 ELSE 0 END) REACH_SUCCESS_USER, --触达成功用户
        SUM(CASE WHEN SEND_DATE = '20220222' AND IS_CONTACT_SUCCESS IS NULL THEN 1 ELSE 0 END) UNKNOWN_IS_REACH_USER, --未知是否触达用户触点闭环部分
        SUM(CASE WHEN SUBSTRING(CONTACT_DATE,1,6) = '202202' AND IS_CONTACT_SUCCESS = '0' THEN 1 ELSE 0 END) REACH_FAIL_USER_LJ, --触达失败用户累计    
        SUM(CASE WHEN SUBSTRING(CONTACT_DATE,1,6) = '202202' AND IS_CONTACT_SUCCESS = '1' THEN 1 ELSE 0 END) REACH_SUCCESS_USER_LJ, --触达成功用户累计
        SUM(CASE WHEN SUBSTRING(SEND_DATE,1,6) ='202202' AND IS_CONTACT_SUCCESS IS NULL THEN 1 ELSE 0 END) UNKNOWN_IS_REACH_USER_LJ --未知是否触达用户触点闭环部分累计
    FROM (
        SELECT 
            DATE_ID,
            CREATE_PROV_ID PROV_ID,
            CREATE_AREA_ID AREA_ID,
            MONTH_ID,
            DAY_ID,
            IS_CONTACT_SUCCESS,
            SEND_DATE,
            CONTACT_DATE,
            STRATEGY_ID
        FROM 
        (
            SELECT 
                DATE_ID,
                CREATE_PROV_ID,
                CREATE_AREA_ID,
                MONTH_ID,
                DAY_ID,
                IS_CONTACT_SUCCESS,
                SEND_DATE,
                CONTACT_DATE,
                SUBSTRB(STRATEGY_ID,1,REGEXP_INSTR(STRATEGY_ID,'-')-1) STRATEGY_ID, --策略ID截取"-"之前部分获取父策略ID
                ROW_NUMBER() OVER(PARTITION BY STRATEGY_ID,CHANNEL_CODE,DEVICE_NUMBER ORDER BY SEND_DATE DESC) RN --根据策略ID,触点编码,用户号码分组打标
            FROM ITSY_DWA.DWA_V_D_CUS_QK_TOUCH_USER_INFO_L7  --触达类拉链表T-7
            WHERE DATE_ID = '20220222'
                AND CREATE_PROV_ID = '010'
                AND SOURCE_TYPE = 'SMS'
        ) T0 
        WHERE RN = '1'
    )
 
    GROUP BY 
        DATE_ID, --日账期
        PROV_ID, --策略归属省分
        AREA_ID, --策略归属地市    
        STRATEGY_ID  --策略ID
)
 
GROUP BY 
    DATE_ID,
    PROV_ID,
    STRATEGY_ID
 
    
UNION ALL 
 
--非短信部分
 
SELECT
    DATE_ID,
    PROV_ID,
    AREA_ID,
    STRATEGY_ID,
    SUM(REACH_FAIL_USER), --触达失败用户
    SUM(REACH_SUCCESS_USER), --触达成功用户
    SUM(UNKNOWN_IS_REACH_USER), --未知是否触达用户触点闭环部分
    SUM(REACH_FAIL_USER_LJ), --触达失败用户累计
    SUM(REACH_SUCCESS_USER_LJ), --触达成功用户累计
    SUM(UNKNOWN_IS_REACH_USER_LJ) --未知是否触达用户触点闭环部分累计
FROM (
 
 
    SELECT 
        DATE_ID, --日账期
        PROV_ID, --策略归属省分
        AREA_ID, --策略归属地市
        STRATEGY_ID, --策略ID
        SUM(CASE WHEN CONTACT_DATE = '20220222' AND IS_CONTACT_SUCCESS = '0' THEN 1 ELSE 0 END) REACH_FAIL_USER, --触达失败用户
        SUM(CASE WHEN CONTACT_DATE = '20220222' AND IS_CONTACT_SUCCESS = '1' THEN 1 ELSE 0 END) REACH_SUCCESS_USER, --触达成功用户
        SUM(CASE WHEN CONTACT_DATE = '20220222' AND IS_CONTACT_SUCCESS IS NULL THEN 1 ELSE 0 END) UNKNOWN_IS_REACH_USER, --未知是否触达用户触点闭环部分
        SUM(CASE WHEN SUBSTRING(CONTACT_DATE,1,6) = '202202' AND IS_CONTACT_SUCCESS = '0' THEN 1 ELSE 0 END) REACH_FAIL_USER_LJ, --触达失败用户累计    
        SUM(CASE WHEN SUBSTRING(CONTACT_DATE,1,6) = '202202' AND IS_CONTACT_SUCCESS = '1' THEN 1 ELSE 0 END) REACH_SUCCESS_USER_LJ, --触达成功用户累计
        SUM(CASE WHEN SUBSTRING(CONTACT_DATE,1,6) = '202202' AND IS_CONTACT_SUCCESS IS NULL THEN 1 ELSE 0 END) UNKNOWN_IS_REACH_USER_LJ --未知是否触达用户触点闭环部分累计
    FROM (
        SELECT 
            DATE_ID,
            CREATE_PROV_ID PROV_ID,
            CREATE_AREA_ID AREA_ID,
            IS_CONTACT_SUCCESS,
            SEND_DATE,
            CONTACT_DATE,
            STRATEGY_ID
        FROM 
        (
            SELECT 
                DATE_ID,
                CREATE_PROV_ID,
                CREATE_AREA_ID,
                IS_CONTACT_SUCCESS,
                SEND_DATE,
                CONTACT_DATE,
                SUBSTRB(STRATEGY_ID,1,REGEXP_INSTR(STRATEGY_ID,'-')-1) STRATEGY_ID, --策略ID截取"-"之前部分获取父策略ID
                ROW_NUMBER() OVER(PARTITION BY STRATEGY_ID,CHANNEL_CODE,DEVICE_NUMBER ORDER BY SEND_DATE DESC) RN --根据策略ID,触点编码,用户号码分组打标
            FROM ITSY_DWA.DWA_V_D_CUS_QK_TOUCH_USER_INFO_L7  --触达类拉链表T-7
            WHERE DATE_ID = '20220222'
                AND CREATE_PROV_ID = '010'
                AND SOURCE_TYPE != 'SMS'
        ) T0 
        WHERE RN = '1'
    )
 
    GROUP BY 
        DATE_ID, --日账期
        PROV_ID, --策略归属省分
        AREA_ID , --策略归属地市
        STRATEGY_ID  --策略ID
)
 
GROUP BY 
    DATE_ID,
    PROV_ID,
    AREA_ID,
    STRATEGY_ID
    
    
UNION ALL 
 
--非短信部分省份汇总
 
SELECT
    DATE_ID,
    PROV_ID,
    '-1',
    STRATEGY_ID,
    SUM(REACH_FAIL_USER), --触达失败用户
    SUM(REACH_SUCCESS_USER), --触达成功用户
    SUM(UNKNOWN_IS_REACH_USER), --未知是否触达用户触点闭环部分
    SUM(REACH_FAIL_USER_LJ), --触达失败用户累计
    SUM(REACH_SUCCESS_USER_LJ), --触达成功用户累计
    SUM(UNKNOWN_IS_REACH_USER_LJ) --未知是否触达用户触点闭环部分累计
FROM (
 
 
    SELECT 
        DATE_ID, --日账期
        PROV_ID, --策略归属省分
        AREA_ID, --策略归属地市
        STRATEGY_ID, --策略ID
        SUM(CASE WHEN CONTACT_DATE = '20220222' AND IS_CONTACT_SUCCESS = '0' THEN 1 ELSE 0 END) REACH_FAIL_USER, --触达失败用户
        SUM(CASE WHEN CONTACT_DATE = '20220222' AND IS_CONTACT_SUCCESS = '1' THEN 1 ELSE 0 END) REACH_SUCCESS_USER, --触达成功用户
        SUM(CASE WHEN CONTACT_DATE = '20220222' AND IS_CONTACT_SUCCESS IS NULL THEN 1 ELSE 0 END) UNKNOWN_IS_REACH_USER, --未知是否触达用户触点闭环部分
        SUM(CASE WHEN SUBSTRING(CONTACT_DATE,1,6) = '202202' AND IS_CONTACT_SUCCESS = '0' THEN 1 ELSE 0 END) REACH_FAIL_USER_LJ, --触达失败用户累计    
        SUM(CASE WHEN SUBSTRING(CONTACT_DATE,1,6) = '202202' AND IS_CONTACT_SUCCESS = '1' THEN 1 ELSE 0 END) REACH_SUCCESS_USER_LJ, --触达成功用户累计
        SUM(CASE WHEN SUBSTRING(CONTACT_DATE,1,6) = '202202' AND IS_CONTACT_SUCCESS IS NULL THEN 1 ELSE 0 END) UNKNOWN_IS_REACH_USER_LJ --未知是否触达用户触点闭环部分累计
    FROM (
        SELECT 
            DATE_ID,
            CREATE_PROV_ID PROV_ID,
            CREATE_AREA_ID AREA_ID,
            IS_CONTACT_SUCCESS,
            SEND_DATE,
            CONTACT_DATE,
            STRATEGY_ID
        FROM 
        (
            SELECT 
                DATE_ID,
                CREATE_PROV_ID,
                CREATE_AREA_ID,
                IS_CONTACT_SUCCESS,
                SEND_DATE,
                CONTACT_DATE,
                SUBSTRB(STRATEGY_ID,1,REGEXP_INSTR(STRATEGY_ID,'-')-1) STRATEGY_ID, --策略ID截取"-"之前部分获取父策略ID
                ROW_NUMBER() OVER(PARTITION BY STRATEGY_ID,CHANNEL_CODE,DEVICE_NUMBER ORDER BY SEND_DATE DESC) RN --根据策略ID,触点编码,用户号码分组打标
            FROM ITSY_DWA.DWA_V_D_CUS_QK_TOUCH_USER_INFO_L7  --触达类拉链表T-7
            WHERE DATE_ID = '20220222'
                AND CREATE_PROV_ID = '010'
                AND SOURCE_TYPE != 'SMS'
        ) T0 
        WHERE RN = '1'
    )
 
    GROUP BY 
        DATE_ID, --日账期
        PROV_ID, --策略归属省分
        AREA_ID , --策略归属地市
        STRATEGY_ID  --策略ID
)
 
GROUP BY 
    DATE_ID,
    PROV_ID,
    STRATEGY_ID
 
  • ITSY_CUBE.DM_KPI_STRATEGY_CH
--INSERT INTO ITSY_CUBE.DM_KPI_STRATEGY_CH PARTITION ON (DATE_ID=''''''||V_DATE||'''''')

--地市、省份基础指标汇总
SELECT 
    DATE_ID, --日账期
    PROV_ID, --策略归属省分
    AREA_ID, --策略归属地市
    STRATEGY_ID, --策略ID
    SUM(REACH_FAIL_USER), --触点失败用户
    SUM(REACH_SUCCESS_USER), --触点成功用户
    SUM(UNKNOWN_IS_REACH_USER), --未知是否触达用户触点闭环部分
    SUM(REACH_FAIL_USER_LJ), --触点失败用户累计
    SUM(REACH_SUCCESS_USER_LJ), --触点成功用户累计
    SUM(UNKNOWN_IS_REACH_USER_LJ) --未知是否触达用户触点闭环部分累计
FROM ITSY_CUBE.DM_KPI_STRATEGY_CH_MID  --策略维度触电指标日更新中间表
WHERE DATE_ID = '''||V_DATE||'''
GROUP BY 
    DATE_ID, --日账期
    PROV_ID, --策略归属省分
    AREA_ID, --策略归属地市
    STRATEGY_ID --策略ID

--全国汇总

UNION ALL 

SELECT 
    DATE_ID, --日账期
    ''111'', --策略归属省分
    ''-1'', --策略归属地市
    STRATEGY_ID, --策略ID
    SUM(REACH_FAIL_USER), --触点失败用户
    SUM(REACH_SUCCESS_USER), --触点成功用户
    SUM(UNKNOWN_IS_REACH_USER), --未知是否触达用户触点闭环部分
    SUM(REACH_FAIL_USER_LJ), --触点失败用户累计
    SUM(REACH_SUCCESS_USER_LJ), --触点成功用户累计
    SUM(UNKNOWN_IS_REACH_USER_LJ) --未知是否触达用户触点闭环部分累计
FROM ITSY_CUBE.DM_KPI_STRATEGY_CH_MID  --策略维度触电指标日更新中间表
WHERE DATE_ID = '''||V_DATE||'''
    AND AREA_ID != ''-1''
GROUP BY 
    DATE_ID, --日账期
    STRATEGY_ID --策略ID

存储过程

P_DM_KPI_STRATEGY_RPT_MID
P_DM_KPI_STRATEGY_RPT
P_DM_KPI_STRATEGY_CH_MID
P_DM_KPI_STRATEGY_CH
  • P_DM_KPI_STRATEGY_RPT_MID
CREATE OR REPLACE PROCEDURE P_DM_KPI_STRATEGY_RPT_MID(
    V_DATE IN VARCHAR ,
    V_PROV IN VARCHAR ,
    V_RETCODE OUT VARCHAR ,
    V_RETINFO OUT VARCHAR
)
    AS
    /*@
    ****************************************************************
    *名称 --%@NAME:                    策略维度日表中间表(基础值和省份汇总值)
    *功能描述 --%@COMMENT:             
    *执行周期 --%@PERIOD:              测试
    *参数 --%@PARAM:                   V_DATE  '20220301'
    *参数 --%@PARAM:V_RETCODE          过程运行结束成功与否标志
    *参数 --%@PARAM:V_RETINFO          过程运行结束成功与否描述
    *创建人 --%@CREATOR:             FX 
    *创建时间 --%@CREATED_TIME:     20220317
    *修改记录 --%@MODIFY:
    *来源表 --%@FROM:    ITSY_DWA.DWA_V_D_CUS_QK_USERS_INFO --全客用户信息宽表
    *目标表 --%@TO:       ITSY_CUBE.DM_KPI_STRATEGY_RPT_MID
    *修改记录 --%@MODIFY:           
    ******************************************************************
    @*/
    
    V_SQL       VARCHAR(20000);
    V_CNT       NUMBER;
    V_PART_NAME VARCHAR(100);
    V_MONTH        VARCHAR(6);
    V_DAY        VARCHAR(2);
   
BEGIN
    V_MONTH:=SUBSTR(V_DATE, 1, 6);
    V_DAY:=SUBSTR(V_DATE, 7, 2);
    V_PART_NAME:='PART_'||V_DATE||'_'||V_PROV;
    V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_STRATEGY_RPT_MID'' AND PART_NAME='''||V_PART_NAME||'''';
    EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    
    IF V_CNT =0 THEN 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_STRATEGY_RPT_MID ADD PARTITION '||V_PART_NAME||'(DATE_ID='''||V_DATE||''',PROV_ID='''||V_PROV||''')';
    ELSE 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_STRATEGY_RPT_MID TRUNCATE PARTITION '||V_PART_NAME;
    END IF;
    
    V_SQL := 'INSERT INTO ITSY_CUBE.DM_KPI_STRATEGY_RPT_MID PARTITION ON (DATE_ID='''||V_DATE||''',PROV_ID='''||V_PROV||''')
SELECT 
    MONTH_ID, --账期
    CREATE_PROV_ID, --策略创建省份
    CREATE_AREA_ID, --策略创建地市
    STRATEGY_ID, --策略ID
    SUM(MARKET_DELINEATE_USER),--营销圈定用户
    SUM(FILTER_USER),--过滤用户
    SUM(FILTER_LATER_USER),--过滤后用户
    SUM(UNKNOWN_IS_REACH_USER),--未知是否触达用户触点未闭环部分
    SUM(UNKNOWN_IS_REACH_USER_B),--未知是否触达用户触点闭环部分
    SUM(REACH_CB_NOT_ORDER_USER),--触达成功cB未订购用户
    SUM(REACH_CB_ORDER_USER),--触达成功cB订购用户
    SUM(UNKNOWN_CB_ORDER_USER),--未知是否触达cB订购用户
    SUM(REACH_CB_ORDER_SUCCESS_USER),--触达成功cB订购成功用户
    SUM(REACH_CB_ORDER_FAIL_USER),--触达成功cB订购失败用户
    SUM(REACH_CB_ORDER_IN_USER),--触达成功cB订购中用户
    SUM(UNKNOWN_CB_ORDER_SUCCESS_USER),--未知是否触达cB订购成功用户
    SUM(UNKNOWN_CB_ORDER_FAIL_USER),--未知是否触达cB订购失败用户
    SUM(UNKNOWN_CB_ORDER_IN_USER),--未知是否触达cB订购中用户
    SUM(MARKET_DELINEATE_USER_LJ),--营销圈定用户月累计
    SUM(FILTER_USER_LJ),--过滤用户月累计
    SUM(FILTER_LATER_USER_LJ),--过滤后用户月累计
    SUM(UNKNOWN_IS_REACH_USER_LJ),--未知是否触达用户触点未闭环部分月累计
    SUM(UNKNOWN_IS_REACH_USER_B_LJ),--未知是否触达用户触点闭环部分月累计
    SUM(REACH_CB_NOT_ORDER_USER_LJ),--触达成功cB未订购用户月累计
    SUM(REACH_CB_ORDER_USER_LJ),--触达成功cB订购用户月累计
    SUM(UNKNOWN_CB_ORDER_USER_LJ),--未知是否触达cB订购用户月累计
    SUM(REACH_CB_ORDER_SUCCESS_USER_LJ),--触达成功cB订购成功用户月累计
    SUM(REACH_CB_ORDER_FAIL_USER_LJ),--触达成功cB订购失败用户月累计
    SUM(REACH_CB_ORDER_IN_USER_LJ),--触达成功cB订购中用户月累计
    SUM(UNKNOWN_CB_ORDER_SUCCESS_USER_LJ),--未知是否触达cB订购成功用户月累计
    SUM(UNKNOWN_CB_ORDER_FAIL_USER_LJ),--未知是否触达cB订购失败用户月累计
    SUM(UNKNOWN_CB_ORDER_IN_USER_LJ)--未知是否触达cB订购中用户月累计
FROM (
    SELECT 
        MONTH_ID, --账期
        CREATE_PROV_ID, --策略创建省份
        CREATE_AREA_ID, --策略创建地市
        PARENT_STRATEGY_ID STRATEGY_ID, --策略ID
        COUNT(DISTINCT(CASE WHEN END_DATE >= '''||V_DATE||''' 
            THEN USER_ID END)) MARKET_DELINEATE_USER, --营销圈定用户
        COUNT(DISTINCT(CASE WHEN END_DATE >= '''||V_DATE||''' AND LOOP_RESULT IN (''0'',''1'') AND PUSH_CHANNEL_RESULT = ''0'' 
            THEN USER_ID END)) FILTER_USER, --过滤用户
        COUNT(DISTINCT(CASE WHEN END_DATE >= '''||V_DATE||''' AND (LOOP_RESULT IN (''0'',''1'') AND PUSH_CHANNEL_RESULT = ''0'') OR LOOP_RESULT IN (''2'',''3'') 
            THEN USER_ID END)) FILTER_LATER_USER, --过滤后用户
        COUNT(DISTINCT(CASE WHEN INPUT_DATE = '''||V_DATE||''' AND LOOP_RESULT IN (''2'',''3'') 
            THEN USER_ID END)) UNKNOWN_IS_REACH_USER, --未知是否触达用户触点未闭环部分    
        COUNT(DISTINCT(CASE WHEN CONTACT_TIME = '''||V_DATE||''' AND LOOP_RESULT IN (''2'',''3'') 
            THEN USER_ID END)) UNKNOWN_IS_REACH_USER_B, --未知是否触达用户触点闭环部分(当前的不确定性指标)
        COUNT(DISTINCT(CASE WHEN ACCEPT_DATE = '''||V_DATE||''' AND LOOP_RESULT IN (''0'',''1'') AND PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT = ''1'' AND SUBSCRIBE_STATE IS NULL 
            THEN USER_ID END)) REACH_CB_NOT_ORDER_USER, --触达成功CB未订购用户
        COUNT(DISTINCT(CASE WHEN ACCEPT_DATE = '''||V_DATE||''' AND  LOOP_RESULT IN (''0'',''1'') AND PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT = ''1'' AND SUBSCRIBE_STATE IS NOT NULL 
            THEN USER_ID END)) REACH_CB_ORDER_USER, --触达成功CB订购用户
        COUNT(DISTINCT(CASE WHEN ACCEPT_DATE = '''||V_DATE||''' AND ((LOOP_RESULT IN (''0'',''1'') AND PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT IS NULL) OR LOOP_RESULT IN (''2'',''3'')) AND SUBSCRIBE_STATE IS NOT NULL 
            THEN USER_ID END)) UNKNOWN_CB_ORDER_USER, --未知是否触达CB订购用户
        COUNT(DISTINCT(CASE WHEN ACCEPT_DATE = '''||V_DATE||''' AND LOOP_RESULT IN (''0'',''1'') AND PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT = ''1'' AND SUBSCRIBE_STATE = ''9'' 
            THEN USER_ID END)) REACH_CB_ORDER_SUCCESS_USER, --触达成功CB订购成功用户
        COUNT(DISTINCT(CASE WHEN ACCEPT_DATE = '''||V_DATE||''' AND LOOP_RESULT IN (''0'',''1'') AND PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT = ''1'' AND SUBSCRIBE_STATE = ''3'' 
            THEN USER_ID END)) REACH_CB_ORDER_FAIL_USER, --触达成功CB订购失败用户
        COUNT(DISTINCT(CASE WHEN ACCEPT_DATE = '''||V_DATE||''' AND LOOP_RESULT IN (''0'',''1'') AND PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT = ''1'' AND SUBSCRIBE_STATE IN (''6'',''5'',''2'',''1'',''B'',''0'') 
            THEN USER_ID END)) REACH_CB_ORDER_IN_USER, --触达成功CB订购中用户
        COUNT(DISTINCT(CASE WHEN ACCEPT_DATE = '''||V_DATE||''' AND ((LOOP_RESULT IN (''0'',''1'') AND PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT IS NULL) OR LOOP_RESULT IN (''2'',''3'')) AND SUBSCRIBE_STATE = ''9'' 
            THEN USER_ID END)) UNKNOWN_CB_ORDER_SUCCESS_USER, --未知是否触达CB订购成功用户
        COUNT(DISTINCT(CASE WHEN ACCEPT_DATE = '''||V_DATE||''' AND ((LOOP_RESULT IN (''0'',''1'') AND PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT IS NULL) OR LOOP_RESULT IN (''2'',''3'')) AND SUBSCRIBE_STATE = ''3'' 
            THEN USER_ID END)) UNKNOWN_CB_ORDER_FAIL_USER, --未知是否触达CB订购失败用户
        COUNT(DISTINCT(CASE WHEN ACCEPT_DATE = '''||V_DATE||''' AND ((LOOP_RESULT IN (''0'',''1'') AND PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT IS NULL) OR LOOP_RESULT IN (''2'',''3'')) AND SUBSCRIBE_STATE IN (''6'',''5'',''2'',''1'',''B'',''0'') 
            THEN USER_ID END)) UNKNOWN_CB_ORDER_IN_USER, --未知是否触达CB订购中用户
        COUNT(DISTINCT(USER_ID))MARKET_DELINEATE_USER_LJ,--营销圈定用户月累计
        COUNT(DISTINCT (CASE WHEN LOOP_RESULT IN (''0'',''1'') AND PUSH_CHANNEL_RESULT = ''0''
            THEN USER_ID END))FILTER_USER_LJ,--过滤用户月累计
        COUNT(DISTINCT (CASE WHEN (LOOP_RESULT IN (''0'',''1'') AND PUSH_CHANNEL_RESULT = ''0'') OR LOOP_RESULT IN (''2'',''3'')
            THEN USER_ID END))FILTER_LATER_USER_LJ,--过滤后用户月累计
        COUNT(DISTINCT (CASE WHEN LOOP_RESULT IN (''2'',''3'') AND SUBSTRING(INPUT_DATE, 1, 6) = '''||V_DATE||'''
            THEN USER_ID END))UNKNOWN_IS_REACH_USER_LJ,--未知是否触达用户触点未闭环部分月累计
        COUNT(DISTINCT (CASE WHEN LOOP_RESULT IN (''0'',''1'') AND PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT  IS NULL  AND SUBSTRING(contact_time, 1, 6) = '''||V_DATE||'''
            THEN USER_ID END))UNKNOWN_IS_REACH_USER_B_LJ,--未知是否触达用户触点闭环部分月累计
        COUNT(DISTINCT (CASE WHEN LOOP_RESULT IN (''1'',''2'') AND PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT = ''1'' AND SUBSCRIBE_STATE IS NULL AND SUBSTRING(ACCEPT_DATE, 1, 6) = '''||V_DATE||'''
            THEN USER_ID END))REACH_CB_NOT_ORDER_USER_LJ,--触达成功CB未订购用户月累计
        COUNT(DISTINCT (CASE WHEN LOOP_RESULT IN (''1'',''2'') AND PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT = ''1'' AND SUBSCRIBE_STATE IS NOT NULL AND SUBSTRING(ACCEPT_DATE, 1, 6) = '''||V_DATE||'''
            THEN USER_ID END))REACH_CB_ORDER_USER_LJ,--触达成功CB订购用户月累计
        COUNT(DISTINCT (CASE WHEN ((LOOP_RESULT IN (''1'',''0'') AND PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT IS NULL) OR LOOP_RESULT IN (''2'',''3'')) AND SUBSCRIBE_STATE IS NOT NULL AND SUBSTRING(ACCEPT_DATE, 1, 6) = '''||V_DATE||'''
            THEN USER_ID END))UNKNOWN_CB_ORDER_USER_LJ,--未知是否触达CB订购用户月累计
        COUNT(DISTINCT (CASE WHEN LOOP_RESULT IN (''0'',''1'') AND PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT = ''1'' AND SUBSCRIBE_STATE IN (''9'') AND   SUBSTRING(ACCEPT_DATE, 1, 6) = '''||V_DATE||'''
            THEN USER_ID END ))REACH_CB_ORDER_SUCCESS_USER_LJ,--触达成功CB订购成功用户月累计
        COUNT(DISTINCT (CASE WHEN LOOP_RESULT IN (''0'',''1'') AND PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT = ''1'' AND SUBSCRIBE_STATE IN (''3'') AND SUBSTRING(ACCEPT_DATE, 1, 6) = '''||V_DATE||'''
            THEN USER_ID END ))REACH_CB_ORDER_FAIL_USER_LJ,--触达成功CB订购失败用户月累计
        COUNT(DISTINCT (CASE WHEN LOOP_RESULT IN (''0'',''1'') AND PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT = ''1'' AND SUBSCRIBE_STATE IN (''6'',''5'',''2'',''1'',''B'',''0'') AND SUBSTRING(ACCEPT_DATE, 1, 6) = '''||V_DATE||'''
            THEN USER_ID END ))REACH_CB_ORDER_IN_USER_LJ,--触达成功CB订购中用户月累计
        COUNT(DISTINCT(CASE WHEN ((LOOP_RESULT IN (''0'',''1'')AND PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT = ''1'')OR LOOP_RESULT IN (''2'',''3''))AND SUBSCRIBE_STATE IN (''9'') AND SUBSTRING(ACCEPT_DATE, 1, 6) = '''||V_DATE||'''
            THEN USER_ID END ))UNKNOWN_CB_ORDER_SUCCESS_USER_LJ,--未知是否触达CB订购成功用户月累计
        COUNT(DISTINCT(CASE WHEN ((LOOP_RESULT IN (''0'',''1'')AND PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT = ''1'')OR LOOP_RESULT IN (''2'',''3''))AND SUBSCRIBE_STATE IN (''3'') AND SUBSTRING(ACCEPT_DATE, 1, 6) = '''||V_DATE||'''
            THEN USER_ID END))UNKNOWN_CB_ORDER_FAIL_USER_LJ,--未知是否触达CB订购失败用户月累计
        COUNT(DISTINCT(CASE WHEN ((LOOP_RESULT IN (''0'',''1'')AND PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT = ''1'')OR LOOP_RESULT IN (''2'',''3''))AND SUBSCRIBE_STATE IN (''6'',''5'',''2'',''1'',''B'',''0'') AND SUBSTRING(ACCEPT_DATE, 1, 6) = '''||V_DATE||'''
        THEN USER_ID END))UNKNOWN_CB_ORDER_IN_USER_LJ--未知是否触达CB订购中用户月累计
    FROM ITSY_DWA.DWA_V_D_CUS_QK_USERS_INFO --全客用户信息宽表
    WHERE MONTH_ID = '''||V_DATE||''' 
        AND CREATE_PROV_ID = '''||V_PROV||'''
    GROUP BY 
        MONTH_ID, --账期
        CREATE_PROV_ID , --省份
        CREATE_AREA_ID , --地市
        PARENT_STRATEGY_ID --策略ID
)

GROUP BY 
    MONTH_ID, --账期
    CREATE_PROV_ID, --策略创建省份
    CREATE_AREA_ID, --策略创建地市
    STRATEGY_ID  --策略ID
    
    
UNION ALL 

--省份汇总


SELECT 
    MONTH_ID, --账期
    CREATE_PROV_ID, --策略创建省份
    ''-1'', --策略创建地市
    STRATEGY_ID, --策略ID
    SUM(MARKET_DELINEATE_USER),--营销圈定用户
    SUM(FILTER_USER),--过滤用户
    SUM(FILTER_LATER_USER),--过滤后用户
    SUM(UNKNOWN_IS_REACH_USER),--未知是否触达用户触点未闭环部分
    SUM(UNKNOWN_IS_REACH_USER_B),--未知是否触达用户触点闭环部分
    SUM(REACH_CB_NOT_ORDER_USER),--触达成功cB未订购用户
    SUM(REACH_CB_ORDER_USER),--触达成功cB订购用户
    SUM(UNKNOWN_CB_ORDER_USER),--未知是否触达cB订购用户
    SUM(REACH_CB_ORDER_SUCCESS_USER),--触达成功cB订购成功用户
    SUM(REACH_CB_ORDER_FAIL_USER),--触达成功cB订购失败用户
    SUM(REACH_CB_ORDER_IN_USER),--触达成功cB订购中用户
    SUM(UNKNOWN_CB_ORDER_SUCCESS_USER),--未知是否触达cB订购成功用户
    SUM(UNKNOWN_CB_ORDER_FAIL_USER),--未知是否触达cB订购失败用户
    SUM(UNKNOWN_CB_ORDER_IN_USER),--未知是否触达cB订购中用户
    SUM(MARKET_DELINEATE_USER_LJ),--营销圈定用户月累计
    SUM(FILTER_USER_LJ),--过滤用户月累计
    SUM(FILTER_LATER_USER_LJ),--过滤后用户月累计
    SUM(UNKNOWN_IS_REACH_USER_LJ),--未知是否触达用户触点未闭环部分月累计
    SUM(UNKNOWN_IS_REACH_USER_B_LJ),--未知是否触达用户触点闭环部分月累计
    SUM(REACH_CB_NOT_ORDER_USER_LJ),--触达成功cB未订购用户月累计
    SUM(REACH_CB_ORDER_USER_LJ),--触达成功cB订购用户月累计
    SUM(UNKNOWN_CB_ORDER_USER_LJ),--未知是否触达cB订购用户月累计
    SUM(REACH_CB_ORDER_SUCCESS_USER_LJ),--触达成功cB订购成功用户月累计
    SUM(REACH_CB_ORDER_FAIL_USER_LJ),--触达成功cB订购失败用户月累计
    SUM(REACH_CB_ORDER_IN_USER_LJ),--触达成功cB订购中用户月累计
    SUM(UNKNOWN_CB_ORDER_SUCCESS_USER_LJ),--未知是否触达cB订购成功用户月累计
    SUM(UNKNOWN_CB_ORDER_FAIL_USER_LJ),--未知是否触达cB订购失败用户月累计
    SUM(UNKNOWN_CB_ORDER_IN_USER_LJ)--未知是否触达cB订购中用户月累计
FROM (
    SELECT 
        MONTH_ID, --账期
        CREATE_PROV_ID, --策略创建省份
        ''-1'', --策略创建地市
        PARENT_STRATEGY_ID STRATEGY_ID, --策略ID
        COUNT(DISTINCT(CASE WHEN END_DATE >= '''||V_DATE||''' 
            THEN USER_ID END)) MARKET_DELINEATE_USER, --营销圈定用户
        COUNT(DISTINCT(CASE WHEN END_DATE >= '''||V_DATE||''' AND LOOP_RESULT IN (''0'',''1'') AND PUSH_CHANNEL_RESULT = ''0'' 
            THEN USER_ID END)) FILTER_USER, --过滤用户
        COUNT(DISTINCT(CASE WHEN END_DATE >= '''||V_DATE||''' AND (LOOP_RESULT IN (''0'',''1'') AND PUSH_CHANNEL_RESULT = ''0'') OR LOOP_RESULT IN (''2'',''3'') 
            THEN USER_ID END)) FILTER_LATER_USER, --过滤后用户
        COUNT(DISTINCT(CASE WHEN INPUT_DATE = '''||V_DATE||''' AND LOOP_RESULT IN (''2'',''3'') 
            THEN USER_ID END)) UNKNOWN_IS_REACH_USER, --未知是否触达用户触点未闭环部分    
        COUNT(DISTINCT(CASE WHEN CONTACT_TIME = '''||V_DATE||''' AND LOOP_RESULT IN (''2'',''3'') 
            THEN USER_ID END)) UNKNOWN_IS_REACH_USER_B, --未知是否触达用户触点闭环部分(当前的不确定性指标)
        COUNT(DISTINCT(CASE WHEN ACCEPT_DATE = '''||V_DATE||''' AND LOOP_RESULT IN (''0'',''1'') AND PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT = ''1'' AND SUBSCRIBE_STATE IS NULL 
            THEN USER_ID END)) REACH_CB_NOT_ORDER_USER, --触达成功CB未订购用户
        COUNT(DISTINCT(CASE WHEN ACCEPT_DATE = '''||V_DATE||''' AND  LOOP_RESULT IN (''0'',''1'') AND PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT = ''1'' AND SUBSCRIBE_STATE IS NOT NULL 
            THEN USER_ID END)) REACH_CB_ORDER_USER, --触达成功CB订购用户
        COUNT(DISTINCT(CASE WHEN ACCEPT_DATE = '''||V_DATE||''' AND ((LOOP_RESULT IN (''0'',''1'') AND PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT IS NULL) OR LOOP_RESULT IN (''2'',''3'')) AND SUBSCRIBE_STATE IS NOT NULL 
            THEN USER_ID END)) UNKNOWN_CB_ORDER_USER, --未知是否触达CB订购用户
        COUNT(DISTINCT(CASE WHEN ACCEPT_DATE = '''||V_DATE||''' AND LOOP_RESULT IN (''0'',''1'') AND PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT = ''1'' AND SUBSCRIBE_STATE = ''9'' 
            THEN USER_ID END)) REACH_CB_ORDER_SUCCESS_USER, --触达成功CB订购成功用户
        COUNT(DISTINCT(CASE WHEN ACCEPT_DATE = '''||V_DATE||''' AND LOOP_RESULT IN (''0'',''1'') AND PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT = ''1'' AND SUBSCRIBE_STATE = ''3'' 
            THEN USER_ID END)) REACH_CB_ORDER_FAIL_USER, --触达成功CB订购失败用户
        COUNT(DISTINCT(CASE WHEN ACCEPT_DATE = '''||V_DATE||''' AND LOOP_RESULT IN (''0'',''1'') AND PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT = ''1'' AND SUBSCRIBE_STATE IN (''6'',''5'',''2'',''1'',''B'',''0'') 
            THEN USER_ID END)) REACH_CB_ORDER_IN_USER, --触达成功CB订购中用户
        COUNT(DISTINCT(CASE WHEN ACCEPT_DATE = '''||V_DATE||''' AND ((LOOP_RESULT IN (''0'',''1'') AND PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT IS NULL) OR LOOP_RESULT IN (''2'',''3'')) AND SUBSCRIBE_STATE = ''9'' 
            THEN USER_ID END)) UNKNOWN_CB_ORDER_SUCCESS_USER, --未知是否触达CB订购成功用户
        COUNT(DISTINCT(CASE WHEN ACCEPT_DATE = '''||V_DATE||''' AND ((LOOP_RESULT IN (''0'',''1'') AND PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT IS NULL) OR LOOP_RESULT IN (''2'',''3'')) AND SUBSCRIBE_STATE = ''3'' 
            THEN USER_ID END)) UNKNOWN_CB_ORDER_FAIL_USER, --未知是否触达CB订购失败用户
        COUNT(DISTINCT(CASE WHEN ACCEPT_DATE = '''||V_DATE||''' AND ((LOOP_RESULT IN (''0'',''1'') AND PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT IS NULL) OR LOOP_RESULT IN (''2'',''3'')) AND SUBSCRIBE_STATE IN (''6'',''5'',''2'',''1'',''B'',''0'') 
            THEN USER_ID END)) UNKNOWN_CB_ORDER_IN_USER, --未知是否触达CB订购中用户
        COUNT(DISTINCT(USER_ID))MARKET_DELINEATE_USER_LJ,--营销圈定用户月累计
        COUNT(DISTINCT (CASE WHEN LOOP_RESULT IN (''0'',''1'') AND PUSH_CHANNEL_RESULT = ''0''
            THEN USER_ID END))FILTER_USER_LJ,--过滤用户月累计
        COUNT(DISTINCT (CASE WHEN (LOOP_RESULT IN (''0'',''1'') AND PUSH_CHANNEL_RESULT = ''0'') OR LOOP_RESULT IN (''2'',''3'')
            THEN USER_ID END))FILTER_LATER_USER_LJ,--过滤后用户月累计
        COUNT(DISTINCT (CASE WHEN LOOP_RESULT IN (''2'',''3'') AND SUBSTRING(INPUT_DATE, 1, 6) = '''||V_DATE||'''
            THEN USER_ID END))UNKNOWN_IS_REACH_USER_LJ,--未知是否触达用户触点未闭环部分月累计
        COUNT(DISTINCT (CASE WHEN LOOP_RESULT IN (''0'',''1'') AND PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT  IS NULL  AND SUBSTRING(contact_time, 1, 6) = '''||V_DATE||'''
            THEN USER_ID END))UNKNOWN_IS_REACH_USER_B_LJ,--未知是否触达用户触点闭环部分月累计
        COUNT(DISTINCT (CASE WHEN LOOP_RESULT IN (''1'',''2'') AND PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT = ''1'' AND SUBSCRIBE_STATE IS NULL AND SUBSTRING(ACCEPT_DATE, 1, 6) = '''||V_DATE||'''
            THEN USER_ID END))REACH_CB_NOT_ORDER_USER_LJ,--触达成功CB未订购用户月累计
        COUNT(DISTINCT (CASE WHEN LOOP_RESULT IN (''1'',''2'') AND PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT = ''1'' AND SUBSCRIBE_STATE IS NOT NULL AND SUBSTRING(ACCEPT_DATE, 1, 6) = '''||V_DATE||'''
            THEN USER_ID END))REACH_CB_ORDER_USER_LJ,--触达成功CB订购用户月累计
        COUNT(DISTINCT (CASE WHEN ((LOOP_RESULT IN (''1'',''0'') AND PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT IS NULL) OR LOOP_RESULT IN (''2'',''3'')) AND SUBSCRIBE_STATE IS NOT NULL AND SUBSTRING(ACCEPT_DATE, 1, 6) = '''||V_DATE||'''
            THEN USER_ID END))UNKNOWN_CB_ORDER_USER_LJ,--未知是否触达CB订购用户月累计
        COUNT(DISTINCT (CASE WHEN LOOP_RESULT IN (''0'',''1'') AND PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT = ''1'' AND SUBSCRIBE_STATE IN (''9'') AND   SUBSTRING(ACCEPT_DATE, 1, 6) = '''||V_DATE||'''
            THEN USER_ID END ))REACH_CB_ORDER_SUCCESS_USER_LJ,--触达成功CB订购成功用户月累计
        COUNT(DISTINCT (CASE WHEN LOOP_RESULT IN (''0'',''1'') AND PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT = ''1'' AND SUBSCRIBE_STATE IN (''3'') AND SUBSTRING(ACCEPT_DATE, 1, 6) = '''||V_DATE||'''
            THEN USER_ID END ))REACH_CB_ORDER_FAIL_USER_LJ,--触达成功CB订购失败用户月累计
        COUNT(DISTINCT (CASE WHEN LOOP_RESULT IN (''0'',''1'') AND PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT = ''1'' AND SUBSCRIBE_STATE IN (''6'',''5'',''2'',''1'',''B'',''0'') AND SUBSTRING(ACCEPT_DATE, 1, 6) = '''||V_DATE||'''
            THEN USER_ID END ))REACH_CB_ORDER_IN_USER_LJ,--触达成功CB订购中用户月累计
        COUNT(DISTINCT(CASE WHEN ((LOOP_RESULT IN (''0'',''1'')AND PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT = ''1'')OR LOOP_RESULT IN (''2'',''3''))AND SUBSCRIBE_STATE IN (''9'') AND SUBSTRING(ACCEPT_DATE, 1, 6) = '''||V_DATE||'''
            THEN USER_ID END ))UNKNOWN_CB_ORDER_SUCCESS_USER_LJ,--未知是否触达CB订购成功用户月累计
        COUNT(DISTINCT(CASE WHEN ((LOOP_RESULT IN (''0'',''1'')AND PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT = ''1'')OR LOOP_RESULT IN (''2'',''3''))AND SUBSCRIBE_STATE IN (''3'') AND SUBSTRING(ACCEPT_DATE, 1, 6) = '''||V_DATE||'''
            THEN USER_ID END))UNKNOWN_CB_ORDER_FAIL_USER_LJ,--未知是否触达CB订购失败用户月累计
        COUNT(DISTINCT(CASE WHEN ((LOOP_RESULT IN (''0'',''1'')AND PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT = ''1'')OR LOOP_RESULT IN (''2'',''3''))AND SUBSCRIBE_STATE IN (''6'',''5'',''2'',''1'',''B'',''0'') AND SUBSTRING(ACCEPT_DATE, 1, 6) = '''||V_DATE||'''
        THEN USER_ID END))UNKNOWN_CB_ORDER_IN_USER_LJ--未知是否触达CB订购中用户月累计
    FROM ITSY_DWA.DWA_V_D_CUS_QK_USERS_INFO --全客用户信息宽表
    WHERE MONTH_ID = '''||V_DATE||''' 
        AND CREATE_PROV_ID = '''||V_PROV||'''
    GROUP BY 
        MONTH_ID, --账期
        CREATE_PROV_ID , --省份
        PARENT_STRATEGY_ID --策略ID
)

GROUP BY 
    MONTH_ID, --账期
    CREATE_PROV_ID, --策略创建省份
    STRATEGY_ID  --策略ID
 ';
    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;
    
    V_RETCODE := 'SUCCESS';
    V_RETINFO := '结束';
    
EXCEPTION 
    WHEN OTHERS THEN
        V_RETCODE := 'FAIL';
        V_RETINFO := SQLERRM;
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;


  • P_DM_KPI_STRATEGY_RPT
CREATE OR REPLACE PROCEDURE P_DM_KPI_STRATEGY_RPT(
    V_DATE IN VARCHAR ,
    V_PROV IN VARCHAR ,
    V_RETCODE OUT VARCHAR ,
    V_RETINFO OUT VARCHAR
)
    AS
    /*@
    ****************************************************************
    *名称 --%@NAME:                    策略维度日表
    *功能描述 --%@COMMENT:             
    *执行周期 --%@PERIOD:              测试
    *参数 --%@PARAM:                   V_DATE  '20220301' 
    *参数 --%@PARAM:V_RETCODE          过程运行结束成功与否标志
    *参数 --%@PARAM:V_RETINFO          过程运行结束成功与否描述
    *创建人 --%@CREATOR:             FX 
    *创建时间 --%@CREATED_TIME:     20220322
    *修改记录 --%@MODIFY:
    *来源表 --%@FROM:    ITSY_CUBE.DM_KPI_STRATEGY_RPT_MID --中间表
    *来源表 --%@FROM:    ITSY_CUBE.DM_C_D_CUS_MB_BASE_REPORT --经分表
    *目标表 --%@TO:       ITSY_CUBE.DM_KPI_STRATEGY_RPT
    *修改记录 --%@MODIFY:           
    ******************************************************************
    @*/
    
    V_SQL       VARCHAR(10000);
    V_CNT       NUMBER;
    V_PART_NAME VARCHAR(100);
   
BEGIN
    V_PART_NAME:='PART_'||V_DATE||'_'||V_PROV;
    V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_STRATEGY_RPT'' AND PART_NAME='''||V_PART_NAME||'''';
    EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    
    IF V_CNT =0 THEN 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_STRATEGY_RPT ADD PARTITION '||V_PART_NAME||'(DATE_ID='''||V_DATE||''',PROV_ID='''||V_PROV||''')';
    ELSE 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_STRATEGY_RPT TRUNCATE PARTITION '||V_PART_NAME;
    END IF;
    
    V_SQL := 'INSERT INTO ITSY_CUBE.DM_KPI_STRATEGY_RPT PARTITION ON (DATE_ID='''||V_DATE||''',PROV_ID='''||V_PROV||''')
--地市指标及省份汇总指标

SELECT 
    T0.DATE_ID, --日账期
    T0.PROV_ID, --省份
    T0.AREA_ID, --地市
    T0.STRATEGY_ID, --策略ID
    --日表部分指标
    SUM(T1.USER_NUM), --全网用户
    SUM(T1.USER_NUM) - SUM(T0.MARKET_DELINEATE_USER), --非圈定用户
    SUM(T0.MARKET_DELINEATE_USER), --营销圈定用户
    SUM(T0.FILTER_USER), --过滤用户
    SUM(T0.FILTER_LATER_USER), --过滤后用户
    SUM(T0.UNKNOWN_IS_REACH_USER), --未知是否触达用户触点未闭环部分
    SUM(T0.REACH_CB_NOT_ORDER_USER), --触达成功CB未订购用户
    SUM(T0.REACH_CB_ORDER_USER), --触达成功CB订购用户
    SUM(T0.UNKNOWN_IS_REACH_USER) + SUM(T0.UNKNOWN_IS_REACH_USER_B) - SUM(T0.UNKNOWN_CB_ORDER_USER), --未知是否触达CB未订购用户
    SUM(T0.UNKNOWN_CB_ORDER_USER), --未知是否触达CB订购用户
    SUM(T0.REACH_CB_ORDER_SUCCESS_USER), --触达成功CB订购成功用户
    SUM(T0.REACH_CB_ORDER_FAIL_USER), --触达成功CB订购失败用户
    SUM(T0.REACH_CB_ORDER_IN_USER), --触达成功CB订购中用户
    SUM(T0.UNKNOWN_CB_ORDER_SUCCESS_USER), --未知是否触达CB订购成功用户
    SUM(T0.UNKNOWN_CB_ORDER_FAIL_USER), --未知是否触达CB订购失败用户
    SUM(T0.UNKNOWN_CB_ORDER_IN_USER), --未知是否触达CB订购中用户
    SUM(T0.UNKNOWN_CB_ORDER_USER) + SUM(T0.REACH_CB_ORDER_USER), --CB订购用户
    SUM(T0.REACH_CB_ORDER_SUCCESS_USER) + SUM(T0.UNKNOWN_CB_ORDER_SUCCESS_USER), --CB订购成功用户
    --月表部分指标
    SUM(T1.USER_NUM_LJ), --全网用户累计
    SUM(T1.USER_NUM_LJ) - SUM(T0.MARKET_DELINEATE_USER_LJ), --非圈定用户累计
    SUM(T0.MARKET_DELINEATE_USER_LJ), --营销圈定用户累计
    SUM(T0.FILTER_USER_LJ), --过滤用户累计
    SUM(T0.FILTER_LATER_USER_LJ), --过滤后用户累计
    SUM(T0.UNKNOWN_IS_REACH_USER_LJ), --未知是否触达用户触点未闭环部分累计
    SUM(T0.REACH_CB_NOT_ORDER_USER_LJ), --触达成功CB未订购用户累计
    SUM(T0.REACH_CB_ORDER_USER_LJ), --触达成功CB订购用户累计
    SUM(T0.UNKNOWN_IS_REACH_USER_LJ) + SUM(T0.UNKNOWN_IS_REACH_USER_B_LJ) - SUM(T0.UNKNOWN_CB_ORDER_USER_LJ), --未知是否触达CB未订购用户累计
    SUM(T0.UNKNOWN_CB_ORDER_USER_LJ), --未知是否触达CB订购用户累计
    SUM(T0.REACH_CB_ORDER_SUCCESS_USER_LJ), --触达成功CB订购成功用户累计
    SUM(T0.REACH_CB_ORDER_FAIL_USER_LJ), --触达成功CB订购失败用户累计
    SUM(T0.REACH_CB_ORDER_IN_USER_LJ), --触达成功CB订购中用户累计
    SUM(T0.UNKNOWN_CB_ORDER_SUCCESS_USER_LJ), --未知是否触达CB订购成功用户累计
    SUM(T0.UNKNOWN_CB_ORDER_FAIL_USER_LJ), --未知是否触达CB订购失败用户累计
    SUM(T0.UNKNOWN_CB_ORDER_IN_USER_LJ), --未知是否触达CB订购中用户累计
    SUM(T0.UNKNOWN_CB_ORDER_USER_LJ) + SUM(T0.REACH_CB_ORDER_USER_LJ), --CB订购用户累计
    SUM(T0.REACH_CB_ORDER_SUCCESS_USER_LJ) + SUM(T0.UNKNOWN_CB_ORDER_SUCCESS_USER_LJ) --CB订购成功用户累计
    
FROM (
    SELECT *
    FROM ITSY_CUBE.DM_KPI_STRATEGY_RPT_MID --全客策略维度日表中间表
    WHERE DATE_ID = '''||V_DATE||'''
        AND PROV_ID = '''||V_PROV||'''
) T0 LEFT JOIN (
    SELECT 
        DATE_ID,
        PROV_ID,
        AREA_ID,
        USER_NUM, --全网用户数
        USER_NUM_LJ --全网用户数累计
    FROM ITSY_CUBE.DM_C_D_CUS_MB_BASE_REPORT  --经分表
    WHERE DATE_ID = '''||V_DATE||'''
        AND PROV_ID = '''||V_PROV||'''
) T1 ON T0.DATE_ID = T1.DATE_ID AND T0.PROV_ID = T1.PROV_ID AND T0.AREA_ID = T1.AREA_ID

GROUP BY 
    T0.DATE_ID, --日账期
    T0.PROV_ID, --省份
    T0.AREA_ID, --地市
    T0.STRATEGY_ID --策略ID
    
UNION ALL 

--全国汇总

SELECT 
    T0.DATE_ID, --日账期
    ''111'', --省份
    ''-1'', --地市
    T0.STRATEGY_ID, --策略ID
    --日表部分指标
    SUM(T1.USER_NUM), --全网用户
    SUM(T1.USER_NUM) - SUM(T0.MARKET_DELINEATE_USER), --非圈定用户
    SUM(T0.MARKET_DELINEATE_USER), --营销圈定用户
    SUM(T0.FILTER_USER), --过滤用户
    SUM(T0.FILTER_LATER_USER), --过滤后用户
    SUM(T0.UNKNOWN_IS_REACH_USER), --未知是否触达用户触点未闭环部分
    SUM(T0.REACH_CB_NOT_ORDER_USER), --触达成功CB未订购用户
    SUM(T0.REACH_CB_ORDER_USER), --触达成功CB订购用户
    SUM(T0.UNKNOWN_IS_REACH_USER) + SUM(T0.UNKNOWN_IS_REACH_USER_B) - SUM(T0.UNKNOWN_CB_ORDER_USER), --未知是否触达CB未订购用户
    SUM(T0.UNKNOWN_CB_ORDER_USER), --未知是否触达CB订购用户
    SUM(T0.REACH_CB_ORDER_SUCCESS_USER), --触达成功CB订购成功用户
    SUM(T0.REACH_CB_ORDER_FAIL_USER), --触达成功CB订购失败用户
    SUM(T0.REACH_CB_ORDER_IN_USER), --触达成功CB订购中用户
    SUM(T0.UNKNOWN_CB_ORDER_SUCCESS_USER), --未知是否触达CB订购成功用户
    SUM(T0.UNKNOWN_CB_ORDER_FAIL_USER), --未知是否触达CB订购失败用户
    SUM(T0.UNKNOWN_CB_ORDER_IN_USER), --未知是否触达CB订购中用户
    SUM(T0.UNKNOWN_CB_ORDER_USER) + SUM(T0.REACH_CB_ORDER_USER), --CB订购用户
    SUM(T0.REACH_CB_ORDER_SUCCESS_USER) + SUM(T0.UNKNOWN_CB_ORDER_SUCCESS_USER), --CB订购成功用户
    -- 月表部分指标
    SUM(T1.USER_NUM_LJ), --全网用户累计
    SUM(T1.USER_NUM_LJ) - SUM(T0.MARKET_DELINEATE_USER_LJ), --非圈定用户累计
    SUM(T0.MARKET_DELINEATE_USER_LJ), --营销圈定用户累计
    SUM(T0.FILTER_USER_LJ), --过滤用户累计
    SUM(T0.FILTER_LATER_USER_LJ), --过滤后用户累计
    SUM(T0.UNKNOWN_IS_REACH_USER_LJ), --未知是否触达用户触点未闭环部分累计
    SUM(T0.REACH_CB_NOT_ORDER_USER_LJ), --触达成功CB未订购用户累计
    SUM(T0.REACH_CB_ORDER_USER_LJ), --触达成功CB订购用户累计
    SUM(T0.UNKNOWN_IS_REACH_USER_LJ) + SUM(T0.UNKNOWN_IS_REACH_USER_B_LJ) - SUM(T0.UNKNOWN_CB_ORDER_USER_LJ), --未知是否触达CB未订购用户累计
    SUM(T0.UNKNOWN_CB_ORDER_USER_LJ), --未知是否触达CB订购用户累计
    SUM(T0.REACH_CB_ORDER_SUCCESS_USER_LJ), --触达成功CB订购成功用户累计
    SUM(T0.REACH_CB_ORDER_FAIL_USER_LJ), --触达成功CB订购失败用户累计
    SUM(T0.REACH_CB_ORDER_IN_USER_LJ), --触达成功CB订购中用户累计
    SUM(T0.UNKNOWN_CB_ORDER_SUCCESS_USER_LJ), --未知是否触达CB订购成功用户累计
    SUM(T0.UNKNOWN_CB_ORDER_FAIL_USER_LJ), --未知是否触达CB订购失败用户累计
    SUM(T0.UNKNOWN_CB_ORDER_IN_USER_LJ), --未知是否触达CB订购中用户累计
    SUM(T0.UNKNOWN_CB_ORDER_USER_LJ) + SUM(T0.REACH_CB_ORDER_USER_LJ), --CB订购用户累计
    SUM(T0.REACH_CB_ORDER_SUCCESS_USER_LJ) + SUM(T0.UNKNOWN_CB_ORDER_SUCCESS_USER_LJ) --CB订购成功用户累计
    
FROM (
    SELECT *
    FROM ITSY_CUBE.DM_KPI_STRATEGY_RPT_MID --全客策略维度日表中间表
    WHERE DATE_ID = '''||V_DATE||'''
        AND AREA_ID = ''-1''
) T0 LEFT JOIN (
    SELECT 
        DATE_ID,
        PROV_ID,
        AREA_ID,
        USER_NUM, --全网用户数
        USER_NUM_LJ --全网用户数累计
    FROM ITSY_CUBE.DM_C_D_CUS_MB_BASE_REPORT  --经分表
    WHERE DATE_ID = '''||V_DATE||'''
        AND AREA_ID = ''-1''
) T1 ON T0.DATE_ID = T1.DATE_ID AND T0.PROV_ID = T1.PROV_ID AND T0.AREA_ID = T1.AREA_ID

GROUP BY 
    T0.DATE_ID, --日账期
    T0.STRATEGY_ID
 ';
    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;
    
    V_RETCODE := 'SUCCESS';
    V_RETINFO := '结束';
    
EXCEPTION 
    WHEN OTHERS THEN
        V_RETCODE := 'FAIL';
        V_RETINFO := SQLERRM;
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
  • P_DM_KPI_STRATEGY_CH_MID
CREATE OR REPLACE PROCEDURE P_DM_KPI_STRATEGY_CH_MID(
    V_DATE IN VARCHAR ,
    V_PROV IN VARCHAR ,
    V_RETCODE OUT VARCHAR ,
    V_RETINFO OUT VARCHAR
)  AS
    /*@
    ****************************************************************
    *名称 --%@NAME:                    策略维度触点指标更新日中间表
    *功能描述 --%@COMMENT:             
    *执行周期 --%@PERIOD:              测试
    *参数 --%@PARAM:                   V_DATE  '20220302'
    *参数 --%@PARAM:V_RETCODE          过程运行结束成功与否标志
    *参数 --%@PARAM:V_RETINFO          过程运行结束成功与否描述
    *创建人 --%@CREATOR:             FX 
    *创建时间 --%@CREATED_TIME:     20220325
    *修改记录 --%@MODIFY:
    *来源表 --%@FROM:    ITSY_DWA.DWA_V_D_CUS_QK_TOUCH_USER_INFO_L7  --触达类拉链表T-7
    *目标表 --%@TO:       ITSY_CUBE.DM_KPI_STRATEGY_CH_MID
    *修改记录 --%@MODIFY:           
    ******************************************************************
    @*/
    
    V_SQL       VARCHAR(20000);
    V_CNT       NUMBER;
    V_PART_NAME1 VARCHAR(100);
    V_PART_NAME2 VARCHAR(100);
    V_L1_DAY      VARCHAR(2);
    V_L1D_MON      VARCHAR(6);
    V_L7D_MON      VARCHAR(6);
    V_L7_DATE      VARCHAR(8);
    V_MONTH         VARCHAR(6);
    V_DAY         VARCHAR(2);
BEGIN
    V_MONTH:=SUBSTR(V_DATE, 1, 6);
    V_DAY:=SUBSTR(V_DATE, 7, 2);
    --获取指定日期7天前的账期
    V_L7_DATE:=TO_CHAR(TO_DATE(V_DATE,'YYYYMMDD')-7,'YYYYMMDD');
    --获取指定日期7天前的月账期
    V_L7D_MON:=SUBSTRING('''||V_L7_DATE||''',1,6);
    
    --拼接分区名称
    V_PART_NAME1:='PART_'||V_DATE||'_'||V_PROV;  --根据宽表创建的分区
    V_PART_NAME2:='PART_'||V_L7_DATE||'_'||V_PROV;   --根据拉链表检测的分区
    
    /**
    * Description: 宽表T-1部分脚本
    * Date: 20220325
    */
    
    --检测分区1记录数
    V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_STRATEGY_CH_MID'' AND PART_NAME='''||V_PART_NAME1||'''';
    EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    --根据记录数判断分区是否存在,创建或清空分区
    IF V_CNT =0 THEN 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_STRATEGY_CH_MID ADD PARTITION '||V_PART_NAME1||'(DATE_ID='''||V_DATE||''',PROV_ID='''||V_PROV||''')';
    ELSE 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_STRATEGY_CH_MID TRUNCATE PARTITION '||V_PART_NAME1;
    END IF;
    
    V_SQL := 'INSERT INTO ITSY_CUBE.DM_KPI_STRATEGY_CH_MID PARTITION ON (DATE_ID='''||V_DATE||''',PROV_ID='''||V_PROV||''')
--地市指标
SELECT 
    MONTH_ID,
    PROV_ID,
    AREA_ID,
    PARENT_STRATEGY_ID,
    COUNT(DISTINCT CASE WHEN LOOP_RESULT IN(''0'',''1'') AND PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT = ''1'' AND CONTACT_TIME= '''||V_DATE||'''  THEN USER_ID END),--触达成功用户(当前的不确定的指标)
    COUNT(DISTINCT CASE WHEN PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT = ''0'' AND CONTACT_TIME= '''||V_DATE||'''  THEN USER_ID END),--触达失败用户(当前的不确定的指标)
    COUNT(DISTINCT CASE WHEN LOOP_RESULT IN(''0'',''1'') AND PUSH_CHANNEL_RESULT = ''1'' AND SUBSCRIBE_STATE IS NULL AND CONTACT_TIME= '''||V_DATE||''' THEN USER_ID END),--未知是否触达用户 --触点闭环部分(当前的不确定的指标)
    COUNT(DISTINCT CASE WHEN LOOP_RESULT IN(''0'',''1'') AND PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT = ''1'' AND SUBSTR(CONTACT_TIME,1,6)= '''||V_DATE||'''  THEN USER_ID END),--触达成功用户(当前的不确定的指标)
    COUNT(DISTINCT CASE WHEN PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT = ''0'' AND SUBSTR(CONTACT_TIME,1,6)= '''||V_MONTH||'''  THEN USER_ID END),--触达失败用户(当前的不确定的指标)
    COUNT(DISTINCT CASE WHEN LOOP_RESULT IN(''0'',''1'') AND PUSH_CHANNEL_RESULT = ''1'' AND SUBSCRIBE_STATE IS NULL AND SUBSTR(CONTACT_TIME,1,6)= '''||V_MONTH||''' THEN USER_ID END)--未知是否触达用户 --触点闭环部分(当前的不确定的指标)
FROM (
    SELECT * FROM ITSY_DWA.DWA_V_D_CUS_QK_USERS_INFO 
    WHERE MONTH_ID = '''||V_DATE||'''
        AND PROV_ID = '''||V_PROV||'''
)
GROUP BY 
    MONTH_ID,
    PROV_ID,
    AREA_ID,
    PARENT_STRATEGY_ID
    
UNION ALL 

--省份汇总

SELECT 
    MONTH_ID,
    PROV_ID,
    ''-1'',
    PARENT_STRATEGY_ID,
    COUNT(DISTINCT CASE WHEN LOOP_RESULT IN(''0'',''1'') AND PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT = ''1'' AND CONTACT_TIME= '''||V_DATE||'''  THEN USER_ID END),--触达成功用户(当前的不确定的指标)
    COUNT(DISTINCT CASE WHEN PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT = ''0'' AND CONTACT_TIME= '''||V_DATE||'''  THEN USER_ID END),--触达失败用户(当前的不确定的指标)
    COUNT(DISTINCT CASE WHEN LOOP_RESULT IN(''0'',''1'') AND PUSH_CHANNEL_RESULT = ''1'' AND SUBSCRIBE_STATE IS NULL AND CONTACT_TIME= '''||V_DATE||''' THEN USER_ID END),--未知是否触达用户 --触点闭环部分(当前的不确定的指标)
    COUNT(DISTINCT CASE WHEN LOOP_RESULT IN(''0'',''1'') AND PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT = ''1'' AND SUBSTR(CONTACT_TIME,1,6)= '''||V_DATE||'''  THEN USER_ID END),--触达成功用户(当前的不确定的指标)
    COUNT(DISTINCT CASE WHEN PUSH_CHANNEL_RESULT = ''1'' AND CONTACT_RESULT = ''0'' AND SUBSTR(CONTACT_TIME,1,6)= '''||V_MONTH||'''  THEN USER_ID END),--触达失败用户(当前的不确定的指标)
    COUNT(DISTINCT CASE WHEN LOOP_RESULT IN(''0'',''1'') AND PUSH_CHANNEL_RESULT = ''1'' AND SUBSCRIBE_STATE IS NULL AND SUBSTR(CONTACT_TIME,1,6)= '''||V_MONTH||''' THEN USER_ID END)--未知是否触达用户 --触点闭环部分(当前的不确定的指标)
FROM (
    SELECT * FROM ITSY_DWA.DWA_V_D_CUS_QK_USERS_INFO 
    WHERE MONTH_ID = '''||V_DATE||'''
        AND PROV_ID = '''||V_PROV||'''
)
GROUP BY 
    MONTH_ID,
    PROV_ID,
    PARENT_STRATEGY_ID
';

    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;
    
    /**
    * Description: 拉链表T-7部分脚本
    * Date: 20220325
    */
    
    --检测分区记录数
    V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_STRATEGY_CH_MID'' AND PART_NAME='''||V_PART_NAME2||'''';
    EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    --根据记录数判断分区是否存在,创建或清空分区
    IF V_CNT =0 THEN 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_STRATEGY_CH_MID ADD PARTITION '||V_PART_NAME2||'(DATE_ID='''||V_L7_DATE||''',PROV_ID='''||V_PROV||''')';
    ELSE 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_STRATEGY_CH_MID TRUNCATE PARTITION '||V_PART_NAME2;
    END IF;
    
    V_SQL := 'INSERT INTO ITSY_CUBE.DM_KPI_STRATEGY_CH_MID PARTITION ON (DATE_ID='''||V_L7_DATE||''',PROV_ID='''||V_PROV||''')

--短信部分
SELECT
    DATE_ID,
    PROV_ID,
    AREA_ID,
    STRATEGY_ID,
    SUM(REACH_FAIL_USER), --触达失败用户
    SUM(REACH_SUCCESS_USER), --触达成功用户
    SUM(UNKNOWN_IS_REACH_USER), --未知是否触达用户触点闭环部分
    SUM(REACH_FAIL_USER_LJ), --触达失败用户累计
    SUM(REACH_SUCCESS_USER_LJ), --触达成功用户累计
    SUM(UNKNOWN_IS_REACH_USER_LJ) --未知是否触达用户触点闭环部分累计
FROM (


    SELECT 
        DATE_ID, --日账期
        PROV_ID, --策略归属省分
        AREA_ID, --策略归属地市
        STRATEGY_ID, --策略ID
        SUM(CASE WHEN CONTACT_DATE = '''||V_DATE||''' AND IS_CONTACT_SUCCESS = ''0'' THEN 1 ELSE 0 END) REACH_FAIL_USER, --触达失败用户
        SUM(CASE WHEN CONTACT_DATE = '''||V_DATE||''' AND IS_CONTACT_SUCCESS = ''1'' THEN 1 ELSE 0 END) REACH_SUCCESS_USER, --触达成功用户
        SUM(CASE WHEN SEND_DATE = '''||V_L7_DATE||''' AND IS_CONTACT_SUCCESS IS NULL THEN 1 ELSE 0 END) UNKNOWN_IS_REACH_USER, --未知是否触达用户触点闭环部分
        SUM(CASE WHEN SUBSTRING(CONTACT_DATE,1,6) = '''||V_L7D_MON||''' AND IS_CONTACT_SUCCESS = ''0'' THEN 1 ELSE 0 END) REACH_FAIL_USER_LJ, --触达失败用户累计    
        SUM(CASE WHEN SUBSTRING(CONTACT_DATE,1,6) = '''||V_L7D_MON||''' AND IS_CONTACT_SUCCESS = ''1'' THEN 1 ELSE 0 END) REACH_SUCCESS_USER_LJ, --触达成功用户累计
        SUM(CASE WHEN SUBSTRING(SEND_DATE,1,6) ='''||V_L7D_MON||''' AND IS_CONTACT_SUCCESS IS NULL THEN 1 ELSE 0 END) UNKNOWN_IS_REACH_USER_LJ --未知是否触达用户触点闭环部分累计
    FROM (
        SELECT 
            DATE_ID,
            MONTH_ID,
            DAY_ID,
            CREATE_PROV_ID PROV_ID,
            CREATE_AREA_ID AREA_ID,
            IS_CONTACT_SUCCESS,
            SEND_DATE,
            CONTACT_DATE,
            STRATEGY_ID
        FROM 
        (
            SELECT 
                DATE_ID,
                MONTH_ID,
                DAY_ID,
                CREATE_PROV_ID,
                CREATE_AREA_ID,
                IS_CONTACT_SUCCESS,
                SEND_DATE,
                CONTACT_DATE,
                SUBSTRB(STRATEGY_ID,1,REGEXP_INSTR(STRATEGY_ID,''-'')-1) STRATEGY_ID, --策略ID截取"-"之前部分获取父策略ID
                ROW_NUMBER() OVER(PARTITION BY STRATEGY_ID,CHANNEL_CODE,DEVICE_NUMBER ORDER BY SEND_DATE DESC) RN --根据策略ID,触点编码,用户号码分组打标
            FROM ITSY_DWA.DWA_V_D_CUS_QK_TOUCH_USER_INFO_L7  --触达类拉链表T-7
            WHERE DATE_ID = '''||V_L7_DATE||'''
                AND CREATE_PROV_ID = '''||V_PROV||'''
                AND SOURCE_TYPE = ''SMS''
        ) T0 
        WHERE RN = ''1''
    )

    GROUP BY 
        DATE_ID, --日账期
        PROV_ID, --策略归属省分
        AREA_ID , --策略归属地市
        STRATEGY_ID  --策略ID
)

GROUP BY 
    DATE_ID,
    PROV_ID,
    AREA_ID,
    STRATEGY_ID
    
UNION ALL 

--短信部分省份汇总

SELECT
    DATE_ID,
    PROV_ID,
    ''-1'',
    STRATEGY_ID,
    SUM(REACH_FAIL_USER), --触达失败用户
    SUM(REACH_SUCCESS_USER), --触达成功用户
    SUM(UNKNOWN_IS_REACH_USER), --未知是否触达用户触点闭环部分
    SUM(REACH_FAIL_USER_LJ), --触达失败用户累计
    SUM(REACH_SUCCESS_USER_LJ), --触达成功用户累计
    SUM(UNKNOWN_IS_REACH_USER_LJ) --未知是否触达用户触点闭环部分累计
FROM (


    SELECT 
        DATE_ID, --日账期
        PROV_ID, --策略归属省分
        AREA_ID, --策略归属地市
        STRATEGY_ID, --策略ID
        SUM(CASE WHEN CONTACT_DATE = '''||V_DATE||''' AND IS_CONTACT_SUCCESS = ''0'' THEN 1 ELSE 0 END) REACH_FAIL_USER, --触达失败用户
        SUM(CASE WHEN CONTACT_DATE = '''||V_DATE||''' AND IS_CONTACT_SUCCESS = ''1'' THEN 1 ELSE 0 END) REACH_SUCCESS_USER, --触达成功用户
        SUM(CASE WHEN SEND_DATE = '''||V_L7_DATE||''' AND IS_CONTACT_SUCCESS IS NULL THEN 1 ELSE 0 END) UNKNOWN_IS_REACH_USER, --未知是否触达用户触点闭环部分
        SUM(CASE WHEN SUBSTRING(CONTACT_DATE,1,6) = '''||V_L7D_MON||''' AND IS_CONTACT_SUCCESS = ''0'' THEN 1 ELSE 0 END) REACH_FAIL_USER_LJ, --触达失败用户累计    
        SUM(CASE WHEN SUBSTRING(CONTACT_DATE,1,6) = '''||V_L7D_MON||''' AND IS_CONTACT_SUCCESS = ''1'' THEN 1 ELSE 0 END) REACH_SUCCESS_USER_LJ, --触达成功用户累计
        SUM(CASE WHEN SUBSTRING(SEND_DATE,1,6) ='''||V_L7D_MON||''' AND IS_CONTACT_SUCCESS IS NULL THEN 1 ELSE 0 END) UNKNOWN_IS_REACH_USER_LJ --未知是否触达用户触点闭环部分累计
    FROM (
        SELECT 
            DATE_ID,
            CREATE_PROV_ID PROV_ID,
            CREATE_AREA_ID AREA_ID,
            MONTH_ID,
            DAY_ID,
            IS_CONTACT_SUCCESS,
            SEND_DATE,
            CONTACT_DATE,
            STRATEGY_ID
        FROM 
        (
            SELECT 
                DATE_ID,
                CREATE_PROV_ID,
                CREATE_AREA_ID,
                MONTH_ID,
                DAY_ID,
                IS_CONTACT_SUCCESS,
                SEND_DATE,
                CONTACT_DATE,
                SUBSTRB(STRATEGY_ID,1,REGEXP_INSTR(STRATEGY_ID,''-'')-1) STRATEGY_ID, --策略ID截取"-"之前部分获取父策略ID
                ROW_NUMBER() OVER(PARTITION BY STRATEGY_ID,CHANNEL_CODE,DEVICE_NUMBER ORDER BY SEND_DATE DESC) RN --根据策略ID,触点编码,用户号码分组打标
            FROM ITSY_DWA.DWA_V_D_CUS_QK_TOUCH_USER_INFO_L7  --触达类拉链表T-7
            WHERE DATE_ID = '''||V_L7_DATE||'''
                AND CREATE_PROV_ID = '''||V_PROV||'''
                AND SOURCE_TYPE = ''SMS''
        ) T0 
        WHERE RN = ''1''
    )

    GROUP BY 
        DATE_ID, --日账期
        PROV_ID, --策略归属省分
        AREA_ID, --策略归属地市    
        STRATEGY_ID  --策略ID
)

GROUP BY 
    DATE_ID,
    PROV_ID,
    STRATEGY_ID

    
UNION ALL 

--非短信部分

SELECT
    DATE_ID,
    PROV_ID,
    AREA_ID,
    STRATEGY_ID,
    SUM(REACH_FAIL_USER), --触达失败用户
    SUM(REACH_SUCCESS_USER), --触达成功用户
    SUM(UNKNOWN_IS_REACH_USER), --未知是否触达用户触点闭环部分
    SUM(REACH_FAIL_USER_LJ), --触达失败用户累计
    SUM(REACH_SUCCESS_USER_LJ), --触达成功用户累计
    SUM(UNKNOWN_IS_REACH_USER_LJ) --未知是否触达用户触点闭环部分累计
FROM (


    SELECT 
        DATE_ID, --日账期
        PROV_ID, --策略归属省分
        AREA_ID, --策略归属地市
        STRATEGY_ID, --策略ID
        SUM(CASE WHEN CONTACT_DATE = '''||V_L7_DATE||''' AND IS_CONTACT_SUCCESS = ''0'' THEN 1 ELSE 0 END) REACH_FAIL_USER, --触达失败用户
        SUM(CASE WHEN CONTACT_DATE = '''||V_L7_DATE||''' AND IS_CONTACT_SUCCESS = ''1'' THEN 1 ELSE 0 END) REACH_SUCCESS_USER, --触达成功用户
        SUM(CASE WHEN CONTACT_DATE = '''||V_L7_DATE||''' AND IS_CONTACT_SUCCESS IS NULL THEN 1 ELSE 0 END) UNKNOWN_IS_REACH_USER, --未知是否触达用户触点闭环部分
        SUM(CASE WHEN SUBSTRING(CONTACT_DATE,1,6) = '''||V_L7D_MON||''' AND IS_CONTACT_SUCCESS = ''0'' THEN 1 ELSE 0 END) REACH_FAIL_USER_LJ, --触达失败用户累计    
        SUM(CASE WHEN SUBSTRING(CONTACT_DATE,1,6) = '''||V_L7D_MON||''' AND IS_CONTACT_SUCCESS = ''1'' THEN 1 ELSE 0 END) REACH_SUCCESS_USER_LJ, --触达成功用户累计
        SUM(CASE WHEN SUBSTRING(CONTACT_DATE,1,6) = '''||V_L7D_MON||''' AND IS_CONTACT_SUCCESS IS NULL THEN 1 ELSE 0 END) UNKNOWN_IS_REACH_USER_LJ --未知是否触达用户触点闭环部分累计
    FROM (
        SELECT 
            DATE_ID,
            CREATE_PROV_ID PROV_ID,
            CREATE_AREA_ID AREA_ID,
            IS_CONTACT_SUCCESS,
            SEND_DATE,
            CONTACT_DATE,
            STRATEGY_ID
        FROM 
        (
            SELECT 
                DATE_ID,
                CREATE_PROV_ID,
                CREATE_AREA_ID,
                IS_CONTACT_SUCCESS,
                SEND_DATE,
                CONTACT_DATE,
                SUBSTRB(STRATEGY_ID,1,REGEXP_INSTR(STRATEGY_ID,''-'')-1) STRATEGY_ID, --策略ID截取"-"之前部分获取父策略ID
                ROW_NUMBER() OVER(PARTITION BY STRATEGY_ID,CHANNEL_CODE,DEVICE_NUMBER ORDER BY SEND_DATE DESC) RN --根据策略ID,触点编码,用户号码分组打标
            FROM ITSY_DWA.DWA_V_D_CUS_QK_TOUCH_USER_INFO_L7  --触达类拉链表T-7
            WHERE DATE_ID = '''||V_L7_DATE||'''
                AND CREATE_PROV_ID = '''||V_PROV||'''
                AND SOURCE_TYPE != ''SMS''
        ) T0 
        WHERE RN = ''1''
    )

    GROUP BY 
        DATE_ID, --日账期
        PROV_ID, --策略归属省分
        AREA_ID , --策略归属地市
        STRATEGY_ID  --策略ID
)

GROUP BY 
    DATE_ID,
    PROV_ID,
    AREA_ID,
    STRATEGY_ID
    
    
UNION ALL 

--非短信部分省份汇总

SELECT
    DATE_ID,
    PROV_ID,
    ''-1'',
    STRATEGY_ID,
    SUM(REACH_FAIL_USER), --触达失败用户
    SUM(REACH_SUCCESS_USER), --触达成功用户
    SUM(UNKNOWN_IS_REACH_USER), --未知是否触达用户触点闭环部分
    SUM(REACH_FAIL_USER_LJ), --触达失败用户累计
    SUM(REACH_SUCCESS_USER_LJ), --触达成功用户累计
    SUM(UNKNOWN_IS_REACH_USER_LJ) --未知是否触达用户触点闭环部分累计
FROM (


    SELECT 
        DATE_ID, --日账期
        PROV_ID, --策略归属省分
        AREA_ID, --策略归属地市
        STRATEGY_ID, --策略ID
        SUM(CASE WHEN CONTACT_DATE = '''||V_L7_DATE||''' AND IS_CONTACT_SUCCESS = ''0'' THEN 1 ELSE 0 END) REACH_FAIL_USER, --触达失败用户
        SUM(CASE WHEN CONTACT_DATE = '''||V_L7_DATE||''' AND IS_CONTACT_SUCCESS = ''1'' THEN 1 ELSE 0 END) REACH_SUCCESS_USER, --触达成功用户
        SUM(CASE WHEN CONTACT_DATE = '''||V_L7_DATE||''' AND IS_CONTACT_SUCCESS IS NULL THEN 1 ELSE 0 END) UNKNOWN_IS_REACH_USER, --未知是否触达用户触点闭环部分
        SUM(CASE WHEN SUBSTRING(CONTACT_DATE,1,6) = '''||V_L7D_MON||''' AND IS_CONTACT_SUCCESS = ''0'' THEN 1 ELSE 0 END) REACH_FAIL_USER_LJ, --触达失败用户累计    
        SUM(CASE WHEN SUBSTRING(CONTACT_DATE,1,6) = '''||V_L7D_MON||''' AND IS_CONTACT_SUCCESS = ''1'' THEN 1 ELSE 0 END) REACH_SUCCESS_USER_LJ, --触达成功用户累计
        SUM(CASE WHEN SUBSTRING(CONTACT_DATE,1,6) = '''||V_L7D_MON||''' AND IS_CONTACT_SUCCESS IS NULL THEN 1 ELSE 0 END) UNKNOWN_IS_REACH_USER_LJ --未知是否触达用户触点闭环部分累计
    FROM (
        SELECT 
            DATE_ID,
            CREATE_PROV_ID PROV_ID,
            CREATE_AREA_ID AREA_ID,
            IS_CONTACT_SUCCESS,
            SEND_DATE,
            CONTACT_DATE,
            STRATEGY_ID
        FROM 
        (
            SELECT 
                DATE_ID,
                CREATE_PROV_ID,
                CREATE_AREA_ID,
                IS_CONTACT_SUCCESS,
                SEND_DATE,
                CONTACT_DATE,
                SUBSTRB(STRATEGY_ID,1,REGEXP_INSTR(STRATEGY_ID,''-'')-1) STRATEGY_ID, --策略ID截取"-"之前部分获取父策略ID
                ROW_NUMBER() OVER(PARTITION BY STRATEGY_ID,CHANNEL_CODE,DEVICE_NUMBER ORDER BY SEND_DATE DESC) RN --根据策略ID,触点编码,用户号码分组打标
            FROM ITSY_DWA.DWA_V_D_CUS_QK_TOUCH_USER_INFO_L7  --触达类拉链表T-7
            WHERE DATE_ID = '''||V_L7_DATE||'''
                AND CREATE_PROV_ID = '''||V_PROV||'''
                AND SOURCE_TYPE != ''SMS''
        ) T0 
        WHERE RN = ''1''
    )

    GROUP BY 
        DATE_ID, --日账期
        PROV_ID, --策略归属省分
        AREA_ID , --策略归属地市
        STRATEGY_ID  --策略ID
)

GROUP BY 
    DATE_ID,
    PROV_ID,
    STRATEGY_ID

 ';
    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;
    
    V_RETCODE := 'SUCCESS';
    V_RETINFO := '结束';
    
EXCEPTION 
    WHEN OTHERS THEN
        V_RETCODE := 'FAIL';
        V_RETINFO := SQLERRM;
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
  • P_DM_KPI_STRATEGY_CH
CREATE OR REPLACE PROCEDURE P_DM_KPI_STRATEGY_CH(
    V_DATE IN VARCHAR ,
    V_RETCODE OUT VARCHAR ,
    V_RETINFO OUT VARCHAR
)
    AS
    /*@
    ****************************************************************
    *名称 --%@NAME:                    策略维度触点指标更新日表
    *功能描述 --%@COMMENT:             
    *执行周期 --%@PERIOD:              测试
    *参数 --%@PARAM:                   V_DATE  '20220301'
    *参数 --%@PARAM:V_RETCODE          过程运行结束成功与否标志
    *参数 --%@PARAM:V_RETINFO          过程运行结束成功与否描述
    *创建人 --%@CREATOR:             FX 
    *创建时间 --%@CREATED_TIME:     20220317
    *修改记录 --%@MODIFY:
    *来源表 --%@FROM:    ITSY_CUBE.DM_KPI_STRATEGY_CH_MID
    *目标表 --%@TO:       ITSY_CUBE.DM_KPI_STRATEGY_CH
    *修改记录 --%@MODIFY:           
    ******************************************************************
    @*/
    
    V_SQL       VARCHAR(10000);
    V_CNT       NUMBER;
    V_PART_NAME VARCHAR(100);
BEGIN
    V_PART_NAME:='PART_'||V_DATE;
    V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_STRATEGY_CH'' AND PART_NAME='''||V_PART_NAME||'''';
    EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    
    IF V_CNT =0 THEN 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_STRATEGY_CH ADD PARTITION '||V_PART_NAME||'(DATE_ID='''||V_DATE||''')';
    ELSE 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_STRATEGY_CH TRUNCATE PARTITION '||V_PART_NAME;
    END IF;
    
    V_SQL := 'INSERT INTO ITSY_CUBE.DM_KPI_STRATEGY_CH PARTITION ON (DATE_ID='''||V_DATE||''')

--地市、省份基础指标汇总
SELECT 
    DATE_ID, --日账期
    PROV_ID, --策略归属省分
    AREA_ID, --策略归属地市
    STRATEGY_ID, --策略ID
    SUM(REACH_FAIL_USER), --触点失败用户
    SUM(REACH_SUCCESS_USER), --触点成功用户
    SUM(UNKNOWN_IS_REACH_USER), --未知是否触达用户触点闭环部分
    SUM(REACH_FAIL_USER_LJ), --触点失败用户累计
    SUM(REACH_SUCCESS_USER_LJ), --触点成功用户累计
    SUM(UNKNOWN_IS_REACH_USER_LJ) --未知是否触达用户触点闭环部分累计
FROM ITSY_CUBE.DM_KPI_STRATEGY_CH_MID  --策略维度触电指标日更新中间表
WHERE DATE_ID = '''||V_DATE||'''
GROUP BY 
    DATE_ID, --日账期
    PROV_ID, --策略归属省分
    AREA_ID, --策略归属地市
    STRATEGY_ID --策略ID

--全国汇总

UNION ALL 

SELECT 
    DATE_ID, --日账期
    ''111'', --策略归属省分
    ''-1'', --策略归属地市
    STRATEGY_ID, --策略ID
    SUM(REACH_FAIL_USER), --触点失败用户
    SUM(REACH_SUCCESS_USER), --触点成功用户
    SUM(UNKNOWN_IS_REACH_USER), --未知是否触达用户触点闭环部分
    SUM(REACH_FAIL_USER_LJ), --触点失败用户累计
    SUM(REACH_SUCCESS_USER_LJ), --触点成功用户累计
    SUM(UNKNOWN_IS_REACH_USER_LJ) --未知是否触达用户触点闭环部分累计
FROM ITSY_CUBE.DM_KPI_STRATEGY_CH_MID  --策略维度触电指标日更新中间表
WHERE DATE_ID = '''||V_DATE||'''
    AND AREA_ID != ''-1''
GROUP BY 
    DATE_ID, --日账期
    STRATEGY_ID --策略ID
 ';
    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;
    
    V_RETCODE := 'SUCCESS';
    V_RETINFO := '结束';
    
EXCEPTION 
    WHEN OTHERS THEN
        V_RETCODE := 'FAIL';
        V_RETINFO := SQLERRM;
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

应用层导数

数据云流程

待开发……

Last modification:March 25th, 2022 at 03:56 pm
如果觉得我的文章对你有用,请随意赞赏