-- Task: AI标签特征提取  ID: 1021770953  Type: ODPS_SQL
----odps sql 
----********************************************************************--
----author:杜崇宇
----create time:2024-11-11 15:43:03
----********************************************************************--
----1016prompt
----SELECT  dt
----        ,video_id
----        ,video_title
----        ,video_url
----        ,GET_JSON_OBJECT(data,'$.一、基础信息.视觉/音乐/文字') AS 视觉音乐文字
----        ,GET_JSON_OBJECT(data,'$.一、基础信息.内容选题') AS 内容选题
----        ,GET_JSON_OBJECT(data,'$.一、基础信息.视频主题') AS 视频主题
----        ,GET_JSON_OBJECT(data,'$.二、主体和场景.视频主体') AS 视频主体
----        ,GET_JSON_OBJECT(data,'$.二、主体和场景.视频场景') AS 视频场景
----        ,GET_JSON_OBJECT(data,'$.三、情感与风格.情感倾向') AS 情感倾向
----        ,GET_JSON_OBJECT(data,'$.三、情感与风格.视频风格') AS 视频风格
----        ,GET_JSON_OBJECT(data,'$.四、视频传播性与观众.片尾引导.视频片尾是否有引导观众分享?') AS 片尾引导分享
----        ,GET_JSON_OBJECT(data,'$.四、视频传播性与观众.片尾引导.引导时长') AS 引导时长
----        ,GET_JSON_OBJECT(data,'$.四、视频传播性与观众.传播性判断') AS 传播性判断
----        ,GET_JSON_OBJECT(data,'$.四、视频传播性与观众.观众画像.推测观众的年龄') AS 推测观众年龄
----        ,GET_JSON_OBJECT(data,'$.四、视频传播性与观众.观众画像.推测观众的性别') AS 推测观众性别
----        ,GET_JSON_OBJECT(data,'$.四、视频传播性与观众.观众画像.推测观众的地域') AS 推测观众地域
----        ,GET_JSON_OBJECT(data,'$.五、音画细节.音频细节.视频中的音频信息，是否有歌曲?') AS 是否有歌曲
----        ,GET_JSON_OBJECT(data,'$.五、音画细节.音频细节.视频中的音频信息，歌曲名是什么?') AS 歌曲名
----        ,GET_JSON_OBJECT(data,'$.五、音画细节.视频水印.是否有产品名的水印?') AS 是否有水印
----        ,GET_JSON_OBJECT(data,'$.五、音画细节.视频字幕.是否有字幕?') AS 是否有字幕
----        ,GET_JSON_OBJECT(data,'$.五、音画细节.视频字幕.字幕的颜色') AS 字幕颜色
----        ,GET_JSON_OBJECT(data,'$.五、音画细节.视频字幕.字幕的位置如何') AS 字幕位置
----        ,GET_JSON_OBJECT(data,'$.五、音画细节.视频口播') AS 视频口播
----        ,GET_JSON_OBJECT(data,'$.六、人物与场景.知名人物.视频或音频中是否出现知名人物?') AS 是否有知名人物
----        ,GET_JSON_OBJECT(data,'$.六、人物与场景.人物年龄段') AS 人物年龄段
----        ,GET_JSON_OBJECT(data,'$.六、人物与场景.场景描述') AS 场景描述
----        ,GET_JSON_OBJECT(data,'$.七、时效性与分类.视频一级分类') AS 视频一级分类
----        ,GET_JSON_OBJECT(data,'$.七、时效性与分类.二级分类') AS 模型判断二级分类
----FROM    loghubods.video_tag_info_per5min
----WHERE   dt REGEXP '20241110'
----;
----
----1101prompt
---- 创建分区表语句
--CREATE TABLE loghubods.aitags_basedata
--(
--    video_id     STRING
--    ,video_title STRING
--    ,video_url   STRING
--    -- 基础信息
--    ,视觉音乐文字      STRING
--    ,内容选题        STRING
--    ,视频主题        STRING
--    ,视频关键词       STRING
--    -- 主体和场景
--    ,视频主体        STRING
--    ,视频场景        STRING
--    -- 情感与风格
--    ,情感倾向        STRING
--    ,视频风格        STRING
--    -- 视频传播性与画像
--    ,是否有片尾引导     STRING
--    ,引导时长        BIGINT
--    ,引导强度        STRING
--    ,传播性判断       STRING
--    ,推测观众地域      STRING
--    ,推测观众年龄段     STRING
--    ,推测观众性别      STRING
--    ,推测观众价值类型    STRING
--    ,推测观众用户价值点   STRING
--    ,推测观众用观众收入   STRING
--    -- 音画细节
--    ,背景音类型       STRING
--    ,背景音风格       STRING
--    ,语音类型        STRING
--    ,歌曲名         STRING
--    ,音色          STRING
--    ,产品水印        STRING
--    ,产品名称        STRING
--    ,字幕          STRING
--    ,颜色          STRING
--    ,字号          STRING
--    ,位置          STRING
--    ,视频口播        STRING
--    -- 封面信息
--    ,封面主体        STRING
--    ,人物个数        BIGINT
--    ,文字数量        BIGINT
--    ,文字关键字       STRING
--    ,封面主题        STRING
--    -- 人物与场景
--    ,知名人物        STRING
--    ,人物年龄段       STRING
--    ,场景描述        STRING
--    -- 时效性与分类
--    ,时效性_有无时效        STRING
--        ,时效性_具体时间        STRING
--
--    ,视频一级分类      STRING
--    ,二级分类        STRING
--    ,tag_1       STRING
--    ,tag_name_1  STRING
--    ,tag_score_1 BIGINT
--    ,tag_2       STRING
--    ,tag_name_2  STRING
--    ,tag_score_2 BIGINT
--    ,tag_3       STRING
--    ,tag_name_3  STRING
--    ,tag_score_3 BIGINT
--)
--PARTITIONED BY 
--(
--    dt           STRING
--)
--;
-- 删除指定分区内的数据
--ALTER TABLE loghubods.aitags_basedata  DROP PARTITION (dt = '20241101');
--DROP TABLE IF EXISTS loghubods.aitags_basedata


