-- Task: 01_scenetype和vid的cf特征_20250212  ID: 1023781540  Type: ODPS_SQL
CREATE TABLE IF NOT EXISTS loghubods.scene_type_vid_cf_feature_20250212
(
    sence_type  STRING COMMENT '场景值'
    ,vid_a      STRING COMMENT '视频ID'
    ,vid_b      STRING COMMENT '视频ID'
    ,feature    STRING COMMENT '特征JSON'
)
COMMENT '01_场景和视频与视频之间的cf特征'
PARTITIONED BY 
(
    dt          STRING COMMENT '天'
    ,hh         STRING COMMENT '小时'
)
LIFECYCLE 30
;

INSERT OVERWRITE TABLE loghubods.scene_type_vid_cf_feature_20250212 PARTITION (dt = '${dt}',hh = '${hh}')
WITH t_cf_ros AS 
(
    SELECT  sence_type
            ,vid AS vid_a
            ,item AS vid_b
            ,scores[i1] AS ros_cf_score
            ,ranks[i1] AS ros_cf_rank
            ,"0" AS rov_cf_score
            ,"0" AS rov_cf_rank
    FROM    (
                SELECT  sence_type
                        ,vid
                        ,SPLIT(vids,",") AS vids
                        ,SPLIT(scores,",") AS scores
                        ,SPLIT(ranks,",") AS ranks
                FROM    loghubods.alg_recsys_recall_cf_mid_ros_scenetype
                WHERE   CONCAT(dt,hh) = '${dt}${hh}'
            ) 
    LATERAL VIEW POSEXPLODE(vids) t1 AS
            i1
            ,item
)
,t_cf_rov AS 
(
    SELECT  sence_type
            ,vid AS vid_a
            ,item AS vid_b
            ,"0" AS ros_cf_score
            ,"0" AS ros_cf_rank
            ,scores[i1] AS rov_cf_score
            ,ranks[i1] AS rov_cf_rank
    FROM    (
                SELECT  sence_type
                        ,vid
                        ,SPLIT(vids,",") AS vids
                        ,SPLIT(scores,",") AS scores
                        ,SPLIT(ranks,",") AS ranks
                FROM    loghubods.alg_recsys_recall_cf_mid_rov_scenetype
                WHERE   CONCAT(dt,hh) = '${dt}${hh}'
            ) 
    LATERAL VIEW POSEXPLODE(vids) t1 AS
            i1
            ,item
)SELECT  sence_type
        ,vid_a
        ,vid_b
        ,JSON_FORMAT(JSON_OBJECT(
            "ros_cf_score",CAST(ros_cf_score AS STRING),
            "ros_cf_rank",CAST(ros_cf_rank AS STRING),
            "rov_cf_score",CAST(rov_cf_score AS STRING),
            "rov_cf_rank",CAST(rov_cf_rank AS STRING)
        ))
FROM    (
            SELECT  sence_type
                    ,vid_a
                    ,vid_b
                    ,AVG(CAST(ros_cf_score AS DOUBLE)) AS ros_cf_score
                    ,AVG(CAST(ros_cf_rank AS BIGINT)) AS ros_cf_rank
                    ,AVG(CAST(rov_cf_score AS DOUBLE)) AS rov_cf_score
                    ,AVG(CAST(rov_cf_rank AS BIGINT)) AS rov_cf_rank
            FROM    (
                        SELECT  *
                        FROM    t_cf_ros
                        UNION ALL
                        SELECT  *
                        FROM    t_cf_rov
                    ) 
            GROUP BY sence_type
                     ,vid_a
                     ,vid_b
        )