data_analysis/production_code/loghubods.vid_festive_labels.sql

-- Task: 节日标签视频库  ID: 1019838087  Type: ODPS_SQL
--odps sql 
--********************************************************************--
--author:于卓异
--create time:2024-08-29 17:16:57
--********************************************************************--
CREATE TABLE IF NOT EXISTS vid_festive_labels(
  videoid BIGINT COMMENT '视频ID',
   first_labels STRING COMMENT '一级标签',
   secondary_labels STRING COMMENT '二级标签'
) 
STORED AS ALIORC ;






INSERT INTO  TABLE vid_festive_labels 
SELECT  videoid
        ,CASE   WHEN secondary_labels IN ('元旦','腊八节','小年','除夕','春节','情人节','元宵节','龙抬头','妇女节','劳动节','母亲节','儿童节','端午节','父亲节','建党节','七七事变','建军节','七夕节','中元节','中秋节','毛主席逝世','国庆节','重阳节','感恩节','公祭日','平安夜','圣诞节','毛主席诞辰','初一','初二','初三','初四','初五','初六','初七','初八','初九','初十') THEN '节假日'
                WHEN secondary_labels IN ('小寒','大寒','立春','雨水','惊蛰','春分','清明','谷雨','立夏','小满','芒种','夏至','小暑','大暑','立秋','处暑','白露','秋分','寒露','霜降','立冬','小雪','大雪','冬至') THEN '节气'
                WHEN secondary_labels IN ('早上好','中午好','下午好','晚上好','晚安') THEN '问候语'
                WHEN secondary_labels IN ('祝福') THEN '祝福语'
                WHEN secondary_labels IN ('孙中山诞辰','孙中山逝世','周恩来诞辰','周恩来逝世','邓小平诞辰','邓小平逝世','李克强诞辰','李克强逝世','袁隆平诞辰','袁隆平逝世','彭德怀诞辰','彭德怀逝世','朱德诞辰','朱德逝世','吴尊友逝世') THEN '人物'
                WHEN secondary_labels IN ('两会','315国际消费者权益日','四龙日') THEN '热点'
        END AS first_labels
        ,secondary_labels
