data_analysis/production_code/loghubods.video_dimension_detail_add_column.sql

-- Task: 单内容明细_增加字段_0_1  ID: 1021325740  Type: ODPS_SQL
--@exclude_input=loghubods.dwd_video_element_top_merge
--@exclude_input=loghubods.dwa_recsys_alg_analysis_20250110
--@exclude_input=loghubods.auto_enter_flow_pool_videolist
--@exclude_input=loghubods.request_log_each_day
--@exclude_input=usercdm.manager_user
--@exclude_input=loghubods.content_ai_tags_no_dt
--@exclude_output=loghubods.video_dimension_detail_add_column
--@exclude_input=loghubods.content_ai_tags
--@exclude_input=loghubods.feishu_project_kanban_user
--odps sql 
--********************************************************************--
--author:杜崇宇
--create time:2024-10-18 10:48:36
--********************************************************************--
--DROP TABLE IF EXISTS loghubods.video_dimension_detail_add_column;
--ALTER TABLE loghubods.video_dimension_detail_add_column ADD COLUMNS (推荐天数间隔 BIGINT,复推天数间隔 BIGINT)
--ALTER TABLE loghubods.video_dimension_detail_add_column ADD COLUMNS (含AI标签二级品类 BIGINT,含AI标签映射一级品类 STRING)
--ALTER TABLE loghubods.video_dimension_detail_add_column ADD COLUMNS (merge二级品类 STRING,merge一级品类 STRING)
--ALTER TABLE loghubods.video_dimension_detail_add_column ADD COLUMNS (old_video_id bigint comment '原视频id')

