data_analysis/production_code/loghubods.operators_channel_spider.sql

-- Task: 爬虫产品与负责人  ID: 1008392924  Type: ODPS_SQL
--odps sql 
--********************************************************************--
--odps sql 
--********************************************************************--
--author:杜崇宇
--create time:2022-11-13 03:10:25
--********************************************************************--
--CREATE TABLE IF NOT EXISTS loghubods.operators_channel_spider
--(
--    name  STRING COMMENT ''
--    ,channel STRING COMMENT ''
--    ,uid STRING COMMENT ''
--    ,videoid STRING COMMENT ''
--)
--COMMENT '爬虫渠道'; 
INSERT OVERWRITE TABLE operators_channel_spider
SELECT  DISTINCT a.name
        ,CASE   WHEN b.tags REGEXP '自制内容测试|自制内容搬运｜AIGC自制内容' THEN '自制'
                WHEN b.tags REGEXP '搬运工具' THEN '搬运工具'
                WHEN b.tags REGEXP '搬运改造' THEN '搬运改造'
                WHEN b.tags REGEXP '站内重发' THEN '站内重发'
                WHEN b.tags REGEXP '搬运测试' THEN '搬运测试'
                ELSE '搬运'
        END AS type
        ,a.uid
        ,a.videoid
FROM    (
            SELECT  DISTINCT 'transport' AS type
                    ,'内容运营' AS name
                    ,a.uid
                    ,b.id AS videoid
            FROM    (
                        SELECT  *
                        FROM    operators_channel_spider_base
                    ) a
            LEFT JOIN videoods.wx_video_per1h b
            ON      a.uid = b.uid
            WHERE   a.identity_tagname REGEXP 'transport'
            AND     a.identity_tagname NOT REGEXP '王知微|刘兆恒|阮望'
        ) a
LEFT JOIN   (
                SELECT  a.video_id AS videoid
                        ,CONCAT_WS(',',COLLECT_SET(b.tag_name)) AS tags
                FROM    (
                            SELECT  video_id
                                    ,tag_id
                            FROM    videoods.wx_video_tag_rel_per1h
                        ) a
                LEFT JOIN   (
                                SELECT  tag_id
                                        ,tag_name
                                FROM    videoods.wx_video_tag_per1h
                            ) b
                ON      a.tag_id = b.tag_id
                GROUP BY video_id
            ) b
ON      a.videoid = b.videoid
UNION
SELECT  DISTINCT a.name
        ,CASE   WHEN b.tags REGEXP '#str-老视频复推历史同期_54|#str-老视频复推中周期_57|#str-老视频复推短周期_56' THEN '老视频复推_周期'
                WHEN b.tags REGEXP '#str-老视频复推单视频_24|#str-老视频复推单视频_分发有效分享_24|#str-老视频复推单视频_有效分享_241|#str-老内容复推单视频重复_33|#str-老内容复推单视频重复_分发有效分享_33' THEN '老视频复推_视频'
                WHEN b.tags REGEXP '#str-老视频复推单品类_23|#str-老视频复推单品类_分发有效分享_23|#str-老视频复推混合品类_38' THEN '老视频复推_品类'
                ELSE '老视频复推_未归类'
        END AS type
        ,a.uid
        ,a.videoid
FROM    (
            SELECT  DISTINCT 'oldVideoPush' AS type
                    ,'杜崇宇' AS name
                    ,a.uid
                    ,b.id AS videoid
            FROM    (
                        SELECT  *
                        FROM    operators_channel_spider_base
                    ) a
            LEFT JOIN videoods.wx_video_per1h b
            ON      a.uid = b.uid
            WHERE   a.identity_tagname REGEXP 'oldVideoPush'
        ) a
LEFT JOIN   (
                SELECT  a.video_id AS videoid
                        ,CONCAT_WS(',',COLLECT_SET(b.tag_name)) AS tags
                FROM    (
                            SELECT  video_id
                                    ,tag_id
                            FROM    videoods.wx_video_tag_rel_per1h
                        ) a
                LEFT JOIN   (
                                SELECT  tag_id
                                        ,tag_name
                                FROM    videoods.wx_video_tag_per1h
                            ) b
                ON      a.tag_id = b.tag_id
                GROUP BY video_id
            ) b
