data_analysis/production_code/loghubods.operators_channel.sql

-- Task: 渠道统计优化  ID: 1007013800  Type: ODPS_SQL
--odps sql 
--********************************************************************--
--author:杜崇宇
--create time:2022-11-13 03:10:25
--********************************************************************--
--CREATE TABLE IF NOT EXISTS loghubods.operators_channel
--(
--    type  STRING COMMENT ''
--    ,name STRING COMMENT ''
--    ,uid STRING COMMENT ''
--    ,videoid STRING COMMENT ''
--)
--COMMENT 'TABLE COMMENT';
--select * from operators_channel where videoid='27750839' 


INSERT OVERWRITE TABLE operators_channel

WITH user_tags AS 
(
    SELECT  a.uid -- 使用 CONCAT_WS 函数将收集到的标签名称用逗号拼接成一个字符串
            ,CONCAT_WS(',',COLLECT_SET(tag_name)) AS identity_tagname
    FROM    videoods.wx_video_per1h a
    LEFT JOIN videoods.user_type_tag_rel_per1h b
    ON      a.uid = b.uid
    LEFT JOIN videoods.user_type_tag_per1h c
    ON      b.tag_id = c.tag_id -- 按用户 ID 进行分组，确保每个用户的标签信息被正确聚合
    GROUP BY a.uid
) -- 公共表表达式 excluded_videos，用于找出需要排除的视频 ID
-- 从 videoods.wx_video_tag_rel_per1h 表中筛选出标签 ID 属于特定集合的视频 ID
,excluded_videos AS 
(
    SELECT  DISTINCT video_id AS id
    FROM    videoods.wx_video_tag_rel_per1h
    WHERE   tag_id IN (88467,88530,88468)
) -- 公共表表达式 autopick_videos，用于找出自动挑选的视频 ID
-- 从 videoods.wx_video_tag_rel_per1h 表中筛选出标签 ID 属于特定集合的视频 ID
,autopick_videos AS 
(
    SELECT  DISTINCT video_id AS id
    FROM    videoods.wx_video_tag_rel_per1h
    WHERE   tag_id IN (88467,88530)
) -- 公共表表达式 handpick_videos，用于找出手动挑选的视频 ID
-- 从 videoods.wx_video_tag_rel_per1h 表中筛选出标签 ID 属于特定集合的视频 ID
,handpick_videos AS 
(
    SELECT  DISTINCT video_id AS id
    FROM    videoods.wx_video_tag_rel_per1h
    WHERE   tag_id IN (88468)
) -- 主查询部分，从 user_tags 和 videoods.wx_video_per1h 表中查询数据