WITH t_raw AS 
(
    SELECT  *
            ,CASE   WHEN page IN ("回流后沉浸页&内页feed","详情后沉浸页","首页feed","详情页") THEN "推荐"
                    WHEN page IN ("回流页","其他") THEN "非推荐"
                    ELSE "其他"
            END AS page_type
    FROM    loghubods.dwa_recsys_alg_analysis_20250110
    WHERE   dt = '${bizdate}' --AND     apptype IN ("0","4")
    --AND     abcode IN ("ab0","ab1","ab2","ab3","ab4","ab5","ab6","ab7","ab8","ab9")
    --AND     abcode NOT IN ("ab100")
    --AND     extend_alg IS NOT NULL
    --AND     GET_JSON_OBJECT(extend_alg,'$.scoresMap') IS NOT NULL
) -- 过滤：只保留推荐页面
,t_filtered AS 
(
    SELECT  *
    FROM    t_raw
    WHERE   page_type = "推荐"
) -- 特征提取与维度映射
,t_base AS 
(
    SELECT  dt
            ,apptype
            ,CASE   WHEN apptype IN ("4") AND abcode IN ("ab0","ab1") THEN "实验组-先验地域降权"
                    WHEN apptype IN ("4") AND abcode IN ("ab6","ab7") THEN "实验组-str+校准&ros-统计量"
                    WHEN apptype IN ("4") AND abcode IN ("ab8","ab9") THEN "实验组-str+校准"
                    WHEN apptype IN ("4") AND abcode IN ("ab2","ab3") THEN "对照组"
                    WHEN apptype IN ("4") AND abcode IN ("ab4","ab5") THEN "ab4-5"
                    ELSE "其他"
            END AS abcode
            ,page_type AS page
            ,mid
            ,vid
            ,is_share
            ,share_cnt
            ,is_return_1
            ,is_return_n
            ,return_1_uv
            ,return_n_uv
            ,new_exposure_cnt
            ,flowpool
            ,scoresmap
            ,subsessionid
            ,CAST(GET_JSON_OBJECT(scoresmap,'$.fmRov') AS DOUBLE) AS str_pred
            ,1.22 * pow(CAST(GET_JSON_OBJECT(scoresmap,'$.NorXGBScore') AS DOUBLE),1.15) AS rosn_pred
            ,CAST(GET_JSON_OBJECT(scoresmap,'$.hasReturnRovScore') AS DOUBLE) AS rosn_stat
    FROM    t_filtered
) 
INSERT OVERWRITE TABLE loghubods.video_dimension_detail_add_column PARTITION (dt = '${bizdate}')
SELECT  *
FROM    (
            SELECT  DISTINCT a.数据时间
                    ,a.上传时间
                    ,a.视频id
                    ,a.是否当日新推荐
                    ,a.历史入流量池次数
                    ,a.创建天数间隔
                    ,a.是否七日内创建
                    ,a.视频地址
                    ,a.grafana链接
                    ,a.站内uid
                    ,a.发布者昵称
                    ,a.owner
                    ,a.标题
                    ,a.一级品类
                    ,a.映射一级品类
                    ,a.二级品类
                    ,a.热点品类
                    ,a.类型
                    ,a.上传渠道
                    ,a.推荐状态
                    ,a.首次审核类型
                    ,a.审核人
                    ,a.首次审核时间
                    ,a.首次审核日期
                    ,a.首次机审审核状态
                    ,a.首次机审不通过原因
                    ,a.首次机审推荐状态
                    ,a.首次机审不推荐原因
                    ,a.7日策略入池次数
                    ,a.7日rov入池次数
                    ,a.7日vov入池次数
                    ,a.7日低曝光高ros入池次数
                    ,a.7日手动入池次数
                    ,a.7日内最近一次非自动送入时间
                    ,a.最近一次非自动送入类型
                    ,a.送入人
                    ,a.抓取平台
                    ,a.抓取目标
                    ,a.视频时长
                    ,a.首发videoid
                    ,a.首发uid
                    ,a.首发时间
                    ,a.首发日期
                    ,a.首发播放量
                    ,a.首发来源
                    ,a.首发渠道
                    ,a.是否首发视频
                    ,a.是否首发来源
                    ,a.是否首发渠道
                    ,a.首发距今时间
                    ,a.当日分发曝光pv
                    ,a.当日曝光收益
                    ,a.当日分发分享pv
                    ,a.当日分发回流uv
                    ,a.当日分发拉回曝光pv
                    ,a.vov_t0
                    ,a.rov_t0
                    ,a.vor_t0
                    ,a.str_t0
                    ,a.ros_t0
                    ,a.当日推荐当日分发曝光pv
                    ,a.当日推荐当日曝光收益
                    ,a.当日推荐当日分发分享pv
                    ,a.当日推荐当日分发回流uv
                    ,a.当日推荐当日分发拉回曝光pv
                    ,a.当日推荐vov_t0
                    ,a.当日推荐rov_t0
                    ,a.当日推荐vor_t0
                    ,a.当日推荐str_t0
                    ,a.当日推荐ros_t0
                    ,a.流量池曝光
                    ,a.流量池播放
                    ,a.流量池分享
                    ,a.流量池回流
                    ,a.流量池str
                    ,a.流量池ros
                    ,a.流量池rov
                    ,a.推荐曝光
                    ,a.推荐播放
                    ,a.推荐分享
                    ,a.推荐回流
                    ,a.推荐str
                    ,a.推荐ros
                    ,a.推荐rov
                    ,a.0_1日分发曝光pv
                    ,a.0_1当日分发分享pv
                    ,a.0_1日分发回流uv
                    ,a.0_1日分发拉回曝光pv
                    ,a.vov_t0_1
                    ,a.rov_t0_1
                    ,a.vor_t0_1
                    ,a.str_t0_1
                    ,a.ros_t0_1
                    ,a.0_2日分发曝光pv
                    ,a.0_2当日分发分享pv
                    ,a.0_2日分发回流uv
                    ,a.0_2日分发拉回曝光pv
                    ,a.vov_t0_2
                    ,a.rov_t0_2
                    ,a.vor_t0_2
                    ,a.str_t0_2
                    ,a.ros_t0_2
                    ,a.0_3日分发曝光pv
                    ,a.0_3当日分发分享pv
                    ,a.0_3日分发回流uv
                    ,a.0_3日分发拉回曝光pv
                    ,a.vov_t0_3
                    ,a.rov_t0_3
                    ,a.vor_t0_3
                    ,a.str_t0_3
                    ,a.ros_t0_3
                    ,a.过去7日总发布量
                    ,a.过去7日总推荐量
                    ,a.姓名
                    ,a.出生年份
                    ,a.身份证号码
                    ,a.性别
                    ,a.测试品类
                    ,a.title_duration
                    ,a.最近复推日期
                    ,a.rov入池距当前天数
                    ,a.vov入池距当前天数
                    ,a.低曝光高ros入池距当前天数
                    ,a.手动入池距当前天数
                    ,a.tag_level_2 AS tag_level_2_new
                    ,a.1日分发回流uv
                    ,a.1日分发拉回曝光pv
                    ,a.2日分发回流uv
                    ,a.2日分发拉回曝光pv
                    ,a.3日分发回流uv
                    ,a.3日分发拉回曝光pv
                    ,a.7日分发回流uv
                    ,a.7日分发拉回曝光pv
                    ,a.14日分发回流uv
                    ,a.14日分发拉回曝光pv
                    ,a.30日分发回流uv
                    ,a.30日分发拉回曝光pv
                    ,a.0_7日分发回流uv
                    ,a.0_7日分发拉回曝光pv
                    ,a.0_14日分发回流uv
                    ,a.0_14日分发拉回曝光pv
                    ,a.0_30日分发回流uv
                    ,a.0_30日分发拉回曝光pv
                    ,c.tags
                    ,c.tag_name_1
                    ,c.tag_name_2
                    ,c.tag_name_3
                    ,b.create_date
                    ,CASE   WHEN a.最近复推日期 = '-1' THEN '-1'
                            ELSE SUBSTR(a.最近复推日期,1,8)
                    END AS 最近复推时间
                    ,DATEDIFF(TO_DATE('${bizdate}','YYYYMMDD'),TO_DATE(b.create_date,'YYYYMMDD')) AS 推荐天数间隔
                    ,CASE   WHEN a.最近复推日期 = '-1' THEN '-1'
                            ELSE DATEDIFF(TO_DATE('${bizdate}','YYYYMMDD'),TO_DATE(SUBSTR(a.最近复推日期,1,8),'YYYYMMDD'))
                    END AS 复推天数间隔
                    ,CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                            ELSE c.tag_name_1
                    END AS 包含AI标签二级品类
                    ,CASE   WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '祝福音乐|人生感悟音乐|民族异域音乐|亲情音乐|红歌老歌|音乐知识' THEN '音乐'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '正能量剧情|对口型表演|快闪' THEN '剧情/剧情演绎'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '拟真游戏|麻将|棋牌' THEN '游戏'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '老年审美美女|老年审美帅哥' THEN '随拍/颜值'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '红歌老歌舞蹈|广场舞|舞蹈教程' THEN '舞蹈'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '宠物日常|动物表演|生动物' THEN '动物/萌宠'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '农村生活|农业技术' THEN '三农'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '老年相关科技|未来科幻|国家科技力量' THEN '科技/科技数码'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '保险|理财' THEN '财经'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '亲子日常|K12教育' THEN '母婴/母婴亲子'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '老年相关法律科普|知识科普|生活技巧科普' THEN '法律/科普/人文社科'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '怀念时光|人生忠告|迷信祝福|节日祝福|早中晚好' THEN '情感/情感心理'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '退休前|退休后' THEN '职场/人文社科'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '益智解密|老年教育' THEN '教育/教育培训'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '风景实拍|动植物实拍|人像模特实拍|摄影教学' THEN '摄影摄像'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '名画赏析|杂技柔术|魔术|魔术特效|书法|绘画|木工|口技|大型集体艺术|戏曲戏剧|二人转|其他才艺' THEN '艺术/才艺技能'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '美食测评|美食教程|吃播探店' THEN '美食'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '旅行记录|旅行攻略' THEN '旅行/旅游'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '省份城市亮点|本地新闻|本地生活' THEN '地域本地'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '老年时尚|美妆护肤穿搭' THEN '时尚/美妆'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '传统文化|国际文化' THEN '文化/人文社科'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '搞笑瞬间合集|搞笑段子' THEN '搞笑/休闲娱乐'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '历史名人|当代正能量人物|老明星' THEN '明星/名人'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '老年人上综艺|老年关心纪录片|老综艺影像' THEN '综艺/影视综艺'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '电影切片|电影解说' THEN '电影/影视综艺'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '电视剧切片|电视剧解说' THEN '电视剧/影视综艺'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '中国队比赛|老年运动' THEN '体育/运动'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '健康知识|长寿知识|饮食健康' THEN '医疗健康/长寿/健身'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '健身操' THEN '生活记录/生活'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '老年生活|生活小妙招|园艺花艺' THEN '生活家居/家居家装'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '民生政策|流行病疫情|社会风气|食品安全|贪污腐败|人财诈骗|核污染|惠民新闻|天气变化|国家力量|国际时政|他国政策' THEN '时政社会'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '惊奇事件|罕见画面' THEN '奇人异象'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '中国战争史|中国党史|中国历史影像' THEN '历史'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '国际军事|国内军事|国家统一' THEN '军事'
                            ELSE '-'
                    END AS 含AI标签映射一级品类
                    ,a.tag_level_2
                    ,CASE   WHEN f.tag_level_2 IS NOT NULL THEN f.tag_level_2
                            ELSE c.tag_name_1
                    END AS 二级品类集合
                    ,CASE
                             -- 音乐相关
                               WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '祝福音乐|人生感悟音乐|民族异域音乐|亲情音乐|红歌老歌|音乐知识' THEN '音乐' -- 剧情/剧情演绎相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '正能量剧情|对口型表演|快闪' THEN '剧情/剧情演绎' -- 游戏相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '拟真游戏|麻将|棋牌' THEN '游戏' -- 随拍/颜值相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '老年审美美女|老年审美帅哥' THEN '随拍/颜值' -- 舞蹈相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '红歌老歌舞蹈|广场舞|舞蹈教程' THEN '舞蹈' -- 动物/萌宠相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '宠物日常|动物表演|生动物' THEN '动物/萌宠' -- 三农相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '农村生活|农业技术' THEN '三农' -- 科技/科技数码相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '老年相关科技|未来科幻|国家科技力量' THEN '科技/科技数码' -- 财经相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '保险|理财' THEN '财经' -- 母婴/母婴亲子相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '亲子日常|K12教育' THEN '母婴/母婴亲子' -- 法律/科普/人文社科相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '老年相关法律科普|知识科普|生活技巧科普' THEN '法律/科普/人文社科' -- 情感/情感心理相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '怀念时光|人生忠告|迷信祝福|节日祝福|早中晚好' THEN '情感/情感心理' -- 职场/人文社科相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '退休前|退休后' THEN '职场/人文社科' -- 教育/教育培训相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '益智解密|老年教育' THEN '教育/教育培训' -- 摄影摄像相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '风景实拍|动植物实拍|人像模特实拍|摄影教学' THEN '摄影摄像' -- 艺术/才艺技能相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '名画赏析|杂技柔术|魔术|魔术特效|书法|绘画|木工|口技|大型集体艺术|戏曲戏剧|二人转|其他才艺' THEN '艺术/才艺技能' -- 美食相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '美食测评|美食教程|吃播探店' THEN '美食' -- 旅行/旅游相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '旅行记录|旅行攻略' THEN '旅行/旅游' -- 地域本地相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '省份城市亮点|本地新闻|本地生活' THEN '地域本地' -- 时尚/美妆相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '老年时尚|美妆护肤穿搭' THEN '时尚/美妆' -- 文化/人文社科相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '传统文化|国际文化' THEN '文化/人文社科' -- 搞笑/休闲娱乐相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '搞笑瞬间合集|搞笑段子' THEN '搞笑/休闲娱乐' -- 明星/名人相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '历史名人|当代正能量人物|老明星' THEN '明星/名人' -- 综艺/影视综艺相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '老年人上综艺|老年关心纪录片|老综艺影像|电影切片|电影解说|电视剧切片|电视剧解说' THEN '综艺/影视综艺' -- 体育/运动相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '中国队比赛|老年运动' THEN '体育/运动' -- 医疗健康/长寿/健身相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '健康知识|长寿知识|饮食健康' THEN '医疗健康/长寿/健身' -- 生活记录/生活相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '健身操' THEN '生活记录/生活' -- 生活家居/家居家装相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '老年生活|生活小妙招|园艺花艺' THEN '生活家居/家居家装' -- 时政社会相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '民生政策|流行病疫情|社会风气|食品安全|贪污腐败|人财诈骗|核污染|惠民新闻|天气变化|国家力量|国际时政|他国政策' THEN '时政社会' -- 奇人异象相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '惊奇事件|罕见画面' THEN '奇人异象' -- 历史相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '中国战争史|中国党史|中国历史影像' THEN '历史' -- 军事相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '国际军事|国内军事|国家统一' THEN '军事'
                            ELSE g.一级品类
                    END AS 一级品类集合
                    ,CASE   WHEN h.rank <= 50 THEN '是'
                            ELSE '否'
                    END AS 是否在TOP50
                    ,CASE   WHEN h.rank <= 200 THEN '是'
                            ELSE '否'
                    END AS 是否在TOP200
                    ,h.rank AS 回流rank
                    ,l1.name AS 7日内最近一次送入人
                    ,l1.level AS 7日内最近一次人工入池层数
                    ,l1.date_diff AS 7日内最近一次人工入池距今天数
                    ,l2.name AS 7日内最近一次送入策略
                    ,l2.level AS 7日内最近一次策略入池层数
                    ,l2.date_diff AS 7日内最近一次策略入池距今天数 -- ,j.name as 首次人审审核人
                    -- ,j.audit_time as 首次人审审核时间
                    ,j.首次人审审核状态
                    ,j.首次人审不通过原因
                    ,j.首次人审推荐状态
                    ,k1.tag AS 上推荐实验名称
                    ,k1.date_diff AS 上推荐实验标签距今天数
                    ,k2.tag AS 供给实验名称
                    ,k2.date_diff AS 供给实验标签距今天数
                    ,k3.tag AS 控流量实验名称
                    ,k3.date_diff AS 控流量实验距今天数
                    ,m.widthheight AS 分辨率
                    ,m.widthheight_rate AS 分辨率比值
                    ,m.视觉音乐文字
                    ,m.内容选题
                    ,m.视频主题
                    ,m.视频关键词
                    ,m.视频主体
                    ,m.视频场景
                    ,m.情感倾向
                    ,m.视频风格
                    ,m.是否有片尾引导
                    ,m.引导时长
                    ,m.引导强度
                    ,m.传播性判断
                    ,m.推测观众地域
                    ,m.推测观众年龄段
                    ,m.推测观众性别
                    ,m.推测观众价值类型
                    ,m.推测观众用户价值点
                    ,m.推测观众用观众收入
                    ,m.背景音类型
                    ,m.背景音风格
                    ,m.语音类型
                    ,m.歌曲名
                    ,m.音色
                    ,m.产品水印
                    ,m.产品名称
                    ,m.字幕
                    ,m.颜色
                    ,m.字号
                    ,m.位置
                    ,m.视频口播
                    ,m.封面主体
                    ,m.人物个数
                    ,m.文字数量
                    ,m.文字关键字
                    ,m.封面主题
                    ,m.知名人物
                    ,m.人物年龄段
                    ,m.场景描述
                    ,m.时效性_有无时效
                    ,m.时效性_具体时间
                    ,n.1007回流人数
                    ,n.1008回流人数
                    ,n.带来1007回流的分享数
                    ,n.带来1008回流的分享数
                    ,n.1007进入分发曝光pv
                    ,n.1008进入分发曝光pv
                    ,n.1007回流人数再分享pv
                    ,n.1008回流人数再分享pv
                    ,n.总分享pv
                    ,n.总回流pv
                    ,o.当日总有回流分享pv
                    ,o.当日总回流uv --,o.当日总分享pv
                    ,o.分发分享pv
                    ,o.头部分享pv
                    ,o.当日分发头部分享pv
                    ,o.当日分享当日回流uv
                    ,o.当日分享当日回流一层uv
                    ,o.当日分享当日回流非一层uv
                    ,o.点击非当日分享回流uv
                    ,o.当日分发当日回流uv
                    ,o.非当日分发分享回流uv
                    ,t1.video_id
                    ,t2.是否存在热点
                    ,t2.该热点的特征
                    ,t2.热点内容概括
                    ,t2.判断是热点的原因
                    ,ROW_NUMBER() OVER (PARTITION BY a.dt ORDER BY a.当日分发曝光pv DESC ) AS 曝光rank
                    ,ROW_NUMBER() OVER (PARTITION BY a.dt ORDER BY a.当日分发拉回曝光pv DESC ) AS 拉回曝光rank
                    ,t3.流量池1007回流人数
                    ,t3.流量池1008回流人数
                    ,t3.带来流量池1007回流的分享数
                    ,t3.带来流量池1008回流的分享数
                    ,a.首发账号名
                    ,a.首发owner
                    ,t3.流量池回流人数
                    ,t3.带来流量池回流的分享数
                    ,t4.url
                    ,t5.project_name
                    ,b
                    ,b1
                    ,b2
                    ,b3
                    ,b4
                    ,b5
                    ,ROW_NUMBER() OVER (PARTITION BY a.视频id ORDER BY a.视频id DESC ) AS rank
                    ,br1
                    ,br2
                    ,br3
                    ,br4
                    ,br5
                    ,b_head_share
                    ,b1_head_share
                    ,b2_head_share
                    ,b3_head_share
                    ,b4_head_share
                    ,b_reco_share
                    ,b1_reco_share
                    ,b2_reco_share
                    ,b3_reco_share
                    ,b4_reco_share
                    ,当日分发曝光人数
                    ,0_1_br1
                    ,0_1_br2
                    ,0_1_br3
                    ,0_1_b_head_share
                    ,0_1_b1_head_share
                    ,0_1_b2_head_share
                    ,0_1_b3_head_share
                    ,0_1_b4_head_share
                    ,0_1_b_reco_share
                    ,0_1_b1_reco_share
                    ,0_1_b2_reco_share
                    ,0_1_b3_reco_share
                    ,0_1_b4_reco_share
                    ,0_1_b
                    ,0_1_b1
                    ,0_1_b2
                    ,当日分发当日有回流分享pv
                    ,t6.url AS url1
                    ,t7.project_name AS project_name1
                    ,t8.rovn_copc
                    ,t8.str_copc
                    ,t8.rosn_copc ,t9.总日回流uv,t9.总日分发视频数
,t9.总日推荐视频数,解构选题,
  元素merge,
  分类merge,
  top1元素,
  top1分类
            FROM    loghubods.video_dimension_detail a
            LEFT JOIN   (
                            SELECT  DISTINCT video_id
                                    ,create_time
                                    ,TO_CHAR(create_time,'YYYYMMDD') AS create_date
                            FROM    (
                                        SELECT  DISTINCT video_id
                                                ,start_time
                                                ,create_time
                                                ,ROW_NUMBER() OVER (PARTITION BY video_id ORDER BY create_time ASC ) AS rank
                                        FROM    videoods.flow_pool_level_video_eachday
                                        WHERE   life_cycle_id IS NOT NULL
                                    ) 
                            WHERE   rank = 1
                        ) b
            ON      a.视频id = b.video_id
            LEFT JOIN   (
                            SELECT  DISTINCT title_duration
                                    ,tags
                                    ,tag_name_1
                                    ,tag_name_2
                                    ,tag_name_3
                            FROM    loghubods.content_ai_tags_no_dt
                        ) c
            ON      a.title_duration = c.title_duration
            LEFT JOIN   (
                            SELECT  videoid
                                    ,words_1
                            FROM    (
                                        SELECT  videoid
                                                ,words_1
                                        FROM    videoods.dim_video
                                        LATERAL VIEW EXPLODE(SPLIT(tags,',')) t AS words_1
                                    ) 
                            WHERE   words_1 REGEXP '品类-'
                        ) d
            ON      a.视频id = d.videoid
            LEFT JOIN loghubods.tag_level_2_base f
            ON      a.title_duration = f.title_duration
            LEFT JOIN   (
                            SELECT  DISTINCT title_duration
                                    ,一级品类
                                    ,videoid
                            FROM    (
                                        SELECT  CONCAT(CLEAR_TITLE_SIGNAL(title),'-',total_time) AS title_duration
                                                ,一级品类
                                                ,videoid
                                                ,ROW_NUMBER() OVER (PARTITION BY CONCAT(CLEAR_TITLE_SIGNAL(title),'-',total_time) ORDER BY videoid DESC ) AS rank
                                        FROM    (
                                                    SELECT  DISTINCT videoid
                                                            ,b.title
                                                            ,b.total_time
                                                            ,SUBSTRING_INDEX(words_1,'_',-1) AS 一级品类
                                                    FROM    (
                                                                SELECT  videoid
                                                                        ,words_1
                                                                FROM    (
                                                                            SELECT  videoid
                                                                                    ,words_1
                                                                            FROM    videoods.dim_video
                                                                            LATERAL VIEW EXPLODE(SPLIT(tags,',')) t AS words_1
                                                                        ) 
                                                                WHERE   words_1 REGEXP '一级品类_'
                                                            ) a
                                                    LEFT JOIN videoods.wx_video b
                                                    ON      a.videoid = b.id
                                                ) 
                                    ) 
                            WHERE   rank = 1
                            HAVING  title_duration NOT REGEXP 'None-|null-'
                        ) g
            ON      a.title_duration = g.title_duration
            LEFT JOIN   (
                            SELECT  DISTINCT videoid
                                    ,ROW_NUMBER() OVER (PARTITION BY dt ORDER BY 回流人数 DESC ) AS rank
                            FROM    loghubods.lastday_return
                            WHERE   dt = '${bizdate}'
                        ) h
            ON      a.视频id = h.videoid
            LEFT JOIN   (
                            SELECT  a.video_id
                                    ,a.audit_time
                                    ,audit_status
                                    ,audit_person_id
                                    ,b.name
                                    ,GET_JSON_OBJECT(reason,'$[0].reason') AS reason
                                    ,c.start_time
                                    ,TO_CHAR(FROM_UNIXTIME(c.start_time / 1000),'YYYY-MM-DD hh:mi:ss')
                                    ,UNIX_TIMESTAMP(a.audit_time)
                                    ,ABS(c.start_time / 1000 - UNIX_TIMESTAMP(a.audit_time))
                                    ,CASE   WHEN audit_status = 1 THEN '审核中'
                                            WHEN audit_status = 2 THEN '不通过'
                                            WHEN audit_status = 3 THEN '待修改'
                                            WHEN audit_status = 4 THEN '自己可见'
                                            WHEN audit_status = 5 THEN '通过'
                                            ELSE audit_status
                                    END AS 首次人审审核状态
                                    ,reason AS 首次人审不通过原因
                                    ,CASE   WHEN audit_status = 5
                                                AND c.start_time IS NOT NULL
                                                AND ABS(c.start_time / 1000 - UNIX_TIMESTAMP(a.audit_time)) <= 60 THEN '待推荐'
                                            WHEN audit_status = 5
                                                AND c.start_time IS NOT NULL
                                                AND ABS(c.start_time / 1000 - UNIX_TIMESTAMP(a.audit_time)) > 60 THEN '未推荐'
                                            WHEN audit_status != 5 THEN '未推荐'
                                            WHEN c.start_time IS NULL THEN '未推荐'
                                            ELSE ''
                                    END AS 首次人审推荐状态
                            FROM    (
                                        SELECT  *
                                        FROM    (
                                                    SELECT  *
                                                            ,ROW_NUMBER() OVER (PARTITION BY video_id ORDER BY audit_time ASC ) AS rank
                                                    FROM    videoods.wx_video_audit_record
                                                ) 
                                        WHERE   rank = 1
                                    ) a
                            LEFT JOIN usercdm.manager_user b
                            ON      a.audit_person_id = b.uid
                            LEFT JOIN   (
                                            SELECT  *
                                            FROM    (
                                                        SELECT  video_id
                                                                ,start_time
                                                                ,ROW_NUMBER() OVER (PARTITION BY video_id ORDER BY start_time ASC ) AS rank
                                                        FROM    videoods.flow_pool_level_video_eachday
                                                    ) 
                                            WHERE   rank = 1
                                        ) c
                            ON      a.video_id = c.video_id
                            WHERE   audit_person_id != 227
                        ) j
            ON      a.视频id = j.video_id
            LEFT JOIN   (
                            SELECT  DISTINCT video_id
                                    ,create_time
                                    ,date_diff
                                    ,CASE   WHEN tag_name REGEXP '#str-' THEN REPLACE(tag_name,'#str-','')
                                    END AS tag
                            FROM    (
                                        SELECT  a.video_id
                                                ,a.create_time
                                                ,DATEDIFF(REGEXP_REPLACE('${bizdate}','^(\\d{4})(\\d{2})(\\d{2})$','\\1-\\2-\\3'),TO_CHAR(create_time,'YYYY-MM-DD')) date_diff
                                                ,b.tag_name
                                        FROM    (
                                                    SELECT  video_id
                                                            ,tag_id
                                                            ,create_time
                                                    FROM    videoods.wx_video_tag_rel
                                                ) a
                                        LEFT JOIN   (
                                                        SELECT  tag_id
                                                                ,tag_name
                                                        FROM    videoods.wx_video_tag
                                                    ) b
                                        ON      a.tag_id = b.tag_id
                                    ) 
                            HAVING  tag IS NOT NULL
                        ) k1
            ON      a.视频id = k1.video_id
            LEFT JOIN   (
                            SELECT  DISTINCT video_id
                                    ,create_time
                                    ,date_diff
                                    ,CASE   WHEN tag_name REGEXP 'rol-' THEN REPLACE(tag_name,'rol-','')
                                    END AS tag
                            FROM    (
                                        SELECT  a.video_id
                                                ,a.create_time
                                                ,DATEDIFF(REGEXP_REPLACE('${bizdate}','^(\\d{4})(\\d{2})(\\d{2})$','\\1-\\2-\\3'),TO_CHAR(create_time,'YYYY-MM-DD')) date_diff
                                                ,b.tag_name
                                        FROM    (
                                                    SELECT  video_id
                                                            ,tag_id
                                                            ,create_time
                                                    FROM    videoods.wx_video_tag_rel
                                                ) a
                                        LEFT JOIN   (
                                                        SELECT  tag_id
                                                                ,tag_name
                                                        FROM    videoods.wx_video_tag
                                                    ) b
                                        ON      a.tag_id = b.tag_id
                                    ) 
                            HAVING  tag IS NOT NULL
                        ) k2
            ON      a.视频id = k2.video_id
            LEFT JOIN   (
                            SELECT  DISTINCT video_id
                                    ,create_time
                                    ,date_diff
                                    ,CASE   WHEN tag_name REGEXP 'lev-' THEN REPLACE(tag_name,'lev-','')
                                    END AS tag
                            FROM    (
                                        SELECT  a.video_id
                                                ,a.create_time
                                                ,DATEDIFF(REGEXP_REPLACE('${bizdate}','^(\\d{4})(\\d{2})(\\d{2})$','\\1-\\2-\\3'),TO_CHAR(create_time,'YYYY-MM-DD')) date_diff
                                                ,b.tag_name
                                        FROM    (
                                                    SELECT  video_id
                                                            ,tag_id
                                                            ,create_time
                                                    FROM    videoods.wx_video_tag_rel
                                                ) a
                                        LEFT JOIN   (
                                                        SELECT  tag_id
                                                                ,tag_name
                                                        FROM    videoods.wx_video_tag
                                                    ) b
                                        ON      a.tag_id = b.tag_id
                                    ) 
                            HAVING  tag IS NOT NULL
                        ) k3
            ON      a.视频id = k3.video_id
            LEFT JOIN   (
                            SELECT  video_id
                                    ,start_type
                                    ,create_time
                                    ,name
                                    ,level
                                    ,date_diff
                            FROM    (
                                        SELECT  DISTINCT a.video_id
                                                ,a.start_type
                                                ,a.create_time
                                                ,b.name
                                                ,a.level
                                                ,a.date_diff
                                        FROM    (
                                                    SELECT  video_id
                                                            ,start_type
                                                            ,life_cycle_id
                                                            ,create_time
                                                            ,DATEDIFF(REGEXP_REPLACE('${bizdate}','^(\\d{4})(\\d{2})(\\d{2})$','\\1-\\2-\\3'),TO_CHAR(create_time,'YYYY-MM-DD')) date_diff
                                                            ,CASE   WHEN flow_pool_level_id IN (1,24,30,36,42,48,54,60,66,72,78) THEN '1'
                                                                    WHEN flow_pool_level_id IN (2,25,31,37,43,49,55,61,67,73,79) THEN '2'
                                                                    WHEN flow_pool_level_id IN (3,26,32,38,44,50,56,62,68,74,80) THEN '3'
                                                                    WHEN flow_pool_level_id IN (4,27,33,39,45,51,57,63,69,75,81) THEN '4'
                                                                    WHEN flow_pool_level_id IN (22,28,34,40,46,52,58,64,70,76,82) THEN '5'
                                                                    WHEN flow_pool_level_id IN (23,29,35,41,47,53,59,65,71,77,83) THEN '6'
                                                            END AS level
                                                            ,ROW_NUMBER() OVER (PARTITION BY video_id ORDER BY life_cycle_id DESC ) AS rank
                                                    FROM    videoods.flow_pool_level_video_eachday
                                                    WHERE   start_type IN (1)
                                                    AND     TO_CHAR(create_time,'YYYYMMDD') >= TO_CHAR(DATEADD(TO_DATE('${bizdate}','yyyyMMdd'),-6,'dd'),'yyyyMMdd')
                                                    AND     TO_CHAR(create_time,'YYYYMMDD') <= '${bizdate}'
                                                ) a
                                        LEFT JOIN   (
                                                        SELECT  *
                                                        FROM    (
                                                                    SELECT  *
                                                                            ,ROW_NUMBER() OVER (PARTITION BY videoid ORDER BY dt DESC ) AS rank
                                                                    FROM    (
                                                                                SELECT  DISTINCT b.name
                                                                                        ,dt
                                                                                        ,SUBSTRING_INDEX(SUBSTRING_INDEX(requestbody,'"}','1'),'d":"','-1') AS videoid
                                                                                FROM    loghubods.request_log_each_day a
                                                                                LEFT JOIN usercdm.manager_user b
                                                                                ON      a.operationuid = b.uid
                                                                                WHERE   url = '/manager/flowpool/video/enter'
                                                                                AND     dt <= '${bizdate}'
                                                                                AND     dt >= TO_CHAR(DATEADD(TO_DATE('${bizdate}','yyyyMMdd'),-6,'dd'),'yyyyMMdd')
                                                                            ) 
                                                                ) 
                                                        WHERE   rank = 1
                                                    ) b
                                        ON      a.video_id = b.videoid
                                        WHERE   a.rank = 1
                                    ) 
                            WHERE   start_type = 1
                        ) l1
            ON      a.视频id = l1.video_id
            LEFT JOIN   (
                            SELECT  video_id
                                    ,start_type
                                    ,create_time
                                    ,name
                                    ,date_diff
                                    ,level
                            FROM    (
                                        SELECT  DISTINCT a.video_id
                                                ,a.start_type
                                                ,a.create_time
                                                ,c.type AS name
                                                ,a.date_diff
                                                ,a.level
                                        FROM    (
                                                    SELECT  video_id
                                                            ,start_type
                                                            ,life_cycle_id
                                                            ,DATEDIFF(REGEXP_REPLACE('${bizdate}','^(\\d{4})(\\d{2})(\\d{2})$','\\1-\\2-\\3'),TO_CHAR(create_time,'YYYY-MM-DD')) date_diff
                                                            ,CASE   WHEN flow_pool_level_id IN (1,24,30,36,42,48,54,60,66,72,78) THEN '1'
                                                                    WHEN flow_pool_level_id IN (2,25,31,37,43,49,55,61,67,73,79) THEN '2'
                                                                    WHEN flow_pool_level_id IN (3,26,32,38,44,50,56,62,68,74,80) THEN '3'
                                                                    WHEN flow_pool_level_id IN (4,27,33,39,45,51,57,63,69,75,81) THEN '4'
                                                                    WHEN flow_pool_level_id IN (22,28,34,40,46,52,58,64,70,76,82) THEN '5'
                                                                    WHEN flow_pool_level_id IN (23,29,35,41,47,53,59,65,71,77,83) THEN '6'
                                                            END AS level
                                                            ,create_time
                                                            ,ROW_NUMBER() OVER (PARTITION BY video_id ORDER BY life_cycle_id DESC ) AS rank
                                                    FROM    videoods.flow_pool_level_video_eachday
                                                    WHERE   start_type IN (2)
                                                    AND     TO_CHAR(create_time,'YYYYMMDD') >= TO_CHAR(DATEADD(TO_DATE('${bizdate}','yyyyMMdd'),-6,'dd'),'yyyyMMdd')
                                                    AND     TO_CHAR(create_time,'YYYYMMDD') <= '${bizdate}'
                                                ) a
                                        LEFT JOIN   (
                                                        SELECT  *
                                                        FROM    (
                                                                    SELECT  videoid
                                                                            ,dt
                                                                            ,type
                                                                            ,ROW_NUMBER() OVER (PARTITION BY videoid ORDER BY dt DESC ) AS rank
                                                                    FROM    (
                                                                                SELECT  DISTINCT videoid
                                                                                        ,dt
                                                                                        ,'rov' AS type
                                                                                FROM    loghubods.re_recommend_video_info_df_new
                                                                                WHERE   dt >= TO_CHAR(DATEADD(TO_DATE('${bizdate}','yyyyMMdd'),-6,'dd'),'yyyyMMdd')
                                                                                AND     dt <= '${bizdate}'
                                                                                UNION ALL
                                                                                SELECT  DISTINCT videoid
                                                                                        ,dt
                                                                                        ,'vov' AS type
                                                                                FROM    loghubods.re_recommend_video_info_df_vov
                                                                                WHERE   dt >= TO_CHAR(DATEADD(TO_DATE('${bizdate}','yyyyMMdd'),-6,'dd'),'yyyyMMdd')
                                                                                AND     dt <= '${bizdate}'
                                                                                UNION ALL
                                                                                SELECT  DISTINCT videoid
                                                                                        ,SUBSTRING(dt,1,8) AS dt
                                                                                        ,'ros' AS type
                                                                                FROM    loghubods.auto_enter_flow_pool_videolist
                                                                                WHERE   dt >= CONCAT(TO_CHAR(DATEADD(TO_DATE('${bizdate}','yyyyMMdd'),-6,'dd'),'yyyyMMdd'),'00')
                                                                                AND     dt <= '${bizdate}23'
                                                                            ) 
                                                                ) 
                                                        WHERE   rank = 1
                                                    ) c
                                        ON      a.video_id = c.videoid
                                        WHERE   a.rank = 1
                                    ) 
                            WHERE   start_type = 2
                        ) l2
            ON      a.视频id = l2.video_id
            LEFT JOIN   (
                            SELECT  *
                            FROM    loghubods.videoid_feature_aitags_all
                        ) m
            ON      a.视频id = m.id --加场景
            LEFT JOIN   (
                            --热启动
                            SELECT  a.videoid
                                    ,a.1007回流人数
                                    ,g.1008回流人数
                                    ,a.带来1007回流的分享数
                                    ,g.带来1008回流的分享数
                                    ,a.进入分发曝光pv AS 1007进入分发曝光pv
                                    ,g.进入分发曝光pv AS 1008进入分发曝光pv
                                    ,h.1007回流人数再分享pv
                                    ,i.1008回流人数再分享pv
                                    ,d.share_cnt_pv AS 总分享pv
                                    ,d.click_cnt_pv AS 总回流pv
                            FROM    (
                                        SELECT  a1007.clickobjectid AS videoid
                                                ,COUNT(DISTINCT a1007.machinecode) AS 1007回流人数
                                                ,COUNT(DISTINCT a1007.shareid) AS 带来1007回流的分享数
                                                ,COUNT(b1007.mid) AS 进入分发曝光pv
                                        FROM    (
                                                    SELECT  DISTINCT machinecode
                                                            ,clickobjectid
                                                            ,recomTraceId
                                                            ,a.subsessionid
                                                            ,shareid
                                                    FROM    loghubods.user_share_log a
                                                    LEFT JOIN   (
                                                                    SELECT  DISTINCT mid
                                                                            ,subsessionid
                                                                            ,hotsencetype
                                                                    FROM    loghubods.video_action_log_rp
                                                                    WHERE   dt = '${bizdate}'
                                                                    AND     businesstype = 'videoView'
                                                                    AND     hotsencetype IN (1007)
                                                                ) b
                                                    ON      a.machinecode = b.mid
                                                    AND     a.subsessionid = b.subsessionid
                                                    WHERE   dt = '${bizdate}'
                                                    AND     topic = 'click'
                                                    AND     a.shareid IN (
                                                                SELECT  DISTINCT shareid
                                                                FROM    loghubods.user_share_log
                                                                WHERE   dt = '${bizdate}'
                                                                AND     topic = 'share'
                                                            ) 
                                                    AND     b.mid IS NOT NULL
                                                ) a1007
                                        LEFT JOIN   (
                                                        SELECT  videoid
                                                                ,mid
                                                                ,subsessionid
                                                        FROM    loghubods.video_action_log_rp
                                                        WHERE   dt = '${bizdate}'
                                                        AND     pagesource REGEXP 'category$|recommend$|-pages/user-videos-detail$'
                                                        AND     businesstype = 'videoView'
                                                    ) b1007
                                        ON      a1007.subsessionid = b1007.subsessionid
                                        AND     a1007.machinecode = b1007.mid
                                        GROUP BY a1007.clickobjectid
                                    ) a
                            LEFT JOIN   (
                                            SELECT  a1008.clickobjectid
                                                    ,COUNT(DISTINCT a1008.machinecode) AS 1008回流人数
                                                    ,COUNT(DISTINCT a1008.shareid) AS 带来1008回流的分享数
                                                    ,COUNT(b1008.mid) AS 进入分发曝光pv
                                            FROM    (
                                                        SELECT  DISTINCT machinecode
                                                                ,clickobjectid
                                                                ,recomTraceId
                                                                ,a.subsessionid
                                                                ,shareid
                                                        FROM    loghubods.user_share_log a
                                                        LEFT JOIN   (
                                                                        SELECT  DISTINCT mid
                                                                                ,subsessionid
                                                                                ,hotsencetype
                                                                        FROM    loghubods.video_action_log_rp
                                                                        WHERE   dt = '${bizdate}'
                                                                        AND     businesstype = 'videoView'
                                                                        AND     hotsencetype IN (1008)
                                                                    ) b
                                                        ON      a.machinecode = b.mid
                                                        AND     a.subsessionid = b.subsessionid
                                                        WHERE   dt = '${bizdate}'
                                                        AND     topic = 'click'
                                                        AND     a.shareid IN (
                                                                    SELECT  DISTINCT shareid
                                                                    FROM    loghubods.user_share_log
                                                                    WHERE   dt = '${bizdate}'
                                                                    AND     topic = 'share'
                                                                ) 
                                                        AND     b.mid IS NOT NULL
                                                    ) a1008
                                            LEFT JOIN   (
                                                            SELECT  videoid
                                                                    ,mid
                                                                    ,subsessionid
                                                            FROM    loghubods.video_action_log_rp
                                                            WHERE   dt = '${bizdate}'
                                                            AND     pagesource REGEXP 'category$|recommend$|-pages/user-videos-detail$'
                                                            AND     businesstype = 'videoView'
                                                        ) b1008
                                            ON      a1008.subsessionid = b1008.subsessionid
                                            AND     a1008.machinecode = b1008.mid
                                            GROUP BY a1008.clickobjectid
                                        ) g
                            ON      a.videoid = g.clickobjectid
                            LEFT JOIN   (
                                            SELECT  a1007.clickobjectid
                                                    ,COUNT(DISTINCT b1007.shareid) AS 1007回流人数再分享pv
                                            FROM    (
                                                        SELECT  DISTINCT machinecode
                                                                ,clickobjectid
                                                                ,recomTraceId
                                                                ,a.subsessionid
                                                                ,shareid
                                                        FROM    loghubods.user_share_log a
                                                        LEFT JOIN   (
                                                                        SELECT  DISTINCT mid
                                                                                ,subsessionid
                                                                                ,hotsencetype
                                                                        FROM    loghubods.video_action_log_rp
                                                                        WHERE   dt = '${bizdate}'
                                                                        AND     businesstype = 'videoView'
                                                                        AND     hotsencetype IN (1007)
                                                                    ) b
                                                        ON      a.machinecode = b.mid
                                                        AND     a.subsessionid = b.subsessionid
                                                        WHERE   dt = '${bizdate}'
                                                        AND     topic = 'click'
                                                        AND     a.shareid IN (
                                                                    SELECT  DISTINCT shareid
                                                                    FROM    loghubods.user_share_log
                                                                    WHERE   dt = '${bizdate}'
                                                                    AND     topic = 'share'
                                                                ) 
                                                        AND     b.mid IS NOT NULL
                                                    ) a1007
                                            LEFT JOIN   (
                                                            SELECT  shareobjectid
                                                                    ,shareid
                                                                    ,subsessionid
                                                                    ,machinecode
                                                            FROM    loghubods.user_share_log
                                                            WHERE   dt = '${bizdate}'
                                                            AND     topic = 'share'
                                                        ) b1007
                                            ON      a1007.subsessionid = b1007.subsessionid
                                            AND     a1007.machinecode = b1007.machinecode
                                            GROUP BY a1007.clickobjectid
                                        ) h
                            ON      a.videoid = h.clickobjectid
                            LEFT JOIN   (
                                            SELECT  a1008.clickobjectid
                                                    ,COUNT(DISTINCT b1008.shareid) AS 1008回流人数再分享pv
                                            FROM    (
                                                        SELECT  DISTINCT machinecode
                                                                ,clickobjectid
                                                                ,recomTraceId
                                                                ,a.subsessionid
                                                                ,shareid
                                                        FROM    loghubods.user_share_log a
                                                        LEFT JOIN   (
                                                                        SELECT  DISTINCT mid
                                                                                ,subsessionid
                                                                                ,hotsencetype
                                                                        FROM    loghubods.video_action_log_rp
                                                                        WHERE   dt = '${bizdate}'
                                                                        AND     businesstype = 'videoView'
                                                                        AND     hotsencetype IN (1008)
                                                                    ) b
                                                        ON      a.machinecode = b.mid
                                                        AND     a.subsessionid = b.subsessionid
                                                        WHERE   dt = '${bizdate}'
                                                        AND     topic = 'click'
                                                        AND     a.shareid IN (
                                                                    SELECT  DISTINCT shareid
                                                                    FROM    loghubods.user_share_log
                                                                    WHERE   dt = '${bizdate}'
                                                                    AND     topic = 'share'
                                                                ) 
                                                        AND     b.mid IS NOT NULL
                                                    ) a1008
                                            LEFT JOIN   (
                                                            SELECT  shareobjectid
                                                                    ,shareid
                                                                    ,subsessionid
                                                                    ,machinecode
                                                            FROM    loghubods.user_share_log
                                                            WHERE   dt = '${bizdate}'
                                                            AND     topic = 'share'
                                                        ) b1008
                                            ON      a1008.subsessionid = b1008.subsessionid
                                            AND     a1008.machinecode = b1008.machinecode
                                            GROUP BY a1008.clickobjectid
                                        ) i
                            ON      a.videoid = i.clickobjectid
                            LEFT JOIN   (
                                            SELECT  a.shareobjectid AS videoid
                                                    ,COUNT(DISTINCT a.shareid) AS share_cnt_pv
                                                    ,COUNT(DISTINCT b.machinecode) AS click_cnt_pv
                                            FROM    (
                                                        SELECT  shareobjectid
                                                                ,machinecode
                                                                ,shareid
                                                        FROM    loghubods.user_share_log
                                                        WHERE   dt = '${bizdate}'
                                                        AND     topic = 'share'
                                                    ) a
                                            LEFT JOIN   (
                                                            SELECT  clickobjectid
                                                                    ,machinecode
                                                                    ,shareid
                                                            FROM    loghubods.user_share_log
                                                            WHERE   dt = '${bizdate}'
                                                            AND     topic = 'click'
                                                        ) b
                                            ON      a.shareid = b.shareid
                                            GROUP BY a.shareobjectid
                                        ) d
                            ON      a.videoid = d.videoid
                        ) n
            ON      a.视频id = n.videoid
            LEFT JOIN   (
                            SELECT  a.dt
                                    ,a.videoid
                                    ,a.当日总有回流分享pv
                                    ,a.当日总回流uv
                                    ,b.当日总分享pv
                                    ,d.分发分享pv
                                    ,d.头部分享pv
                                    ,f.当日分发头部分享pv
                                    ,b.当日分享当日回流uv
                                    ,当日分享当日回流一层uv
                                    ,当日分享当日回流非一层uv
                                    ,a.当日总回流uv - b.当日分享当日回流uv AS 点击非当日分享回流uv
                                    ,c.当日分发当日回流uv
                                    ,b.当日分享当日回流uv - c.当日分发当日回流uv AS 非当日分发分享回流uv
                                    ,g.当日分发当日有回流分享pv
                            FROM    (
                                        SELECT  dt
                                                ,clickobjectid AS videoid
                                                ,COUNT(DISTINCT shareid) AS 当日总有回流分享pv
                                                ,COUNT(DISTINCT machinecode) AS 当日总回流uv
                                        FROM    loghubods.user_share_log
                                        WHERE   topic = 'click'
                                        AND     dt = '${bizdate}'
                                        GROUP BY dt
                                                 ,clickobjectid
                                    ) a
                            LEFT JOIN   (
                                            SELECT  a.dt
                                                    ,a.shareobjectid AS videoid
                                                    ,COUNT(DISTINCT a.shareid) AS 当日总分享pv
                                                    ,COUNT(DISTINCT b.machinecode) AS 当日分享当日回流uv
                                                    ,COUNT(DISTINCT CASE    WHEN b.sharedepth = 1 THEN b.machinecode END) AS 当日分享当日回流一层uv
                                                    ,COUNT(DISTINCT CASE    WHEN b.sharedepth > 1 THEN b.machinecode END) AS 当日分享当日回流非一层uv
                                            FROM    (
                                                        SELECT  DISTINCT shareid
                                                                ,dt
                                                                ,shareobjectid
                                                        FROM    loghubods.user_share_log
                                                        WHERE   topic = 'share'
                                                        AND     dt = '${bizdate}'
                                                    ) a
                                            LEFT JOIN   (
                                                            SELECT  DISTINCT shareid
                                                                    ,dt
                                                                    ,machinecode
                                                                    ,sharedepth
                                                                    ,clickobjectid
                                                            FROM    loghubods.user_share_log
                                                            WHERE   topic = 'click'
                                                            AND     dt = '${bizdate}'
                                                        ) b
                                            ON      a.shareid = b.shareid
                                            AND     a.dt = b.dt
                                            GROUP BY a.dt
                                                     ,a.shareobjectid
                                        ) b
                            ON      a.dt = b.dt
                            AND     a.videoid = b.videoid
                            LEFT JOIN   (
                                            SELECT  a.dt
                                                    ,a.videoid --,COUNT(a.mid) AS 当日分发分享pv
                                                    ,COUNT(DISTINCT b.machinecode) AS 当日分发当日回流uv
                                            FROM    (
                                                        SELECT  DISTINCT dt
                                                                ,mid
                                                                ,videoid
                                                                ,GET_JSON_OBJECT(extparams,'$.recomTraceId') AS recomtraceid
                                                        FROM    loghubods.video_action_log_rp
                                                        WHERE   dt = '${bizdate}'
                                                        AND     pagesource REGEXP 'category$|recommend$|-pages/user-videos-detail$'
                                                        AND     businesstype = 'videoShareFriend'
                                                    ) a --当日分发回流
                                            LEFT JOIN   (
                                                            SELECT  DISTINCT dt
                                                                    ,machinecode
                                                                    ,clickobjectid
                                                                    ,recomTraceId
                                                                    ,subsessionid
                                                            FROM    loghubods.user_share_log
                                                            WHERE   dt = '${bizdate}'
                                                            AND     topic = 'click'
                                                        ) b
                                            ON      a.recomTraceId = b.recomTraceId
                                            AND     a.videoid = b.clickobjectid
                                            AND     a.dt = b.dt
                                            GROUP BY a.dt
                                                     ,a.videoid
                                        ) c
                            ON      a.dt = c.dt
                            AND     a.videoid = c.videoid
                            LEFT JOIN   (
                                            SELECT  dt
                                                    ,shareobjectid AS videoid
                                                    ,COUNT(DISTINCT 
                                                          CASE    WHEN pagesource REGEXP 'category$|recommend$|-pages/user-videos-detail$' THEN shareid END
                                                    ) AS 分发分享pv
                                                    ,COUNT(DISTINCT CASE    WHEN pagesource REGEXP 'pages/user-videos-share$' THEN shareid END) AS 头部分享pv
                                            FROM    loghubods.user_share_log
                                            WHERE   topic = 'share'
                                            AND     dt = '${bizdate}'
                                            GROUP BY dt
                                                     ,videoid
                                        ) d
                            ON      a.dt = d.dt
                            AND     a.videoid = d.videoid
                            LEFT JOIN   (
                                            SELECT  dt
                                                    ,videoid
                                                    ,COUNT(DISTINCT 分享页shareid) AS 当日分发头部分享pv
                                            FROM    (
                                                        SELECT  DISTINCT a.dt
                                                                ,a.shareobjectid AS videoid
                                                                ,a.rootshareid
                                                                ,a.shareid AS 分享页shareid
                                                                ,b.shareid
                                                                ,recommend_share_dt
                                                        FROM    (
                                                                    SELECT  DISTINCT dt
                                                                            ,shareobjectid
                                                                            ,rootshareid
                                                                            ,shareid
                                                                    FROM    loghubods.user_share_log
                                                                    WHERE   topic = 'share'
                                                                    AND     dt = '${bizdate}'
                                                                    AND     pagesource REGEXP 'pages/user-videos-share$'
                                                                ) a
                                                        LEFT JOIN   (
                                                                        SELECT  DISTINCT shareid
                                                                                ,TO_CHAR(FROM_UNIXTIME(clienttimestamp / 1000),'YYYYMMDD') AS recommend_share_dt
                                                                        FROM    loghubods.user_share_log
                                                                        WHERE   topic = 'share'
                                                                        AND     pagesource REGEXP 'category$|recommend$|-pages/user-videos-detail$'
                                                                        AND     dt = '${bizdate}'
                                                                    ) b
                                                        ON      a.rootshareid = b.shareid
                                                        WHERE   recommend_share_dt = '${bizdate}'
                                                    ) 
                                            GROUP BY dt
                                                     ,videoid
                                        ) f
                            ON      a.videoid = f.videoid
                            LEFT JOIN   (
                                            SELECT  a.videoid --,COUNT(a.mid) AS 当日分发分享pv
                                                    ,COUNT(DISTINCT b.shareid) AS 当日分发当日有回流分享pv
                                            FROM    (
                                                        SELECT  DISTINCT dt
                                                                ,mid
                                                                ,videoid
                                                                ,shareid
                                                        FROM    loghubods.video_action_log_rp
                                                        WHERE   dt = '${bizdate}'
                                                        AND     pagesource REGEXP 'category$|recommend$|-pages/user-videos-detail$'
                                                        AND     businesstype = 'videoShareFriend'
                                                    ) a --当日分发回流
                                            LEFT JOIN   (
                                                            SELECT  DISTINCT dt
                                                                    ,machinecode
                                                                    ,clickobjectid
                                                                    ,recomTraceId
                                                                    ,subsessionid
                                                                    ,shareid
                                                            FROM    loghubods.user_share_log
                                                            WHERE   dt = '${bizdate}'
                                                            AND     topic = 'click'
                                                        ) b
                                            ON      a.shareid = b.shareid
                                            GROUP BY a.videoid
                                        ) g
                            ON      a.videoid = g.videoid
                        ) o
            ON      a.视频id = o.videoid
            LEFT JOIN   (
                            SELECT  video_id
                                    ,copy_video_id
                            FROM    loghubods.old_video_re_recommend
                        ) t1
            ON      a.视频id = t1.copy_video_id
            LEFT JOIN   (
                            SELECT  video_id
                                    ,是否存在热点
                                    ,该热点的特征
                                    ,热点内容概括
                                    ,判断是热点的原因
                            FROM    loghubods.aitags_basedata
                            WHERE   dt = '${bizdate}'
                        ) t2
            ON      a.视频id = t2.video_id
            LEFT JOIN   (
                            --热启动
                            SELECT  a.videoid
                                    ,a.流量池1007回流人数
                                    ,g.流量池1008回流人数
                                    ,h.流量池回流人数
                                    ,a.带来流量池1007回流的分享数
                                    ,g.带来流量池1008回流的分享数
                                    ,h.带来流量池回流的分享数
                            FROM    (
                                        SELECT  a.videoid
                                                ,COUNT(DISTINCT b.shareid) AS 带来流量池1007回流的分享数
                                                ,COUNT(DISTINCT c.machinecode) AS 流量池1007回流人数
                                        FROM    (
                                                    SELECT  DISTINCT mid
                                                            ,subsessionid
                                                            ,hotsencetype
                                                            ,videoid
                                                    FROM    loghubods.video_action_log_rp
                                                    WHERE   dt = '${bizdate}'
                                                    AND     businesstype = 'videoView' --AND     hotsencetype IN (1007)
                                                    AND     flowpool NOT REGEXP '#1$'
                                                    AND     LENGTH(flowpool) > 4
                                                    AND     pagesource REGEXP 'category$|recommend$|-pages/user-videos-detail$'
                                                ) a
                                        LEFT JOIN   (
                                                        SELECT  shareobjectid
                                                                ,machinecode
                                                                ,shareid
                                                                ,subsessionid
                                                        FROM    loghubods.user_share_log
                                                        WHERE   dt = '${bizdate}'
                                                        AND     topic = 'share'
                                                    ) b
                                        ON      a.subsessionid = b.subsessionid
                                        AND     a.mid = b.machinecode
                                        AND     a.videoid = b.shareobjectid
                                        LEFT JOIN   (
                                                        SELECT  clickobjectid
                                                                ,machinecode
                                                                ,shareid
                                                                ,subsessionid
                                                        FROM    loghubods.user_share_log
                                                        WHERE   dt = '${bizdate}'
                                                        AND     topic = 'click'
                                                    ) c
                                        ON      b.shareid = c.shareid
                                        LEFT JOIN   (
                                                        SELECT  DISTINCT mid
                                                                ,subsessionid
                                                                ,hotsencetype
                                                        FROM    loghubods.video_action_log_rp
                                                        WHERE   dt = '${bizdate}'
                                                        AND     businesstype = 'videoView'
                                                        AND     hotsencetype IN (1007)
                                                    ) d
                                        ON      c.machinecode = d.mid
                                        AND     c.subsessionid = d.subsessionid
                                        WHERE   d.mid IS NOT NULL
                                        GROUP BY a.videoid
                                    ) a
                            LEFT JOIN   (
                                            SELECT  a.videoid
                                                    ,COUNT(DISTINCT b.shareid) AS 带来流量池1008回流的分享数
                                                    ,COUNT(DISTINCT c.machinecode) AS 流量池1008回流人数
                                            FROM    (
                                                        SELECT  DISTINCT mid
                                                                ,subsessionid
                                                                ,hotsencetype
                                                                ,videoid
                                                        FROM    loghubods.video_action_log_rp
                                                        WHERE   dt = '${bizdate}'
                                                        AND     businesstype = 'videoView'
                                                        AND     hotsencetype IN (1008)
                                                        AND     flowpool NOT REGEXP '#1$'
                                                        AND     LENGTH(flowpool) > 4
                                                        AND     pagesource REGEXP 'category$|recommend$|-pages/user-videos-detail$'
                                                    ) a
                                            LEFT JOIN   (
                                                            SELECT  shareobjectid
                                                                    ,machinecode
                                                                    ,shareid
                                                                    ,subsessionid
                                                            FROM    loghubods.user_share_log
                                                            WHERE   dt = '${bizdate}'
                                                            AND     topic = 'share'
                                                        ) b
                                            ON      a.subsessionid = b.subsessionid
                                            AND     a.mid = b.machinecode
                                            AND     a.videoid = b.shareobjectid
                                            LEFT JOIN   (
                                                            SELECT  clickobjectid
                                                                    ,machinecode
                                                                    ,shareid
                                                                    ,subsessionid
                                                            FROM    loghubods.user_share_log
                                                            WHERE   dt = '${bizdate}'
                                                            AND     topic = 'click'
                                                        ) c
                                            ON      b.shareid = c.shareid
                                            LEFT JOIN   (
                                                            SELECT  DISTINCT mid
                                                                    ,subsessionid
                                                                    ,hotsencetype
                                                            FROM    loghubods.video_action_log_rp
                                                            WHERE   dt = '${bizdate}'
                                                            AND     businesstype = 'videoView'
                                                            AND     hotsencetype IN (1008)
                                                        ) d
                                            ON      c.machinecode = d.mid
                                            AND     c.subsessionid = d.subsessionid
                                            WHERE   d.mid IS NOT NULL
                                            GROUP BY a.videoid
                                        ) g
                            ON      a.videoid = g.videoid
                            LEFT JOIN   (
                                            SELECT  a.videoid
                                                    ,COUNT(DISTINCT b.shareid) AS 带来流量池回流的分享数
                                                    ,COUNT(DISTINCT c.machinecode) AS 流量池回流人数
                                            FROM    (
                                                        SELECT  DISTINCT mid
                                                                ,subsessionid
                                                                ,hotsencetype
                                                                ,videoid
                                                        FROM    loghubods.video_action_log_rp
                                                        WHERE   dt = '${bizdate}'
                                                        AND     businesstype = 'videoView'
                                                        AND     flowpool NOT REGEXP '#1$'
                                                        AND     LENGTH(flowpool) > 4
                                                        AND     pagesource REGEXP 'category$|recommend$|-pages/user-videos-detail$'
                                                    ) a
                                            LEFT JOIN   (
                                                            SELECT  shareobjectid
                                                                    ,machinecode
                                                                    ,shareid
                                                                    ,subsessionid
                                                            FROM    loghubods.user_share_log
                                                            WHERE   dt = '${bizdate}'
                                                            AND     topic = 'share'
                                                        ) b
                                            ON      a.subsessionid = b.subsessionid
                                            AND     a.mid = b.machinecode
                                            AND     a.videoid = b.shareobjectid
                                            LEFT JOIN   (
                                                            SELECT  clickobjectid
                                                                    ,machinecode
                                                                    ,shareid
                                                                    ,subsessionid
                                                            FROM    loghubods.user_share_log
                                                            WHERE   dt = '${bizdate}'
                                                            AND     topic = 'click'
                                                        ) c
                                            ON      b.shareid = c.shareid
                                            GROUP BY a.videoid
                                        ) h
                            ON      a.videoid = h.videoid
                            ORDER BY 流量池回流人数 DESC
                        ) t3
            ON      a.视频id = t3.videoid
            LEFT JOIN   (
                            SELECT  video_id AS videoid
                                    ,publish_content_id AS url
                            FROM    videoods.aigc_publish_content_mapping
                        ) t4
            ON      a.视频id = t4.videoid
            LEFT JOIN   (
                            SELECT  DISTINCT project_name
                                    ,uid_1 AS uid
                            FROM    loghubods.feishu_project_kanban_user
                            LATERAL VIEW EXPLODE(SPLIT(uid,',')) t AS uid_1
                        ) t5
            ON      a.站内uid = t5.uid
            LEFT JOIN   (
                            SELECT  video_id AS videoid
                                    ,publish_content_id AS url
                            FROM    videoods.aigc_publish_content_mapping
                        ) t6
            ON      a.首发videoid = t6.videoid
            LEFT JOIN   (
                            SELECT  DISTINCT project_name
                                    ,uid_1 AS uid
                            FROM    loghubods.feishu_project_kanban_user
                            LATERAL VIEW EXPLODE(SPLIT(uid,',')) t AS uid_1
                        ) t7
            ON      a.首发uid = t7.uid
            LEFT JOIN   (
                            SELECT  vid
                                    ,round((SUM(return_n_uv) / COUNT(1)) / NULLIF(AVG(str_pred * rosn_pred),0),4) AS rovn_copc
                                    ,round((SUM(is_return_1) / COUNT(1)) / NULLIF(SUM(str_pred) / COUNT(1),0),4) AS str_copc
                                    ,round((SUM(return_n_uv) / NULLIF(SUM(is_return_1),0)) / NULLIF(SUM(rosn_pred) / COUNT(1),0),4) AS rosn_copc
                            FROM    t_base
                            GROUP BY vid
                        ) t8
            ON      a.视频id = t8.vid 
             LEFT JOIN loghubods.days_total_data t9
            ON a.dt=t9.dt  LEFT JOIN loghubods.dwd_video_element_top_merge t10
            ON      a.视频id = t10.vid
            AND     t10.dt = '20260427'
            WHERE   a.dt = '${bizdate}'
            ORDER BY a.推荐曝光 DESC
        ) 