ON      a.videoid = b.videoid
UNION
SELECT  DISTINCT CASE    WHEN a.identity_tagname REGEXP '鲁涛' THEN '鲁涛'
                WHEN a.identity_tagname REGEXP '余海涛' THEN '余海涛'
                WHEN a.identity_tagname REGEXP '范军' THEN '范军'
                WHEN a.identity_tagname REGEXP '罗情' THEN '罗情'
                WHEN a.identity_tagname REGEXP '刘诗雨' THEN '刘诗雨'
                WHEN a.identity_tagname REGEXP '阮望' THEN '阮望'
                WHEN a.identity_tagname REGEXP '王知微' THEN '王知微'
                WHEN a.identity_tagname REGEXP '尹梦莎' THEN '尹梦莎'
                WHEN a.identity_tagname REGEXP '信欣' THEN '信欣'
                WHEN a.identity_tagname REGEXP '刘梓漩' THEN '刘梓漩'
                WHEN a.identity_tagname REGEXP '刘兆恒' THEN '刘兆恒'
                WHEN a.identity_tagname REGEXP '张博' THEN '张博'
                 END AS name
        ,'aidit' AS channel
        ,a.uid
        ,b.id AS videoid
FROM    (
            SELECT  *
            FROM    operators_channel_spider_base
        ) a