FROM    (
            SELECT  id AS videoid
                    ,title
                    ,CASE   WHEN title REGEXP '元旦' THEN '元旦'
                            WHEN title REGEXP '腊八' THEN '腊八节'
                            WHEN title REGEXP '小年' THEN '小年'
                            WHEN title REGEXP '除夕' THEN '除夕'
                            WHEN title REGEXP '春节|新春佳节|新年|过年|大年' THEN '春节'
                            WHEN title REGEXP '初一|迎春日|开门红|新春' THEN '初一'
                            WHEN title REGEXP '初二|犯娘日|迎婿日|回娘家|祭财神' THEN '初二'
                            WHEN title REGEXP '初三|赤狗日|福神' THEN '初三'
                            WHEN title REGEXP '初四|迎接神灵日|灶神|喜神|五路|扔穷' THEN '初四'
                            WHEN title REGEXP '初五|破五|迎财神|送穷神|财神日|五穷' THEN '初五'
                            WHEN title REGEXP '初六' THEN '初六'
                            WHEN title REGEXP '初七' THEN '初七'
                            WHEN title REGEXP '初八' THEN '初八'
                            WHEN title REGEXP '初九' THEN '初九'
                            WHEN title REGEXP '初十' THEN '初十'
                            WHEN title REGEXP '情人节' THEN '情人节'
                            WHEN title REGEXP '元宵|正月十五' THEN '元宵节'
                            WHEN title REGEXP '龙抬头|二月二|2月2|剃头日|春耕节|农事节|青龙节|春龙节|龙头节' THEN '龙抬头'
                            WHEN title REGEXP '妇女节|三八|38节|38节快乐|38节祝福|女神节|女王节' THEN '妇女节'
                            WHEN title REGEXP '5.1|5·1|五一|劳动节|国际示威游行日|51' THEN '劳动节'
                            WHEN title REGEXP '母亲节' THEN '母亲节'
                            WHEN title REGEXP '儿童节|六一' THEN '儿童节'
                            WHEN title REGEXP '端午' THEN '端午节'
                            WHEN title REGEXP '父亲节' THEN '父亲节'
                            WHEN title REGEXP '七一|建党' THEN '建党节'
                            WHEN title REGEXP '七七事变' THEN '七七事变'
                            WHEN title REGEXP '建军节|八一' THEN '建军节'
                            WHEN title REGEXP '七夕' THEN '七夕节'
                            WHEN title REGEXP '鬼节|中元节' THEN '中元节'
                            WHEN title REGEXP '中秋|元旦将至' THEN '中秋节'
                            WHEN title REGEXP '毛主席逝世' THEN '毛主席逝世'
                            WHEN title REGEXP '国庆' THEN '国庆节'
                            WHEN title REGEXP '重阳' THEN '重阳节'
                            WHEN title REGEXP '感恩节' THEN '感恩节'
                            WHEN title REGEXP '公祭日|南京大屠杀' THEN '公祭日'
                            WHEN title REGEXP '平安夜' THEN '平安夜'
                            WHEN title REGEXP '圣诞' THEN '圣诞节'
                            WHEN title REGEXP '毛主席诞辰' THEN '毛主席诞辰'
                            WHEN title REGEXP '小寒' THEN '小寒'
                            WHEN title REGEXP '大寒' THEN '大寒'
                            WHEN title REGEXP '立春' THEN '立春'
                            WHEN title REGEXP '雨水' THEN '雨水'
                            WHEN title REGEXP '惊蛰' THEN '惊蛰'
                            WHEN title REGEXP '春分' THEN '春分'
                            WHEN title REGEXP '清明|踏青节|踏青节|行清节|三月节|祭祖' THEN '清明'
                            WHEN title REGEXP '谷雨' THEN '谷雨'
                            WHEN title REGEXP '立夏' THEN '立夏'
                            WHEN title REGEXP '小满' THEN '小满'
                            WHEN title REGEXP '芒种' THEN '芒种'
                            WHEN title REGEXP '夏至' THEN '夏至'
                            WHEN title REGEXP '小暑' THEN '小暑'
                            WHEN title REGEXP '大暑' THEN '大暑'
                            WHEN title REGEXP '立秋' THEN '立秋'
                            WHEN title REGEXP '处暑' THEN '处暑'
                            WHEN title REGEXP '白露' THEN '白露'
                            WHEN title REGEXP '秋分' THEN '秋分'
                            WHEN title REGEXP '寒露' THEN '寒露'
                            WHEN title REGEXP '霜降' THEN '霜降'
                            WHEN title REGEXP '立冬' THEN '立冬'
                            WHEN title REGEXP '小雪' THEN '小雪'
                            WHEN title REGEXP '大雪' THEN '大雪'
                            WHEN title REGEXP '冬至' THEN '冬至'
                            WHEN title REGEXP '早晨|早上|清晨' THEN '早上好'
                            WHEN title REGEXP '中午好|中午' THEN '中午好'
                            WHEN title REGEXP '下午好|下午' THEN '下午好'
                            WHEN title REGEXP '晚上好|晚上' THEN '晚上好'
                            WHEN title REGEXP '晚安' THEN '晚安'
                            WHEN title REGEXP '祝福|祝愿|祝你|祝贺|祝大家|祝您|祝好运|祝群主|祝朋友' THEN '祝福'
                            WHEN title REGEXP '孙中山诞辰' THEN '孙中山诞辰'
                            WHEN title REGEXP '孙中山逝世' THEN '孙中山逝世'
                            WHEN title REGEXP '周恩来诞辰' THEN '周恩来诞辰'
                            WHEN title REGEXP '周恩来逝世' THEN '周恩来逝世'
                            WHEN title REGEXP '邓小平诞辰' THEN '邓小平诞辰'
                            WHEN title REGEXP '邓小平逝世' THEN '邓小平逝世'
                            WHEN title REGEXP '李克强诞辰' THEN '李克强诞辰'
                            WHEN title REGEXP '李克强逝世' THEN '李克强逝世'
                            WHEN title REGEXP '袁隆平诞辰' THEN '袁隆平诞辰'
                            WHEN title REGEXP '袁隆平逝世' THEN '袁隆平逝世'
                            WHEN title REGEXP '彭德怀诞辰' THEN '彭德怀诞辰'
                            WHEN title REGEXP '彭德怀逝世' THEN '彭德怀逝世'
                            WHEN title REGEXP '朱德诞辰' THEN '朱德诞辰'
                            WHEN title REGEXP '朱德逝世' THEN '朱德逝世'
                            WHEN title REGEXP '吴尊友逝世' THEN '吴尊友逝世'
                            WHEN title REGEXP '两会|人大代表|人民代表大会|政协' THEN '两会'
                            WHEN title REGEXP '315|消费者|三一五|3·15|打假日' THEN '315国际消费者权益日'
                            WHEN title REGEXP '四龙日|龙年龙月龙日龙时' THEN '四龙日'
                    END AS secondary_labels
                    ,gmt_create
                    ,recommend_status
            FROM    videoods.wx_video_per1h
            WHERE   title NOT REGEXP '2018|2019|2020|2021|2022|2023|鼠年|牛年|虎年|兔年|小年糕|小年轻' --AND     TO_CHAR(gmt_create,'YYYYMMDDHH') = '${datetime}${hhl}' 
            AND     TO_CHAR(gmt_create,'YYYYMMDDHH') = '${datetime}${hhl}'
            --AND     TO_CHAR(gmt_create,'YYYYMMDDHH') <= '${datetime}${hhl}'
            AND     recommend_status IN (-6,-7)
        ) 
        where '${hhl}'<>23