WHERE   rank = 1

-- Task: 单内容明细_增加字段  ID: 1021144914  Type: ODPS_SQL
--@exclude_input=loghubods.dwd_video_element_top_merge
--@exclude_input=loghubods.dwa_recsys_alg_analysis_20250110
--@exclude_input=loghubods.feishu_project_kanban_user
--@exclude_input=loghubods.auto_enter_flow_pool_videolist
--@exclude_input=loghubods.request_log_each_day
--@exclude_input=usercdm.manager_user
--@exclude_input=loghubods.content_ai_tags_no_dt
--@exclude_input=loghubods.content_ai_tags
--odps sql 
--********************************************************************--
--author:杜崇宇
--create time:2024-10-18 10:48:36
--********************************************************************--
--DROP TABLE IF EXISTS loghubods.video_dimension_detail_add_column;
--ALTER TABLE loghubods.video_dimension_detail_add_column ADD COLUMNS (推荐天数间隔 BIGINT,复推天数间隔 BIGINT)
--ALTER TABLE loghubods.video_dimension_detail_add_column ADD COLUMNS (含AI标签二级品类 BIGINT,含AI标签映射一级品类 STRING)
--ALTER TABLE loghubods.video_dimension_detail_add_column ADD COLUMNS (merge二级品类 STRING,merge一级品类 STRING)
--ALTER TABLE loghubods.video_dimension_detail_add_column ADD COLUMNS (在top50 STRING,在top200 STRING,回流rank STRING)
-- ALTER TABLE loghubods.video_dimension_detail_add_column ADD COLUMNS (
--     7日内最近一次送入人 STRING
--     ,7日内最近一次人工入池层数 bigint
--     ,7日内最近一次人工入池距今天数 bigint
--     ,7日内最近一次送入策略 STRING
--     ,7日内最近一次策略入池层数 bigint
--     ,7日内最近一次策略入池距今天数 bigint
--     ,首次人审审核状态 STRING
--     ,首次人审不通过原因 STRING
--     ,首次人审推荐状态 STRING
--     ,上推荐实验名称 STRING
--     ,上推荐实验标签距今天数 bigint
--     ,供给实验名称 STRING
--     ,供给实验标签距今天数 bigint
--     ,控流量实验名称 STRING
--     ,控流量实验距今天数 bigint
-- )
--ALTER TABLE loghubods.video_dimension_detail_add_column ADD COLUMNS(
--   有回流分享pv BIGINT
--   ,累计分享回流uv BIGINT
--   --,总分享pv  BIGINT
--   ,分发分享pv BIGINT
--   ,头部分享pv BIGINT
--   ,当日分发头部分享pv BIGINT
--   ,当日分享当日回流uv BIGINT
--   ,当日分享当日回流首层uv BIGINT
--   ,当日分享当日回流非首层uv BIGINT
--   ,非当日分享回流uv BIGINT
--   ,n当日分发回流uv BIGINT
--   ,非当日分发回流uv BIGINT
--)
--ALTER TABLE loghubods.video_dimension_detail_add_column CHANGE 控流量实验名称 实验层 STRING;
--ALTER TABLE loghubods.video_dimension_detail_add_column CHANGE 控流量实验距今天数 实验层标签距今天数 bigint;
--ALTER TABLE loghubods.video_dimension_detail_add_column CHANGE 总回流pv 总回流uv bigint
--ALTER TABLE loghubods.video_dimension_detail_add_column ADD COLUMNS (流量池1007回流人数 BIGINT ,流量池1008回流人数 BIGINT,带来流量池1007回流的分享数 BIGINT,带来流量池1008回流的分享数 BIGINT)
--ALTER TABLE loghubods.video_dimension_detail_add_column ADD COLUMNS (首发账号名 STRING  ,首发owner STRING);
--ALTER TABLE loghubods.video_dimension_detail_add_column ADD COLUMNS (rank BIGINT )
--ALTER TABLE loghubods.video_dimension_detail_add_column ADD COLUMNS (b3 BIGINT,b4 BIGINT,b5 BIGINT)
--ALTER TABLE loghubods.video_dimension_detail_add_column change COLUMN rank rename TO b0;
--
--ALTER TABLE loghubods.video_dimension_detail_add_column change COLUMN b rename TO b01;
--ALTER TABLE loghubods.video_dimension_detail_add_column change COLUMN b1 rename TO b02;
--ALTER TABLE loghubods.video_dimension_detail_add_column change COLUMN b2 rename TO b03;
--ALTER TABLE loghubods.video_dimension_detail_add_column change COLUMN b3 rename TO b04;
--ALTER TABLE loghubods.video_dimension_detail_add_column change COLUMN b4 rename TO b05;
--ALTER TABLE loghubods.video_dimension_detail_add_column change COLUMN b5 rename TO rank0;
--ALTER TABLE loghubods.video_dimension_detail_add_column ADD COLUMNS (br1 BIGINT,br2 BIGINT,br3 BIGINT,br4 BIGINT,br5 BIGINT)
--ALTER TABLE loghubods.video_dimension_detail_add_column ADD COLUMNS (当日分发曝光人数 BIGINT)
--
--ALTER TABLE loghubods.video_dimension_detail_add_column ADD COLUMNS (分发有回流分享pv BIGINT)--b1_head_share BIGINT,b2_head_share BIGINT,b3_head_share BIGINT,b4_head_share BIGINT);
--ALTER TABLE loghubods.video_dimension_detail_add_column ADD COLUMNS (b_reco_share BIGINT,--b1_reco_share BIGINT,b2_reco_share BIGINT,b3_reco_share BIGINT,b4_reco_share BIGINT);
--ALTER TABLE loghubods.video_dimension_detail_add_column ADD COLUMNS (aidit详情_首发 string,项目名称_首发 string);
--ALTER TABLE loghubods.video_dimension_detail_add_column ADD COLUMNS (总日回流uv
-- BIGINT ,总日分发视频数
-- BIGINT ,总日推荐视频数
-- BIGINT)


