-- Task: 重复内容标签  ID: 1020979778  Type: ODPS_SQL
--odps sql 
--********************************************************************--
--author:杜崇宇
--create time:2024-09-25 17:09:30
--********************************************************************--
--CREATE TABLE IF NOT EXISTS loghubods.tag_level_2_base
--(
--    title_duration  STRING COMMENT '去emoji标题'
--    ,tag_level_2 STRING COMMENT '二级品类'
--)
--COMMENT '二级品类表'
-- ;
--
--DROP TABLE IF EXISTS  loghubods.tag_level_2_base;


INSERT OVERWRITE TABLE loghubods.tag_level_2_base
SELECT  DISTINCT title_duration
        ,二级品类
FROM    (
            SELECT  title_duration
                    ,二级品类
                    ,ROW_NUMBER() OVER (PARTITION BY title_duration ORDER BY id DESC ) AS rank
            FROM    (
                        SELECT  DISTINCT a.id
                                ,a.title
                                ,CLEAR_TITLE_SIGNAL(a.title) AS clear_title
                                ,a.total_time
                                ,CONCAT(CLEAR_TITLE_SIGNAL(a.title),'-',a.total_time) AS title_duration
                                ,SUBSTRING_INDEX(b.words_1,'-',-1) AS 二级品类
                        FROM    videoods.wx_video a
                        LEFT JOIN   (
                                        SELECT  DISTINCT a.video_id AS videoid
                                                ,b.tag_name AS words_1
                                        FROM    (
                                                    SELECT  video_id
                                                            ,tag_id
                                                    FROM    videoods.wx_video_tag_rel
                                                ) a
                                        LEFT JOIN   (
                                                        SELECT  tag_id
                                                                ,tag_name
                                                        FROM    videoods.wx_video_tag
                                                        WHERE   tag_name REGEXP '品类-'
                                                    ) b
                                        ON      a.tag_id = b.tag_id
                                        HAVING  words_1 IS NOT NULL
                                    ) b
                        ON      a.id = b.videoid
                        HAVING  二级品类 IS NOT NULL
                    ) 
        ) 
WHERE   rank = 1