-- 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