WITH t_raw AS 
(
    SELECT  *
            ,CASE   WHEN page IN ("回流后沉浸页&内页feed","详情后沉浸页","首页feed","详情页") THEN "推荐"
                    WHEN page IN ("回流页","其他") THEN "非推荐"
                    ELSE "其他"
            END AS page_type
    FROM    loghubods.dwa_recsys_alg_analysis_20250110
    WHERE   dt = '${bizdate}' --AND     apptype IN ("0","4")
    --AND     abcode IN ("ab0","ab1","ab2","ab3","ab4","ab5","ab6","ab7","ab8","ab9")
    --AND     abcode NOT IN ("ab100")
    --AND     extend_alg IS NOT NULL
    --AND     GET_JSON_OBJECT(extend_alg,'$.scoresMap') IS NOT NULL
) -- 过滤：只保留推荐页面
,t_filtered AS 
(
    SELECT  *
    FROM    t_raw
    WHERE   page_type = "推荐"
) -- 特征提取与维度映射
,t_base AS 
(
    SELECT  dt
            ,apptype
            ,CASE   WHEN apptype IN ("4") AND abcode IN ("ab0","ab1") THEN "实验组-先验地域降权"
                    WHEN apptype IN ("4") AND abcode IN ("ab6","ab7") THEN "实验组-str+校准&ros-统计量"
                    WHEN apptype IN ("4") AND abcode IN ("ab8","ab9") THEN "实验组-str+校准"
                    WHEN apptype IN ("4") AND abcode IN ("ab2","ab3") THEN "对照组"
                    WHEN apptype IN ("4") AND abcode IN ("ab4","ab5") THEN "ab4-5"
                    ELSE "其他"
            END AS abcode
            ,page_type AS page
            ,mid
            ,vid
            ,is_share
            ,share_cnt
            ,is_return_1
            ,is_return_n
            ,return_1_uv
            ,return_n_uv
            ,new_exposure_cnt
            ,flowpool
            ,scoresmap
            ,subsessionid
            ,CAST(GET_JSON_OBJECT(scoresmap,'$.fmRov') AS DOUBLE) AS str_pred
            ,1.22 * pow(CAST(GET_JSON_OBJECT(scoresmap,'$.NorXGBScore') AS DOUBLE),1.15) AS rosn_pred
            ,CAST(GET_JSON_OBJECT(scoresmap,'$.hasReturnRovScore') AS DOUBLE) AS rosn_stat
    FROM    t_filtered
)
INSERT OVERWRITE TABLE loghubods.video_dimension_detail_add_column PARTITION (dt = '${bizdate}')
SELECT  *
FROM    (
            SELECT  DISTINCT a.数据时间
                    ,a.上传时间
                    ,a.视频id
                    ,a.是否当日新推荐
                    ,a.历史入流量池次数
                    ,a.创建天数间隔
                    ,a.是否七日内创建
                    ,a.视频地址
                    ,a.grafana链接
                    ,a.站内uid
                    ,a.发布者昵称
                    ,a.owner
                    ,a.标题
                    ,a.一级品类
                    ,a.映射一级品类
                    ,a.二级品类
                    ,a.热点品类
                    ,a.类型
                    ,a.上传渠道
                    ,a.推荐状态
                    ,a.首次审核类型
                    ,a.审核人
                    ,a.首次审核时间
                    ,a.首次审核日期
                    ,a.首次机审审核状态
                    ,a.首次机审不通过原因
                    ,a.首次机审推荐状态
                    ,a.首次机审不推荐原因
                    ,a.7日策略入池次数
                    ,a.7日rov入池次数
                    ,a.7日vov入池次数
                    ,a.7日低曝光高ros入池次数
                    ,a.7日手动入池次数
                    ,a.7日内最近一次非自动送入时间
                    ,a.最近一次非自动送入类型
                    ,a.送入人
                    ,a.抓取平台
                    ,a.抓取目标
                    ,a.视频时长
                    ,a.首发videoid
                    ,a.首发uid
                    ,a.首发时间
                    ,a.首发日期
                    ,a.首发播放量
                    ,a.首发来源
                    ,a.首发渠道
                    ,a.是否首发视频
                    ,a.是否首发来源
                    ,a.是否首发渠道
                    ,a.首发距今时间
                    ,a.当日分发曝光pv
                    ,a.当日曝光收益
                    ,a.当日分发分享pv
                    ,a.当日分发回流uv
                    ,a.当日分发拉回曝光pv
                    ,a.vov_t0
                    ,a.rov_t0
                    ,a.vor_t0
                    ,a.str_t0
                    ,a.ros_t0
                    ,a.当日推荐当日分发曝光pv
                    ,a.当日推荐当日曝光收益
                    ,a.当日推荐当日分发分享pv
                    ,a.当日推荐当日分发回流uv
                    ,a.当日推荐当日分发拉回曝光pv
                    ,a.当日推荐vov_t0
                    ,a.当日推荐rov_t0
                    ,a.当日推荐vor_t0
                    ,a.当日推荐str_t0
                    ,a.当日推荐ros_t0
                    ,a.流量池曝光
                    ,a.流量池播放
                    ,a.流量池分享
                    ,a.流量池回流
                    ,a.流量池str
                    ,a.流量池ros
                    ,a.流量池rov
                    ,a.推荐曝光
                    ,a.推荐播放
                    ,a.推荐分享
                    ,a.推荐回流
                    ,a.推荐str
                    ,a.推荐ros
                    ,a.推荐rov
                    ,a.0_1日分发曝光pv
                    ,a.0_1当日分发分享pv
                    ,a.0_1日分发回流uv
                    ,a.0_1日分发拉回曝光pv
                    ,a.vov_t0_1
                    ,a.rov_t0_1
                    ,a.vor_t0_1
                    ,a.str_t0_1
                    ,a.ros_t0_1
                    ,a.0_2日分发曝光pv
                    ,a.0_2当日分发分享pv
                    ,a.0_2日分发回流uv
                    ,a.0_2日分发拉回曝光pv
                    ,a.vov_t0_2
                    ,a.rov_t0_2
                    ,a.vor_t0_2
                    ,a.str_t0_2
                    ,a.ros_t0_2
                    ,a.0_3日分发曝光pv
                    ,a.0_3当日分发分享pv
                    ,a.0_3日分发回流uv
                    ,a.0_3日分发拉回曝光pv
                    ,a.vov_t0_3
                    ,a.rov_t0_3
                    ,a.vor_t0_3
                    ,a.str_t0_3
                    ,a.ros_t0_3
                    ,a.过去7日总发布量
                    ,a.过去7日总推荐量
                    ,a.姓名
                    ,a.出生年份
                    ,a.身份证号码
                    ,a.性别
                    ,a.测试品类
                    ,a.title_duration
                    ,a.最近复推日期
                    ,a.rov入池距当前天数
                    ,a.vov入池距当前天数
                    ,a.低曝光高ros入池距当前天数
                    ,a.手动入池距当前天数
                    ,a.tag_level_2 AS tag_level_2_new
                    ,a.1日分发回流uv
                    ,a.1日分发拉回曝光pv
                    ,a.2日分发回流uv
                    ,a.2日分发拉回曝光pv
                    ,a.3日分发回流uv
                    ,a.3日分发拉回曝光pv
                    ,a.7日分发回流uv
                    ,a.7日分发拉回曝光pv
                    ,a.14日分发回流uv
                    ,a.14日分发拉回曝光pv
                    ,a.30日分发回流uv
                    ,a.30日分发拉回曝光pv
                    ,a.0_7日分发回流uv
                    ,a.0_7日分发拉回曝光pv
                    ,a.0_14日分发回流uv
                    ,a.0_14日分发拉回曝光pv
                    ,a.0_30日分发回流uv
                    ,a.0_30日分发拉回曝光pv
                    ,c.tags
                    ,c.tag_name_1
                    ,c.tag_name_2
                    ,c.tag_name_3
                    ,b.create_date
                    ,CASE   WHEN a.最近复推日期 = '-1' THEN '-1'
                            ELSE SUBSTR(a.最近复推日期,1,8)
                    END AS 最近复推时间
                    ,DATEDIFF(TO_DATE('${bizdate}','YYYYMMDD'),TO_DATE(b.create_date,'YYYYMMDD')) AS 推荐天数间隔
                    ,CASE   WHEN a.最近复推日期 = '-1' THEN '-1'
                            ELSE DATEDIFF(TO_DATE('${bizdate}','YYYYMMDD'),TO_DATE(SUBSTR(a.最近复推日期,1,8),'YYYYMMDD'))
                    END AS 复推天数间隔
                    ,CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                            ELSE c.tag_name_1
                    END AS 包含AI标签二级品类
                    ,CASE   WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '祝福音乐|人生感悟音乐|民族异域音乐|亲情音乐|红歌老歌|音乐知识' THEN '音乐'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '正能量剧情|对口型表演|快闪' THEN '剧情/剧情演绎'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '拟真游戏|麻将|棋牌' THEN '游戏'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '老年审美美女|老年审美帅哥' THEN '随拍/颜值'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '红歌老歌舞蹈|广场舞|舞蹈教程' THEN '舞蹈'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '宠物日常|动物表演|生动物' THEN '动物/萌宠'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '农村生活|农业技术' THEN '三农'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '老年相关科技|未来科幻|国家科技力量' THEN '科技/科技数码'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '保险|理财' THEN '财经'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '亲子日常|K12教育' THEN '母婴/母婴亲子'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '老年相关法律科普|知识科普|生活技巧科普' THEN '法律/科普/人文社科'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '怀念时光|人生忠告|迷信祝福|节日祝福|早中晚好' THEN '情感/情感心理'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '退休前|退休后' THEN '职场/人文社科'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '益智解密|老年教育' THEN '教育/教育培训'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '风景实拍|动植物实拍|人像模特实拍|摄影教学' THEN '摄影摄像'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '名画赏析|杂技柔术|魔术|魔术特效|书法|绘画|木工|口技|大型集体艺术|戏曲戏剧|二人转|其他才艺' THEN '艺术/才艺技能'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '美食测评|美食教程|吃播探店' THEN '美食'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '旅行记录|旅行攻略' THEN '旅行/旅游'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '省份城市亮点|本地新闻|本地生活' THEN '地域本地'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '老年时尚|美妆护肤穿搭' THEN '时尚/美妆'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '传统文化|国际文化' THEN '文化/人文社科'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '搞笑瞬间合集|搞笑段子' THEN '搞笑/休闲娱乐'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '历史名人|当代正能量人物|老明星' THEN '明星/名人'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '老年人上综艺|老年关心纪录片|老综艺影像' THEN '综艺/影视综艺'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '电影切片|电影解说' THEN '电影/影视综艺'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '电视剧切片|电视剧解说' THEN '电视剧/影视综艺'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '中国队比赛|老年运动' THEN '体育/运动'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '健康知识|长寿知识|饮食健康' THEN '医疗健康/长寿/健身'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '健身操' THEN '生活记录/生活'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '老年生活|生活小妙招|园艺花艺' THEN '生活家居/家居家装'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '民生政策|流行病疫情|社会风气|食品安全|贪污腐败|人财诈骗|核污染|惠民新闻|天气变化|国家力量|国际时政|他国政策' THEN '时政社会'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '惊奇事件|罕见画面' THEN '奇人异象'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '中国战争史|中国党史|中国历史影像' THEN '历史'
                            WHEN CASE   WHEN SUBSTRING_INDEX(d.words_1,'-',-1) IS NOT NULL THEN SUBSTRING_INDEX(d.words_1,'-',-1)
                                    ELSE c.tag_name_1
                            END REGEXP '国际军事|国内军事|国家统一' THEN '军事'
                            ELSE '-'
                    END AS 含AI标签映射一级品类
                    ,a.tag_level_2
                    ,CASE   WHEN f.tag_level_2 IS NOT NULL THEN f.tag_level_2
                            ELSE c.tag_name_1
                    END AS 二级品类集合
                    ,CASE
                             -- 音乐相关
                               WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '祝福音乐|人生感悟音乐|民族异域音乐|亲情音乐|红歌老歌|音乐知识' THEN '音乐' -- 剧情/剧情演绎相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '正能量剧情|对口型表演|快闪' THEN '剧情/剧情演绎' -- 游戏相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '拟真游戏|麻将|棋牌' THEN '游戏' -- 随拍/颜值相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '老年审美美女|老年审美帅哥' THEN '随拍/颜值' -- 舞蹈相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '红歌老歌舞蹈|广场舞|舞蹈教程' THEN '舞蹈' -- 动物/萌宠相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '宠物日常|动物表演|生动物' THEN '动物/萌宠' -- 三农相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '农村生活|农业技术' THEN '三农' -- 科技/科技数码相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '老年相关科技|未来科幻|国家科技力量' THEN '科技/科技数码' -- 财经相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '保险|理财' THEN '财经' -- 母婴/母婴亲子相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '亲子日常|K12教育' THEN '母婴/母婴亲子' -- 法律/科普/人文社科相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '老年相关法律科普|知识科普|生活技巧科普' THEN '法律/科普/人文社科' -- 情感/情感心理相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '怀念时光|人生忠告|迷信祝福|节日祝福|早中晚好' THEN '情感/情感心理' -- 职场/人文社科相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '退休前|退休后' THEN '职场/人文社科' -- 教育/教育培训相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '益智解密|老年教育' THEN '教育/教育培训' -- 摄影摄像相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '风景实拍|动植物实拍|人像模特实拍|摄影教学' THEN '摄影摄像' -- 艺术/才艺技能相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '名画赏析|杂技柔术|魔术|魔术特效|书法|绘画|木工|口技|大型集体艺术|戏曲戏剧|二人转|其他才艺' THEN '艺术/才艺技能' -- 美食相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '美食测评|美食教程|吃播探店' THEN '美食' -- 旅行/旅游相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '旅行记录|旅行攻略' THEN '旅行/旅游' -- 地域本地相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '省份城市亮点|本地新闻|本地生活' THEN '地域本地' -- 时尚/美妆相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '老年时尚|美妆护肤穿搭' THEN '时尚/美妆' -- 文化/人文社科相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '传统文化|国际文化' THEN '文化/人文社科' -- 搞笑/休闲娱乐相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '搞笑瞬间合集|搞笑段子' THEN '搞笑/休闲娱乐' -- 明星/名人相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '历史名人|当代正能量人物|老明星' THEN '明星/名人' -- 综艺/影视综艺相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '老年人上综艺|老年关心纪录片|老综艺影像|电影切片|电影解说|电视剧切片|电视剧解说' THEN '综艺/影视综艺' -- 体育/运动相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '中国队比赛|老年运动' THEN '体育/运动' -- 医疗健康/长寿/健身相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '健康知识|长寿知识|饮食健康' THEN '医疗健康/长寿/健身' -- 生活记录/生活相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '健身操' THEN '生活记录/生活' -- 生活家居/家居家装相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '老年生活|生活小妙招|园艺花艺' THEN '生活家居/家居家装' -- 时政社会相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '民生政策|流行病疫情|社会风气|食品安全|贪污腐败|人财诈骗|核污染|惠民新闻|天气变化|国家力量|国际时政|他国政策' THEN '时政社会' -- 奇人异象相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '惊奇事件|罕见画面' THEN '奇人异象' -- 历史相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '中国战争史|中国党史|中国历史影像' THEN '历史' -- 军事相关
                            WHEN COALESCE(f.tag_level_2,c.tag_name_1) REGEXP '国际军事|国内军事|国家统一' THEN '军事'
                            ELSE g.一级品类
                    END AS 一级品类集合
                    ,CASE   WHEN h.rank <= 50 THEN '是'
                            ELSE '否'
                    END AS 是否在TOP50
                    ,CASE   WHEN h.rank <= 200 THEN '是'
                            ELSE '否'
                    END AS 是否在TOP200
                    ,h.rank AS 回流rank
                    ,l1.name AS 7日内最近一次送入人
                    ,l1.level AS 7日内最近一次人工入池层数
                    ,l1.date_diff AS 7日内最近一次人工入池距今天数
                    ,l2.name AS 7日内最近一次送入策略
                    ,l2.level AS 7日内最近一次策略入池层数
                    ,l2.date_diff AS 7日内最近一次策略入池距今天数 -- ,j.name as 首次人审审核人
                    -- ,j.audit_time as 首次人审审核时间
                    ,j.首次人审审核状态
                    ,j.首次人审不通过原因
                    ,j.首次人审推荐状态
                    ,k1.tag AS 上推荐实验名称
                    ,k1.date_diff AS 上推荐实验标签距今天数
                    ,k2.tag AS 供给实验名称
                    ,k2.date_diff AS 供给实验标签距今天数
                    ,k3.tag AS 控流量实验名称
                    ,k3.date_diff AS 控流量实验距今天数
                    ,m.widthheight AS 分辨率
                    ,m.widthheight_rate AS 分辨率比值
                    ,m.视觉音乐文字
                    ,m.内容选题
                    ,m.视频主题
                    ,m.视频关键词
                    ,m.视频主体
                    ,m.视频场景
                    ,m.情感倾向
                    ,m.视频风格
                    ,m.是否有片尾引导
                    ,m.引导时长
                    ,m.引导强度
                    ,m.传播性判断
                    ,m.推测观众地域
                    ,m.推测观众年龄段
                    ,m.推测观众性别
                    ,m.推测观众价值类型
                    ,m.推测观众用户价值点
                    ,m.推测观众用观众收入
                    ,m.背景音类型
                    ,m.背景音风格
                    ,m.语音类型
                    ,m.歌曲名
                    ,m.音色
                    ,m.产品水印
                    ,m.产品名称
                    ,m.字幕
                    ,m.颜色
                    ,m.字号
                    ,m.位置
                    ,m.视频口播
                    ,m.封面主体
                    ,m.人物个数
                    ,m.文字数量
                    ,m.文字关键字
                    ,m.封面主题
                    ,m.知名人物
                    ,m.人物年龄段
                    ,m.场景描述
                    ,m.时效性_有无时效
                    ,m.时效性_具体时间
                    ,n.1007回流人数
                    ,n.1008回流人数
                    ,n.带来1007回流的分享数
                    ,n.带来1008回流的分享数
                    ,n.1007进入分发曝光pv
                    ,n.1008进入分发曝光pv
                    ,n.1007回流人数再分享pv
                    ,n.1008回流人数再分享pv
                    ,n.总分享pv
                    ,n.总回流pv
                    ,o.当日总有回流分享pv
                    ,o.当日总回流uv --,o.当日总分享pv
                    ,o.分发分享pv
                    ,o.头部分享pv
                    ,o.当日分发头部分享pv
                    ,o.当日分享当日回流uv
                    ,o.当日分享当日回流一层uv
                    ,o.当日分享当日回流非一层uv
                    ,o.点击非当日分享回流uv
                    ,o.当日分发当日回流uv
                    ,o.非当日分发分享回流uv
                    ,t1.video_id
                    ,t2.是否存在热点
                    ,t2.该热点的特征
                    ,t2.热点内容概括
                    ,t2.判断是热点的原因
                    ,ROW_NUMBER() OVER (PARTITION BY a.dt ORDER BY a.当日分发曝光pv DESC ) AS 曝光rank
                    ,ROW_NUMBER() OVER (PARTITION BY a.dt ORDER BY a.当日分发拉回曝光pv DESC ) AS 拉回曝光rank
                    ,t3.流量池1007回流人数
                    ,t3.流量池1008回流人数
                    ,t3.带来流量池1007回流的分享数
                    ,t3.带来流量池1008回流的分享数
                    ,a.首发账号名
                    ,a.首发owner
                    ,t3.流量池回流人数
                    ,t3.带来流量池回流的分享数
                    ,t4.url
                    ,t5.project_name
                    ,b
                    ,b1
                    ,b2
                    ,b3
                    ,b4
                    ,b5
                    ,ROW_NUMBER() OVER (PARTITION BY a.视频id ORDER BY a.视频id DESC ) AS rank
                    ,br1
                    ,br2
                    ,br3
                    ,br4
                    ,br5
                    ,b_head_share
                    ,b1_head_share
                    ,b2_head_share
                    ,b3_head_share
                    ,b4_head_share
                    ,b_reco_share
                    ,b1_reco_share
                    ,b2_reco_share
                    ,b3_reco_share
                    ,b4_reco_share
                    ,当日分发曝光人数
                    ,0_1_br1
                    ,0_1_br2
                    ,0_1_br3
                    ,0_1_b_head_share
                    ,0_1_b1_head_share
                    ,0_1_b2_head_share
                    ,0_1_b3_head_share
                    ,0_1_b4_head_share
                    ,0_1_b_reco_share
                    ,0_1_b1_reco_share
                    ,0_1_b2_reco_share
                    ,0_1_b3_reco_share
                    ,0_1_b4_reco_share
                    ,0_1_b
                    ,0_1_b1
                    ,0_1_b2
                    ,当日分发当日有回流分享pv
                    ,t6.url AS url1
                    ,t7.project_name AS project_name1
                    ,t8.rovn_copc
                    ,t8.str_copc
                    ,t8.rosn_copc
                    ,t9.总日回流uv
                    ,t9.总日分发视频数
                    ,t9.总日推荐视频数
                    ,解构选题
                    ,元素merge
                    ,分类merge
                    ,top1元素
                    ,top1分类
            FROM    loghubods.video_dimension_detail a
            LEFT JOIN   (
                            SELECT  DISTINCT video_id
                                    ,create_time
                                    ,TO_CHAR(create_time,'YYYYMMDD') AS create_date
                            FROM    (
                                        SELECT  DISTINCT video_id
                                                ,start_time
                                                ,create_time
                                                ,ROW_NUMBER() OVER (PARTITION BY video_id ORDER BY create_time ASC ) AS rank
                                        FROM    videoods.flow_pool_level_video_eachday
                                        WHERE   life_cycle_id IS NOT NULL
                                    ) 
                            WHERE   rank = 1
                        ) b
            ON      a.视频id = b.video_id
            LEFT JOIN   (
                            SELECT  DISTINCT title_duration
                                    ,tags
                                    ,tag_name_1
                                    ,tag_name_2
                                    ,tag_name_3
                            FROM    loghubods.content_ai_tags_no_dt
                        ) c
            ON      a.title_duration = c.title_duration
            LEFT JOIN   (
                            SELECT  videoid
                                    ,words_1
                            FROM    (
                                        SELECT  videoid
                                                ,words_1
                                        FROM    videoods.dim_video
                                        LATERAL VIEW EXPLODE(SPLIT(tags,',')) t AS words_1
                                    ) 
                            WHERE   words_1 REGEXP '品类-'
                        ) d
            ON      a.视频id = d.videoid
            LEFT JOIN loghubods.tag_level_2_base f
            ON      a.title_duration = f.title_duration
            LEFT JOIN   (
                            SELECT  DISTINCT title_duration
                                    ,一级品类
                                    ,videoid
                            FROM    (
                                        SELECT  CONCAT(CLEAR_TITLE_SIGNAL(title),'-',total_time) AS title_duration
                                                ,一级品类
                                                ,videoid
                                                ,ROW_NUMBER() OVER (PARTITION BY CONCAT(CLEAR_TITLE_SIGNAL(title),'-',total_time) ORDER BY videoid DESC ) AS rank
                                        FROM    (
                                                    SELECT  DISTINCT videoid
                                                            ,b.title
                                                            ,b.total_time
                                                            ,SUBSTRING_INDEX(words_1,'_',-1) AS 一级品类
                                                    FROM    (
                                                                SELECT  videoid
                                                                        ,words_1
                                                                FROM    (
                                                                            SELECT  videoid
                                                                                    ,words_1
                                                                            FROM    videoods.dim_video
                                                                            LATERAL VIEW EXPLODE(SPLIT(tags,',')) t AS words_1
                                                                        ) 
                                                                WHERE   words_1 REGEXP '一级品类_'
                                                            ) a
                                                    LEFT JOIN videoods.wx_video b
                                                    ON      a.videoid = b.id
                                                ) 
                                    ) 
                            WHERE   rank = 1
                            HAVING  title_duration NOT REGEXP 'None-|null-'
                        ) g
            ON      a.title_duration = g.title_duration
            LEFT JOIN   (
                            SELECT  DISTINCT videoid
                                    ,ROW_NUMBER() OVER (PARTITION BY dt ORDER BY 回流人数 DESC ) AS rank
                            FROM    loghubods.lastday_return
                            WHERE   dt = '${bizdate}'
                        ) h
            ON      a.视频id = h.videoid
            LEFT JOIN   (
                            SELECT  a.video_id
                                    ,a.audit_time
                                    ,audit_status
                                    ,audit_person_id
                                    ,b.name
                                    ,GET_JSON_OBJECT(reason,'$[0].reason') AS reason
                                    ,c.start_time
                                    ,TO_CHAR(FROM_UNIXTIME(c.start_time / 1000),'YYYY-MM-DD hh:mi:ss')
                                    ,UNIX_TIMESTAMP(a.audit_time)
                                    ,ABS(c.start_time / 1000 - UNIX_TIMESTAMP(a.audit_time))
                                    ,CASE   WHEN audit_status = 1 THEN '审核中'
                                            WHEN audit_status = 2 THEN '不通过'
                                            WHEN audit_status = 3 THEN '待修改'
                                            WHEN audit_status = 4 THEN '自己可见'
                                            WHEN audit_status = 5 THEN '通过'
                                            ELSE audit_status
                                    END AS 首次人审审核状态
                                    ,reason AS 首次人审不通过原因
                                    ,CASE   WHEN audit_status = 5
                                                AND c.start_time IS NOT NULL
                                                AND ABS(c.start_time / 1000 - UNIX_TIMESTAMP(a.audit_time)) <= 60 THEN '待推荐'
                                            WHEN audit_status = 5
                                                AND c.start_time IS NOT NULL
                                                AND ABS(c.start_time / 1000 - UNIX_TIMESTAMP(a.audit_time)) > 60 THEN '未推荐'
                                            WHEN audit_status != 5 THEN '未推荐'
                                            WHEN c.start_time IS NULL THEN '未推荐'
                                            ELSE ''
                                    END AS 首次人审推荐状态
                            FROM    (
                                        SELECT  *
                                        FROM    (
                                                    SELECT  *
                                                            ,ROW_NUMBER() OVER (PARTITION BY video_id ORDER BY audit_time ASC ) AS rank
                                                    FROM    videoods.wx_video_audit_record
                                                ) 
                                        WHERE   rank = 1
                                    ) a
                            LEFT JOIN usercdm.manager_user b
                            ON      a.audit_person_id = b.uid
                            LEFT JOIN   (
                                            SELECT  *
                                            FROM    (
                                                        SELECT  video_id
                                                                ,start_time
                                                                ,ROW_NUMBER() OVER (PARTITION BY video_id ORDER BY start_time ASC ) AS rank
                                                        FROM    videoods.flow_pool_level_video_eachday
                                                    ) 
                                            WHERE   rank = 1
                                        ) c
                            ON      a.video_id = c.video_id
                            WHERE   audit_person_id != 227
                        ) j
            ON      a.视频id = j.video_id
            LEFT JOIN   (
                            SELECT  DISTINCT video_id
                                    ,create_time
                                    ,date_diff
                                    ,CASE   WHEN tag_name REGEXP '#str-' THEN REPLACE(tag_name,'#str-','')
                                    END AS tag
                            FROM    (
                                        SELECT  a.video_id
                                                ,a.create_time
                                                ,DATEDIFF(REGEXP_REPLACE('${bizdate}','^(\\d{4})(\\d{2})(\\d{2})$','\\1-\\2-\\3'),TO_CHAR(create_time,'YYYY-MM-DD')) date_diff
                                                ,b.tag_name
                                        FROM    (
                                                    SELECT  video_id
                                                            ,tag_id
                                                            ,create_time
                                                    FROM    videoods.wx_video_tag_rel
                                                ) a
                                        LEFT JOIN   (
                                                        SELECT  tag_id
                                                                ,tag_name
                                                        FROM    videoods.wx_video_tag
                                                    ) b
                                        ON      a.tag_id = b.tag_id
                                    ) 
                            HAVING  tag IS NOT NULL
                        ) k1
            ON      a.视频id = k1.video_id
            LEFT JOIN   (
                            SELECT  DISTINCT video_id
                                    ,create_time
                                    ,date_diff
                                    ,CASE   WHEN tag_name REGEXP 'rol-' THEN REPLACE(tag_name,'rol-','')
                                    END AS tag
                            FROM    (
                                        SELECT  a.video_id
                                                ,a.create_time
                                                ,DATEDIFF(REGEXP_REPLACE('${bizdate}','^(\\d{4})(\\d{2})(\\d{2})$','\\1-\\2-\\3'),TO_CHAR(create_time,'YYYY-MM-DD')) date_diff
                                                ,b.tag_name
                                        FROM    (
                                                    SELECT  video_id
                                                            ,tag_id
                                                            ,create_time
                                                    FROM    videoods.wx_video_tag_rel
                                                ) a
                                        LEFT JOIN   (
                                                        SELECT  tag_id
                                                                ,tag_name
                                                        FROM    videoods.wx_video_tag
                                                    ) b
                                        ON      a.tag_id = b.tag_id
                                    ) 
                            HAVING  tag IS NOT NULL
                        ) k2
            ON      a.视频id = k2.video_id
            LEFT JOIN   (
                            SELECT  DISTINCT video_id
                                    ,create_time
                                    ,date_diff
                                    ,CASE   WHEN tag_name REGEXP 'lev-' THEN REPLACE(tag_name,'lev-','')
                                    END AS tag
                            FROM    (
                                        SELECT  a.video_id
                                                ,a.create_time
                                                ,DATEDIFF(REGEXP_REPLACE('${bizdate}','^(\\d{4})(\\d{2})(\\d{2})$','\\1-\\2-\\3'),TO_CHAR(create_time,'YYYY-MM-DD')) date_diff
                                                ,b.tag_name
                                        FROM    (
                                                    SELECT  video_id
                                                            ,tag_id
                                                            ,create_time
                                                    FROM    videoods.wx_video_tag_rel
                                                ) a
                                        LEFT JOIN   (
                                                        SELECT  tag_id
                                                                ,tag_name
                                                        FROM    videoods.wx_video_tag
                                                    ) b
                                        ON      a.tag_id = b.tag_id
                                    ) 
                            HAVING  tag IS NOT NULL
                        ) k3
            ON      a.视频id = k3.video_id
            LEFT JOIN   (
                            SELECT  video_id
                                    ,start_type
                                    ,create_time
                                    ,name
                                    ,level
                                    ,date_diff
                            FROM    (
                                        SELECT  DISTINCT a.video_id
                                                ,a.start_type
                                                ,a.create_time
                                                ,b.name
                                                ,a.level
                                                ,a.date_diff
                                        FROM    (
                                                    SELECT  video_id
                                                            ,start_type
                                                            ,life_cycle_id
                                                            ,create_time
                                                            ,DATEDIFF(REGEXP_REPLACE('${bizdate}','^(\\d{4})(\\d{2})(\\d{2})$','\\1-\\2-\\3'),TO_CHAR(create_time,'YYYY-MM-DD')) date_diff
                                                            ,CASE   WHEN flow_pool_level_id IN (1,24,30,36,42,48,54,60,66,72,78) THEN '1'
                                                                    WHEN flow_pool_level_id IN (2,25,31,37,43,49,55,61,67,73,79) THEN '2'
                                                                    WHEN flow_pool_level_id IN (3,26,32,38,44,50,56,62,68,74,80) THEN '3'
                                                                    WHEN flow_pool_level_id IN (4,27,33,39,45,51,57,63,69,75,81) THEN '4'
                                                                    WHEN flow_pool_level_id IN (22,28,34,40,46,52,58,64,70,76,82) THEN '5'
                                                                    WHEN flow_pool_level_id IN (23,29,35,41,47,53,59,65,71,77,83) THEN '6'
                                                            END AS level
                                                            ,ROW_NUMBER() OVER (PARTITION BY video_id ORDER BY life_cycle_id DESC ) AS rank
                                                    FROM    videoods.flow_pool_level_video_eachday
                                                    WHERE   start_type IN (1)
                                                    AND     TO_CHAR(create_time,'YYYYMMDD') >= TO_CHAR(DATEADD(TO_DATE('${bizdate}','yyyyMMdd'),-6,'dd'),'yyyyMMdd')
                                                    AND     TO_CHAR(create_time,'YYYYMMDD') <= '${bizdate}'
                                                ) a
                                        LEFT JOIN   (
                                                        SELECT  *
                                                        FROM    (
                                                                    SELECT  *
                                                                            ,ROW_NUMBER() OVER (PARTITION BY videoid ORDER BY dt DESC ) AS rank
                                                                    FROM    (
                                                                                SELECT  DISTINCT b.name
                                                                                        ,dt
                                                                                        ,SUBSTRING_INDEX(SUBSTRING_INDEX(requestbody,'"}','1'),'d":"','-1') AS videoid
                                                                                FROM    loghubods.request_log_each_day a
                                                                                LEFT JOIN usercdm.manager_user b
                                                                                ON      a.operationuid = b.uid
                                                                                WHERE   url = '/manager/flowpool/video/enter'
                                                                                AND     dt <= '${bizdate}'
                                                                                AND     dt >= TO_CHAR(DATEADD(TO_DATE('${bizdate}','yyyyMMdd'),-6,'dd'),'yyyyMMdd')
                                                                            ) 
                                                                ) 
                                                        WHERE   rank = 1
                                                    ) b
                                        ON      a.video_id = b.videoid
                                        WHERE   a.rank = 1
                                    ) 
                            WHERE   start_type = 1
                        ) l1
            ON      a.视频id = l1.video_id
            LEFT JOIN   (
                            SELECT  video_id
                                    ,start_type
                                    ,create_time
                                    ,name
                                    ,date_diff
                                    ,level
                            FROM    (
                                        SELECT  DISTINCT a.video_id
                                                ,a.start_type
                                                ,a.create_time
                                                ,c.type AS name
                                                ,a.date_diff
                                                ,a.level
                                        FROM    (
                                                    SELECT  video_id
                                                            ,start_type
                                                            ,life_cycle_id
                                                            ,DATEDIFF(REGEXP_REPLACE('${bizdate}','^(\\d{4})(\\d{2})(\\d{2})$','\\1-\\2-\\3'),TO_CHAR(create_time,'YYYY-MM-DD')) date_diff
                                                            ,CASE   WHEN flow_pool_level_id IN (1,24,30,36,42,48,54,60,66,72,78) THEN '1'
                                                                    WHEN flow_pool_level_id IN (2,25,31,37,43,49,55,61,67,73,79) THEN '2'
                                                                    WHEN flow_pool_level_id IN (3,26,32,38,44,50,56,62,68,74,80) THEN '3'
                                                                    WHEN flow_pool_level_id IN (4,27,33,39,45,51,57,63,69,75,81) THEN '4'
                                                                    WHEN flow_pool_level_id IN (22,28,34,40,46,52,58,64,70,76,82) THEN '5'
                                                                    WHEN flow_pool_level_id IN (23,29,35,41,47,53,59,65,71,77,83) THEN '6'
                                                            END AS level
                                                            ,create_time
                                                            ,ROW_NUMBER() OVER (PARTITION BY video_id ORDER BY life_cycle_id DESC ) AS rank
                                                    FROM    videoods.flow_pool_level_video_eachday
                                                    WHERE   start_type IN (2)
                                                    AND     TO_CHAR(create_time,'YYYYMMDD') >= TO_CHAR(DATEADD(TO_DATE('${bizdate}','yyyyMMdd'),-6,'dd'),'yyyyMMdd')
                                                    AND     TO_CHAR(create_time,'YYYYMMDD') <= '${bizdate}'
                                                ) a
                                        LEFT JOIN   (
                                                        SELECT  *
                                                        FROM    (
                                                                    SELECT  videoid
                                                                            ,dt
                                                                            ,type
                                                                            ,ROW_NUMBER() OVER (PARTITION BY videoid ORDER BY dt DESC ) AS rank
                                                                    FROM    (
                                                                                SELECT  DISTINCT videoid
                                                                                        ,dt
                                                                                        ,'rov' AS type
                                                                                FROM    loghubods.re_recommend_video_info_df_new
                                                                                WHERE   dt >= TO_CHAR(DATEADD(TO_DATE('${bizdate}','yyyyMMdd'),-6,'dd'),'yyyyMMdd')
                                                                                AND     dt <= '${bizdate}'
                                                                                UNION ALL
                                                                                SELECT  DISTINCT videoid
                                                                                        ,dt
                                                                                        ,'vov' AS type
                                                                                FROM    loghubods.re_recommend_video_info_df_vov
                                                                                WHERE   dt >= TO_CHAR(DATEADD(TO_DATE('${bizdate}','yyyyMMdd'),-6,'dd'),'yyyyMMdd')
                                                                                AND     dt <= '${bizdate}'
                                                                                UNION ALL
                                                                                SELECT  DISTINCT videoid
                                                                                        ,SUBSTRING(dt,1,8) AS dt
                                                                                        ,'ros' AS type
                                                                                FROM    loghubods.auto_enter_flow_pool_videolist
                                                                                WHERE   dt >= CONCAT(TO_CHAR(DATEADD(TO_DATE('${bizdate}','yyyyMMdd'),-6,'dd'),'yyyyMMdd'),'00')
                                                                                AND     dt <= '${bizdate}23'
                                                                            ) 
                                                                ) 
                                                        WHERE   rank = 1
                                                    ) c
                                        ON      a.video_id = c.videoid
                                        WHERE   a.rank = 1
                                    ) 
                            WHERE   start_type = 2
                        ) l2
            ON      a.视频id = l2.video_id
            LEFT JOIN   (
                            SELECT  *
                            FROM    loghubods.videoid_feature_aitags_all
                            LIMIT   100
                        ) m
            ON      a.视频id = m.id --加场景
            LEFT JOIN   (
                            --热启动
                            SELECT  a.videoid
                                    ,a.1007回流人数
                                    ,g.1008回流人数
                                    ,a.带来1007回流的分享数
                                    ,g.带来1008回流的分享数
                                    ,a.进入分发曝光pv AS 1007进入分发曝光pv
                                    ,g.进入分发曝光pv AS 1008进入分发曝光pv
                                    ,h.1007回流人数再分享pv
                                    ,i.1008回流人数再分享pv
                                    ,d.share_cnt_pv AS 总分享pv
                                    ,d.click_cnt_pv AS 总回流pv
                            FROM    (
                                        SELECT  a1007.clickobjectid AS videoid
                                                ,COUNT(DISTINCT a1007.machinecode) AS 1007回流人数
                                                ,COUNT(DISTINCT a1007.shareid) AS 带来1007回流的分享数
                                                ,COUNT(b1007.mid) AS 进入分发曝光pv
                                        FROM    (
                                                    SELECT  DISTINCT machinecode
                                                            ,clickobjectid
                                                            ,recomTraceId
                                                            ,a.subsessionid
                                                            ,shareid
                                                    FROM    loghubods.user_share_log a
                                                    LEFT JOIN   (
                                                                    SELECT  DISTINCT mid
                                                                            ,subsessionid
                                                                            ,hotsencetype
                                                                    FROM    loghubods.video_action_log_rp
                                                                    WHERE   dt = '${bizdate}'
                                                                    AND     businesstype = 'videoView'
                                                                    AND     hotsencetype IN (1007)
                                                                ) b
                                                    ON      a.machinecode = b.mid
                                                    AND     a.subsessionid = b.subsessionid
                                                    WHERE   dt = '${bizdate}'
                                                    AND     topic = 'click'
                                                    AND     a.shareid IN (
                                                                SELECT  DISTINCT shareid
                                                                FROM    loghubods.user_share_log
                                                                WHERE   dt = '${bizdate}'
                                                                AND     topic = 'share'
                                                            ) 
                                                    AND     b.mid IS NOT NULL
                                                ) a1007
                                        LEFT JOIN   (
                                                        SELECT  videoid
                                                                ,mid
                                                                ,subsessionid
                                                        FROM    loghubods.video_action_log_rp
                                                        WHERE   dt = '${bizdate}'
                                                        AND     pagesource REGEXP 'category$|recommend$|-pages/user-videos-detail$'
                                                        AND     businesstype = 'videoView'
                                                    ) b1007
                                        ON      a1007.subsessionid = b1007.subsessionid
                                        AND     a1007.machinecode = b1007.mid
                                        GROUP BY a1007.clickobjectid
                                    ) a
                            LEFT JOIN   (
                                            SELECT  a1008.clickobjectid
                                                    ,COUNT(DISTINCT a1008.machinecode) AS 1008回流人数
                                                    ,COUNT(DISTINCT a1008.shareid) AS 带来1008回流的分享数
                                                    ,COUNT(b1008.mid) AS 进入分发曝光pv
                                            FROM    (
                                                        SELECT  DISTINCT machinecode
                                                                ,clickobjectid
                                                                ,recomTraceId
                                                                ,a.subsessionid
                                                                ,shareid
                                                        FROM    loghubods.user_share_log a
                                                        LEFT JOIN   (
                                                                        SELECT  DISTINCT mid
                                                                                ,subsessionid
                                                                                ,hotsencetype
                                                                        FROM    loghubods.video_action_log_rp
                                                                        WHERE   dt = '${bizdate}'
                                                                        AND     businesstype = 'videoView'
                                                                        AND     hotsencetype IN (1008)
                                                                    ) b
                                                        ON      a.machinecode = b.mid
                                                        AND     a.subsessionid = b.subsessionid
                                                        WHERE   dt = '${bizdate}'
                                                        AND     topic = 'click'
                                                        AND     a.shareid IN (
                                                                    SELECT  DISTINCT shareid
                                                                    FROM    loghubods.user_share_log
                                                                    WHERE   dt = '${bizdate}'
                                                                    AND     topic = 'share'
                                                                ) 
                                                        AND     b.mid IS NOT NULL
                                                    ) a1008
                                            LEFT JOIN   (
                                                            SELECT  videoid
                                                                    ,mid
                                                                    ,subsessionid
                                                            FROM    loghubods.video_action_log_rp
                                                            WHERE   dt = '${bizdate}'
                                                            AND     pagesource REGEXP 'category$|recommend$|-pages/user-videos-detail$'
                                                            AND     businesstype = 'videoView'
                                                        ) b1008
                                            ON      a1008.subsessionid = b1008.subsessionid
                                            AND     a1008.machinecode = b1008.mid
                                            GROUP BY a1008.clickobjectid
                                        ) g
                            ON      a.videoid = g.clickobjectid
                            LEFT JOIN   (
                                            SELECT  a1007.clickobjectid
                                                    ,COUNT(DISTINCT b1007.shareid) AS 1007回流人数再分享pv
                                            FROM    (
                                                        SELECT  DISTINCT machinecode
                                                                ,clickobjectid
                                                                ,recomTraceId
                                                                ,a.subsessionid
                                                                ,shareid
                                                        FROM    loghubods.user_share_log a
                                                        LEFT JOIN   (
                                                                        SELECT  DISTINCT mid
                                                                                ,subsessionid
                                                                                ,hotsencetype
                                                                        FROM    loghubods.video_action_log_rp
                                                                        WHERE   dt = '${bizdate}'
                                                                        AND     businesstype = 'videoView'
                                                                        AND     hotsencetype IN (1007)
                                                                    ) b
                                                        ON      a.machinecode = b.mid
                                                        AND     a.subsessionid = b.subsessionid
                                                        WHERE   dt = '${bizdate}'
                                                        AND     topic = 'click'
                                                        AND     a.shareid IN (
                                                                    SELECT  DISTINCT shareid
                                                                    FROM    loghubods.user_share_log
                                                                    WHERE   dt = '${bizdate}'
                                                                    AND     topic = 'share'
                                                                ) 
                                                        AND     b.mid IS NOT NULL
                                                    ) a1007
                                            LEFT JOIN   (
                                                            SELECT  shareobjectid
                                                                    ,shareid
                                                                    ,subsessionid
                                                                    ,machinecode
                                                            FROM    loghubods.user_share_log
                                                            WHERE   dt = '${bizdate}'
                                                            AND     topic = 'share'
                                                        ) b1007
                                            ON      a1007.subsessionid = b1007.subsessionid
                                            AND     a1007.machinecode = b1007.machinecode
                                            GROUP BY a1007.clickobjectid
                                        ) h
                            ON      a.videoid = h.clickobjectid
                            LEFT JOIN   (
                                            SELECT  a1008.clickobjectid
                                                    ,COUNT(DISTINCT b1008.shareid) AS 1008回流人数再分享pv
                                            FROM    (
                                                        SELECT  DISTINCT machinecode
                                                                ,clickobjectid
                                                                ,recomTraceId
                                                                ,a.subsessionid
                                                                ,shareid
                                                        FROM    loghubods.user_share_log a
                                                        LEFT JOIN   (
                                                                        SELECT  DISTINCT mid
                                                                                ,subsessionid
                                                                                ,hotsencetype
                                                                        FROM    loghubods.video_action_log_rp
                                                                        WHERE   dt = '${bizdate}'
                                                                        AND     businesstype = 'videoView'
                                                                        AND     hotsencetype IN (1008)
                                                                    ) b
                                                        ON      a.machinecode = b.mid
                                                        AND     a.subsessionid = b.subsessionid
                                                        WHERE   dt = '${bizdate}'
                                                        AND     topic = 'click'
                                                        AND     a.shareid IN (
                                                                    SELECT  DISTINCT shareid
                                                                    FROM    loghubods.user_share_log
                                                                    WHERE   dt = '${bizdate}'
                                                                    AND     topic = 'share'
                                                                ) 
                                                        AND     b.mid IS NOT NULL
                                                    ) a1008
                                            LEFT JOIN   (
                                                            SELECT  shareobjectid
                                                                    ,shareid
                                                                    ,subsessionid
                                                                    ,machinecode
                                                            FROM    loghubods.user_share_log
                                                            WHERE   dt = '${bizdate}'
                                                            AND     topic = 'share'
                                                        ) b1008
                                            ON      a1008.subsessionid = b1008.subsessionid
                                            AND     a1008.machinecode = b1008.machinecode
                                            GROUP BY a1008.clickobjectid
                                        ) i
                            ON      a.videoid = i.clickobjectid
                            LEFT JOIN   (
                                            SELECT  a.shareobjectid AS videoid
                                                    ,COUNT(DISTINCT a.shareid) AS share_cnt_pv
                                                    ,COUNT(DISTINCT b.machinecode) AS click_cnt_pv
                                            FROM    (
                                                        SELECT  shareobjectid
                                                                ,machinecode
                                                                ,shareid
                                                        FROM    loghubods.user_share_log
                                                        WHERE   dt = '${bizdate}'
                                                        AND     topic = 'share'
                                                    ) a
                                            LEFT JOIN   (
                                                            SELECT  clickobjectid
                                                                    ,machinecode
                                                                    ,shareid
                                                            FROM    loghubods.user_share_log
                                                            WHERE   dt = '${bizdate}'
                                                            AND     topic = 'click'
                                                        ) b
                                            ON      a.shareid = b.shareid
                                            GROUP BY a.shareobjectid
                                        ) d
                            ON      a.videoid = d.videoid
                        ) n
            ON      a.视频id = n.videoid
            LEFT JOIN   (
                            SELECT  a.dt
                                    ,a.videoid
                                    ,a.当日总有回流分享pv
                                    ,a.当日总回流uv
                                    ,b.当日总分享pv
                                    ,d.分发分享pv
                                    ,d.头部分享pv
                                    ,f.当日分发头部分享pv
                                    ,b.当日分享当日回流uv
                                    ,当日分享当日回流一层uv
                                    ,当日分享当日回流非一层uv
                                    ,a.当日总回流uv - b.当日分享当日回流uv AS 点击非当日分享回流uv
                                    ,c.当日分发当日回流uv
                                    ,b.当日分享当日回流uv - c.当日分发当日回流uv AS 非当日分发分享回流uv
                                    ,g.当日分发当日有回流分享pv
                            FROM    (
                                        SELECT  dt
                                                ,clickobjectid AS videoid
                                                ,COUNT(DISTINCT shareid) AS 当日总有回流分享pv
                                                ,COUNT(DISTINCT machinecode) AS 当日总回流uv
                                        FROM    loghubods.user_share_log
                                        WHERE   topic = 'click'
                                        AND     dt = '${bizdate}'
                                        GROUP BY dt
                                                 ,clickobjectid
                                    ) a
                            LEFT JOIN   (
                                            SELECT  a.dt
                                                    ,a.shareobjectid AS videoid
                                                    ,COUNT(DISTINCT a.shareid) AS 当日总分享pv
                                                    ,COUNT(DISTINCT b.machinecode) AS 当日分享当日回流uv
                                                    ,COUNT(DISTINCT CASE    WHEN b.sharedepth = 1 THEN b.machinecode END) AS 当日分享当日回流一层uv
                                                    ,COUNT(DISTINCT CASE    WHEN b.sharedepth > 1 THEN b.machinecode END) AS 当日分享当日回流非一层uv
                                            FROM    (
                                                        SELECT  DISTINCT shareid
                                                                ,dt
                                                                ,shareobjectid
                                                        FROM    loghubods.user_share_log
                                                        WHERE   topic = 'share'
                                                        AND     dt = '${bizdate}'
                                                    ) a
                                            LEFT JOIN   (
                                                            SELECT  DISTINCT shareid
                                                                    ,dt
                                                                    ,machinecode
                                                                    ,sharedepth
                                                                    ,clickobjectid
                                                            FROM    loghubods.user_share_log
                                                            WHERE   topic = 'click'
                                                            AND     dt = '${bizdate}'
                                                        ) b
                                            ON      a.shareid = b.shareid
                                            AND     a.dt = b.dt
                                            GROUP BY a.dt
                                                     ,a.shareobjectid
                                        ) b
                            ON      a.dt = b.dt
                            AND     a.videoid = b.videoid
                            LEFT JOIN   (
                                            SELECT  a.dt
                                                    ,a.videoid --,COUNT(a.mid) AS 当日分发分享pv
                                                    ,COUNT(DISTINCT b.machinecode) AS 当日分发当日回流uv
                                            FROM    (
                                                        SELECT  DISTINCT dt
                                                                ,mid
                                                                ,videoid
                                                                ,GET_JSON_OBJECT(extparams,'$.recomTraceId') AS recomtraceid
                                                        FROM    loghubods.video_action_log_rp
                                                        WHERE   dt = '${bizdate}'
                                                        AND     pagesource REGEXP 'category$|recommend$|-pages/user-videos-detail$'
                                                        AND     businesstype = 'videoShareFriend'
                                                    ) a --当日分发回流
                                            LEFT JOIN   (
                                                            SELECT  DISTINCT dt
                                                                    ,machinecode
                                                                    ,clickobjectid
                                                                    ,recomTraceId
                                                                    ,subsessionid
                                                            FROM    loghubods.user_share_log
                                                            WHERE   dt = '${bizdate}'
                                                            AND     topic = 'click'
                                                        ) b
                                            ON      a.recomTraceId = b.recomTraceId
                                            AND     a.videoid = b.clickobjectid
                                            AND     a.dt = b.dt
                                            GROUP BY a.dt
                                                     ,a.videoid
                                        ) c
                            ON      a.dt = c.dt
                            AND     a.videoid = c.videoid
                            LEFT JOIN   (
                                            SELECT  dt
                                                    ,shareobjectid AS videoid
                                                    ,COUNT(DISTINCT 
                                                          CASE    WHEN pagesource REGEXP 'category$|recommend$|-pages/user-videos-detail$' THEN shareid END
                                                    ) AS 分发分享pv
                                                    ,COUNT(DISTINCT CASE    WHEN pagesource REGEXP 'pages/user-videos-share$' THEN shareid END) AS 头部分享pv
                                            FROM    loghubods.user_share_log
                                            WHERE   topic = 'share'
                                            AND     dt = '${bizdate}'
                                            GROUP BY dt
                                                     ,videoid
                                        ) d
                            ON      a.dt = d.dt
                            AND     a.videoid = d.videoid
                            LEFT JOIN   (
                                            SELECT  dt
                                                    ,videoid
                                                    ,COUNT(DISTINCT 分享页shareid) AS 当日分发头部分享pv
                                            FROM    (
                                                        SELECT  DISTINCT a.dt
                                                                ,a.shareobjectid AS videoid
                                                                ,a.rootshareid
                                                                ,a.shareid AS 分享页shareid
                                                                ,b.shareid
                                                                ,recommend_share_dt
                                                        FROM    (
                                                                    SELECT  DISTINCT dt
                                                                            ,shareobjectid
                                                                            ,rootshareid
                                                                            ,shareid
                                                                    FROM    loghubods.user_share_log
                                                                    WHERE   topic = 'share'
                                                                    AND     dt = '${bizdate}'
                                                                    AND     pagesource REGEXP 'pages/user-videos-share$'
                                                                ) a
                                                        LEFT JOIN   (
                                                                        SELECT  DISTINCT shareid
                                                                                ,TO_CHAR(FROM_UNIXTIME(clienttimestamp / 1000),'YYYYMMDD') AS recommend_share_dt
                                                                        FROM    loghubods.user_share_log
                                                                        WHERE   topic = 'share'
                                                                        AND     pagesource REGEXP 'category$|recommend$|-pages/user-videos-detail$'
                                                                        AND     dt = '${bizdate}'
                                                                    ) b
                                                        ON      a.rootshareid = b.shareid
                                                        WHERE   recommend_share_dt = '${bizdate}'
                                                    ) 
                                            GROUP BY dt
                                                     ,videoid
                                        ) f
                            ON      a.videoid = f.videoid
                            LEFT JOIN   (
                                            SELECT  a.videoid --,COUNT(a.mid) AS 当日分发分享pv
                                                    ,COUNT(DISTINCT b.shareid) AS 当日分发当日有回流分享pv
                                            FROM    (
                                                        SELECT  DISTINCT dt
                                                                ,mid
                                                                ,videoid
                                                                ,shareid
                                                        FROM    loghubods.video_action_log_rp
                                                        WHERE   dt = '${bizdate}'
                                                        AND     pagesource REGEXP 'category$|recommend$|-pages/user-videos-detail$'
                                                        AND     businesstype = 'videoShareFriend'
                                                    ) a --当日分发回流
                                            LEFT JOIN   (
                                                            SELECT  DISTINCT dt
                                                                    ,machinecode
                                                                    ,clickobjectid
                                                                    ,recomTraceId
                                                                    ,subsessionid
                                                                    ,shareid
                                                            FROM    loghubods.user_share_log
                                                            WHERE   dt = '${bizdate}'
                                                            AND     topic = 'click'
                                                        ) b
                                            ON      a.shareid = b.shareid
                                            GROUP BY a.videoid
                                        ) g
                            ON      a.videoid = g.videoid
                        ) o
            ON      a.视频id = o.videoid
            LEFT JOIN   (
                            SELECT  video_id
                                    ,copy_video_id
                            FROM    loghubods.old_video_re_recommend
                        ) t1
            ON      a.视频id = t1.copy_video_id
            LEFT JOIN   (
                            SELECT  video_id
                                    ,是否存在热点
                                    ,该热点的特征
                                    ,热点内容概括
                                    ,判断是热点的原因
                            FROM    loghubods.aitags_basedata
                            WHERE   dt = '${bizdate}'
                        ) t2
            ON      a.视频id = t2.video_id
            LEFT JOIN   (
                            --热启动
                            SELECT  a.videoid
                                    ,a.流量池1007回流人数
                                    ,g.流量池1008回流人数
                                    ,h.流量池回流人数
                                    ,a.带来流量池1007回流的分享数
                                    ,g.带来流量池1008回流的分享数
                                    ,h.带来流量池回流的分享数
                            FROM    (
                                        SELECT  a.videoid
                                                ,COUNT(DISTINCT b.shareid) AS 带来流量池1007回流的分享数
                                                ,COUNT(DISTINCT c.machinecode) AS 流量池1007回流人数
                                        FROM    (
                                                    SELECT  DISTINCT mid
                                                            ,subsessionid
                                                            ,hotsencetype
                                                            ,videoid
                                                    FROM    loghubods.video_action_log_rp
                                                    WHERE   dt = '${bizdate}'
                                                    AND     businesstype = 'videoView' --AND     hotsencetype IN (1007)
                                                    AND     flowpool NOT REGEXP '#1$'
                                                    AND     LENGTH(flowpool) > 4
                                                    AND     pagesource REGEXP 'category$|recommend$|-pages/user-videos-detail$'
                                                ) a
                                        LEFT JOIN   (
                                                        SELECT  shareobjectid
                                                                ,machinecode
                                                                ,shareid
                                                                ,subsessionid
                                                        FROM    loghubods.user_share_log
                                                        WHERE   dt = '${bizdate}'
                                                        AND     topic = 'share'
                                                    ) b
                                        ON      a.subsessionid = b.subsessionid
                                        AND     a.mid = b.machinecode
                                        AND     a.videoid = b.shareobjectid
                                        LEFT JOIN   (
                                                        SELECT  clickobjectid
                                                                ,machinecode
                                                                ,shareid
                                                                ,subsessionid
                                                        FROM    loghubods.user_share_log
                                                        WHERE   dt = '${bizdate}'
                                                        AND     topic = 'click'
                                                    ) c
                                        ON      b.shareid = c.shareid
                                        LEFT JOIN   (
                                                        SELECT  DISTINCT mid
                                                                ,subsessionid
                                                                ,hotsencetype
                                                        FROM    loghubods.video_action_log_rp
                                                        WHERE   dt = '${bizdate}'
                                                        AND     businesstype = 'videoView'
                                                        AND     hotsencetype IN (1007)
                                                    ) d
                                        ON      c.machinecode = d.mid
                                        AND     c.subsessionid = d.subsessionid
                                        WHERE   d.mid IS NOT NULL
                                        GROUP BY a.videoid
                                    ) a
                            LEFT JOIN   (
                                            SELECT  a.videoid
                                                    ,COUNT(DISTINCT b.shareid) AS 带来流量池1008回流的分享数
                                                    ,COUNT(DISTINCT c.machinecode) AS 流量池1008回流人数
                                            FROM    (
                                                        SELECT  DISTINCT mid
                                                                ,subsessionid
                                                                ,hotsencetype
                                                                ,videoid
                                                        FROM    loghubods.video_action_log_rp
                                                        WHERE   dt = '${bizdate}'
                                                        AND     businesstype = 'videoView'
                                                        AND     hotsencetype IN (1008)
                                                        AND     flowpool NOT REGEXP '#1$'
                                                        AND     LENGTH(flowpool) > 4
                                                        AND     pagesource REGEXP 'category$|recommend$|-pages/user-videos-detail$'
                                                    ) a
                                            LEFT JOIN   (
                                                            SELECT  shareobjectid
                                                                    ,machinecode
                                                                    ,shareid
                                                                    ,subsessionid
                                                            FROM    loghubods.user_share_log
                                                            WHERE   dt = '${bizdate}'
                                                            AND     topic = 'share'
                                                        ) b
                                            ON      a.subsessionid = b.subsessionid
                                            AND     a.mid = b.machinecode
                                            AND     a.videoid = b.shareobjectid
                                            LEFT JOIN   (
                                                            SELECT  clickobjectid
                                                                    ,machinecode
                                                                    ,shareid
                                                                    ,subsessionid
                                                            FROM    loghubods.user_share_log
                                                            WHERE   dt = '${bizdate}'
                                                            AND     topic = 'click'
                                                        ) c
                                            ON      b.shareid = c.shareid
                                            LEFT JOIN   (
                                                            SELECT  DISTINCT mid
                                                                    ,subsessionid
                                                                    ,hotsencetype
                                                            FROM    loghubods.video_action_log_rp
                                                            WHERE   dt = '${bizdate}'
                                                            AND     businesstype = 'videoView'
                                                            AND     hotsencetype IN (1008)
                                                        ) d
                                            ON      c.machinecode = d.mid
                                            AND     c.subsessionid = d.subsessionid
                                            WHERE   d.mid IS NOT NULL
                                            GROUP BY a.videoid
                                        ) g
                            ON      a.videoid = g.videoid
                            LEFT JOIN   (
                                            SELECT  a.videoid
                                                    ,COUNT(DISTINCT b.shareid) AS 带来流量池回流的分享数
                                                    ,COUNT(DISTINCT c.machinecode) AS 流量池回流人数
                                            FROM    (
                                                        SELECT  DISTINCT mid
                                                                ,subsessionid
                                                                ,hotsencetype
                                                                ,videoid
                                                        FROM    loghubods.video_action_log_rp
                                                        WHERE   dt = '${bizdate}'
                                                        AND     businesstype = 'videoView'
                                                        AND     flowpool NOT REGEXP '#1$'
                                                        AND     LENGTH(flowpool) > 4
                                                        AND     pagesource REGEXP 'category$|recommend$|-pages/user-videos-detail$'
                                                    ) a
                                            LEFT JOIN   (
                                                            SELECT  shareobjectid
                                                                    ,machinecode
                                                                    ,shareid
                                                                    ,subsessionid
                                                            FROM    loghubods.user_share_log
                                                            WHERE   dt = '${bizdate}'
                                                            AND     topic = 'share'
                                                        ) b
                                            ON      a.subsessionid = b.subsessionid
                                            AND     a.mid = b.machinecode
                                            AND     a.videoid = b.shareobjectid
                                            LEFT JOIN   (
                                                            SELECT  clickobjectid
                                                                    ,machinecode
                                                                    ,shareid
                                                                    ,subsessionid
                                                            FROM    loghubods.user_share_log
                                                            WHERE   dt = '${bizdate}'
                                                            AND     topic = 'click'
                                                        ) c
                                            ON      b.shareid = c.shareid
                                            GROUP BY a.videoid
                                        ) h
                            ON      a.videoid = h.videoid
                            ORDER BY 流量池回流人数 DESC
                        ) t3
            ON      a.视频id = t3.videoid
            LEFT JOIN   (
                            SELECT  video_id AS videoid
                                    ,publish_content_id AS url
                            FROM    videoods.aigc_publish_content_mapping
                        ) t4
            ON      a.视频id = t4.videoid
            LEFT JOIN   (
                            SELECT  DISTINCT project_name
                                    ,uid_1 AS uid
                            FROM    loghubods.feishu_project_kanban_user
                            LATERAL VIEW EXPLODE(SPLIT(uid,',')) t AS uid_1
                        ) t5
            ON      a.站内uid = t5.uid
            LEFT JOIN   (
                            SELECT  video_id AS videoid
                                    ,publish_content_id AS url
                            FROM    videoods.aigc_publish_content_mapping
                        ) t6
            ON      a.首发videoid = t6.videoid
            LEFT JOIN   (
                            SELECT  DISTINCT project_name
                                    ,uid_1 AS uid
                            FROM    loghubods.feishu_project_kanban_user
                            LATERAL VIEW EXPLODE(SPLIT(uid,',')) t AS uid_1
                        ) t7
            ON      a.首发uid = t7.uid
            LEFT JOIN   (
                            SELECT  vid
                                    ,round((SUM(return_n_uv) / COUNT(1)) / NULLIF(AVG(str_pred * rosn_pred),0),4) AS rovn_copc
                                    ,round((SUM(is_return_1) / COUNT(1)) / NULLIF(SUM(str_pred) / COUNT(1),0),4) AS str_copc
                                    ,round((SUM(return_n_uv) / NULLIF(SUM(is_return_1),0)) / NULLIF(SUM(rosn_pred) / COUNT(1),0),4) AS rosn_copc
                            FROM    t_base
                            GROUP BY vid
                        ) t8
            ON      a.视频id = t8.vid
            LEFT JOIN loghubods.days_total_data t9
            ON      a.dt = t9.dt
            LEFT JOIN loghubods.dwd_video_element_top_merge t10
            ON      a.视频id = t10.vid
            AND     t10.dt = '20260427'
            WHERE   a.dt = '${bizdate}'
            ORDER BY a.推荐曝光 DESC
        ) 
WHERE   rank = 1