select * from (SELECT 
    CASE 
        -- 范军的相关条件
        WHEN ut.identity_tagname REGEXP 'spider' AND ut.identity_tagname REGEXP '公众号爬虫新' AND ut.identity_tagname REGEXP '账号' AND ut.identity_tagname REGEXP '范军' THEN '垂直spider'
        WHEN ut.identity_tagname REGEXP '机器自动改造' AND ut.identity_tagname REGEXP '范军' AND wv.id NOT IN (SELECT id FROM excluded_videos) THEN '全面spider'
        WHEN ut.identity_tagname REGEXP '机器自动改造' AND ut.identity_tagname REGEXP '范军' AND wv.id IN (SELECT id FROM autopick_videos) THEN '全面spider-autopick'
        WHEN ut.identity_tagname REGEXP '机器自动改造' AND ut.identity_tagname REGEXP '范军' AND wv.id IN (SELECT id FROM handpick_videos) THEN '全面spider-handpick'
        WHEN ut.identity_tagname REGEXP 'spider' AND ut.identity_tagname REGEXP '小年糕爬虫' AND ut.identity_tagname REGEXP '范军' AND ut.identity_tagname REGEXP '账号' THEN '垂直spider'
        WHEN ut.identity_tagname REGEXP 'spider' AND ut.identity_tagname REGEXP '公众新号' AND ut.identity_tagname REGEXP '范军' AND ut.identity_tagname REGEXP '账号' THEN '垂直spider'
        WHEN ut.identity_tagname REGEXP 'spider' AND ut.identity_tagname REGEXP '抖音爬虫' AND ut.identity_tagname REGEXP '范军' AND ut.identity_tagname REGEXP '账号' THEN '垂直spider'
        WHEN ut.identity_tagname REGEXP 'spider' AND ut.identity_tagname REGEXP '西瓜新爬虫' AND ut.identity_tagname REGEXP '范军' AND ut.identity_tagname REGEXP '账号' THEN '垂直spider'
        WHEN ut.identity_tagname REGEXP 'spider' AND ut.identity_tagname REGEXP '快手爬虫' AND ut.identity_tagname REGEXP '范军' AND ut.identity_tagname REGEXP '账号' THEN '垂直spider'
        WHEN ut.identity_tagname REGEXP '范军' AND ut.identity_tagname REGEXP '长沙运营引入' THEN '垂直spider'
        WHEN ut.identity_tagname REGEXP '范军' AND ut.identity_tagname REGEXP '机器制作视频' THEN 'AGC'
        -- 余海涛的相关条件
        WHEN ut.identity_tagname REGEXP 'spider' AND ut.identity_tagname REGEXP '看一看爬虫' AND ut.identity_tagname REGEXP '推荐' AND ut.identity_tagname REGEXP '首页推荐' AND ut.identity_tagname REGEXP '余海涛' THEN '垂直spider'
        WHEN ut.identity_tagname REGEXP '机器自动改造' AND ut.identity_tagname REGEXP '余海涛' AND wv.id NOT IN (SELECT id FROM excluded_videos) THEN '全面spider'
        WHEN ut.identity_tagname REGEXP '机器自动改造' AND ut.identity_tagname REGEXP '余海涛' AND wv.id IN (SELECT id FROM autopick_videos) THEN '全面spider-autopick'
        WHEN ut.identity_tagname REGEXP '机器自动改造' AND ut.identity_tagname REGEXP '余海涛' AND wv.id IN (SELECT id FROM handpick_videos) THEN '全面spider-handpick'
        WHEN ut.identity_tagname REGEXP 'spider' AND ut.identity_tagname REGEXP '小年糕爬虫' AND ut.identity_tagname REGEXP '余海涛' AND ut.identity_tagname REGEXP '账号' THEN '垂直spider'
        WHEN ut.identity_tagname REGEXP 'spider' AND ut.identity_tagname REGEXP '公众新号' AND ut.identity_tagname REGEXP '余海涛' AND ut.identity_tagname REGEXP '账号' THEN '垂直spider'
        WHEN ut.identity_tagname REGEXP 'spider' AND ut.identity_tagname REGEXP '抖音爬虫' AND ut.identity_tagname REGEXP '余海涛' AND ut.identity_tagname REGEXP '账号' THEN '垂直spider'
        WHEN ut.identity_tagname REGEXP 'spider' AND ut.identity_tagname REGEXP '西瓜新爬虫' AND ut.identity_tagname REGEXP '余海涛' AND ut.identity_tagname REGEXP '账号' THEN '垂直spider'
        WHEN ut.identity_tagname REGEXP 'spider' AND ut.identity_tagname REGEXP '快手爬虫' AND ut.identity_tagname REGEXP '余海涛' AND ut.identity_tagname REGEXP '账号' THEN '垂直spider'
        WHEN ut.identity_tagname REGEXP '余海涛' AND ut.identity_tagname REGEXP '长沙运营引入' THEN '垂直spider'
        WHEN ut.identity_tagname REGEXP '余海涛' AND ut.identity_tagname REGEXP '机器制作视频' THEN 'AGC'
        -- 罗情的相关条件
        WHEN ut.identity_tagname REGEXP 'spider' AND ut.identity_tagname REGEXP '西瓜视频爬虫' AND ut.identity_tagname REGEXP '推荐' AND ut.identity_tagname REGEXP '播放榜' AND ut.identity_tagname REGEXP '罗情' THEN '垂直spider'
        WHEN ut.identity_tagname REGEXP '机器自动改造' AND ut.identity_tagname REGEXP '罗情' AND wv.id NOT IN (SELECT id FROM excluded_videos) THEN '全面spider'
        WHEN ut.identity_tagname REGEXP '机器自动改造' AND ut.identity_tagname REGEXP '罗情' AND wv.id IN (SELECT id FROM autopick_videos) THEN '全面spider-autopick'
        WHEN ut.identity_tagname REGEXP '机器自动改造' AND ut.identity_tagname REGEXP '罗情' AND wv.id IN (SELECT id FROM handpick_videos) THEN '全面spider-handpick'
        WHEN ut.identity_tagname REGEXP 'spider' AND ut.identity_tagname REGEXP '小年糕爬虫' AND ut.identity_tagname REGEXP '罗情' AND ut.identity_tagname REGEXP '账号' THEN '垂直spider'
        WHEN ut.identity_tagname REGEXP 'spider' AND ut.identity_tagname REGEXP '公众新号' AND ut.identity_tagname REGEXP '罗情' AND ut.identity_tagname REGEXP '账号' THEN '垂直spider'
        WHEN ut.identity_tagname REGEXP 'spider' AND ut.identity_tagname REGEXP '抖音爬虫' AND ut.identity_tagname REGEXP '罗情' AND ut.identity_tagname REGEXP '账号' THEN '垂直spider'
        WHEN ut.identity_tagname REGEXP 'spider' AND ut.identity_tagname REGEXP '西瓜新爬虫' AND ut.identity_tagname REGEXP '罗情' AND ut.identity_tagname REGEXP '账号' THEN '垂直spider'
        WHEN ut.identity_tagname REGEXP 'spider' AND ut.identity_tagname REGEXP '快手爬虫' AND ut.identity_tagname REGEXP '罗情' AND ut.identity_tagname REGEXP '账号' THEN '垂直spider'
        WHEN ut.identity_tagname REGEXP '罗情' AND ut.identity_tagname REGEXP '长沙运营引入' THEN '垂直spider'
        WHEN ut.identity_tagname REGEXP '罗情' AND ut.identity_tagname REGEXP '机器制作视频' THEN 'AGC'


         WHEN ut.identity_tagname REGEXP 'spider' AND ut.identity_tagname REGEXP '看一看爬虫' AND ut.identity_tagname REGEXP '推荐' AND ut.identity_tagname REGEXP '鲁涛播放榜' AND ut.identity_tagname REGEXP '鲁涛' THEN '垂直spider'
        WHEN ut.identity_tagname REGEXP '机器自动改造' AND ut.identity_tagname REGEXP '鲁涛' AND wv.id NOT IN (SELECT id FROM excluded_videos) THEN '全面spider'
        WHEN ut.identity_tagname REGEXP '机器自动改造' AND ut.identity_tagname REGEXP '鲁涛' AND wv.id IN (SELECT id FROM autopick_videos) THEN '全面spider-autopick'
        WHEN ut.identity_tagname REGEXP '机器自动改造' AND ut.identity_tagname REGEXP '鲁涛' AND wv.id IN (SELECT id FROM handpick_videos) THEN '全面spider-handpick'
        WHEN ut.identity_tagname REGEXP 'spider' AND ut.identity_tagname REGEXP '小年糕爬虫' AND ut.identity_tagname REGEXP '鲁涛' AND ut.identity_tagname REGEXP '账号' THEN '垂直spider'
        WHEN ut.identity_tagname REGEXP 'spider' AND ut.identity_tagname REGEXP '公众新号' AND ut.identity_tagname REGEXP '鲁涛' AND ut.identity_tagname REGEXP '账号' THEN '垂直spider'
        WHEN ut.identity_tagname REGEXP 'spider' AND ut.identity_tagname REGEXP '抖音爬虫' AND ut.identity_tagname REGEXP '鲁涛' AND ut.identity_tagname REGEXP '账号' THEN '垂直spider'
        WHEN ut.identity_tagname REGEXP 'spider' AND ut.identity_tagname REGEXP '西瓜新爬虫' AND ut.identity_tagname REGEXP '鲁涛' AND ut.identity_tagname REGEXP '账号' THEN '垂直spider'
        WHEN ut.identity_tagname REGEXP 'spider' AND ut.identity_tagname REGEXP '快手爬虫' AND ut.identity_tagname REGEXP '鲁涛' AND ut.identity_tagname REGEXP '账号' THEN '垂直spider'
        WHEN ut.identity_tagname REGEXP '鲁涛' AND ut.identity_tagname REGEXP '长沙运营引入' THEN '垂直spider'
        WHEN ut.identity_tagname REGEXP '鲁涛' AND ut.identity_tagname REGEXP '机器制作视频' THEN 'AGC'

                WHEN ut.identity_tagname REGEXP '刘诗雨' AND ut.identity_tagname REGEXP '长沙运营引入' THEN '垂直spider'
        WHEN ut.identity_tagname REGEXP '刘诗雨' AND ut.identity_tagname REGEXP '机器自动改造' AND wv.id NOT IN (SELECT id FROM excluded_videos) THEN '全面spider'
        WHEN ut.identity_tagname REGEXP '刘诗雨' AND ut.identity_tagname REGEXP '机器自动改造' AND wv.id IN (SELECT id FROM autopick_videos) THEN '全面spider-autopick'
        WHEN ut.identity_tagname REGEXP '刘诗雨' AND ut.identity_tagname REGEXP '机器自动改造' AND wv.id IN (SELECT id FROM handpick_videos) THEN '全面spider-handpick'
        WHEN ut.identity_tagname REGEXP '刘诗雨' AND ut.identity_tagname REGEXP '机器制作视频' THEN 'AGC'

        WHEN ut.identity_tagname REGEXP '王玉婷' AND ut.identity_tagname REGEXP '长沙运营引入' THEN '垂直spider'
        WHEN ut.identity_tagname REGEXP '王玉婷' AND ut.identity_tagname REGEXP '机器自动改造' AND wv.id NOT IN (SELECT id FROM excluded_videos) THEN '全面spider'
        WHEN ut.identity_tagname REGEXP '王玉婷' AND ut.identity_tagname REGEXP '机器自动改造' AND wv.id IN (SELECT id FROM autopick_videos) THEN '全面spider-autopick'
        WHEN ut.identity_tagname REGEXP '王玉婷' AND ut.identity_tagname REGEXP '机器自动改造' AND wv.id IN (SELECT id FROM handpick_videos) THEN '全面spider-handpick'
        WHEN ut.identity_tagname REGEXP '王玉婷' AND ut.identity_tagname REGEXP '机器制作视频' THEN 'AGC'


        WHEN ut.identity_tagname REGEXP '刘坤宇' AND ut.identity_tagname REGEXP '机器自动改造' AND wv.id NOT IN (SELECT id FROM excluded_videos) THEN '全面spider'
        WHEN ut.identity_tagname REGEXP '刘坤宇' AND ut.identity_tagname REGEXP '机器自动改造' AND wv.id IN (SELECT id FROM autopick_videos) THEN '全面spider-autopick'
        WHEN ut.identity_tagname REGEXP '刘坤宇' AND ut.identity_tagname REGEXP '机器自动改造' AND wv.id IN (SELECT id FROM handpick_videos) THEN '全面spider-handpick'
        WHEN ut.identity_tagname REGEXP '刘坤宇' AND ut.identity_tagname REGEXP '机器制作视频' AND     ut.identity_tagname REGEXP 'AI自制' THEN 'AGC'
        WHEN ut.identity_tagname REGEXP '阮望' AND ut.identity_tagname  REGEXP '机器制作视频' AND     ut.identity_tagname REGEXP 'AI自制' THEN 'AGC'

        WHEN ut.identity_tagname REGEXP '刘梓漩' AND ut.identity_tagname REGEXP '机器制作视频' AND     ut.identity_tagname REGEXP 'AI自制'  THEN 'AGC' 

        WHEN ut.identity_tagname REGEXP '王知微' AND ut.identity_tagname REGEXP '机器自动改造'  THEN '全面spider'
        WHEN ut.identity_tagname REGEXP '王知微' AND ut.identity_tagname REGEXP '机器制作视频' AND     ut.identity_tagname REGEXP 'AI自制'  THEN 'AGC' 
        WHEN  ut.identity_tagname REGEXP '机器制作视频' AND     ut.identity_tagname REGEXP 'AI自制'  THEN 'AGC' 
    END AS name,
    -- 根据不同的人员标签确定 channel 字段值
    CASE 
        WHEN ut.identity_tagname REGEXP '范军' THEN '范军'
        WHEN ut.identity_tagname REGEXP '余海涛' THEN '余海涛'
        WHEN ut.identity_tagname REGEXP '罗情' THEN '罗情'
        WHEN ut.identity_tagname REGEXP '鲁涛' THEN '鲁涛'
        WHEN ut.identity_tagname REGEXP '刘诗雨' THEN '刘诗雨'
        WHEN ut.identity_tagname REGEXP '王玉婷' THEN '王玉婷'
        WHEN ut.identity_tagname REGEXP '刘坤宇' THEN '刘坤宇'
        WHEN ut.identity_tagname REGEXP '阮望' THEN '阮望'
        WHEN ut.identity_tagname REGEXP '王知微' THEN '王知微'
        WHEN ut.identity_tagname REGEXP '刘梓漩' THEN '刘梓漩'
        WHEN ut.identity_tagname REGEXP '刘兆恒' THEN '刘兆恒'
        WHEN ut.identity_tagname REGEXP '张博' THEN '张博'
        WHEN ut.identity_tagname REGEXP '尹梦莎' THEN '尹梦莎'
        WHEN ut.identity_tagname REGEXP '马晗' THEN '马晗'
        else '无负责人'
    END AS channel,
    ut.uid,
    wv.id AS videoid
FROM 
    user_tags ut
LEFT JOIN 
    videoods.wx_video_per1h wv ON ut.uid = wv.uid WHERE ut.identity_tagname NOT REGEXP '信欣|王雪珂')
    where name is not null  ;

