-- Task: 00_推荐特征sample表_20250212  ID: 1023840988  Type: ODPS_SQL
--@exclude_input=loghubods.statistics_log_hour
CREATE TABLE IF NOT EXISTS loghubods.dwd_recsys_alg_sample_all_20250212
(
    apptype               STRING
    ,uid                  STRING
    ,mid                  STRING
    ,vid                  STRING
    ,sessionid            STRING
    ,subsessionid         STRING
    ,pagesource           STRING
    ,page                 STRING
    ,recommendlogvo       STRING
    ,abcode               STRING
    ,recommendpagetype    STRING
    ,recomtraceid         STRING
    ,headvideoid          STRING
    ,rootsourceid         STRING
    ,hotsencetype         STRING
    ,flowpool             STRING
    ,level                STRING
    ,clientip             STRING
    ,brand                STRING
    ,`model`              STRING
    ,system               STRING
    ,wechatversion        STRING
    ,sdkversion           STRING
    ,province             STRING
    ,city                 STRING
    ,ts                   STRING
    ,is_share             STRING
    ,share_cnt            STRING
    ,is_return_1          STRING
    ,return_1_pv          STRING
    ,return_1_uv          STRING
    ,return_1_mids        STRING
    ,is_return_n          STRING
    ,return_n_pv          STRING
    ,return_n_uv          STRING
    ,return_n_mids        STRING
    ,is_return_noself     STRING
    ,return_1_uv_noself   STRING
    ,return_1_mids_noself STRING
    ,is_return_n_noself   STRING
    ,return_n_uv_noself   STRING
    ,return_n_mids_noself STRING
    ,new_exposure_cnt     STRING
    ,extend               STRING
    ,score                STRING
    ,extend_alg           STRING
    ,allfeaturemap        STRING
    ,metafeaturemap       STRING
    ,v1_feature           STRING COMMENT '待排序视频基础信息'
    ,v2_feature           STRING COMMENT '头部视频基础信息'
    ,b1_feature           STRING
    ,b2_feature           STRING
    ,b3_feature           STRING
    ,b4_feature           STRING
    ,b5_feature           STRING
    ,b6_feature           STRING
    ,b7_feature           STRING
    ,b8_feature           STRING
    ,b9_feature           STRING
    ,b10_feature          STRING
    ,b11_feature          STRING
    ,b12_feature          STRING
    ,b13_feature          STRING
    ,c1_feature           STRING
    ,c2_feature           STRING
    ,c3_feature           STRING
    ,c4_feature           STRING
    ,c5_feature           STRING
    ,c6_feature           STRING
    ,c7_feature           STRING
    ,c8_feature           STRING
    ,c9_feature           STRING
    ,d1_feature           STRING
    ,d2_feature           STRING
    ,d3_feature           STRING
)
COMMENT '推荐全量样本表[20250212版]'
PARTITIONED BY 
(
    dt                    STRING COMMENT '天'
    ,hh                   STRING COMMENT '小时'
)
LIFECYCLE 365
;

