-- Task: 12_推荐场景下的视频特征_长周期_20250212  ID: 1023818581  Type: ODPS_SQL
--@exclude_input=loghubods.alg_vid_feature_basic_info
--@exclude_input=loghubods.dwd_recsys_alg_exposure_base_20250108
CREATE TABLE IF NOT EXISTS loghubods.alg_vid_long_period_recommend_exp_feature_20250212
(
    vid      STRING COMMENT '视频id'
    ,feature STRING COMMENT '特征JSON'
)
COMMENT '11_推荐场景下的视频长周期特征'
PARTITIONED BY 
(
    dt       STRING COMMENT '天'
)
LIFECYCLE 30
;

INSERT OVERWRITE TABLE loghubods.alg_vid_long_period_recommend_exp_feature_20250212 PARTITION (dt = '${dt}')
WITH t_exp AS 
(
    SELECT  vid
            ,apptype
            ,page
            ,recommendpagetype
            ,province
            ,hotsencetype
            ,machineinfo_brand AS brand
            ,is_share
            ,return_n_uv
            ,share_cnt
            ,is_return_1
            ,new_exposure_cnt
            ,UNIX_TIMESTAMP(TO_DATE('${dt}','YYYYMMDD')) AS ts_now
            ,UNIX_TIMESTAMP(TO_DATE('${dt}','YYYYMMDD')) - CAST(ts AS BIGINT) AS ts_diff
    FROM    loghubods.dwd_recsys_alg_exposure_base_20250108
    WHERE   dt BETWEEN TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}','YYYYMMDD')) - 3600 * 24 * 60),'YYYYMMDD') AND TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}','YYYYMMDD')) - 3600 * 1 * 24),'YYYYMMDD')
    AND     (
                page IN ("详情后沉浸页","详情页")
                OR      (
                            page = "回流后沉浸页&内页feed"
                            AND     recommendpagetype REGEXP "-pages/user-videos-share-recommend-detail$"
                )
    )
    AND     apptype NOT IN ("12")
)
,t_basic_info AS 
(
    SELECT  *
    FROM    (
                SELECT  vid
                        ,COALESCE(GET_JSON_OBJECT(feature,"$.channel"),"unknown") AS channel
                        ,COALESCE(GET_JSON_OBJECT(feature,"$.merge_first_level_cate"),"unknown") AS merge_cate1
                        ,COALESCE(GET_JSON_OBJECT(feature,"$.merge_second_level_cate"),"unknown") AS merge_cate2
                        ,COALESCE(GET_JSON_OBJECT(feature,"$.festive_label1"),"unknown") AS festive_label1
                        ,COALESCE(GET_JSON_OBJECT(feature,"$.festive_label2"),"unknown") AS festive_label2
                        ,ROW_NUMBER() OVER (PARTITION BY vid ) AS rn
                FROM    loghubods.alg_vid_feature_basic_info
                WHERE   CONCAT(dt,hh) = "${dt}"
                AND     hh = 23
            ) 
    WHERE   rn = 1
)
,t_base AS 
(
    SELECT  ta.*
            ,info.channel
            ,info.merge_cate1
            ,info.merge_cate2
            ,info.festive_label1
            ,info.festive_label2
    FROM    t_exp ta
    LEFT JOIN t_basic_info info
    ON      ta.vid = info.vid
)
,t_agg AS 
(
    SELECT  vid
            ,SUM(CASE    WHEN ts_diff <= 3600 * 24 * 7 THEN 1 ELSE 0 END) AS exp_7d
            ,SUM(CASE    WHEN ts_diff <= 3600 * 24 * 14 THEN 1 ELSE 0 END) AS exp_14d
            ,SUM(CASE    WHEN ts_diff <= 3600 * 24 * 30 THEN 1 ELSE 0 END) AS exp_30d
            ,SUM(CASE    WHEN ts_diff <= 3600 * 24 * 60 THEN 1 ELSE 0 END) AS exp_60d
            ,SUM(CASE    WHEN ts_diff <= 3600 * 24 * 7 THEN is_share ELSE 0 END) AS is_share_7d
            ,SUM(CASE    WHEN ts_diff <= 3600 * 24 * 14 THEN is_share ELSE 0 END) AS is_share_14d
            ,SUM(CASE    WHEN ts_diff <= 3600 * 24 * 30 THEN is_share ELSE 0 END) AS is_share_30d
            ,SUM(CASE    WHEN ts_diff <= 3600 * 24 * 60 THEN is_share ELSE 0 END) AS is_share_60d
            ,SUM(CASE    WHEN ts_diff <= 3600 * 24 * 7 THEN share_cnt ELSE 0 END) AS share_cnt_7d
            ,SUM(CASE    WHEN ts_diff <= 3600 * 24 * 14 THEN share_cnt ELSE 0 END) AS share_cnt_14d
            ,SUM(CASE    WHEN ts_diff <= 3600 * 24 * 30 THEN share_cnt ELSE 0 END) AS share_cnt_30d
            ,SUM(CASE    WHEN ts_diff <= 3600 * 24 * 60 THEN share_cnt ELSE 0 END) AS share_cnt_60d
            ,SUM(CASE    WHEN ts_diff <= 3600 * 24 * 7 THEN is_return_1 ELSE 0 END) AS is_return_1_7d
            ,SUM(CASE    WHEN ts_diff <= 3600 * 24 * 14 THEN is_return_1 ELSE 0 END) AS is_return_1_14d
            ,SUM(CASE    WHEN ts_diff <= 3600 * 24 * 30 THEN is_return_1 ELSE 0 END) AS is_return_1_30d
            ,SUM(CASE    WHEN ts_diff <= 3600 * 24 * 60 THEN is_return_1 ELSE 0 END) AS is_return_1_60d
            ,SUM(CASE    WHEN ts_diff <= 3600 * 24 * 7 THEN is_return_1 ELSE 0 END) AS return_n_uv_7d
            ,SUM(CASE    WHEN ts_diff <= 3600 * 24 * 14 THEN is_return_1 ELSE 0 END) AS return_n_uv_14d
            ,SUM(CASE    WHEN ts_diff <= 3600 * 24 * 30 THEN is_return_1 ELSE 0 END) AS return_n_uv_30d
            ,SUM(CASE    WHEN ts_diff <= 3600 * 24 * 60 THEN is_return_1 ELSE 0 END) AS return_n_uv_60d
            ,SUM(CASE    WHEN ts_diff <= 3600 * 24 * 7 THEN new_exposure_cnt ELSE 0 END) AS new_exposure_cnt_7d
            ,SUM(CASE    WHEN ts_diff <= 3600 * 24 * 14 THEN new_exposure_cnt ELSE 0 END) AS new_exposure_cnt_14d
            ,SUM(CASE    WHEN ts_diff <= 3600 * 24 * 30 THEN new_exposure_cnt ELSE 0 END) AS new_exposure_cnt_30d
            ,SUM(CASE    WHEN ts_diff <= 3600 * 24 * 60 THEN new_exposure_cnt ELSE 0 END) AS new_exposure_cnt_60d
    FROM    t_base
    WHERE   ts_diff >= 0
    GROUP BY vid
)
,t_index AS 
(
    SELECT  *
            ,ROUND(COALESCE(is_share_7d / exp_7d,0),6) AS str_one_7d
            ,ROUND(COALESCE(return_n_uv_7d / is_share_7d,0),6) AS ros_one_7d
            ,ROUND(COALESCE(share_cnt_7d / exp_7d,0),6) AS str_7d
            ,ROUND(COALESCE(return_n_uv_7d / share_cnt_7d,0),6) AS ros_7d
            ,ROUND(COALESCE(is_return_1_7d / exp_7d,0),6) AS str_plus_7d
            ,ROUND(COALESCE(return_n_uv_7d / is_return_1_7d,0),6) AS ros_minus_7d
            ,ROUND(COALESCE(return_n_uv_7d / exp_7d,0),6) AS rovn_7d
            ,ROUND(COALESCE(is_share_14d / exp_14d,0),6) AS str_one_14d
            ,ROUND(COALESCE(return_n_uv_14d / is_share_14d,0),6) AS ros_one_14d
            ,ROUND(COALESCE(share_cnt_14d / exp_14d,0),6) AS str_14d
            ,ROUND(COALESCE(return_n_uv_14d / share_cnt_14d,0),6) AS ros_14d
            ,ROUND(COALESCE(is_return_1_14d / exp_14d,0),6) AS str_plus_14d
            ,ROUND(COALESCE(return_n_uv_14d / is_return_1_14d,0),6) AS ros_minus_14d
            ,ROUND(COALESCE(return_n_uv_14d / exp_14d,0),6) AS rovn_14d
            ,ROUND(COALESCE(is_share_30d / exp_30d,0),6) AS str_one_30d
            ,ROUND(COALESCE(return_n_uv_30d / is_share_30d,0),6) AS ros_one_30d
            ,ROUND(COALESCE(share_cnt_30d / exp_30d,0),6) AS str_30d
            ,ROUND(COALESCE(return_n_uv_30d / share_cnt_30d,0),6) AS ros_30d
            ,ROUND(COALESCE(is_return_1_30d / exp_30d,0),6) AS str_plus_30d
            ,ROUND(COALESCE(return_n_uv_30d / is_return_1_30d,0),6) AS ros_minus_30d
            ,ROUND(COALESCE(return_n_uv_30d / exp_30d,0),6) AS rovn_30d
            ,ROUND(COALESCE(is_share_60d / exp_60d,0),6) AS str_one_60d
            ,ROUND(COALESCE(return_n_uv_60d / is_share_60d,0),6) AS ros_one_60d
            ,ROUND(COALESCE(share_cnt_60d / exp_60d,0),6) AS str_60d
            ,ROUND(COALESCE(return_n_uv_60d / share_cnt_60d,0),6) AS ros_60d
            ,ROUND(COALESCE(is_return_1_60d / exp_60d,0),6) AS str_plus_60d
            ,ROUND(COALESCE(return_n_uv_60d / is_return_1_60d,0),6) AS ros_minus_60d
            ,ROUND(COALESCE(return_n_uv_60d / exp_60d,0),6) AS rovn_60d
    FROM    t_agg
)
,t_result AS 
(
    SELECT  vid
            ,JSON_FORMAT(
                        JSON_OBJECT("exp_7d",CAST(exp_7d AS STRING),"exp_14d",CAST(exp_14d AS STRING),"exp_30d",CAST(exp_30d AS STRING),"exp_60d",CAST(exp_60d AS STRING),"is_share_7d",CAST(is_share_7d AS STRING),"is_share_14d",CAST(is_share_14d AS STRING),"is_share_30d",CAST(is_share_30d AS STRING),"is_share_60d",CAST(is_share_60d AS STRING),"share_cnt_7d",CAST(share_cnt_7d AS STRING),"share_cnt_14d",CAST(share_cnt_14d AS STRING),"share_cnt_30d",CAST(share_cnt_30d AS STRING),"share_cnt_60d",CAST(share_cnt_60d AS STRING),"is_return_1_7d",CAST(is_return_1_7d AS STRING),"is_return_1_14d",CAST(is_return_1_14d AS STRING),"is_return_1_30d",CAST(is_return_1_30d AS STRING),"is_return_1_60d",CAST(is_return_1_60d AS STRING),"return_n_uv_7d",CAST(return_n_uv_7d AS STRING),"return_n_uv_14d",CAST(return_n_uv_14d AS STRING),"return_n_uv_30d",CAST(return_n_uv_30d AS STRING),"return_n_uv_60d",CAST(return_n_uv_60d AS STRING),"new_exposure_cnt_7d",CAST(new_exposure_cnt_7d AS STRING),"new_exposure_cnt_14d",CAST(new_exposure_cnt_14d AS STRING),"new_exposure_cnt_30d",CAST(new_exposure_cnt_30d AS STRING),"new_exposure_cnt_60d",CAST(new_exposure_cnt_60d AS STRING),"str_one_7d",CAST(str_one_7d AS STRING),"ros_one_7d",CAST(ros_one_7d AS STRING),"str_7d",CAST(str_7d AS STRING),"ros_7d",CAST(ros_7d AS STRING),"str_plus_7d",CAST(str_plus_7d AS STRING),"ros_minus_7d",CAST(ros_minus_7d AS STRING),"rovn_7d",CAST(rovn_7d AS STRING),"str_one_14d",CAST(str_one_14d AS STRING),"ros_one_14d",CAST(ros_one_14d AS STRING),"str_14d",CAST(str_14d AS STRING),"ros_14d",CAST(ros_14d AS STRING),"str_plus_14d",CAST(str_plus_14d AS STRING),"ros_minus_14d",CAST(ros_minus_14d AS STRING),"rovn_14d",CAST(rovn_14d AS STRING),"str_one_30d",CAST(str_one_30d AS STRING),"ros_one_30d",CAST(ros_one_30d AS STRING),"str_30d",CAST(str_30d AS STRING),"ros_30d",CAST(ros_30d AS STRING),"str_plus_30d",CAST(str_plus_30d AS STRING),"ros_minus_30d",CAST(ros_minus_30d AS STRING),"rovn_30d",CAST(rovn_30d AS STRING),"str_one_60d",CAST(str_one_60d AS STRING),"ros_one_60d",CAST(ros_one_60d AS STRING),"str_60d",CAST(str_60d AS STRING),"ros_60d",CAST(ros_60d AS STRING),"str_plus_60d",CAST(str_plus_60d AS STRING),"ros_minus_60d",CAST(ros_minus_60d AS STRING),"rovn_60d",CAST(rovn_60d AS STRING))
            )
    FROM    t_index
)SELECT  *
FROM    t_result
;