INSERT INTO  TABLE operators_channel
WITH user_tags AS 
(
    SELECT  a.uid -- 使用 CONCAT_WS 函数将收集到的标签名称用逗号拼接成一个字符串
            ,CONCAT_WS(',',COLLECT_SET(tag_name)) AS identity_tagname
    FROM    videoods.wx_video_per1h a
    LEFT JOIN videoods.user_type_tag_rel_per1h b
    ON      a.uid = b.uid
    LEFT JOIN videoods.user_type_tag_per1h c
    ON      b.tag_id = c.tag_id -- 按用户 ID 进行分组，确保每个用户的标签信息被正确聚合
    GROUP BY a.uid
) -- 公共表表达式 excluded_videos，用于找出需要排除的视频 ID
-- 从 videoods.wx_video_tag_rel_per1h 表中筛选出标签 ID 属于特定集合的视频 ID

select 
*
from 
(SELECT 
    
    CASE 
        -- 垂直 spider 相关条件
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '节日祝福幸福吉祥' AND identity_tagname REGEXP '推荐' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '好友视频' AND identity_tagname REGEXP '推荐' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '趣转' AND identity_tagname REGEXP '推荐' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '祝福的问候' AND identity_tagname REGEXP '推荐' THEN '垂直spider'
        WHEN identity_tagname REGEXP '垂直spider-封面测试' AND identity_tagname REGEXP '信欣' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '老年圈' AND identity_tagname REGEXP '推荐' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '老年生活快乐' AND identity_tagname REGEXP '推荐' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '新视圈' AND identity_tagname REGEXP '推荐' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '福气好运到' AND identity_tagname REGEXP '推荐' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '超级票圈' AND identity_tagname REGEXP '推荐' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '绚烂祝福' AND identity_tagname REGEXP '推荐' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '新春祝福' AND identity_tagname REGEXP '推荐' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '朝朝欢喜祝福语' AND identity_tagname REGEXP '推荐' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '公众号爬虫新' AND identity_tagname REGEXP '账号' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '西瓜视频爬虫' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' AND identity_tagname REGEXP '王雪珂播放榜' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '小年糕爬虫' AND identity_tagname REGEXP '账号' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '小年糕爬虫' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '西瓜视频爬虫' AND identity_tagname REGEXP '搜索' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '西瓜新爬虫' AND identity_tagname REGEXP '账号' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '快手爬虫' AND identity_tagname REGEXP '账号' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '快手爬虫' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN b.uid = 71617897 AND identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '抖音爬虫' AND identity_tagname REGEXP '账号' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '抖音爬虫' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP 'youtube爬虫' AND identity_tagname REGEXP '账号' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '众妙音信' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '刚刚都传' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '本山祝福' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '吉祥幸福' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '岁岁年年迎福气' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '祝福圈子' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '海豚祝福' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '福气旺' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '小年糕+' AND identity_tagname REGEXP '王雪珂' AND (identity_tagname REGEXP '20230928' OR identity_tagname REGEXP '20231010') THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '祝福生活' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '中老年娱乐' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '老年队伍' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '看一看爬虫' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' AND identity_tagname REGEXP '知足常乐' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '看一看爬虫' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' AND identity_tagname REGEXP 'hcm' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '老年团队' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '花好月圆中老年' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '欢欢喜喜祝福到' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '优乐搞笑小视频' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '看一看线下' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '看一看pius' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '好看视频' AND identity_tagname REGEXP '账号' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '祝万物复苏' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '漂漂圈' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '珊瑚祝福' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '祝福咱们中老年之视频' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '每天送祝福' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '经典旺福气' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '退休大本营' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '美好星河' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '吉祥祝福为你传递好运' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '开心幸福到万家' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '吉祥佳节要祝福' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '鲸鱼祝福' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '祝尽善尽美' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '祝福意气风发' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '天星小视频' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '节日应祝福快乐' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '老友每日祝福' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '祝福你欢裕如意吉祥' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '佳节祝福富足吉祥' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '节日祝福咱们五谷丰登' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '祝春华秋实' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '祝百岁之好' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '开心快乐常相伴' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '视频刷刷' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '海鸟祝福' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '打开迎好运' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '福小顺' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '新万物复苏' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '新尽善尽美' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '新欢欢喜喜' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '博清祝福' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '鲨鱼祝福' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '佳节祝福喜乐多多' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '公众新号' AND identity_tagname REGEXP '账号' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '小年糕话题' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '祝福好运暴富' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '愿你福气常在' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '最好送你' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '天天聚福气' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '节日祝福你祥贵' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '小年糕爬虫' AND identity_tagname REGEXP '账号' AND identity_tagname REGEXP 'xng自动抓账号' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '祝福圈推荐流' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '小年糕推荐流' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP '快手小程序' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '祝福年年顺心吉祥' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '祝福快转' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '乐跑乐动' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '篻圈故事' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '微圈视频' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '乐跑迪捷' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '好运祝福多' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '愿你福气满满' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '节日祝福花开富贵' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
        WHEN identity_tagname REGEXP 'spider' AND identity_tagname REGEXP '玲玲快乐祝福' AND identity_tagname REGEXP '推荐' AND identity_tagname REGEXP '王雪珂' THEN '垂直spider'
    
                            WHEN identity_tagname REGEXP '信欣'
                                AND identity_tagname REGEXP '中青看点相关推荐'
                                AND identity_tagname REGEXP '相关推荐' THEN '垂直spider'
                            WHEN identity_tagname REGEXP '信欣'
                                AND identity_tagname REGEXP '中青看点账号'
                                AND identity_tagname REGEXP 'zhanghao' THEN '垂直spider'
                            WHEN identity_tagname REGEXP '信欣'
                                AND identity_tagname REGEXP '中青看点推荐'
                                AND identity_tagname REGEXP '中青看点' THEN '垂直spider'


        -- 全面 spider 相关条件
        WHEN identity_tagname REGEXP '机器自动改造' AND identity_tagname REGEXP '王雪珂' AND b.id NOT IN (
            SELECT DISTINCT video_id 
            FROM videoods.wx_video_tag_rel_per1h 
            WHERE tag_id IN (88467,88530,88468)
        ) THEN '全面spider'
        WHEN identity_tagname REGEXP '机器自动改造' AND identity_tagname REGEXP '王雪珂' AND b.id IN (
            SELECT DISTINCT video_id 
            FROM videoods.wx_video_tag_rel_per1h 
            WHERE tag_id IN (88467,88530)
        ) THEN '全面spider-autopick'
        WHEN identity_tagname REGEXP '机器自动改造' AND identity_tagname REGEXP '王雪珂' AND b.id IN (
            SELECT DISTINCT video_id 
            FROM videoods.wx_video_tag_rel_per1h 
            WHERE tag_id IN (88468)
        ) THEN '全面spider-handpick'
        WHEN identity_tagname REGEXP '信欣' AND identity_tagname REGEXP '机器自动改造' AND b.id NOT IN (
            SELECT DISTINCT video_id 
            FROM videoods.wx_video_tag_rel_per1h 
            WHERE tag_id IN (88467,88530,88468)
        ) THEN '全面spider'
        WHEN identity_tagname REGEXP '信欣' AND identity_tagname REGEXP '机器自动改造' AND b.id IN (
            SELECT DISTINCT video_id 
            FROM videoods.wx_video_tag_rel_per1h 
            WHERE tag_id IN (88467,88530)
        ) THEN '全面spider-autopick'
        WHEN identity_tagname REGEXP '信欣' AND identity_tagname REGEXP '机器自动改造' AND b.id IN (
            SELECT DISTINCT video_id 
            FROM videoods.wx_video_tag_rel_per1h 
            WHERE tag_id IN (88468)
        ) THEN '全面spider-handpick'
         WHEN  identity_tagname REGEXP '信欣' AND identity_tagname REGEXP '机器制作视频' AND     identity_tagname REGEXP 'AI自制'  THEN 'AGC' 
         WHEN  identity_tagname REGEXP '信欣' AND identity_tagname REGEXP 'spider' AND     identity_tagname REGEXP '垂直重发'  THEN '垂直spider' 
        ELSE NULL
    END AS name,
    '信欣' AS channel,
    user_tags.uid,
    b.id AS videoid
FROM 
    user_tags 
LEFT JOIN 
    videoods.wx_video_per1h b ON user_tags.uid = b.uid) where 
 name is not null