LEFT JOIN videoods.wx_video_per1h b
ON      a.uid = b.uid
WHERE   a.identity_tagname REGEXP '机器制作视频'
AND     a.identity_tagname REGEXP 'AI自制'
UNION
SELECT  *
FROM    (
            SELECT  CASE    WHEN a.identity_tagname REGEXP '范军' THEN '范军'
                            WHEN a.identity_tagname REGEXP '鲁涛' THEN '鲁涛'
                            WHEN a.identity_tagname REGEXP '余海涛' THEN '余海涛'
                            WHEN a.identity_tagname REGEXP '罗情' THEN '罗情'
                            WHEN a.identity_tagname REGEXP '王知微' THEN '王知微'
                            WHEN a.identity_tagname REGEXP '刘诗雨' THEN '刘诗雨'
                            ELSE NULL
                    END AS name
                    ,CASE   WHEN a.identity_tagname REGEXP 'spider'
                                AND a.identity_tagname REGEXP '公众号爬虫新'
                                AND a.identity_tagname REGEXP '账号' THEN '公众号'
                            WHEN a.identity_tagname REGEXP '范军'
                                AND a.identity_tagname REGEXP '机器自动改造'
                                AND a.identity_tagname NOT REGEXP '单点视频' THEN '机器自动改造'
                            WHEN a.identity_tagname REGEXP '范军'
                                AND a.identity_tagname REGEXP '机器制作视频'
                                AND a.identity_tagname NOT REGEXP 'AI自制' THEN '机器制作视频'
                            WHEN a.identity_tagname REGEXP '鲁涛'
                                AND a.identity_tagname REGEXP '机器自动改造'
                                AND a.identity_tagname NOT REGEXP '单点视频' THEN '机器自动改造'
                            WHEN a.identity_tagname REGEXP '鲁涛'
                                AND a.identity_tagname REGEXP '机器制作视频'
                                AND a.identity_tagname NOT REGEXP 'AI自制' THEN '机器制作视频'
                            WHEN a.identity_tagname REGEXP '鲁涛'
                                AND a.identity_tagname REGEXP '机器自动改造'
                                AND a.identity_tagname REGEXP '单点视频' THEN '单点视频'
                            WHEN a.identity_tagname REGEXP 'spider'
                                AND a.identity_tagname REGEXP '小年糕爬虫'
                                AND a.identity_tagname REGEXP '账号' THEN '小年糕'
                            WHEN a.identity_tagname REGEXP 'spider'
                                AND a.identity_tagname REGEXP '公众新号'
                                AND a.identity_tagname REGEXP '账号' THEN '公众新号'
                            WHEN a.identity_tagname REGEXP 'spider'
                                AND a.identity_tagname REGEXP '抖音爬虫'
                                AND a.identity_tagname REGEXP '账号' THEN '抖音'
                            WHEN a.identity_tagname REGEXP 'spider'
                                AND a.identity_tagname REGEXP '西瓜新爬虫'
                                AND a.identity_tagname REGEXP '账号' THEN '西瓜视频爬虫'
                            WHEN a.identity_tagname REGEXP 'spider'
                                AND a.identity_tagname REGEXP '快手爬虫'
                                AND a.identity_tagname REGEXP '账号' THEN '快手爬虫'
                            WHEN a.identity_tagname REGEXP 'spider'
                                AND a.identity_tagname REGEXP '视频号'
                                AND a.identity_tagname REGEXP '账号' THEN '视频H品类账号'
                            WHEN a.identity_tagname REGEXP '范军'
                                AND a.identity_tagname REGEXP '机器自动改造'
                                AND a.identity_tagname REGEXP '单点视频' THEN '单点视频'
                            WHEN a.identity_tagname REGEXP 'spider'
                                AND a.identity_tagname REGEXP '看一看爬虫'
                                AND a.identity_tagname REGEXP '推荐'
                                AND a.identity_tagname REGEXP '鲁涛播放榜' THEN '看一看'
                            WHEN a.identity_tagname REGEXP 'spider'
                                AND a.identity_tagname REGEXP '看一看爬虫'
                                AND a.identity_tagname REGEXP '推荐'
                                AND a.identity_tagname REGEXP '首页推荐'
                                AND a.identity_tagname REGEXP '余海涛' THEN '看一看'
                            WHEN a.identity_tagname REGEXP '余海涛'
                                AND a.identity_tagname REGEXP '机器自动改造'
                                AND a.identity_tagname NOT REGEXP '单点视频' THEN '机器自动改造'
                            WHEN a.identity_tagname REGEXP '余海涛'
                                AND a.identity_tagname REGEXP '机器制作视频'
                                AND a.identity_tagname NOT REGEXP 'AI自制' THEN '机器制作视频'
                            WHEN a.identity_tagname REGEXP 'spider'
                                AND a.identity_tagname REGEXP '小年糕爬虫'
                                AND a.identity_tagname REGEXP '余海涛'
                                AND a.identity_tagname REGEXP '账号' THEN '小年糕'
                            WHEN a.identity_tagname REGEXP 'spider'
                                AND a.identity_tagname REGEXP '公众新号'
                                AND a.identity_tagname REGEXP '余海涛'
                                AND a.identity_tagname REGEXP '账号' THEN '公众新号'
                            WHEN a.identity_tagname REGEXP 'spider'
                                AND a.identity_tagname REGEXP '抖音爬虫'
                                AND a.identity_tagname REGEXP '余海涛'
                                AND a.identity_tagname REGEXP '账号' THEN '抖音'
                            WHEN a.identity_tagname REGEXP 'spider'
                                AND a.identity_tagname REGEXP '西瓜新爬虫'
                                AND a.identity_tagname REGEXP '余海涛'
                                AND a.identity_tagname REGEXP '账号' THEN '西瓜视频爬虫'
                            WHEN a.identity_tagname REGEXP 'spider'
                                AND a.identity_tagname REGEXP '快手爬虫'
                                AND a.identity_tagname REGEXP '余海涛'
                                AND a.identity_tagname REGEXP '账号' THEN '快手爬虫'
                            WHEN a.identity_tagname REGEXP 'spider'
                                AND a.identity_tagname REGEXP '视频号'
                                AND a.identity_tagname REGEXP '余海涛'
                                AND a.identity_tagname REGEXP '账号' THEN '视频H品类账号'
                            WHEN a.identity_tagname REGEXP '余海涛'
                                AND a.identity_tagname REGEXP '机器自动改造'
                                AND a.identity_tagname REGEXP '单点视频' THEN '单点视频'
                            WHEN a.identity_tagname REGEXP 'spider'
                                AND a.identity_tagname REGEXP '西瓜视频爬虫'
                                AND a.identity_tagname REGEXP '推荐'
                                AND a.identity_tagname REGEXP '播放榜'
                                AND a.identity_tagname REGEXP '罗情' THEN '西瓜'
                            WHEN a.identity_tagname REGEXP '罗情'
                                AND a.identity_tagname REGEXP '机器自动改造'
                                AND a.identity_tagname NOT REGEXP '单点视频' THEN '机器自动改造'
                            WHEN a.identity_tagname REGEXP '罗情'
                                AND a.identity_tagname REGEXP '机器制作视频'
                                AND a.identity_tagname NOT REGEXP 'AI自制' THEN '机器制作视频'
                            WHEN a.identity_tagname REGEXP 'spider'
                                AND a.identity_tagname REGEXP '小年糕爬虫'
                                AND a.identity_tagname REGEXP '罗情'
                                AND a.identity_tagname REGEXP '账号' THEN '小年糕'
                            WHEN a.identity_tagname REGEXP 'spider'
                                AND a.identity_tagname REGEXP '公众新号'
                                AND a.identity_tagname REGEXP '罗情'
                                AND a.identity_tagname REGEXP '账号' THEN '公众新号'
                            WHEN a.identity_tagname REGEXP 'spider'
                                AND a.identity_tagname REGEXP '抖音爬虫'
                                AND a.identity_tagname REGEXP '罗情'
                                AND a.identity_tagname REGEXP '账号' THEN '抖音'
                            WHEN a.identity_tagname REGEXP 'spider'
                                AND a.identity_tagname REGEXP '西瓜新爬虫'
                                AND a.identity_tagname REGEXP '罗情'
                                AND a.identity_tagname REGEXP '账号' THEN '西瓜视频爬虫'
                            WHEN a.identity_tagname REGEXP 'spider'
                                AND a.identity_tagname REGEXP '快手爬虫'
                                AND a.identity_tagname REGEXP '罗情'
                                AND a.identity_tagname REGEXP '账号' THEN '快手爬虫'
                            WHEN a.identity_tagname REGEXP 'spider'
                                AND a.identity_tagname REGEXP '视频号'
                                AND a.identity_tagname REGEXP '罗情'
                                AND a.identity_tagname REGEXP '账号' THEN '视频H品类账号'
                            WHEN a.identity_tagname REGEXP '罗情'
                                AND a.identity_tagname REGEXP '机器自动改造'
                                AND a.identity_tagname REGEXP '单点视频' THEN '单点视频'
                            WHEN a.identity_tagname REGEXP '王知微' AND a.identity_tagname REGEXP '机器自动改造' THEN CASE   WHEN a.identity_tagname REGEXP '直接溯源' THEN '直接溯源'
                                    WHEN a.identity_tagname REGEXP '相似溯源' THEN '相似溯源'
                                    WHEN a.identity_tagname REGEXP '单点视频' THEN '单点视频'
                                    WHEN a.identity_tagname REGEXP '抖音品类账号' THEN '抖音品类账号'
                                    WHEN a.identity_tagname REGEXP '快手品类账号' THEN '快手品类账号'
                                    WHEN a.identity_tagname REGEXP '视频H品类账号' THEN '视频H品类账号'
                                    WHEN a.identity_tagname REGEXP '快手关键词抓取' THEN '快手关键词抓取'
                                    WHEN a.identity_tagname REGEXP '抖音关键词抓取' THEN '抖音关键词抓取'
                                    WHEN a.identity_tagname REGEXP '视频号关键词抓取' THEN '视频号关键词抓取'
                                    WHEN a.identity_tagname REGEXP '快手创作者版爬虫' THEN '快手创作者版爬虫'
                                    ELSE '机器自动改造'
                            END
                            WHEN a.identity_tagname REGEXP '刘诗雨'
                                AND a.identity_tagname REGEXP '机器自动改造'
                                AND a.identity_tagname NOT REGEXP '单点视频' THEN '机器自动改造'
                            WHEN a.identity_tagname REGEXP '刘诗雨'
                                AND a.identity_tagname REGEXP '机器制作视频'
                                AND a.identity_tagname NOT REGEXP 'AI自制' THEN '机器制作视频'
                            WHEN a.identity_tagname REGEXP '刘诗雨'
                                AND a.identity_tagname REGEXP '机器自动改造'
                                AND a.identity_tagname REGEXP '单点视频' THEN '单点视频'
                            WHEN a.identity_tagname REGEXP '范军'
                                AND a.identity_tagname REGEXP '机器制作视频'
                                AND a.identity_tagname NOT REGEXP 'AI自制' THEN '机器制作视频'
                            ELSE NULL
                    END AS channel
                    ,a.uid
                    ,b.id AS videoid
            FROM    operators_channel_spider_base a
            LEFT JOIN videoods.wx_video_per1h b
            ON      a.uid = b.uid
        ) 
