-- Task: 视频标签_单内容  ID: 1021080500  Type: ODPS_SQL
--odps sql 
--********************************************************************--
--author:杜崇宇
--create time:2024-09-27 15:18:24
--********************************************************************--
--重复内容标签
--DROP TABLE IF EXISTS loghubods.video_ai_tags;
--CREATE TABLE IF NOT EXISTS loghubods.video_ai_tags
--(
--    type            STRING COMMENT ''
--    ,videoid        BIGINT COMMENT ''
--    ,title          STRING COMMENT ''
--    ,total_time     BIGINT COMMENT ''
--    ,title_duration STRING COMMENT ''
--    ,video_path     STRING COMMENT ''
--    ,tags           STRING COMMENT ''
--    ,tag_1          STRING COMMENT ''
--    ,tag_name_1     STRING COMMENT ''
--    ,tag_score_1    BIGINT COMMENT ''
--    ,tag_2          STRING COMMENT ''
--    ,tag_name_2     STRING COMMENT ''
--    ,tag_score_2    BIGINT COMMENT ''
--    ,tag_3          STRING COMMENT ''
--    ,tag_name_3     STRING COMMENT ''
--    ,tag_score_3    BIGINT COMMENT ''
--)
--COMMENT 'AI内容标签'
--PARTITIONED BY 
--(
--    dt              STRING COMMENT '分区'
--)
--LIFECYCLE 365
--;
--INSERT OVERWRITE TABLE loghubods.video_ai_tags PARTITION (dt = '${today}${hh}')
--SELECT  DISTINCT type AS type
--        ,video_id
--        ,a.video_title
--        ,b.total_time
--        ,CONCAT(CLEAR_TITLE_SIGNAL(b.title),'-',b.total_time) AS title_duration
--        ,video_url
--        ,GET_JSON_OBJECT(data,'$.八、时效性与分类.二级分类') AS tags
--        ,GET_JSON_OBJECT(data,'$.八、时效性与分类.二级分类[0]') AS tag_1
--        ,REGEXP_REPLACE(SPLIT(GET_JSON_OBJECT(data,'$.八、时效性与分类.二级分类[0]'),'、')[0],'品类-','') 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
--        ,REGEXP_REPLACE(SPLIT(GET_JSON_OBJECT(data,'$.八、时效性与分类.二级分类[1]'),'、')[0],'品类-','') 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
--        ,REGEXP_REPLACE(SPLIT(GET_JSON_OBJECT(data,'$.八、时效性与分类.二级分类[2]'),'、')[0],'品类-','') AS tag_name_3
--        ,REGEXP_REPLACE(SPLIT(GET_JSON_OBJECT(data,'$.八、时效性与分类.二级分类[2]'),'、')[1],'分数-','') AS tag_score_3
--FROM    loghubods.video_tag_info_per5min a
--LEFT JOIN videoods.wx_video_per1h b
--ON      a.video_id = b.id
--WHERE   dt >= '${today}${hh}00'
--AND     dt <= '${today}${hh}55'
--HAVING  tags IS NOT NULL


INSERT OVERWRITE TABLE loghubods.video_ai_tags PARTITION (dt = '${today}${hh}')


SELECT  DISTINCT type AS type
        ,video_id
        ,a.video_title
        ,b.total_time
        ,CONCAT(CLEAR_TITLE_SIGNAL(b.title),'-',b.total_time) AS title_duration
        ,video_url
        ,GET_JSON_OBJECT(data,'$.八、时效性与分类.二级分类') AS tags
        ,GET_JSON_OBJECT(data,'$.八、时效性与分类.二级分类[0]') AS tag_1
        ,REGEXP_REPLACE(SPLIT(GET_JSON_OBJECT(data,'$.八、时效性与分类.二级分类[0]'),'、')[0],'品类-','') 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
        ,REGEXP_REPLACE(SPLIT(GET_JSON_OBJECT(data,'$.八、时效性与分类.二级分类[1]'),'、')[0],'品类-','') 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
        ,REGEXP_REPLACE(SPLIT(GET_JSON_OBJECT(data,'$.八、时效性与分类.二级分类[2]'),'、')[0],'品类-','') AS tag_name_3
        ,REGEXP_REPLACE(SPLIT(GET_JSON_OBJECT(data,'$.八、时效性与分类.二级分类[2]'),'、')[1],'分数-','') AS tag_score_3
FROM    loghubods.result_log a
LEFT JOIN videoods.wx_video_per1h b
ON      a.video_id = b.id
WHERE   dt = '${today}${hh}'
HAVING  tags IS NOT NULL