union 
SELECT  DISTINCT 'userupload' AS type
        ,'杜崇宇' AS name
        ,a.uid
        ,b.id AS videoid
FROM    (
            SELECT  a.uid
                    ,CONCAT_WS(',',COLLECT_SET(tag_name)) AS identity_tagname
            FROM    videoods.wx_video_per1h a
            LEFT JOIN videoods.user_type_tag_rel_per1h b
            ON      a.uid = b.uid
            LEFT JOIN videoods.user_type_tag_per1h c
            ON      b.tag_id = c.tag_id
            GROUP BY a.uid
        ) a
LEFT JOIN videoods.wx_video_per1h b
ON      a.uid = b.uid
WHERE   (
            (
                        a.identity_tagname NOT REGEXP 'spider'
                        AND     a.identity_tagname NOT REGEXP 'transport'
                        AND     a.identity_tagname NOT REGEXP '机器制作视频'
                        AND     a.identity_tagname NOT REGEXP 'oldVideoPush'
            )
            OR      a.identity_tagname IS NULL
)
AND     b.id NOT IN (
            SELECT  DISTINCT video_id AS id
            FROM    videoods.wx_video_tag_rel_per1h
            WHERE   tag_id IN (87812,88050,88251)
        )  --新增策略统计
UNION
SELECT  DISTINCT 'UGC-handpick' AS type
        ,'杜崇宇' AS name
        ,a.uid
        ,b.video_id