WHERE   channel IS NOT NULL
AND     name IS NOT NULL
UNION
SELECT  *
FROM    (
            SELECT  DISTINCT '信欣' AS name
                    ,CASE   WHEN a.identity_tagname REGEXP '公众号爬虫新' THEN '公众号'
                            WHEN a.identity_tagname REGEXP '西瓜视频爬虫' AND a.identity_tagname REGEXP '推荐' THEN '西瓜'
                            WHEN a.identity_tagname REGEXP '西瓜视频爬虫' AND a.identity_tagname REGEXP '搜索' THEN '西瓜'
                            WHEN a.identity_tagname REGEXP '西瓜新爬虫' AND a.identity_tagname REGEXP '账号' THEN '西瓜'
                            WHEN a.identity_tagname REGEXP '小年糕爬虫' AND a.identity_tagname REGEXP '推荐' THEN '小年糕'
                            WHEN a.identity_tagname REGEXP '小年糕爬虫' AND a.identity_tagname REGEXP '账号' THEN '小年糕'
                            WHEN a.identity_tagname REGEXP 'xng自动抓账号'
                                AND a.identity_tagname REGEXP '小年糕爬虫'
                                AND a.identity_tagname REGEXP '账号' THEN '小年糕'
                            WHEN a.identity_tagname REGEXP '小年糕+'
                                AND a.identity_tagname REGEXP '王雪珂'
                                AND (a.identity_tagname REGEXP '20230928'
                                OR a.identity_tagname REGEXP '20231010') THEN '小年糕+'
                            WHEN a.identity_tagname REGEXP '小年糕话题' THEN '小年糕话题'
                            WHEN a.identity_tagname REGEXP '快手爬虫' AND a.identity_tagname REGEXP '账号' THEN '快手'
                            WHEN a.identity_tagname REGEXP '快手爬虫' AND a.identity_tagname REGEXP '推荐' THEN '快手'
                            WHEN a.identity_tagname REGEXP '快手品类账号' THEN '快手品类账号'
                            WHEN a.identity_tagname REGEXP '快手推荐流' THEN '快手推荐流'
                            WHEN a.identity_tagname REGEXP '快手小程序' THEN '快手小程序'
                            WHEN a.identity_tagname REGEXP '快手创作者版爬虫' THEN '快手创作者版爬虫'
                            WHEN a.identity_tagname REGEXP '抖音爬虫' AND a.identity_tagname REGEXP '账号' THEN '抖音'
                            WHEN a.identity_tagname REGEXP '抖音爬虫' AND a.identity_tagname REGEXP '推荐' THEN '抖音'
                            WHEN a.identity_tagname REGEXP '抖音品类账号' THEN '抖音品类账号'
                            WHEN a.identity_tagname REGEXP '抖音推荐流' THEN '抖音推荐流'
                            WHEN a.identity_tagname REGEXP 'youtube爬虫' THEN 'youtube'
                            WHEN a.identity_tagname REGEXP '众妙音信' THEN '众妙音信'
                            WHEN a.identity_tagname REGEXP '刚刚都传' THEN '刚刚都传'
                            WHEN a.identity_tagname REGEXP '本山祝福' THEN '本山祝福'
                            WHEN a.identity_tagname REGEXP '吉祥幸福' THEN '吉祥幸福'
                            WHEN a.identity_tagname REGEXP '岁岁年年迎福气' THEN '岁岁年年迎福气'
                            WHEN a.identity_tagname REGEXP '祝福圈子' THEN '祝福圈子'
                            WHEN a.identity_tagname REGEXP '海豚祝福' THEN '海豚祝福'
                            WHEN a.identity_tagname REGEXP '福气旺' THEN '福气旺'
                            WHEN a.identity_tagname REGEXP '祝福生活' THEN '祝福生活'
                            WHEN a.identity_tagname REGEXP '中老年娱乐' THEN '中老年娱乐'
                            WHEN a.identity_tagname REGEXP '老年队伍' THEN '老年队伍'
                            WHEN a.identity_tagname REGEXP '看一看爬虫' AND a.identity_tagname REGEXP '知足常乐' THEN '看一看'
                            WHEN a.identity_tagname REGEXP '看一看爬虫' AND a.identity_tagname REGEXP 'hcm' THEN '看一看'
                            WHEN a.identity_tagname REGEXP '花好月圆中老年' THEN '花好月圆中老年'
                            WHEN a.identity_tagname REGEXP '老年团队' THEN '老年团队'
                            WHEN a.identity_tagname REGEXP '欢欢喜喜祝福到' THEN '欢欢喜喜祝福到'
                            WHEN a.identity_tagname REGEXP '优乐搞笑小视频' THEN '优乐搞笑小视频'
                            WHEN a.identity_tagname REGEXP '看一看线下' THEN '看一看线下'
                            WHEN a.identity_tagname REGEXP '看一看pius' THEN '看一看plus'
                            WHEN a.identity_tagname REGEXP '好看视频' THEN '好看视频'
                            WHEN a.identity_tagname REGEXP '祝万物复苏' THEN '祝万物复苏'
                            WHEN a.identity_tagname REGEXP '漂漂圈' THEN '漂漂圈'
                            WHEN a.identity_tagname REGEXP '珊瑚祝福' THEN '珊瑚祝福'
                            WHEN a.identity_tagname REGEXP '祝福咱们中老年之视频' THEN '祝福咱们中老年之视频'
                            WHEN a.identity_tagname REGEXP '每天送祝福' THEN '每天送祝福'
                            WHEN a.identity_tagname REGEXP '经典旺福气' THEN '经典旺福气'
                            WHEN a.identity_tagname REGEXP '退休大本营' THEN '退休大本营'
                            WHEN a.identity_tagname REGEXP '美好星河' THEN '美好星河'
                            WHEN a.identity_tagname REGEXP '吉祥祝福为你传递好运' THEN '吉祥祝福为你传递好运'
                            WHEN a.identity_tagname REGEXP '开心幸福到万家' THEN '开心幸福到万家'
                            WHEN a.identity_tagname REGEXP '吉祥佳节要祝福' THEN '吉祥佳节要祝福'
                            WHEN a.identity_tagname REGEXP '鲸鱼祝福' THEN '鲸鱼祝福'
                            WHEN a.identity_tagname REGEXP '祝尽善尽美' THEN '祝尽善尽美'
                            WHEN a.identity_tagname REGEXP '祝福意气风发' THEN '祝福意气风发'
                            WHEN a.identity_tagname REGEXP '天星小视频' THEN '天星小视频'
                            WHEN a.identity_tagname REGEXP '节日应祝福快乐' THEN '节日应祝福快乐'
                            WHEN a.identity_tagname REGEXP '老友每日祝福' THEN '老友每日祝福'
                            WHEN a.identity_tagname REGEXP '祝福你欢裕如意吉祥' THEN '祝福你欢裕如意吉祥'
                            WHEN a.identity_tagname REGEXP '佳节祝福富足吉祥' THEN '佳节祝福富足吉祥'
                            WHEN a.identity_tagname REGEXP '节日祝福咱们五谷丰登' THEN '节日祝福咱们五谷丰登'
                            WHEN a.identity_tagname REGEXP '祝春华秋实' THEN '祝春华秋实'
                            WHEN a.identity_tagname REGEXP '祝百岁之好' THEN '祝百岁之好'
                            WHEN a.identity_tagname REGEXP '开心快乐常相伴' THEN '开心快乐常相伴'
                            WHEN a.identity_tagname REGEXP '视频刷刷' THEN '视频刷刷'
                            WHEN a.identity_tagname REGEXP '海鸟祝福' THEN '海鸟祝福'
                            WHEN a.identity_tagname REGEXP '福小顺' THEN '福小顺'
                            WHEN a.identity_tagname REGEXP '新欢欢喜喜' THEN '欢欢喜喜祝福到新'
                            WHEN a.identity_tagname REGEXP '新万物复苏' THEN '祝万物复苏new'
                            WHEN a.identity_tagname REGEXP '新尽善尽美' THEN '祝尽善尽美new'
                            WHEN a.identity_tagname REGEXP '鲨鱼祝福' THEN '鲨鱼祝福'
                            WHEN a.identity_tagname REGEXP '博清祝福' THEN '博清祝福'
                            WHEN a.identity_tagname REGEXP '佳节祝福喜乐多多' THEN '佳节祝福喜乐多多'
                            WHEN a.identity_tagname REGEXP '公众新号' THEN '公众新号'
                            WHEN a.identity_tagname REGEXP '祝福圈推荐流' THEN '祝福圈推荐流'
                            WHEN a.identity_tagname REGEXP '小年糕推荐流' THEN '小年糕推荐流'
                            WHEN a.identity_tagname REGEXP '视频号推荐流' THEN '视频号推荐流'
                            WHEN a.identity_tagname REGEXP '快手推荐流' THEN '快手推荐流'
                            WHEN a.identity_tagname REGEXP '抖音推荐流' THEN '抖音推荐流'
                            WHEN a.identity_tagname REGEXP '祝福快转' THEN '祝福快转'
                            WHEN a.identity_tagname REGEXP '乐跑乐动' THEN '乐跑乐动'
                            WHEN a.identity_tagname REGEXP '篻圈故事' THEN '篻圈故事'
                            WHEN a.identity_tagname REGEXP '微圈视频' THEN '微圈视频'
                            WHEN a.identity_tagname REGEXP '好运祝福多' THEN '好运祝福多'
                            WHEN a.identity_tagname REGEXP '愿你福气满满' THEN '愿你福气满满'
                            WHEN a.identity_tagname REGEXP '节日祝福花开富贵' THEN '节日祝福花开富贵'
                            WHEN a.identity_tagname REGEXP '玲玲快乐祝福' THEN '玲玲快乐祝福'
                            WHEN a.identity_tagname REGEXP '乐跑迪捷' THEN '乐跑迪捷'
                            WHEN a.identity_tagname REGEXP '搬运搜索top视频溯源账号' THEN '搬运搜索top视频溯源账号'
                            WHEN a.identity_tagname REGEXP '祝福好运暴富' THEN '祝福好运暴富'
                            WHEN a.identity_tagname REGEXP '祝福年年顺心吉祥' THEN '祝福年年顺心吉祥'
                            WHEN a.identity_tagname REGEXP '最好送你' THEN '最好送你'
                            WHEN a.identity_tagname REGEXP '愿你福气常在' THEN '愿你福气常在'
                            WHEN a.identity_tagname REGEXP '天天聚福气' THEN '天天聚福气'
                            WHEN a.identity_tagname REGEXP '节日祝福你祥贵' THEN '节日祝福你祥贵'
                            WHEN a.identity_tagname REGEXP '快手创作者版爬虫' THEN '快手创作者版爬虫'
                            WHEN a.identity_tagname REGEXP '抖音品类账号' THEN '抖音品类账号'
                            WHEN a.identity_tagname REGEXP '单点视频' THEN '单点视频'
                            WHEN a.identity_tagname REGEXP '打开迎好运' THEN '打开迎好运'
                            WHEN a.identity_tagname REGEXP '抖音关键词抓取' THEN '抖音关键词抓取'
                            WHEN a.identity_tagname REGEXP '快手关键词抓取' AND a.identity_tagname REGEXP '信欣' THEN '快手关键词抓取'
                            WHEN a.identity_tagname REGEXP '快手关键词抓取' AND a.identity_tagname REGEXP '刘坤宇' THEN '快手关键词抓取'
                            WHEN a.identity_tagname REGEXP '视频号关键词抓取' THEN '视频号关键词抓取'
                            WHEN a.identity_tagname REGEXP '节日祝福幸福吉祥' THEN '节日祝福幸福吉祥'
                            WHEN a.identity_tagname REGEXP '好友视频' THEN '好友视频'
                            WHEN a.identity_tagname REGEXP '趣转' THEN '趣转'
                            WHEN a.identity_tagname REGEXP '祝福的问候' THEN '祝福的问候'
                            WHEN a.identity_tagname REGEXP '老年圈' THEN '老年圈'
                            WHEN a.identity_tagname REGEXP '老年生活快乐' THEN '老年生活快乐'
                            WHEN a.identity_tagname REGEXP '新视圈' THEN '新视圈'
                            WHEN a.identity_tagname REGEXP '福气好运到' THEN '福气好运到'
                            WHEN a.identity_tagname REGEXP '超级票圈' THEN '超级票圈'
                            WHEN a.identity_tagname REGEXP '绚烂祝福' THEN '绚烂祝福'
                            WHEN a.identity_tagname REGEXP '新春祝福' THEN '新春祝福'
                            WHEN a.identity_tagname REGEXP '朝朝欢喜祝福语' THEN '朝朝欢喜祝福语'
                            WHEN a.identity_tagname REGEXP '信欣'
                                AND a.identity_tagname REGEXP '机器自动改造'
                                AND NOT a.identity_tagname REGEXP '视频H品类账号|快手关键词抓取|抖音关键词抓取|视频号关键词抓取' THEN CASE   WHEN a.identity_tagname REGEXP '直接溯源' THEN '直接溯源'
                                    WHEN a.identity_tagname REGEXP '相似溯源' THEN '相似溯源'
                                    WHEN a.identity_tagname REGEXP '单点视频' THEN '单点视频'
                                    WHEN a.identity_tagname REGEXP '抖音品类账号' THEN '抖音品类账号'
                                    WHEN a.identity_tagname REGEXP '快手品类账号' THEN '快手品类账号'
                                    ELSE '机器自动改造'
                            END
                            WHEN a.identity_tagname REGEXP '信欣'
                                AND a.identity_tagname REGEXP '机器自动改造'
                                AND a.identity_tagname REGEXP '视频H品类账号' THEN '视频H品类账号'
                            WHEN a.identity_tagname REGEXP '王雪珂'
                                AND a.identity_tagname REGEXP '机器自动改造'
                                AND a.identity_tagname REGEXP '快手品类账号' THEN '快手品类账号'
                            WHEN a.identity_tagname REGEXP '王雪珂'
                                AND a.identity_tagname REGEXP '机器自动改造'
                                AND a.identity_tagname REGEXP '视频号推荐流' THEN '视频号推荐流'
                            WHEN a.identity_tagname REGEXP '王雪珂'
                                AND a.identity_tagname REGEXP '机器自动改造'
                                AND a.identity_tagname REGEXP '快手推荐流' THEN '快手推荐流'
                            WHEN a.identity_tagname REGEXP '王雪珂'
                                AND a.identity_tagname REGEXP '机器自动改造'
                                AND a.identity_tagname REGEXP '抖音推荐流' THEN '抖音推荐流'
                                WHEN a.identity_tagname REGEXP '信欣'
                                AND a.identity_tagname REGEXP 'spider'
                                AND a.identity_tagname REGEXP '垂直重发' THEN '垂直重发'
                            WHEN a.identity_tagname REGEXP '王雪珂' AND a.identity_tagname REGEXP '机器自动改造' THEN CASE   WHEN a.identity_tagname REGEXP '直接溯源' THEN '直接溯源'
                                    WHEN a.identity_tagname REGEXP '相似溯源' THEN '相似溯源'
                                    WHEN a.identity_tagname REGEXP '单点视频' THEN '单点视频'
                                    WHEN a.identity_tagname REGEXP '抖音品类账号' THEN '抖音品类账号'
                                    WHEN a.identity_tagname REGEXP '快手品类账号' THEN '快手品类账号'
                                    WHEN a.identity_tagname REGEXP '视频H品类账号' THEN '视频H品类账号'
                                    WHEN a.identity_tagname REGEXP '快手关键词抓取' THEN '快手关键词抓取'
                                    WHEN a.identity_tagname REGEXP '抖音关键词抓取' THEN '抖音关键词抓取'
                                    WHEN a.identity_tagname REGEXP '视频号关键词抓取' THEN '视频号关键词抓取'
                                    WHEN a.identity_tagname REGEXP '快手创作者版爬虫' THEN '快手创作者版爬虫'
                                    ELSE '机器自动改造'
                            END
                            ELSE '未知渠道'
                    END AS channel
                    ,a.uid
                    ,b.id AS videoid
            FROM    (
                        SELECT  *
                        FROM    operators_channel_spider_base
                    ) a
            LEFT JOIN videoods.wx_video_per1h b
            ON      a.uid = b.uid
            WHERE   a.identity_tagname REGEXP '信欣|王雪珂'
            UNION
            SELECT  DISTINCT '信欣' AS name
                    ,CASE   WHEN a.identity_tagname REGEXP 'xng自动抓账号'
                                AND a.identity_tagname REGEXP '小年糕爬虫'
                                AND a.identity_tagname REGEXP '账号' THEN '小年糕'
                            WHEN a.identity_tagname REGEXP '信欣'
                                AND a.identity_tagname REGEXP '中青看点相关推荐'
                                AND a.identity_tagname REGEXP '相关推荐' THEN '中青看点相关推荐'
                            WHEN a.identity_tagname REGEXP '信欣'
                                AND a.identity_tagname REGEXP '中青看点账号'
                                AND a.identity_tagname REGEXP 'zhanghao' THEN '中青看点账号'
                            WHEN a.identity_tagname REGEXP '信欣'
                                AND a.identity_tagname REGEXP '中青看点推荐'
                                AND a.identity_tagname REGEXP '中青看点' THEN '中青看点推荐'
                            ELSE '未知渠道'
                    END AS channel
                    ,a.uid
                    ,b.id AS videoid
            FROM    (
                        SELECT  *
                        FROM    operators_channel_spider_base
                    ) a
            LEFT JOIN videoods.wx_video_per1h b
            ON      a.uid = b.uid
        ) 
