-- Task: all曝光底表  ID: 1018595946  Type: ODPS_SQL
--odps sql 
--********************************************************************--
--author:于卓异
--create time:2024-07-11 17:37:42
--********************************************************************--
--@exclude_input=loghubods.alg_vid_feature_all_exp_base_add
--@exclude_input=loghubods.video_action_log_flow_new
--odps sql 
--********************************************************************--
--author:于卓异
--create time:2024-07-09 14:06:40
--********************************************************************--
--CREATE TABLE IF NOT EXISTS loghubods.alg_vid_feature_all_exp_base_add
--(
--    apptype            STRING
--    ,pagesource        STRING
--    ,uid               STRING
--    ,mid               STRING
--    ,vid               STRING
--    ,logtimestamp      STRING
--    ,businesstype      STRING
--    ,sessionid         STRING
--    ,subsessionid      STRING
--    ,abinfodata        STRING
--    ,recommendlogvo    STRING
--    ,extparams         STRING
--    ,abcode            STRING
--    ,recommendPageType STRING
--    ,flowpool          STRING
--    ,level             STRING
--)
--COMMENT 'TABLE COMMENT'
--PARTITIONED BY 
--(
--    dt                 STRING COMMENT '天'
--    ,hh                STRING COMMENT '小时'
--)
--LIFECYCLE 30
--;
INSERT OVERWRITE TABLE loghubods.alg_vid_feature_all_exp_base_add PARTITION (dt = '${dt}',hh = '${hh}')
SELECT DISTINCT  apptype
            ,pagesource
            ,uid
            ,mid
            ,videoid AS vid
            ,logtimestamp
            ,businesstype
            ,sessionid
            ,subsessionid
            ,abinfodata
            ,recommendlogvo
            ,extparams
            ,GET_JSON_OBJECT(extparams,'$.eventInfos.ab_test003') AS abcode
            ,GET_JSON_OBJECT(extparams,'$.recommendPageType') AS recommendPageType
            ,flowpool
            ,SPLIT(flowpool,'#')[2] AS level
    FROM    loghubods.video_action_log_flow_new 
WHERE   CONCAT(year,month,day,hour) = TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 1),'YYYYMMDDHH')
    AND     apptype NOT IN ('12')
    AND     pagesource REGEXP 'category$|recommend$|-pages/user-videos-detail$|-pages/user-videos-share$'
    AND     businesstype IN ('videoView');


--drop table loghubods.alg_vid_feature_all_exp_base_v2 
--CREATE TABLE IF NOT EXISTS loghubods.alg_vid_feature_all_exp_base_v2 
--(
--         apptype  STRING 
--        ,pagesource STRING
--        ,uid STRING
--        ,mid STRING
--        ,vid  STRING
--        ,logtimestamp STRING
--        ,businesstype STRING
--        ,sessionid STRING
--        ,subsessionid STRING
--        ,abinfodata STRING
--        ,recommendlogvo STRING
--        ,extparams STRING
--        ,abcode STRING
--        ,recommendPageType  STRING
--        ,flowpool STRING
--        ,level STRING    
--)
--COMMENT 'TABLE COMMENT'
--PARTITIONED BY 
--(
--    dt STRING COMMENT '天'
--   ,hh STRING COMMENT '小时'
--
--
--)
--LIFECYCLE 30
--;



INSERT OVERWRITE  TABLE loghubods.alg_vid_feature_all_exp_base_v2 PARTITION (dt = '${dt}',hh = '${hh}')
SELECT  DISTINCT  apptype
        ,pagesource
        ,uid
        ,mid
        ,vid
        ,logtimestamp
        ,businesstype
        ,sessionid
        ,subsessionid
        ,abinfodata
        ,recommendlogvo
        ,extparams
        ,abcode
        ,recommendPageType
        ,flowpool
        ,level
FROM    loghubods.alg_vid_feature_all_exp_base_add
WHERE   CONCAT(dt,hh) BETWEEN TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * ${hours_early}),'YYYYMMDDHH') AND TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH'))),'YYYYMMDDHH')