INSERT OVERWRITE TABLE loghubods.aitags_basedata PARTITION (dt = '${bizdate}')
SELECT  video_id
        ,video_title
        ,video_url --基础信息
        ,GET_JSON_OBJECT(data,'$.一、基础信息.关键维度') AS 视觉音乐文字
        ,GET_JSON_OBJECT(data,'$.一、基础信息.内容选题') AS 内容选题
        ,GET_JSON_OBJECT(data,'$.一、基础信息.视频主题') AS 视频主题
        ,GET_JSON_OBJECT(data,'$.一、基础信息.视频关键词') AS 视频关键词 --主体和场景
        ,GET_JSON_OBJECT(data,'$.二、主体和场景.视频主体') AS 视频主体
        ,GET_JSON_OBJECT(data,'$.二、主体和场景.视频场景') AS 视频场景 --情感与风格
        ,GET_JSON_OBJECT(data,'$.三、情感与风格.情感倾向') AS 情感倾向
        ,GET_JSON_OBJECT(data,'$.三、情感与风格.视频风格') AS 视频风格 --视频传播性与画像
        ,GET_JSON_OBJECT(data,'$.四、视频传播性与画像.片尾引导.有无') AS 是否有片尾引导
        ,GET_JSON_OBJECT(data,'$.四、视频传播性与画像.片尾引导.引导时长') AS 引导时长
        ,GET_JSON_OBJECT(data,'$.四、视频传播性与画像.片尾引导.引导强度') AS 引导强度
        ,GET_JSON_OBJECT(data,'$.四、视频传播性与画像.传播性判断') AS 传播性判断
        ,GET_JSON_OBJECT(data,'$.四、视频传播性与画像.视频用户画像.地域') AS 推测观众地域
        ,GET_JSON_OBJECT(data,'$.四、视频传播性与画像.视频用户画像.年龄段') AS 推测观众年龄段
        ,GET_JSON_OBJECT(data,'$.四、视频传播性与画像.视频用户画像.性别') AS 推测观众性别
        ,GET_JSON_OBJECT(data,'$.四、视频传播性与画像.视频用户画像.价值类型') AS 推测观众价值类型
        ,GET_JSON_OBJECT(data,'$.四、视频传播性与画像.视频用户画像.用户价值点') AS 推测观众用户价值点
        ,GET_JSON_OBJECT(data,'$.四、视频传播性与画像.视频用户画像.观众收入') AS 推测观众用观众收入 --音画细节
        ,GET_JSON_OBJECT(data,'$.五、音画细节.音频细节.背景音类型') AS 背景音类型
        ,GET_JSON_OBJECT(data,'$.五、音画细节.音频细节.背景音风格') AS 背景音风格
        ,GET_JSON_OBJECT(data,'$.五、音画细节.音频细节.语音类型') AS 语音类型
        ,GET_JSON_OBJECT(data,'$.五、音画细节.音频细节.歌曲名') AS 歌曲名
        ,GET_JSON_OBJECT(data,'$.五、音画细节.音频细节.音色') AS 音色
        ,GET_JSON_OBJECT(data,'$.五、音画细节.视频水印.产品水印') AS 产品水印
        ,GET_JSON_OBJECT(data,'$.五、音画细节.视频水印.产品名称') AS 产品名称
        ,GET_JSON_OBJECT(data,'$.五、音画细节.视频字幕.字幕') AS 字幕
        ,GET_JSON_OBJECT(data,'$.五、音画细节.视频字幕.颜色') AS 颜色
        ,GET_JSON_OBJECT(data,'$.五、音画细节.视频字幕.字号') AS 字号
        ,GET_JSON_OBJECT(data,'$.五、音画细节.视频字幕.位置') AS 位置
        ,GET_JSON_OBJECT(data,'$.五、音画细节.视频口播') AS 视频口播 --封面信息
        ,GET_JSON_OBJECT(data,'$.六、封面信息.封面主体') AS 封面主体
        ,GET_JSON_OBJECT(data,'$.六、封面信息.人物个数') AS 人物个数
        ,GET_JSON_OBJECT(data,'$.六、封面信息.文字数量') AS 文字数量
        ,GET_JSON_OBJECT(data,'$.六、封面信息.文字关键字') AS 文字关键字
        ,GET_JSON_OBJECT(data,'$.六、封面信息.封面主题') AS 封面主题 --人物与场景
        ,GET_JSON_OBJECT(data,'$.七、人物与场景.知名人物') AS 知名人物
        ,GET_JSON_OBJECT(data,'$.七、人物与场景.人物年龄段') AS 人物年龄段
        ,GET_JSON_OBJECT(data,'$.七、人物与场景.场景描述') AS 场景描述 --时效性与分类
        ,GET_JSON_OBJECT(data,'$.八、时效性与分类.时效性.有无时效') AS 时效性有无时效
        ,GET_JSON_OBJECT(data,'$.八、时效性与分类.时效性.具体时间') AS 时效性具体时间
        ,CASE   WHEN GET_JSON_OBJECT(DATA,'$.八、时效性与分类.视频一级分类') IN ('一级品类_音乐','一级品类_剧情 / 剧情演绎','一级品类_二次元','一级品类_游戏','一级品类_公益','一级品类_随拍 / 颜值','一级品类_舞蹈','一级品类_动物 / 萌宠','一级品类_三农','一级品类_科技 / 科技数码','一级品类_财经','一级品类_母婴 / 母婴亲子','一级品类_法律 / 人文社科','一级品类_科普 / 人文社科','一级品类_情感 / 情感心理','一级品类_职场 / 人文社科','一级品类_教育 / 教育培训','一级品类_摄影摄像','一级品类_艺术 / 才艺技能','一级品类_美食','一级品类_旅行 / 旅游','一级品类_地域本地','一级品类_时尚 / 时尚 / 美妆','一级品类_文化 / 人文社科','一级品类_搞笑 / 休闲娱乐','一级品类_明星 / 名人','一级品类_综艺','一级品类_影视综艺','一级品类_电影','一级品类_电视剧','一级品类_汽车','一级品类_体育 / 运动','一级品类_医疗健康 / 长寿 / 健身','一级品类_生活记录 / 生活','一级品类_生活家居 / 家居家装','一级品类_时政社会','一级品类_奇人异象','一级品类_历史','一级品类_军事','一级品类_宗教','一级品类_短剧','一级品类_收藏品') THEN GET_JSON_OBJECT(DATA,'$.八、时效性与分类.视频一级分类')
                ELSE '无'
        END AS 视频一级分类
        ,GET_JSON_OBJECT(DATA,'$.八、时效性与分类.二级分类') AS 二级分类
        ,GET_JSON_OBJECT(data,'$.八、时效性与分类.二级分类[0]') AS tag_1
        ,CASE   WHEN REGEXP_REPLACE(SPLIT(GET_JSON_OBJECT(data,'$.八、时效性与分类.二级分类[0]'),'、')[0],'品类-','') IN ('祝福音乐','中国战争史','中国历史影像','知识科普','正能量剧情','杂技柔术','早中晚好','益智解密','饮食健康','戏曲戏剧','未来科幻','天气变化','他国政策','贪污腐败','书法','食品安全','社会风气','生活小妙招','生活技巧科普','省份城市亮点','人生忠告','人财诈骗','亲子日常','亲情音乐','木工','魔术特效','迷信祝福','民族异域音乐','民生政策','名画赏析','美食教程','麻将','旅行攻略','历史名人','老综艺影像','老年相关法律科普','老年时尚','老年审美美女','老年生活','老明星','惊奇事件','节日祝福','健身操','健康知识','惠民新闻','绘画','怀念时光','红歌老歌','罕见画面','国际文化','国家统一','国家力量','国家科技力量','搞笑段子','风景实拍','对口型表演','动物萌宠','动物表演','大型集体艺术','当代正能量人物','传统文化','吃播探店','长寿知识','本地生活','K12教育','(老)电影切片') THEN REGEXP_REPLACE(SPLIT(GET_JSON_OBJECT(data,'$.八、时效性与分类.二级分类[0]'),'、')[0],'品类-','')
                ELSE '无'
        END AS tag_name_1
        ,REGEXP_REPLACE(SPLIT(GET_JSON_OBJECT(data,'$.八、时效性与分类.二级分类[0]'),'、')[1],'分数-','') AS tag_score_1
        ,GET_JSON_OBJECT(data,'$.八、时效性与分类.二级分类[1]') AS tag_2
        ,CASE   WHEN REGEXP_REPLACE(SPLIT(GET_JSON_OBJECT(data,'$.八、时效性与分类.二级分类[1]'),'、')[0],'品类-','') IN ('祝福音乐','中国战争史','中国历史影像','知识科普','正能量剧情','杂技柔术','早中晚好','益智解密','饮食健康','戏曲戏剧','未来科幻','天气变化','他国政策','贪污腐败','书法','食品安全','社会风气','生活小妙招','生活技巧科普','省份城市亮点','人生忠告','人财诈骗','亲子日常','亲情音乐','木工','魔术特效','迷信祝福','民族异域音乐','民生政策','名画赏析','美食教程','麻将','旅行攻略','历史名人','老综艺影像','老年相关法律科普','老年时尚','老年审美美女','老年生活','老明星','惊奇事件','节日祝福','健身操','健康知识','惠民新闻','绘画','怀念时光','红歌老歌','罕见画面','国际文化','国家统一','国家力量','国家科技力量','搞笑段子','风景实拍','对口型表演','动物萌宠','动物表演','大型集体艺术','当代正能量人物','传统文化','吃播探店','长寿知识','本地生活','K12教育','(老)电影切片') THEN REGEXP_REPLACE(SPLIT(GET_JSON_OBJECT(data,'$.八、时效性与分类.二级分类[1]'),'、')[0],'品类-','') ELSE '无' END AS tag_name_2
        ,REGEXP_REPLACE(SPLIT(GET_JSON_OBJECT(data,'$.八、时效性与分类.二级分类[1]'),'、')[1],'分数-','') AS tag_score_2
        ,GET_JSON_OBJECT(data,'$.八、时效性与分类.二级分类[2]') AS tag_3
        ,CASE   WHEN REGEXP_REPLACE(SPLIT(GET_JSON_OBJECT(data,'$.八、时效性与分类.二级分类[2]'),'、')[0],'品类-','') IN ('祝福音乐','中国战争史','中国历史影像','知识科普','正能量剧情','杂技柔术','早中晚好','益智解密','饮食健康','戏曲戏剧','未来科幻','天气变化','他国政策','贪污腐败','书法','食品安全','社会风气','生活小妙招','生活技巧科普','省份城市亮点','人生忠告','人财诈骗','亲子日常','亲情音乐','木工','魔术特效','迷信祝福','民族异域音乐','民生政策','名画赏析','美食教程','麻将','旅行攻略','历史名人','老综艺影像','老年相关法律科普','老年时尚','老年审美美女','老年生活','老明星','惊奇事件','节日祝福','健身操','健康知识','惠民新闻','绘画','怀念时光','红歌老歌','罕见画面','国际文化','国家统一','国家力量','国家科技力量','搞笑段子','风景实拍','对口型表演','动物萌宠','动物表演','大型集体艺术','当代正能量人物','传统文化','吃播探店','长寿知识','本地生活','K12教育','(老)电影切片') THEN REGEXP_REPLACE(SPLIT(GET_JSON_OBJECT(data,'$.八、时效性与分类.二级分类[2]'),'、')[0],'品类-','')ELSE '无' END AS tag_name_3
        ,REGEXP_REPLACE(SPLIT(GET_JSON_OBJECT(data,'$.八、时效性与分类.二级分类[2]'),'、')[1],'分数-','') AS tag_score_3
        ,regexp_replace(GET_JSON_OBJECT(GET_JSON_OBJECT(data,'$.八、时效性与分类.节日时效性'),'$.具体时效') , '\\[|"|\\]', '') AS 具体时效 
        ,regexp_replace(GET_JSON_OBJECT(GET_JSON_OBJECT(data,'$.八、时效性与分类.节日时效性'),'$.是否节日视频') , '\\[|"|\\]', '') as 是否节日视频
        ,regexp_replace(GET_JSON_OBJECT(GET_JSON_OBJECT(data,'$.八、时效性与分类.节日时效性'),'$.节日名称') , '\\[|"|\\]', '')  as 节日名称

        ,regexp_replace(GET_JSON_OBJECT(GET_JSON_OBJECT(data,'$.九、热点信息'),'$.是否存在热点') , '\\[|"|\\]', '')  as 是否存在热点
        ,regexp_replace(GET_JSON_OBJECT(GET_JSON_OBJECT(data,'$.九、热点信息'),'$.该热点的特征') , '\\[|"|\\]', '')  as 该热点的特征
        ,regexp_replace(GET_JSON_OBJECT(GET_JSON_OBJECT(data,'$.九、热点信息'),'$.热点内容概括') , '\\[|"|\\]', '')  as 热点内容概括
        ,regexp_replace(GET_JSON_OBJECT(GET_JSON_OBJECT(data,'$.九、热点信息'),'$.判断是热点的原因') , '\\[|"|\\]', '')  as 判断是热点的原因
FROM    loghubods.result_log_day 
WHERE   dt = '${bizdate}'
;