data_analysis/table_gen/loghubods.dwd_recsys_alg_exposure_base_20250108.sql

--@exclude_input=loghubods.video_action_log_flow_new
--@exclude_input=loghubods.user_share_log_flow
--*********************
-- alg_recsys_rank_labelmatch_20250108
--*********************
--drop table loghubods.dwd_recsys_alg_exposure_base_20250108;
CREATE TABLE IF NOT EXISTS loghubods.dwd_recsys_alg_exposure_base_20250108
(
    apptype                    STRING
    ,uid                       STRING
    ,mid                       STRING
    ,vid                       STRING
    ,sessionid                 STRING
    ,subsessionid              STRING
    ,pagesource                STRING
    ,page                      STRING
    ,recommendlogvo            STRING COMMENT '推荐算法的返回结果日志存在这个字段中'
    ,abcode                    STRING COMMENT '推荐算法的ab分组:ab0'
    ,recommendpagetype         STRING COMMENT '用于区分pagesource相同时某些场景的。三种回流头部；两种下滑-沉浸页下滑和feed下滑。 -pages/user-videos-share-recommend-detail 是沉浸页。'
    ,recomtraceid              STRING COMMENT '在后端调取推荐服务之前生成。前端降级会空；后端也可能为空。'
    ,headvideoid               STRING
    ,rootsourceid              STRING COMMENT '区分touliu等流量，咨询产品。'
    ,hotsencetype              STRING
    ,flowpool                  STRING COMMENT '非流量池，是空字符串。没有null值。'
    ,level                     STRING COMMENT '非流量池，是null。'
    ,clientip                  STRING
    ,machineinfo_brand         STRING
    ,machineinfo_model         STRING
    ,machineinfo_system        STRING
    ,machineinfo_wechatversion STRING
    ,machineinfo_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
)
PARTITIONED BY 
(
    dt                         STRING COMMENT '日期:20240105'
    ,hh                        STRING COMMENT '小时:04'
)
STORED AS ALIORC
TBLPROPERTIES ('comment' = '推荐算法-labelmatch表-20250108更新最新版')
LIFECYCLE 3650
;

SET hive.exec.dynamic.partition = true
;

SET hive.exec.dynamic.partition.mode = nonstrict
;

SET odps.stage.mapper.split.size = 1024
;

