-- Task: ROVn的CF策略_v2  ID: 1018464715  Type: ODPS_SQL
--@exclude_input=loghubods.video_action_log_flow_new
--@exclude_input=loghubods.vid_rovn_cf_base
--@exclude_input=loghubods.user_share_log_flow
--odps sql 
--********************************************************************--
--author:于卓异
--create time:2024-05-17 13:20:28
--********************************************************************--
CREATE TABLE IF NOT EXISTS loghubods.alg_recsys_feature_cf_i2i_v2
(
    vid_A     STRING COMMENT ''
    ,vid_B    STRING COMMENT ''
    ,exp      STRING COMMENT ''
    ,return_n STRING COMMENT ''
    ,rovn     STRING COMMENT ''
    ,feature  STRING COMMENT ''
)
PARTITIONED BY 
(
    dt        STRING COMMENT '日期:20240105'
    ,hh       STRING COMMENT '小时:04'
)
STORED AS ALIORC
TBLPROPERTIES ('comment' = 'TABLE COMMENT')
LIFECYCLE 365
;

--ALTER TABLE loghubods.alg_recsys_feature_cf_i2i_v2 ADD COLUMNS (feature STRING)
INSERT OVERWRITE TABLE loghubods.alg_recsys_feature_cf_i2i_v2 PARTITION (dt = '${dt}',hh = '${hh}')
WITH t_recommend AS 
(
    SELECT  a.mid AS mid
            ,a.vid AS vid
            ,a.subsessionid
            ,a.pagesource
            ,MIN(a.clienttimestamp) AS clienttimestamp
            ,COUNT(DISTINCT b.mid) AS return_cnt
    FROM    (
                SELECT  __topic__
                        ,eventinfos
                        ,apptype
                        ,shareobjectid AS vid
                        ,machinecode AS mid
                        ,clienttimestamp
                        ,pagesource
                        ,parentpagesource
                        ,parentrootpagesource
                        ,shareid
                        ,rootshareid
                        ,subsessionid
                FROM    loghubods.user_share_log_flow
                WHERE   CONCAT(year,month,day,hour) BETWEEN TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 24 * ${day}),'YYYYMMDDHH') AND TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 1),'YYYYMMDDHH')
                AND     __topic__ IN ('share')
                AND     pagesource REGEXP '-pages/user-videos-share-recommend$'
            ) a
    LEFT JOIN   (
                    SELECT  __topic__
                            ,eventinfos
                            ,apptype
                            ,clickobjectid AS vid
                            ,machinecode AS mid
                            ,clienttimestamp
                            ,pagesource
                            ,parentpagesource
                            ,parentrootpagesource
                            ,shareid
                            ,rootshareid
                            ,subsessionid
                    FROM    loghubods.user_share_log_flow
                    WHERE   CONCAT(year,month,day,hour) BETWEEN TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 24 * ${day}),'YYYYMMDDHH') AND TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 1),'YYYYMMDDHH')
                    AND     __topic__ IN ('click')
                ) b
    ON      a.shareid = b.rootshareid
    GROUP BY a.mid
             ,a.vid
             ,a.subsessionid
             ,a.pagesource
)
,t_recommend_vid AS 
(
    SELECT  a.mid
            ,a.vid AS vid_A
            ,a.subsessionid
            ,c.vid AS vid_B
            ,c.return_cnt
    FROM    (
                SELECT  apptype
                        ,pagesource
                        ,uid
                        ,mid
                        ,vid
                        ,logtimestamp
                        ,businesstype
                        ,sessionid
                        ,subsessionid
                        ,abinfodata
                        ,recommendlogvo
                        ,extparams
                        ,abcode
                        ,recommendPageType
                        ,flowpool
                        ,level
                FROM    loghubods.alg_vid_feature_all_exp_base_v2
                WHERE   dt = '${dt}'
                AND     hh = '${hh}'
                AND     pagesource REGEXP '-pages/user-videos-share$'
                AND     GET_JSON_OBJECT(extparams,'$.recommendPageType') REGEXP '-pages/user-videos-share-recommend-detail'
                AND     subsessionid IN (
                            SELECT  DISTINCT subsessionid
                            FROM    t_recommend
                            WHERE   return_cnt > 0
                        ) 
            ) a
    LEFT JOIN   (
                    SELECT  mid
                            ,vid
                            ,a.subsessionid
                            ,a.pagesource
                            ,clienttimestamp
                            ,return_cnt
                    FROM    t_recommend a
                ) c
    ON      a.subsessionid = c.subsessionid
    AND     a.mid = c.mid
    AND     a.logtimestamp <= c.clienttimestamp
)
SELECT  *
        ,JSON_FORMAT(JSON_OBJECT("exp",view,"return_n",return,"rovn",ROV)) AS feature
FROM    (
            SELECT  vid_A
                    ,vid_B
                    ,cast(COUNT(subsessionid) as string) AS view
                    ,cast(SUM(return_cnt) as string) AS return
                    ,cast(round(SUM(return_cnt) / COUNT(subsessionid),6) as string) AS ROV
            FROM    t_recommend_vid
            WHERE   vid_B IS NOT NULL
            GROUP BY vid_A
                     ,vid_B
        ) 
;

CREATE TABLE IF NOT EXISTS loghubods.alg_recsys_recall_cf_rovn_v2
(
    vid          STRING COMMENT ''
    ,videoid_arr STRING COMMENT ''
    ,score_arr   STRING COMMENT ''
    ,cnt         STRING COMMENT ''
)
PARTITIONED BY 
(
    dt           STRING COMMENT '日期:20240105'
    ,hh          STRING COMMENT '小时:04'
)
STORED AS ALIORC
TBLPROPERTIES ('comment' = 'TABLE COMMENT')
LIFECYCLE 365
;

INSERT OVERWRITE TABLE loghubods.alg_recsys_recall_cf_rovn_v2 PARTITION (dt = '${dt}',hh = '${hh}')
WITH t_group AS 
(
    SELECT  vid_A AS key
            ,vid_B AS vid
            ,rovn AS score
            ,ROW_NUMBER() OVER (PARTITION BY vid_A ORDER BY rovn DESC ) AS rank_num
    FROM    loghubods.alg_recsys_feature_cf_i2i_v2
    WHERE   dt = '${dt}'
    AND     hh = '${hh}'
    AND     exp > ${value}
)
,t_recall AS 
(
    SELECT  key
            ,CONCAT_WS(',',COLLECT_LIST(vid)) AS videoid_arr
            ,CONCAT_WS(',',COLLECT_LIST(CAST(score AS STRING))) AS score_arr
            ,COUNT(vid) AS cnt
    FROM    (
                SELECT  key
                        ,vid
                        ,score
                        ,rank_num
                FROM    t_group
                WHERE   rank_num <= 100
                ORDER BY rank_num ASC
            ) 
    GROUP BY key
)SELECT  *
FROM    t_recall