[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
- 数仓：在用户信息宽表中增加字段source_type，区分SMS（来源于触达表）
原有触达拉链表拆分为触达用户表（T-1日表，按日更新）+短信拉链表（T-1拉链近60天数据，一个day_id是包含近60天的数据） - 经分：触达需要回刷的触达类指标，口径变更，账期增加字段day_id、month_id，缩小数据范围
- 经分：经分侧拿用户信息宽表非短信部分+短信拉链表形成新表，以便出T-8指标
- 经分：由于短信拉链表中不包含user_id，剔重需按照“用户手机号码+策略+触点+发送时间+接触时间”：
group by 用户手机号码+策略+触点，order by 发送时间+接触时间，降序后取1条 - 经分需注意：月指标需要每天更新
风险：方案变更可能导致延期
行云脚本开发
由于来源表的宽表月账期也是按日刷新，所以在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;应用层导数
数据云流程
待开发……