FROM    videoods.wx_video_per1h a
LEFT JOIN   (
                SELECT  DISTINCT video_id
                        ,tag_id
                        ,CASE   WHEN tag_name REGEXP '#str-' THEN tag_name
                        END AS tag
                FROM    (
                            SELECT  a.video_id
                                    ,a.tag_id
                                    ,b.tag_name
                            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
                        )  --HAVING  tag IS NOT NULL
            ) b
ON      a.id = b.video_id
WHERE   b.tag_id IN (87812)
UNION
SELECT  DISTINCT 'UGC-autopick' AS type
        ,'杜崇宇' AS name
        ,a.uid
        ,b.video_id
FROM    videoods.wx_video_per1h a
LEFT JOIN   (
                SELECT  DISTINCT video_id
                        ,tag_id
                        ,CASE   WHEN tag_name REGEXP '#str-' THEN tag_name
                        END AS tag
                FROM    (
                            SELECT  a.video_id
                                    ,a.tag_id
                                    ,b.tag_name
                            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
                        )  --HAVING  tag IS NOT NULL
            ) b
ON      a.id = b.video_id
WHERE   b.tag_id IN (88050,88251)
UNION ----信欣爬虫
SELECT  DISTINCT 'oldVideoPush' AS type
        ,'杜崇宇' AS name
        ,a.uid
        ,b.id AS videoid