;
INSERT INTO TABLE vid_festive_labels 
SELECT  videoid
        ,CASE   WHEN secondary_labels IN ('元旦','腊八节','小年','除夕','春节','情人节','元宵节','龙抬头','妇女节','劳动节','母亲节','儿童节','端午节','父亲节','建党节','七七事变','建军节','七夕节','中元节','中秋节','毛主席逝世','国庆节','重阳节','感恩节','公祭日','平安夜','圣诞节','毛主席诞辰','初一','初二','初三','初四','初五','初六','初七','初八','初九','初十') THEN '节假日'
                WHEN secondary_labels IN ('小寒','大寒','立春','雨水','惊蛰','春分','清明','谷雨','立夏','小满','芒种','夏至','小暑','大暑','立秋','处暑','白露','秋分','寒露','霜降','立冬','小雪','大雪','冬至') THEN '节气'
                WHEN secondary_labels IN ('早上好','中午好','下午好','晚上好','晚安') THEN '问候语'
                WHEN secondary_labels IN ('祝福') THEN '祝福语'
                WHEN secondary_labels IN ('孙中山诞辰','孙中山逝世','周恩来诞辰','周恩来逝世','邓小平诞辰','邓小平逝世','李克强诞辰','李克强逝世','袁隆平诞辰','袁隆平逝世','彭德怀诞辰','彭德怀逝世','朱德诞辰','朱德逝世','吴尊友逝世') THEN '人物'
                WHEN secondary_labels IN ('两会','315国际消费者权益日','四龙日') THEN '热点'
        END AS first_labels
        ,secondary_labels