INSERT OVERWRITE TABLE loghubods.dwd_recsys_alg_exposure_base_20250108 PARTITION (dt,hh)
WITH t_return AS 
(
    SELECT  *
            ,CONCAT(dthh,":",shareid,":",vid,":",dthh_id) AS id
    FROM    (
                SELECT  CONCAT(year,month,day,hour) AS dthh
                        ,apptype
                        ,machinecode AS mid
                        ,clickobjectid AS vid
                        ,sessionid
                        ,subsessionid -- 注意这是回流对应的subsessionid，每次回流点击会重置，可以通过这个字段找到回流的曝光。
                        ,shareid
                        ,rootshareid
                        ,CAST(clienttimestamp / 1000 AS BIGINT) AS ts
                        ,ROW_NUMBER() OVER (PARTITION BY CONCAT(year,month,day,hour),apptype,machinecode,clickobjectid,sessionid,subsessionid,shareid,rootshareid ORDER BY clienttimestamp DESC ) AS rn
                        ,ROW_NUMBER() OVER (PARTITION BY CONCAT(year,month,day,hour),shareid,clickobjectid ORDER BY clienttimestamp ) AS dthh_id
                FROM    loghubods.user_share_log_flow -- 回流行为，理应subsessionid只有一条，但有脏数据，去重。
                WHERE   CONCAT(year,month,day,hour) BETWEEN TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 25),'YYYYMMDDHH') AND TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 1),'YYYYMMDDHH') --WHERE   CONCAT(year,month,day,hour) = TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 25),'YYYYMMDDHH')
                AND     __topic__ = 'click'
                AND     apptype IS NOT NULL
                AND     apptype NOT IN ('12') -- 12的pagesoucre是h5-share和h5-detail 暂时过滤掉 不做处理
                AND     machinecode IS NOT NULL
                AND     clickobjectid IS NOT NULL
                AND     pagesource REGEXP "-pages/user-videos-share$" -- 存在脏数据 vlog-gzh /mine/mine-info$ 结尾的，都过滤掉。
            ) 
    WHERE   rn = 1
)
,t_share_from_sharelog AS 
(
    SELECT  *
    FROM    (
                SELECT  CONCAT(year,month,day,hour) AS dthh
                        ,apptype
                        ,machinecode AS mid
                        ,shareobjectid AS vid
                        ,sessionid
                        ,subsessionid
                        ,pagesource
                        ,shareid
                        ,CAST(clienttimestamp / 1000 AS BIGINT) AS ts
                        ,ROW_NUMBER() OVER (PARTITION BY CONCAT(year,month,day,hour),apptype,machinecode,shareobjectid,sessionid,subsessionid,pagesource,shareid ORDER BY clienttimestamp DESC ) AS rn
                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 * 25),'YYYYMMDDHH') AND TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 1),'YYYYMMDDHH') --WHERE   CONCAT(year,month,day,hour) = TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 25),'YYYYMMDDHH')
                AND     __topic__ = 'share'
                AND     apptype IS NOT NULL
                AND     apptype NOT IN ('12')
                AND     machinecode IS NOT NULL
                AND     shareobjectid IS NOT NULL
            ) 
    WHERE   rn = 1
)
,t_exposure AS 
(
    SELECT  dthh_id
            ,dthh
            ,apptype
            ,uid
            ,mid
            ,vid
            ,sessionid
            ,subsessionid
            ,rootsessionid_new
            ,pagesource
            ,recommendlogvo
            ,abcode
            ,recommendpagetype
            ,recomtraceid
            ,headvideoid
            ,rootsourceid
            ,hotsencetype
            ,animationscenetype
            ,JSON_PARSE(IF(JSON_VALID(extparams),extparams,"{}")) AS extParams
            ,flowpool
            ,level
            ,clientip
            ,machineinfo_brand
            ,machineinfo_model
            ,machineinfo_system
            ,machineinfo_wechatversion
            ,machineinfo_sdkversion
            ,province
            ,city
            ,versioncode
            ,ts
            ,rn
            ,id
            ,dt
            ,hh
    FROM    loghubods.dwd_recsys_alg_exposure_base_view_20250402
    WHERE   CONCAT(dt,hh) BETWEEN TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 25),'YYYYMMDDHH') AND TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 1),'YYYYMMDDHH')
)
,t_exposure_recommend AS 
(
    SELECT  *
    FROM    t_exposure
    WHERE   pagesource REGEXP 'category$|recommend$|-pages/user-videos-detail$'
)
,t_return_exposure_1 AS -- 曝光关联回流，用于计算viewh24                                
(
    SELECT  *
    FROM    (
                SELECT  t1.id AS exposure_id
                        ,t1.mid AS mid
                        ,t1.vid AS vid
                        ,t1.subsessionid AS subsessionid
                        ,t1.sessionid AS sessionid
                        ,t1.headvideoid AS headvideoid
                        ,t1.dthh
                        ,t2.id AS return_id
                        ,ROW_NUMBER() OVER (PARTITION BY t1.id ORDER BY t2.ts DESC ) AS rn
                FROM    t_exposure_recommend t1
                LEFT JOIN t_return t2
                ON      t1.mid = t2.mid
                AND     t1.headvideoid = t2.vid
                AND     t1.subsessionid = t2.subsessionid
            ) 
    WHERE   rn = 1
)
,t_return_exposure_2 AS -- 曝光关联回流，用于计算viewh24                                
(
    SELECT  *
    FROM    (
                SELECT  t1.exposure_id AS exposure_id
                        ,t1.mid AS mid
                        ,t1.vid AS vid
                        ,t1.subsessionid AS subsessionid
                        ,t1.sessionid AS sessionid
                        ,t1.headvideoid AS headvideoid
                        ,t1.dthh
                        ,t2.id AS return_id
                        ,ROW_NUMBER() OVER (PARTITION BY t1.exposure_id ORDER BY t2.ts DESC ) AS rn
                FROM    (
                            SELECT  *
                            FROM    t_return_exposure_1
                            WHERE   return_id IS NULL
                        ) t1
                LEFT JOIN t_return t2
                ON      t1.mid = t2.mid
                AND     t1.headvideoid = t2.vid
                AND     t1.sessionid = t2.sessionid
            ) 
    WHERE   rn = 1
)
,t_return_exposure_3 AS -- 曝光关联回流，用于计算viewh24                                
(
    SELECT  *
    FROM    (
                SELECT  t1.exposure_id AS exposure_id
                        ,t1.mid AS mid
                        ,t1.vid AS vid
                        ,t1.subsessionid AS subsessionid
                        ,t1.sessionid AS sessionid
                        ,t1.headvideoid AS headvideoid
                        ,t1.dthh
                        ,t2.id AS return_id
                        ,ROW_NUMBER() OVER (PARTITION BY t1.exposure_id ORDER BY t2.ts DESC ) AS rn
                FROM    (
                            SELECT  *
                            FROM    t_return_exposure_2
                            WHERE   return_id IS NULL
                        ) t1
                LEFT JOIN t_return t2
                ON      t1.mid = t2.mid
                AND     t1.subsessionid = t2.subsessionid
            ) 
    WHERE   rn = 1
)
,t_return_exposure_4 AS -- 曝光关联回流，用于计算viewh24                                
(
    SELECT  *
    FROM    (
                SELECT  t1.exposure_id AS exposure_id
                        ,t1.mid AS mid
                        ,t1.vid AS vid
                        ,t1.subsessionid AS subsessionid
                        ,t1.sessionid AS sessionid
                        ,t1.headvideoid AS headvideoid
                        ,t1.dthh
                        ,t2.id AS return_id
                        ,ROW_NUMBER() OVER (PARTITION BY t1.exposure_id ORDER BY t2.ts DESC ) AS rn
                FROM    (
                            SELECT  *
                            FROM    t_return_exposure_3
                            WHERE   return_id IS NULL
                        ) t1
                LEFT JOIN t_return t2
                ON      t1.mid = t2.mid
                AND     t1.sessionid = t2.sessionid
            ) 
    WHERE   rn = 1
)
,t_return_exposure AS 
(
    SELECT  a.*
            ,b.exposure_cnt AS new_exposure_cnt
    FROM    t_return a
    LEFT JOIN   (
                    SELECT  return_id
                            ,COUNT(1) AS exposure_cnt
                    FROM    (
                                SELECT  *
                                FROM    t_return_exposure_1
                                WHERE   return_id IS NOT NULL
                                UNION ALL
                                SELECT  *
                                FROM    t_return_exposure_2
                                WHERE   return_id IS NOT NULL
                                UNION ALL
                                SELECT  *
                                FROM    t_return_exposure_3
                                WHERE   return_id IS NOT NULL
                                UNION ALL
                                SELECT  *
                                FROM    t_return_exposure_4
                                WHERE   return_id IS NOT NULL
                            ) 
                    GROUP BY return_id
                ) b
    ON      a.id = b.return_id
)
,t_normal_share_exposure_1 AS -- 开始处理常规的分享与曝光关联                                                                                          
(
    SELECT  *
    FROM    (
                SELECT  t1.dthh
                        ,t1.apptype
                        ,t1.mid
                        ,t1.vid
                        ,t1.sessionid
                        ,t1.subsessionid
                        ,t1.pagesource
                        ,t1.shareid
                        ,t1.ts
                        ,t2.id AS exposure_id
                        ,t2.ts AS exposure_ts
                        ,ROW_NUMBER() OVER (PARTITION BY t1.dthh,t1.apptype,t1.mid,t1.vid,t1.sessionid,t1.subsessionid,t1.pagesource,t1.shareid ORDER BY t2.ts DESC ) AS rn
                FROM    t_share_from_sharelog t1
                LEFT JOIN t_exposure t2
                ON      t1.apptype = t2.apptype
                AND     t1.mid = t2.mid
                AND     t1.vid = t2.vid
                AND     t1.subsessionid = t2.subsessionid
                AND     t1.pagesource = t2.pagesource
                AND     t1.ts >= t2.ts
                WHERE   t1.pagesource NOT REGEXP "pages/detail-user-videos-share-recommend$"
            ) 
    WHERE   rn = 1
)
,t_normal_share_exposure_2 AS 
(
    SELECT  *
    FROM    (
                SELECT  t1.dthh
                        ,t1.apptype
                        ,t1.mid
                        ,t1.vid
                        ,t1.sessionid
                        ,t1.subsessionid
                        ,t1.pagesource
                        ,t1.shareid
                        ,t1.ts
                        ,t2.id AS exposure_id
                        ,t2.ts AS exposure_ts
                        ,ROW_NUMBER() OVER (PARTITION BY t1.dthh,t1.apptype,t1.mid,t1.vid,t1.sessionid,t1.subsessionid,t1.pagesource,t1.shareid ORDER BY t2.ts DESC ) AS rn
                FROM    (
                            SELECT  *
                            FROM    t_normal_share_exposure_1
                            WHERE   exposure_id IS NULL
                        ) t1
                LEFT JOIN t_exposure t2
                ON      t1.apptype = t2.apptype
                AND     t1.mid = t2.mid
                AND     t1.vid = t2.vid
                AND     t1.sessionid = t2.sessionid
                AND     t1.pagesource = t2.pagesource
                AND     t1.ts >= t2.ts
            ) 
    WHERE   rn = 1
)
,t_normal_share_exposure_3 AS 
(
    SELECT  *
    FROM    (
                SELECT  t1.dthh
                        ,t1.apptype
                        ,t1.mid
                        ,t1.vid
                        ,t1.sessionid
                        ,t1.subsessionid
                        ,t1.pagesource
                        ,t1.shareid
                        ,t1.ts
                        ,t2.id AS exposure_id
                        ,t2.ts AS exposure_ts
                        ,ROW_NUMBER() OVER (PARTITION BY t1.dthh,t1.apptype,t1.mid,t1.vid,t1.sessionid,t1.subsessionid,t1.pagesource,t1.shareid ORDER BY t2.ts DESC ) AS rn
                FROM    (
                            SELECT  *
                            FROM    t_normal_share_exposure_2
                            WHERE   exposure_id IS NULL
                        ) t1
                LEFT JOIN t_exposure t2
                ON      t1.apptype = t2.apptype
                AND     t1.mid = t2.mid
                AND     t1.vid = t2.vid
                AND     t1.subsessionid = t2.subsessionid
                AND     t1.pagesource = t2.pagesource
            ) 
    WHERE   rn = 1
)
,t_normal_share_exposure_4 AS 
(
    SELECT  *
    FROM    (
                SELECT  t1.dthh
                        ,t1.apptype
                        ,t1.mid
                        ,t1.vid
                        ,t1.sessionid
                        ,t1.subsessionid
                        ,t1.pagesource
                        ,t1.shareid
                        ,t1.ts
                        ,t2.id AS exposure_id
                        ,t2.ts AS exposure_ts
                        ,ROW_NUMBER() OVER (PARTITION BY t1.dthh,t1.apptype,t1.mid,t1.vid,t1.sessionid,t1.subsessionid,t1.pagesource,t1.shareid ORDER BY t2.ts DESC ) AS rn
                FROM    (
                            SELECT  *
                            FROM    t_normal_share_exposure_3
                            WHERE   exposure_id IS NULL
                        ) t1
                LEFT JOIN t_exposure t2
                ON      t1.apptype = t2.apptype
                AND     t1.mid = t2.mid
                AND     t1.vid = t2.vid
                AND     t1.sessionid = t2.sessionid
                AND     t1.pagesource = t2.pagesource
            ) 
    WHERE   rn = 1
)
,t_normal_share_exposure_5 AS 
(
    SELECT  *
    FROM    (
                SELECT  t1.dthh
                        ,t1.apptype
                        ,t1.mid
                        ,t1.vid
                        ,t1.sessionid
                        ,t1.subsessionid
                        ,t1.pagesource
                        ,t1.shareid
                        ,t1.ts
                        ,t2.id AS exposure_id
                        ,t2.ts AS exposure_ts
                        ,ROW_NUMBER() OVER (PARTITION BY t1.dthh,t1.apptype,t1.mid,t1.vid,t1.sessionid,t1.subsessionid,t1.pagesource,t1.shareid ORDER BY t2.ts DESC ) AS rn
                FROM    (
                            SELECT  *
                            FROM    t_normal_share_exposure_4
                            WHERE   exposure_id IS NULL
                        ) t1
                LEFT JOIN t_exposure t2
                ON      t1.apptype = t2.apptype
                AND     t1.mid = t2.mid
                AND     t1.vid = t2.vid
                AND     t1.subsessionid = t2.subsessionid
            ) 
    WHERE   rn = 1
)
,t_normal_share_exposure_6 AS 
(
    SELECT  *
    FROM    (
                SELECT  t1.dthh
                        ,t1.apptype
                        ,t1.mid
                        ,t1.vid
                        ,t1.sessionid
                        ,t1.subsessionid
                        ,t1.pagesource
                        ,t1.shareid
                        ,t1.ts
                        ,t2.id AS exposure_id
                        ,t2.ts AS exposure_ts
                        ,ROW_NUMBER() OVER (PARTITION BY t1.dthh,t1.apptype,t1.mid,t1.vid,t1.sessionid,t1.subsessionid,t1.pagesource,t1.shareid ORDER BY t2.ts DESC ) AS rn
                FROM    (
                            SELECT  *
                            FROM    t_normal_share_exposure_5
                            WHERE   exposure_id IS NULL
                        ) t1
                LEFT JOIN t_exposure t2
                ON      t1.apptype = t2.apptype
                AND     t1.mid = t2.mid
                AND     t1.vid = t2.vid
                AND     t1.sessionid = t2.sessionid
            ) 
    WHERE   rn = 1
)
,t_exposure_detail AS 
(
    SELECT  *
    FROM    t_exposure
    WHERE   pagesource REGEXP "-pages/user-videos-detail$|pages/detail-recommend$"
)
,t_no_normal_share_exposure_1 AS -- 开始处理非常规的分享与曝光关联                                                                                         
(
    SELECT  *
    FROM    (
                SELECT  t1.dthh
                        ,t1.apptype
                        ,t1.mid
                        ,t1.vid
                        ,t1.sessionid
                        ,t1.subsessionid
                        ,t1.pagesource
                        ,t1.shareid
                        ,t1.ts
                        ,t2.id AS exposure_id
                        ,t2.ts AS exposure_ts
                        ,ROW_NUMBER() OVER (PARTITION BY t1.dthh,t1.apptype,t1.mid,t1.vid,t1.sessionid,t1.subsessionid,t1.pagesource,t1.shareid ORDER BY t2.ts DESC ) AS rn
                FROM    t_share_from_sharelog t1
                LEFT JOIN t_exposure_detail t2
                ON      t1.apptype = t2.apptype
                AND     t1.mid = t2.mid
                AND     t1.vid = t2.vid
                AND     t1.subsessionid = t2.subsessionid
                AND     t1.ts >= t2.ts
                WHERE   t1.pagesource REGEXP "pages/detail-user-videos-share-recommend$"
            ) 
    WHERE   rn = 1
)
,t_no_normal_share_exposure_2 AS 
(
    SELECT  *
    FROM    (
                SELECT  t1.dthh
                        ,t1.apptype
                        ,t1.mid
                        ,t1.vid
                        ,t1.sessionid
                        ,t1.subsessionid
                        ,t1.pagesource
                        ,t1.shareid
                        ,t1.ts
                        ,t2.id AS exposure_id
                        ,t2.ts AS exposure_ts
                        ,ROW_NUMBER() OVER (PARTITION BY t1.dthh,t1.apptype,t1.mid,t1.vid,t1.sessionid,t1.subsessionid,t1.pagesource,t1.shareid ORDER BY t2.ts DESC ) AS rn
                FROM    (
                            SELECT  *
                            FROM    t_no_normal_share_exposure_1
                            WHERE   exposure_id IS NULL
                        ) t1
                LEFT JOIN t_exposure_detail t2
                ON      t1.apptype = t2.apptype
                AND     t1.mid = t2.mid
                AND     t1.vid = t2.vid
                AND     t1.sessionid = t2.sessionid
                AND     t1.ts >= t2.ts
            ) 
    WHERE   rn = 1
)
,t_no_normal_share_exposure_3 AS 
(
    SELECT  *
    FROM    (
                SELECT  t1.dthh
                        ,t1.apptype
                        ,t1.mid
                        ,t1.vid
                        ,t1.sessionid
                        ,t1.subsessionid
                        ,t1.pagesource
                        ,t1.shareid
                        ,t1.ts
                        ,t2.id AS exposure_id
                        ,t2.ts AS exposure_ts
                        ,ROW_NUMBER() OVER (PARTITION BY t1.dthh,t1.apptype,t1.mid,t1.vid,t1.sessionid,t1.subsessionid,t1.pagesource,t1.shareid ORDER BY t2.ts DESC ) AS rn
                FROM    (
                            SELECT  *
                            FROM    t_no_normal_share_exposure_2
                            WHERE   exposure_id IS NULL
                        ) t1
                LEFT JOIN t_exposure_detail t2
                ON      t1.apptype = t2.apptype
                AND     t1.mid = t2.mid
                AND     t1.vid = t2.vid
                AND     t1.subsessionid = t2.subsessionid
            ) 
    WHERE   rn = 1
)
,t_no_normal_share_exposure_4 AS 
(
    SELECT  *
    FROM    (
                SELECT  t1.dthh
                        ,t1.apptype
                        ,t1.mid
                        ,t1.vid
                        ,t1.sessionid
                        ,t1.subsessionid
                        ,t1.pagesource
                        ,t1.shareid
                        ,t1.ts
                        ,t2.id AS exposure_id
                        ,t2.ts AS exposure_ts
                        ,ROW_NUMBER() OVER (PARTITION BY t1.dthh,t1.apptype,t1.mid,t1.vid,t1.sessionid,t1.subsessionid,t1.pagesource,t1.shareid ORDER BY t2.ts DESC ) AS rn
                FROM    (
                            SELECT  *
                            FROM    t_no_normal_share_exposure_3
                            WHERE   exposure_id IS NULL
                        ) t1
                LEFT JOIN t_exposure_detail t2
                ON      t1.apptype = t2.apptype
                AND     t1.mid = t2.mid
                AND     t1.vid = t2.vid
                AND     t1.sessionid = t2.sessionid
            ) 
    WHERE   rn = 1
)
,t_share_exposure AS 
(
    SELECT  *
    FROM    t_normal_share_exposure_1
    WHERE   exposure_id IS NOT NULL
    UNION ALL
    SELECT  *
    FROM    t_normal_share_exposure_2
    WHERE   exposure_id IS NOT NULL
    UNION ALL
    SELECT  *
    FROM    t_normal_share_exposure_3
    WHERE   exposure_id IS NOT NULL
    UNION ALL
    SELECT  *
    FROM    t_normal_share_exposure_4
    WHERE   exposure_id IS NOT NULL
    UNION ALL
    SELECT  *
    FROM    t_normal_share_exposure_5
    WHERE   exposure_id IS NOT NULL
    UNION ALL
    SELECT  *
    FROM    t_normal_share_exposure_6
    UNION ALL
    SELECT  *
    FROM    t_no_normal_share_exposure_1
    WHERE   exposure_id IS NOT NULL
    UNION ALL
    SELECT  *
    FROM    t_no_normal_share_exposure_2
    WHERE   exposure_id IS NOT NULL
    UNION ALL
    SELECT  *
    FROM    t_no_normal_share_exposure_3
    WHERE   exposure_id IS NOT NULL
    UNION ALL
    SELECT  *
    FROM    t_no_normal_share_exposure_4
)
,t_share_with_label AS 
(
    SELECT  a.dthh
            ,a.apptype -- join 条件
            ,a.mid
            ,a.vid -- join 条件
            ,a.sessionid
            ,a.subsessionid
            ,a.pagesource
            ,a.shareid -- join 条件
            ,a.ts
            ,a.exposure_id
            ,COALESCE(b.return_1_pv,0) AS return_1_pv
            ,COALESCE(b.return_1_uv,0) AS return_1_uv
            ,b.return_1_mids AS return_1_mids -- 可能为null，再决策是否提前处理。
            ,COALESCE(c.return_n_pv,0) AS return_n_pv
            ,COALESCE(c.return_n_uv,0) AS return_n_uv
            ,c.return_n_mids AS return_n_mids -- 可能为null，再决策是否提前处理。
            ,COALESCE(c.new_exposure_cnt,0) AS new_exposure_cnt
    FROM    t_share_exposure a
    LEFT JOIN   (
                    SELECT  shareid
                            ,vid
                            ,apptype
                            ,COUNT(1) AS return_1_pv
                            ,COUNT(DISTINCT mid) AS return_1_uv
                            ,CONCAT_WS(',',COLLECT_SET(mid)) AS return_1_mids
                    FROM    t_return
                    GROUP BY shareid
                             ,vid
                             ,apptype
                ) b
    ON      a.shareid = b.shareid
    AND     a.vid = b.vid
    AND     a.apptype = b.apptype
    LEFT JOIN   (
                    SELECT  rootshareid
                            ,vid
                            ,apptype
                            ,COUNT(1) AS return_n_pv
                            ,COUNT(DISTINCT mid) AS return_n_uv
                            ,CONCAT_WS(',',COLLECT_SET(mid)) AS return_n_mids
                            ,SUM(new_exposure_cnt) AS new_exposure_cnt
                    FROM    t_return_exposure
                    GROUP BY rootshareid
                             ,vid
                             ,apptype
                ) c
    ON      a.shareid = c.rootshareid
    AND     a.vid = c.vid
    AND     a.apptype = c.apptype
)
,t_share_with_label_group AS 
(
    SELECT  exposure_id
            ,COUNT(1) AS share_cnt
            ,SUM(return_1_pv) AS return_1_pv
            ,COALESCE(SIZE(SPLIT(DEDUPLICATION4LIST(CONCAT_WS(',',COLLECT_LIST(return_1_mids))),",")),0) AS return_1_uv
            ,DEDUPLICATION4LIST(CONCAT_WS(',',COLLECT_LIST(return_1_mids))) AS return_1_mids -- 可能是null
            ,SUM(return_n_pv) AS return_n_pv
            ,COALESCE(SIZE(SPLIT(DEDUPLICATION4LIST(CONCAT_WS(',',COLLECT_LIST(return_n_mids))),",")),0) AS return_n_uv
            ,DEDUPLICATION4LIST(CONCAT_WS(',',COLLECT_LIST(return_n_mids))) AS return_n_mids -- 可能是null
            ,SUM(new_exposure_cnt) AS new_exposure_cnt
    FROM    t_share_with_label
    GROUP BY exposure_id
)
,t_root_source_id_group_name AS 
(
    SELECT  *
    FROM    (
                SELECT  root_source_id
                        ,group_name
                        ,ROW_NUMBER() OVER (PARTITION BY root_source_id ) AS rn
                FROM    loghubods.changwen_rootsourceid_group_hour
                WHERE   dt = MAX_PT('loghubods.changwen_rootsourceid_group_hour')
            ) 
    WHERE   rn = 1
)
,t_exposure_share_return AS 
(
    SELECT  apptype
            ,uid
            ,mid
            ,vid
            ,sessionid
            ,subsessionid
            ,pagesource
            ,CASE   WHEN pagesource REGEXP 'pages/user-videos-share-recommend$' THEN '回流后沉浸页&内页feed'
                    WHEN pagesource REGEXP 'pages/detail-recommend$' THEN '详情后沉浸页'
                    WHEN pagesource REGEXP 'pages/user-videos-share$' THEN '回流页'
                    WHEN pagesource REGEXP 'pages/user-videos-detail$' THEN '详情页'
                    WHEN pagesource REGEXP 'pages/category$' THEN '首页feed'
                    ELSE '其他'
            END AS pagesource_new
            ,recommendlogvo -- 推荐算法的返回结果日志存在这个字段中
            ,abcode -- 推荐算法的ab分组
            ,recommendpagetype -- 三种回流头部；两种下滑-沉浸页下滑和feed下滑
            ,recomtraceid
            ,headvideoid
            ,rootsourceid
            ,hotsencetype
            ,flowpool -- 14#68#3#1735262438476#2
            ,level
            ,clientip
            ,machineinfo_brand
            ,machineinfo_model
            ,machineinfo_system
            ,machineinfo_wechatversion
            ,machineinfo_sdkversion
            ,province
            ,city
            ,ts
            ,IF(COALESCE(share_cnt,0) > 0,1,0) AS is_share
            ,COALESCE(share_cnt,0) AS share_cnt
            ,IF(COALESCE(return_1_uv,0) > 0,1,0) AS is_return_1
            ,COALESCE(return_1_pv,0) AS return_1_pv
            ,COALESCE(return_1_uv,0) AS return_1_uv
            ,return_1_mids -- 可能是null
            ,IF(COALESCE(return_n_pv,0) > 0,1,0) AS is_return_n
            ,COALESCE(return_n_pv,0) AS return_n_pv
            ,COALESCE(return_n_uv,0) AS return_n_uv
            ,return_n_mids -- 可能是null
            ,IF(COALESCE(COALESCE(SIZE(ARRAY_REMOVE(SPLIT(return_1_mids,","),mid)),0),0) > 0,1,0) AS is_return_noself
            ,COALESCE(SIZE(ARRAY_REMOVE(SPLIT(return_1_mids,","),mid)),0) AS return_1_uv_noself
            ,ARRAY_JOIN(ARRAY_REMOVE(SPLIT(return_1_mids,","),mid),",") AS return_1_mids_noself
            ,IF(COALESCE(COALESCE(SIZE(ARRAY_REMOVE(SPLIT(return_n_mids,","),mid)),0),0) > 0,1,0) AS is_return_n_noself
            ,COALESCE(SIZE(ARRAY_REMOVE(SPLIT(return_n_mids,","),mid)),0) AS return_n_uv_noself
            ,ARRAY_JOIN(ARRAY_REMOVE(SPLIT(return_n_mids,","),mid),",") AS return_n_mids_noself
            ,COALESCE(new_exposure_cnt) AS new_exposure_cnt
            ,JSON_FORMAT(
                        JSON_OBJECT("animationSceneType",animationSceneType,"extParams",extParams,"rootsessionid",rootsessionid_new,"versioncode",versioncode,"group_name",tc.group_name)
            ) AS extend
            ,SUBSTR(dthh,1,8) AS dt
            ,SUBSTR(dthh,9,2) AS hh
    FROM    t_exposure ta
    LEFT JOIN t_share_with_label_group tb
    ON      ta.id = tb.exposure_id
    LEFT JOIN t_root_source_id_group_name tc
    ON      ta.rootsourceid = tc.root_source_id
)SELECT  *
FROM    t_exposure_share_return
;