-- Task: 04_用户回流点击对应的tag  ID: 1017584776  Type: ODPS_SQL
--@exclude_input=loghubods.user_share_log_flow
--odps sql 
--********************************************************************--
--author:于卓异
--create time:2024-05-13 14:35:10
--********************************************************************--
CREATE TABLE IF NOT EXISTS loghubods.alg_mid_feature_return_tags
(
    mid     STRING COMMENT 'mid'
    ,feature JSON  COMMENT '数值'
    ,tag_ts STRING COMMENT ''
)
COMMENT 'TABLE COMMENT'
PARTITIONED BY 
(
    dt       STRING COMMENT '日期'
    ,hh STRING COMMENT '小时'
)
LIFECYCLE 30
;
SET odps.sql.python.version = cp37
;

INSERT OVERWRITE TABLE loghubods.alg_mid_feature_return_tags PARTITION (dt = '${dt}',hh = '${hh}')



WITH t_return AS 
(
    SELECT  __topic__
            ,eventinfos
            ,apptype
            ,clickobjectid AS vid
            ,shareobjectid
            ,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 * ${hours_early}),'YYYYMMDDHH') AND TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 1),'YYYYMMDDHH')
    AND     __topic__ IN ('click')
    AND     apptype NOT IN ('12')
    AND     apptype IS NOT NULL
    AND     clickobjectid IS NOT NULL
    AND     machinecode IS NOT NULL
    AND     machinecode != ""
    AND     clienttimestamp IS NOT NULL 
    and  rootPageSource not  REGEXP '-pages/home'-- todo 加限制，保证是头部回流----非自分享回流
)
,t_return_ AS 
(
    SELECT  apptype
            ,mid
            ,vid
            ,pagesource
            ,subsessionid
            ,MIN(clienttimestamp) AS clienttimestamp
    FROM    t_return
    GROUP BY apptype
             ,mid
             ,vid
             ,pagesource
             ,subsessionid
)
,t_title AS 
(
    SELECT  t1.vid AS vid
            ,t2.title AS title
            ,FENCI_STR2STR(t2.title,3) AS tags
    FROM    (
                SELECT  DISTINCT vid
                        ,"1"
                FROM    t_return_
            ) t1
    JOIN    (
                SELECT  DISTINCT id
                        ,title
                FROM    videoods.wx_video_per1h
                WHERE   title IS NOT NULL
                AND     title != ""
            ) t2
    ON      t1.vid = t2.id
)
,t_title_tag AS 
(
    SELECT  vid
            ,title
            ,tag
    FROM    (
                SELECT  vid
                        ,title
                        ,tags
                FROM    t_title
                WHERE   tags IS NOT NULL
                AND     tags != ""
            ) 
    LATERAL VIEW EXPLODE(SPLIT(tags,',')) t AS tag
)
,t_join AS 
(
    SELECT  mid
            ,tag
            ,CAST((
                        UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - clienttimestamp / 1000
            ) AS BIGINT) AS ts_diff
    FROM    t_return_ t1
    JOIN    t_title_tag t2
    ON      t1.vid = t2.vid
)
,t_group AS 
(
    SELECT  mid
            ,CONCAT_WS(',',COLLECT_LIST(CONCAT(tag,":",ts_diff))) AS tag_ts
    FROM    t_join
    GROUP BY mid
)
,t_mid_tags AS 
(
    SELECT  mid
            ,JSON_OBJECT('tags_2h',tags_2h,"tags_1d",tags_1d,"tags_3d",tags_3d,"tags_7d",tags_7d,"tags_14d",tags_14d) AS feature
            ,tag_ts
    FROM    (
                SELECT  mid
                        ,tag_ts
                        ,get_top_tags(tag_ts,3600 * 2,10) AS tags_2h
                        ,get_top_tags(tag_ts,3600 * 24,10) AS tags_1d
                        ,get_top_tags(tag_ts,3600 * 24 * 3,10) AS tags_3d
                        ,get_top_tags(tag_ts,3600 * 24 * 7,10) AS tags_7d
                        ,get_top_tags(tag_ts,3600 * 24 * 14,10) AS tags_14d -- 336
                FROM    t_group
            ) 
)
SELECT  *
FROM    t_mid_tags