INSERT OVERWRITE TABLE loghubods.dwd_recsys_alg_sample_all_20250212 PARTITION (dt,hh)
WITH t_exp AS 
(
    SELECT  apptype
            ,uid
            ,mid
            ,vid
            ,CASE   WHEN uid IS NOT NULL
                        AND LENGTH(uid) > 0
                        AND uid != 'null' THEN uid
                    ELSE mid
            END AS merge_mid
            ,sessionid
            ,subsessionid
            ,pagesource
            ,page
            ,recommendlogvo
            ,abcode
            ,recommendpagetype
            ,recomtraceid
            ,headvideoid
            ,rootsourceid
            ,hotsencetype
            ,flowpool
            ,level
            ,clientip
            ,machineinfo_brand AS brand
            ,machineinfo_model AS model
            ,machineinfo_system AS system
            ,machineinfo_wechatversion AS wechatversion
            ,machineinfo_sdkversion AS sdkversion
            ,province
            ,city
            ,ts
            ,is_share
            ,share_cnt
            ,is_return_1
            ,return_1_pv
            ,return_1_uv
            ,return_1_mids
            ,is_return_n
            ,return_n_pv
            ,return_n_uv
            ,return_n_mids
            ,is_return_noself
            ,return_1_uv_noself
            ,return_1_mids_noself
            ,is_return_n_noself
            ,return_n_uv_noself
            ,return_n_mids_noself
            ,new_exposure_cnt
            ,extend
            ,dt
            ,hh
            ,CASE   WHEN hotsencetype IN ("1007","1008") THEN hotsencetype
                    ELSE "other"
            END hotsencetype_2
    FROM    loghubods.dwd_recsys_alg_exposure_base_20250108
    WHERE   CONCAT(dt,hh) = TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 25),'YYYYMMDDHH')
    AND     apptype NOT IN ("12")
)
,t_basic_info AS 
(
    SELECT  *
    FROM    (
                SELECT  vid
                        ,JSON_FORMAT(feature) AS feature
                        ,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
                        ,COALESCE(GET_JSON_OBJECT(feature,"$.festive_label2"),"unknown") AS festive
                        ,COALESCE(GET_JSON_OBJECT(feature,"$.title_time_w_h_unionid"),"unknown") AS title_time_w_h_unionid
                        ,ROW_NUMBER() OVER (PARTITION BY vid ) AS rn
                FROM    loghubods.alg_vid_feature_basic_info
                WHERE   CONCAT(dt,hh) = TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 25),'YYYYMMDDHH')
            ) 
    WHERE   rn = 1
)
,t_result AS 
(
    SELECT  ta.apptype AS apptype
            ,ta.uid AS uid
            ,ta.mid AS mid
            ,ta.vid AS vid
            ,ta.sessionid AS sessionid
            ,ta.subsessionid AS subsessionid
            ,ta.pagesource AS pagesource
            ,ta.page AS page
            ,ta.recommendlogvo AS recommendlogvo
            ,ta.abcode AS abcode
            ,ta.recommendpagetype AS recommendpagetype
            ,ta.recomtraceid AS recomtraceid
            ,ta.headvideoid AS headvideoid
            ,ta.rootsourceid AS rootsourceid
            ,ta.hotsencetype AS hotsencetype
            ,ta.flowpool AS flowpool
            ,ta.level AS level
            ,ta.clientip AS clientip
            ,ta.brand AS brand
            ,ta.model AS model
            ,ta.system AS system
            ,ta.wechatversion AS wechatversion
            ,ta.sdkversion AS sdkversion
            ,ta.province AS province
            ,ta.city AS city
            ,ta.ts AS ts
            ,ta.is_share AS is_share
            ,ta.share_cnt AS share_cnt
            ,ta.is_return_1 AS is_return_1
            ,ta.return_1_pv AS return_1_pv
            ,ta.return_1_uv AS return_1_uv
            ,ta.return_1_mids AS return_1_mids
            ,ta.is_return_n AS is_return_n
            ,ta.return_n_pv AS return_n_pv
            ,ta.return_n_uv AS return_n_uv
            ,ta.return_n_mids AS return_n_mids
            ,ta.is_return_noself AS is_return_noself
            ,ta.return_1_uv_noself AS return_1_uv_noself
            ,ta.return_1_mids_noself AS return_1_mids_noself
            ,ta.is_return_n_noself AS is_return_n_noself
            ,ta.return_n_uv_noself AS return_n_uv_noself
            ,ta.return_n_mids_noself AS return_n_mids_noself
            ,ta.new_exposure_cnt AS new_exposure_cnt
            ,ta.extend AS extend
            ,e1.score AS score
            ,e1.extend_alg AS extend_alg
            ,e1.allfeaturemap AS allfeaturemap
            ,e1.metafeaturemap AS metafeaturemap
            ,v1.feature AS v1_feature
            ,v2.feature AS v2_feature
            ,b1.feature AS b1_feature
            ,b2.feature AS b2_feature
            ,b3.feature AS b3_feature
            ,b4.feature AS b4_feature
            ,b5.feature AS b5_feature
            ,b6.feature AS b6_feature
            ,b7.feature AS b7_feature
            ,b8.feature AS b8_feature
            ,b9.feature AS b9_feature
            ,b10.feature AS b10_feature
            ,b11.feature AS b11_feature
            ,b12.feature AS b12_feature
            ,b13.feature AS b13_feature
            ,c1.feature AS c1_feature
            ,"{}" AS c2_feature
            ,"{}" AS c3_feature
            ,"{}" AS c4_feature
            ,c5.feature AS c5_feature
            ,c6.feature AS c6_feature
            ,c7.feature AS c7_feature
            ,c8.feature AS c8_feature
            ,c9.feature AS c9_feature
            ,d1.feature AS d1_feature
            ,d2.feature AS d2_feature
            ,d3.feature2 AS d3_feature
            ,ta.dt AS dt
            ,ta.hh AS hh
    FROM    t_exp ta
    LEFT JOIN t_basic_info v1
    ON      ta.vid = v1.vid
    LEFT JOIN t_basic_info v2
    ON      ta.headvideoid = v2.vid
    LEFT JOIN   (
                    SELECT  *
                    FROM    loghubods.alg_vid_global_feature_20250212
                    WHERE   CONCAT(dt,hh) = TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 25),'YYYYMMDDHH')
                ) b1
    ON      ta.vid = b1.vid
    LEFT JOIN   (
                    SELECT  *
                    FROM    loghubods.alg_vid_recommend_exp_feature_20250212
                    WHERE   CONCAT(dt,hh) = TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 25),'YYYYMMDDHH')
                ) b2
    ON      ta.vid = b2.vid
    LEFT JOIN   (
                    SELECT  *
                    FROM    loghubods.alg_vid_recommend_flowpool_exp_feature_20250212
                    WHERE   CONCAT(dt,hh) = TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 25),'YYYYMMDDHH')
                ) b3
    ON      ta.vid = b3.vid
    LEFT JOIN   (
                    SELECT  *
                    FROM    loghubods.alg_vid_apptype_recommend_exp_feature_20250212
                    WHERE   CONCAT(dt,hh) = TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 25),'YYYYMMDDHH')
                ) b4
    ON      ta.vid = b4.vid
    AND     ta.apptype = b4.apptype
    LEFT JOIN   (
                    SELECT  *
                    FROM    loghubods.alg_vid_province_recommend_exp_feature_20250212
                    WHERE   CONCAT(dt,hh) = TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 25),'YYYYMMDDHH')
                ) b5
    ON      ta.vid = b5.vid
    AND     ta.province = b5.province
    LEFT JOIN   (
                    SELECT  *
                    FROM    loghubods.alg_vid_brand_recommend_exp_feature_20250212
                    WHERE   CONCAT(dt,hh) = TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 25),'YYYYMMDDHH')
                ) b6
    ON      ta.vid = b6.vid
    AND     ta.brand = b6.brand
    LEFT JOIN   (
                    SELECT  *
                    FROM    loghubods.alg_vid_hotsencetype_recommend_exp_feature_20250212
                    WHERE   CONCAT(dt,hh) = TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 25),'YYYYMMDDHH')
                ) b7
    ON      ta.vid = b7.vid
    AND     ta.hotsencetype_2 = b7.hotsencetype
    LEFT JOIN   (
                    SELECT  *
                    FROM    loghubods.alg_merge_cate1_recommend_exp_feature_20250212
                    WHERE   CONCAT(dt,hh) = TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 25),'YYYYMMDDHH')
                ) b8
    ON      v1.merge_cate1 = b8.merge_cate1
    LEFT JOIN   (
                    SELECT  *
                    FROM    loghubods.alg_merge_cate2_recommend_exp_feature_20250212
                    WHERE   CONCAT(dt,hh) = TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 25),'YYYYMMDDHH')
                ) b9
    ON      v1.merge_cate2 = b9.merge_cate2
    LEFT JOIN   (
                    SELECT  *
                    FROM    loghubods.alg_channel_recommend_exp_feature_20250212
                    WHERE   CONCAT(dt,hh) = TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 25),'YYYYMMDDHH')
                ) b10
    ON      v1.channel = b10.channel
    LEFT JOIN   (
                    SELECT  *
                    FROM    loghubods.alg_festive_recommend_exp_feature_20250212
                    WHERE   CONCAT(dt,hh) = TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 25),'YYYYMMDDHH')
                ) b11
    ON      v1.festive = b11.festive
    LEFT JOIN   (
                    SELECT  *
                    FROM    loghubods.alg_vid_long_period_recommend_exp_feature_20250212
                    WHERE   dt = TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 25),'YYYYMMDD')
                ) b12
    ON      ta.vid = b12.vid
    LEFT JOIN   (
                    SELECT  *
                    FROM    loghubods.alg_video_unionid_recommend_exp_feature_20250212
                    WHERE   CONCAT(dt,hh) = TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 25),'YYYYMMDDHH')
                ) b13
    ON      v1.title_time_w_h_unionid = b13.video_unionid
    LEFT JOIN   (
                    SELECT  *
                    FROM    loghubods.mid_global_feature_20250212
                    WHERE   CONCAT(dt,hh) = TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 25),'YYYYMMDDHH')
                ) c1
    ON      ta.mid = c1.mid
    -- LEFT JOIN   (
    --                 SELECT  *
    --                 FROM    loghubods.mid_merge_cate1_feature_20250212
    --                 WHERE   CONCAT(dt,hh) = TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 25),'YYYYMMDDHH')
    --             ) c2
    -- ON      ta.mid = c2.mid
    -- AND     v1.merge_cate1 = c2.merge_cate1
    -- LEFT JOIN   (
    --                 SELECT  *
    --                 FROM    loghubods.mid_merge_cate2_feature_20250212
    --                 WHERE   CONCAT(dt,hh) = TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 25),'YYYYMMDDHH')
    --             ) c3
    -- ON      ta.mid = c3.mid
    -- AND     v1.merge_cate2 = c3.merge_cate2
    -- LEFT JOIN   (
    --                 SELECT  *
    --                 FROM    loghubods.mid_u2u_friend_index_feature_20250212
    --                 WHERE   CONCAT(dt,hh) = TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 25),'YYYYMMDDHH')
    --             ) c4
    -- ON      ta.mid = c4.mid
    LEFT JOIN   (
                    SELECT  mid
                            ,JSON_FORMAT(feature) AS feature
                    FROM    loghubods.alg_mid_feature_return_tags
                    WHERE   CONCAT(dt,hh) = TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 25),'YYYYMMDDHH')
                ) c5
    ON      ta.mid = c5.mid
    LEFT JOIN   (
                    SELECT  mid
                            ,JSON_FORMAT(feature) AS feature
                    FROM    loghubods.alg_mid_feature_share_tags
                    WHERE   CONCAT(dt,hh) = TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 25),'YYYYMMDDHH')
                ) c6
    ON      ta.mid = c6.mid
    LEFT JOIN   (
                    SELECT  mid
                            ,JSON_FORMAT(feature) AS feature
                    FROM    loghubods.alg_mid_feature_sharecf
                    WHERE   CONCAT(dt,hh) = TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 25),'YYYYMMDDHH')
                ) c7
    ON      ta.mid = c7.mid
    LEFT JOIN   (
                    SELECT  mid
                            ,JSON_FORMAT(feature) AS feature
                    FROM    loghubods.alg_mid_feature_returncf
                    WHERE   CONCAT(dt,hh) = TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 25),'YYYYMMDDHH')
                ) c8
    ON      ta.mid = c8.mid
    LEFT JOIN   (
                    SELECT  *
                    FROM    loghubods.alg_recsys_feature_user_share_return_stat
                    WHERE   CONCAT(dt,hh) = TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 25),'YYYYMMDDHH')
                ) c9
    ON      ta.merge_mid = c9.mid
    LEFT JOIN   (
                    SELECT  *
                    FROM    loghubods.scene_type_vid_cf_feature_20250212
                    WHERE   CONCAT(dt,hh) = TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 25),'YYYYMMDDHH')
                ) d1
    ON      ta.headvideoid = d1.vid_a
    AND     ta.vid = d1.vid_b
    AND     ta.hotsencetype = d1.sence_type
    LEFT JOIN   (
                    SELECT  *
                    FROM    loghubods.vid_click_cf_feature_20250212
                    WHERE   CONCAT(dt,hh) = TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 25),'YYYYMMDDHH')
                ) d2
    ON      ta.headvideoid = d2.vid_a
    AND     ta.vid = d2.vid_b
    LEFT JOIN   (
                    SELECT  *
                            ,JSON_FORMAT(JSON_OBJECT("exp",exp,"return_n",return_n,"rovn",rovn)) AS feature2
                    FROM    loghubods.alg_recsys_feature_cf_i2i_v2
                    WHERE   CONCAT(dt,hh) = TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 25),'YYYYMMDDHH')
                ) d3
    ON      ta.headvideoid = d3.vid_a
    AND     ta.vid = d3.vid_b
    LEFT JOIN   (
                    SELECT  videoid
                            ,recommendtraceid
                            ,allfeaturemap
                            ,metafeaturemap
                            ,score
                            ,JSON_FORMAT(
                                        JSON_OBJECT("pushFrom",pushFrom,"relevantVideoId",relevantvideoid,"rovScore",rovscore,"scoreRos",scoreros,"scoreStr",scorestr,"scoresMap",scoresMap,"sortScore",sortScore,"pushfromrank",pushfromrank)
                            ) AS extend_alg
                    FROM    (
                                SELECT  pushfrom
                                        ,rovscore
                                        ,score
                                        ,scoreros
                                        ,scorestr
                                        ,JSON_PARSE(scoresmap) AS scoresmap
                                        ,sortscore
                                        ,videoid
                                        ,relevantvideoid
                                        ,metafeaturemap
                                        ,allfeaturemap
                                        ,recommendtraceid
                                        ,JSON_PARSE(pushfromrank) AS pushfromrank
                                        ,ROW_NUMBER() OVER (PARTITION BY videoid,recommendtraceid ) AS rn
                                FROM    loghubods.statistics_log_hour
                                WHERE   dt = TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 25),'YYYYMMDDHH')
                                AND     (
                                            JSON_VALID(scoresmap) = TRUE
                                            AND      JSON_VALID(pushfromrank) = TRUE
                                )
                            ) 
                    WHERE   rn = 1
                ) e1
    ON      ta.vid = e1.videoid
    AND     ta.recomtraceid = e1.recommendtraceid
)SELECT  *
FROM    t_result
;