-- Task: 视频标签_title_druation_无分区  ID: 1021799017  Type: ODPS_SQL
--odps sql 
--********************************************************************--
--author:杜崇宇
--create time:2024-09-27 15:18:24
--********************************************************************--
--重复内容标签
--DROP TABLE IF EXISTS loghubods.content_ai_tags_no_dt;
--CREATE TABLE IF NOT EXISTS loghubods.content_ai_tags_no_dt
--(
--    type            STRING COMMENT ''
--    ,videoid        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内容标签'

INSERT OVERWRITE TABLE loghubods.content_ai_tags_no_dt 
SELECT  DISTINCT type
        ,videoid
        ,title_duration
        ,video_path
        ,tags
        ,tag_1
        ,CASE   WHEN INSTR(tag_name_1,'{') > 0 THEN GET_JSON_OBJECT(tag_name_1,'$.品类')  
                
                ELSE SUBSTRING_INDEX(tag_name_1,',',1)
        END AS processed_tag_name_1
        ,tag_score_1
        ,tag_2
        ,CASE   WHEN INSTR(tag_name_2,'{') > 0 THEN GET_JSON_OBJECT(tag_name_2,'$.品类')  
                ELSE SUBSTRING_INDEX(tag_name_2,',',1)
        END AS processed_tag_name_2
        ,tag_score_2
        ,tag_3
        ,CASE   WHEN INSTR(tag_name_3,'{') > 0 THEN  GET_JSON_OBJECT(tag_name_3,'$.品类')  
                ELSE SUBSTRING_INDEX(tag_name_3,',',1)
        END AS processed_tag_name_3
        ,tag_score_3
FROM    (
            SELECT  DISTINCT type
                    ,videoid
                    ,title_duration
                    ,video_path
                    ,tags
                    ,tag_1
                    ,tag_name_1
                    ,tag_score_1
                    ,tag_2
                    ,tag_name_2
                    ,tag_score_2
                    ,tag_3
                    ,tag_name_3
                    ,tag_score_3
            FROM    (
                        SELECT  type
                                ,videoid
                                ,title_duration
                                ,video_path
                                ,tags
                                ,tag_1
                                ,tag_name_1
                                ,tag_score_1
                                ,tag_2
                                ,tag_name_2
                                ,tag_score_2
                                ,tag_3
                                ,tag_name_3
                                ,tag_score_3
                                ,ROW_NUMBER() OVER (PARTITION BY title_duration ORDER BY videoid DESC ) AS rank
                        FROM    loghubods.video_ai_tags
                        WHERE   dt <= '${bizdate}${hh}'
                        AND     type != 'test'
                    ) 
            WHERE   rank = 1
        ) 
HAVING  processed_tag_name_1 NOT REGEXP ':|：|,|，|{|分数|-|-'