FROM    (
            SELECT  a.uid
                    ,CONCAT_WS(',',COLLECT_SET(tag_name)) AS identity_tagname
            FROM    videoods.wx_video_per1h a
            LEFT JOIN videoods.user_type_tag_rel_per1h b
            ON      a.uid = b.uid
            LEFT JOIN videoods.user_type_tag_per1h c
            ON      b.tag_id = c.tag_id
            GROUP BY a.uid
        ) a
LEFT JOIN videoods.wx_video_per1h b
ON      a.uid = b.uid
WHERE   a.identity_tagname REGEXP 'oldVideoPush'
union 
SELECT  *
FROM    (
            SELECT  DISTINCT 'transport' AS type
                    ,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 '王知微'
                            WHEN a.identity_tagname REGEXP '刘兆恒' THEN '刘兆恒'
                            WHEN a.identity_tagname REGEXP '阮望' THEN '阮望'
                    END AS name
                    ,a.uid
                    ,b.id AS videoid
            FROM    (
                        SELECT  a.uid
                                ,CONCAT_WS(',',COLLECT_SET(tag_name)) AS identity_tagname
                        FROM    videoods.wx_video_per1h a
                        LEFT JOIN videoods.user_type_tag_rel_per1h b
                        ON      a.uid = b.uid
                        LEFT JOIN videoods.user_type_tag_per1h c
                        ON      b.tag_id = c.tag_id
                        GROUP BY a.uid
                    ) a
            LEFT JOIN videoods.wx_video_per1h b
            ON      a.uid = b.uid
            AND     a.identity_tagname REGEXP 'transport'
        ) 
WHERE   videoid IS NOT NULL
AND     name IS NOT NULL;