-- Task: 提取重复视频结果  ID: 1021796755  Type: ODPS_SQL
----odps sql 
----********************************************************************--
----author:杜崇宇
----create time:2024-11-11 20:31:52
----********************************************************************--
--CREATE TABLE loghubods.aitags_repeatdata
--(
--        title_duration STRING
--,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
--)
--;
--DROP TABLE IF EXISTS loghubods.aitags_repeatdata
INSERT OVERWRITE TABLE loghubods.aitags_repeatdata PARTITION (dt = '${bizdate}')
SELECT  DISTINCT title_duration
        ,video_id
        ,video_title
        ,video_url
        ,视觉音乐文字
        ,内容选题
        ,视频主题
        ,视频关键词
        ,视频主体
        ,视频场景
        ,情感倾向
        ,视频风格
        ,是否有片尾引导
        ,引导时长
        ,引导强度
        ,传播性判断
        ,推测观众地域
        ,推测观众年龄段
        ,推测观众性别
        ,推测观众价值类型
        ,推测观众用户价值点
        ,推测观众用观众收入
        ,背景音类型
        ,背景音风格
        ,语音类型
        ,歌曲名
        ,音色
        ,产品水印
        ,产品名称
        ,字幕
        ,颜色
        ,字号
        ,位置
        ,视频口播
        ,封面主体
        ,人物个数
        ,文字数量
        ,文字关键字
        ,封面主题
        ,知名人物
        ,人物年龄段
        ,场景描述
        ,时效性_有无时效
        ,时效性_具体时间
        ,视频一级分类
        ,二级分类
        ,tag_1
        ,tag_name_1
        ,tag_score_1
        ,tag_2
        ,tag_name_2
        ,tag_score_2
        ,tag_3
        ,tag_name_3
        ,tag_score_3
        ,具体时效
        ,是否节日视频
        ,case WHEN 是否节日视频='是' and 节日名称 REGEXP '端午' THEN '端午节' 
              WHEN 是否节日视频='是' and 节日名称 REGEXP '儿童节|六一' THEN '儿童节' 
              WHEN 是否节日视频='是' and 节日名称 REGEXP '国庆' THEN '国庆节' 
              WHEN 是否节日视频='是' and 节日名称 REGEXP '父亲' THEN '父亲节' 
              WHEN 是否节日视频='是' and 节日名称 REGEXP '七夕' THEN '七夕节'
              WHEN 是否节日视频='是' and 节日名称 REGEXP '母亲节' THEN '母亲节' 
              WHEN 是否节日视频='是' and 节日名称 REGEXP '重阳' THEN '重阳节' 
              WHEN 是否节日视频='是' and 节日名称 REGEXP '妇女节|三八|38节|女神节|女王节' THEN '妇女节'
              WHEN 是否节日视频='是' and 节日名称 REGEXP '鬼节|中元' THEN '中元节'
              WHEN 是否节日视频='是' and 节日名称  REGEXP '中秋|元旦将至' THEN '中秋节'
              WHEN 是否节日视频='是' and 节日名称 REGEXP '元宵|正月十五' THEN '元宵节' 
              WHEN 是否节日视频='是' and 节日名称 REGEXP '建军节|八一' THEN '建军节'
              WHEN 是否节日视频='是' and 节日名称 REGEXP  '清明|踏青节|行清节|三月节|祭祖' THEN '清明'
              WHEN 是否节日视频='是' and 节日名称 REGEXP '七一|建党' THEN '建党节'
              WHEN 是否节日视频='是' and 节日名称 REGEXP '感恩' THEN '感恩节'
              WHEN 是否节日视频='是' and 节日名称 REGEXP '教师' THEN '教师节'
              WHEN 是否节日视频='是' and 节日名称 REGEXP '五四|青年节' THEN '青年节'
              WHEN 是否节日视频='是' and 节日名称 REGEXP '腊八' THEN '腊八节'
              ELSE 节日名称 end  节日名称
FROM    (
            SELECT  *
            FROM    (
                        SELECT  a.*
                                ,CONCAT(CLEAR_TITLE_SIGNAL(b.title),'-',total_time) AS title_duration
                                ,ROW_NUMBER() OVER (PARTITION BY CONCAT(CLEAR_TITLE_SIGNAL(b.title),'-',total_time) ORDER BY CAST(video_id AS BIGINT) DESC ) AS rank
                        FROM    loghubods.aitags_basedata a
                        LEFT JOIN videoods.wx_video b
                        ON      a.video_id = b.id
                        WHERE   dt <= '${bizdate}'
                        AND     tag_name_1 IS NOT NULL
                    ) 
            WHERE   rank = 1
        )