FROM    (
            SELECT  id AS videoid
                    ,title
                    ,CASE   WHEN title REGEXP '元旦' THEN '元旦'
                            WHEN title REGEXP '腊八' THEN '腊八节'
                            WHEN title REGEXP '小年' THEN '小年'
                            WHEN title REGEXP '除夕' THEN '除夕'
                            WHEN title REGEXP '春节|新春佳节|新年|过年|大年' THEN '春节'
                            WHEN title REGEXP '初一|迎春日|开门红|新春' THEN '初一'
                            WHEN title REGEXP '初二|犯娘日|迎婿日|回娘家|祭财神' THEN '初二'
                            WHEN title REGEXP '初三|赤狗日|福神' THEN '初三'
                            WHEN title REGEXP '初四|迎接神灵日|灶神|喜神|五路|扔穷' THEN '初四'
                            WHEN title REGEXP '初五|破五|迎财神|送穷神|财神日|五穷' THEN '初五'
                            WHEN title REGEXP '初六' THEN '初六'
                            WHEN title REGEXP '初七' THEN '初七'
                            WHEN title REGEXP '初八' THEN '初八'
                            WHEN title REGEXP '初九' THEN '初九'
                            WHEN title REGEXP '初十' THEN '初十'
                            WHEN title REGEXP '情人节' THEN '情人节'
                            WHEN title REGEXP '元宵|正月十五' THEN '元宵节'
                            WHEN title REGEXP '龙抬头|二月二|2月2|剃头日|春耕节|农事节|青龙节|春龙节|龙头节' THEN '龙抬头'
                            WHEN title REGEXP '妇女节|三八|38节|38节快乐|38节祝福|女神节|女王节' THEN '妇女节'
                            WHEN title REGEXP '5.1|5·1|五一|劳动节|国际示威游行日|51' THEN '劳动节'
                            WHEN title REGEXP '母亲节' THEN '母亲节'
                            WHEN title REGEXP '儿童节|六一' THEN '儿童节'
                            WHEN title REGEXP '端午' THEN '端午节'
                            WHEN title REGEXP '父亲节' THEN '父亲节'
                            WHEN title REGEXP '七一|建党' THEN '建党节'
                            WHEN title REGEXP '七七事变' THEN '七七事变'
                            WHEN title REGEXP '建军节|八一' THEN '建军节'
                            WHEN title REGEXP '七夕' THEN '七夕节'
                            WHEN title REGEXP '鬼节|中元节' THEN '中元节'
                            WHEN title REGEXP '中秋|元旦将至' THEN '中秋节'
                            WHEN title REGEXP '毛主席逝世' THEN '毛主席逝世'
                            WHEN title REGEXP '国庆' THEN '国庆节'
                            WHEN title REGEXP '重阳' THEN '重阳节'
                            WHEN title REGEXP '感恩节' THEN '感恩节'
                            WHEN title REGEXP '公祭日|南京大屠杀' THEN '公祭日'
                            WHEN title REGEXP '平安夜' THEN '平安夜'
                            WHEN title REGEXP '圣诞' THEN '圣诞节'
                            WHEN title REGEXP '毛主席诞辰' THEN '毛主席诞辰'
                            WHEN title REGEXP '小寒' THEN '小寒'
                            WHEN title REGEXP '大寒' THEN '大寒'
                            WHEN title REGEXP '立春' THEN '立春'
                            WHEN title REGEXP '雨水' THEN '雨水'
                            WHEN title REGEXP '惊蛰' THEN '惊蛰'
                            WHEN title REGEXP '春分' THEN '春分'
                            WHEN title REGEXP '清明|踏青节|踏青节|行清节|三月节|祭祖' THEN '清明'
                            WHEN title REGEXP '谷雨' THEN '谷雨'
                            WHEN title REGEXP '立夏' THEN '立夏'
                            WHEN title REGEXP '小满' THEN '小满'
                            WHEN title REGEXP '芒种' THEN '芒种'
                            WHEN title REGEXP '夏至' THEN '夏至'
                            WHEN title REGEXP '小暑' THEN '小暑'
                            WHEN title REGEXP '大暑' THEN '大暑'
                            WHEN title REGEXP '立秋' THEN '立秋'
                            WHEN title REGEXP '处暑' THEN '处暑'
                            WHEN title REGEXP '白露' THEN '白露'
                            WHEN title REGEXP '秋分' THEN '秋分'
                            WHEN title REGEXP '寒露' THEN '寒露'
                            WHEN title REGEXP '霜降' THEN '霜降'
                            WHEN title REGEXP '立冬' THEN '立冬'
                            WHEN title REGEXP '小雪' THEN '小雪'
                            WHEN title REGEXP '大雪' THEN '大雪'
                            WHEN title REGEXP '冬至' THEN '冬至'
                            WHEN title REGEXP '早晨|早上|清晨' THEN '早上好'
                            WHEN title REGEXP '中午好|中午' THEN '中午好'
                            WHEN title REGEXP '下午好|下午' THEN '下午好'
                            WHEN title REGEXP '晚上好|晚上' THEN '晚上好'
                            WHEN title REGEXP '晚安' THEN '晚安'
                            WHEN title REGEXP '祝福|祝愿|祝你|祝贺|祝大家|祝您|祝好运|祝群主|祝朋友' THEN '祝福'
                            WHEN title REGEXP '孙中山诞辰' THEN '孙中山诞辰'
                            WHEN title REGEXP '孙中山逝世' THEN '孙中山逝世'
                            WHEN title REGEXP '周恩来诞辰' THEN '周恩来诞辰'
                            WHEN title REGEXP '周恩来逝世' THEN '周恩来逝世'
                            WHEN title REGEXP '邓小平诞辰' THEN '邓小平诞辰'
                            WHEN title REGEXP '邓小平逝世' THEN '邓小平逝世'
                            WHEN title REGEXP '李克强诞辰' THEN '李克强诞辰'
                            WHEN title REGEXP '李克强逝世' THEN '李克强逝世'
                            WHEN title REGEXP '袁隆平诞辰' THEN '袁隆平诞辰'
                            WHEN title REGEXP '袁隆平逝世' THEN '袁隆平逝世'
                            WHEN title REGEXP '彭德怀诞辰' THEN '彭德怀诞辰'
                            WHEN title REGEXP '彭德怀逝世' THEN '彭德怀逝世'
                            WHEN title REGEXP '朱德诞辰' THEN '朱德诞辰'
                            WHEN title REGEXP '朱德逝世' THEN '朱德逝世'
                            WHEN title REGEXP '吴尊友逝世' THEN '吴尊友逝世'
                            WHEN title REGEXP '两会|人大代表|人民代表大会|政协' THEN '两会'
                            WHEN title REGEXP '315|消费者|三一五|3·15|打假日' THEN '315国际消费者权益日'
                            WHEN title REGEXP '四龙日|龙年龙月龙日龙时' THEN '四龙日'
                    END AS secondary_labels
                    ,gmt_create
                    ,recommend_status
            FROM    videoods.wx_video_per1h
            WHERE   title NOT REGEXP '2018|2019|2020|2021|2022|2023|鼠年|牛年|虎年|兔年|小年糕|小年轻' --AND     TO_CHAR(gmt_create,'YYYYMMDDHH') = '${datetime}${hhl}' 
            AND     TO_CHAR(gmt_create,'YYYYMMDDHH') = '${datetime-1}${hhl}'
            AND     recommend_status IN (-6,-7)
        ) 
        where '${hhl}'=23
;