WHERE   channel <> '未知渠道'
UNION
SELECT  DISTINCT a.name
        ,CASE   WHEN b.tags REGEXP '自制内容测试|自制内容搬运｜AIGC自制内容' THEN '自制'
                WHEN b.tags REGEXP '搬运工具' THEN '搬运工具'
                WHEN b.tags REGEXP '搬运改造' THEN '搬运改造'
                WHEN b.tags REGEXP '站内重发' THEN '站内重发'
                WHEN b.tags REGEXP '搬运测试' THEN '搬运测试'
                ELSE '搬运'
        END AS type
        ,a.uid
        ,a.videoid
FROM    (
            SELECT  DISTINCT 'transport' AS type
                    ,'王知微' AS name
                    ,a.uid
                    ,b.id AS videoid
            FROM    (
                        SELECT  *
                        FROM    operators_channel_spider_base
                    ) a
            LEFT JOIN videoods.wx_video_per1h b
            ON      a.uid = b.uid
            WHERE   a.identity_tagname REGEXP 'transport'
            AND     a.identity_tagname REGEXP '王知微'
        ) a
LEFT JOIN   (
                SELECT  a.video_id AS videoid
                        ,CONCAT_WS(',',COLLECT_SET(b.tag_name)) AS tags
                FROM    (
                            SELECT  video_id
                                    ,tag_id
                            FROM    videoods.wx_video_tag_rel_per1h
                        ) a
                LEFT JOIN   (
                                SELECT  tag_id
                                        ,tag_name
                                FROM    videoods.wx_video_tag_per1h
                            ) b
                ON      a.tag_id = b.tag_id
                GROUP BY video_id
            ) b
ON      a.videoid = b.videoid
;