data_analysis/table_gen/loghubods.ods_user_active_log_info_day.sql

--odps sql 


--********************************************************************--
--author:李晓阳
--create time:2025-07-30 11:36:56
--desc mid区分渠道承接和日常的明细数据表 (优化上线)
--********************************************************************--
CREATE TABLE IF NOT EXISTS loghubods.ods_user_active_log_info_day
(
    apptype             STRING COMMENT '应用类型'
    ,loginuid           STRING COMMENT '用户id'
    ,opengid            STRING COMMENT '群id'
    ,type               STRING COMMENT '群类型'
    ,sencetype          STRING COMMENT '群类型_编码'
    ,hotsencetype       STRING COMMENT '热启动群类型_编码'
    ,machinecode        STRING COMMENT '设备唯一标识'
    ,subsessionid       STRING COMMENT '子会话ID'
    ,sessionid          STRING COMMENT '会话ID'
    ,rootsourceid       STRING COMMENT '根来源ID'
    ,rootsessionid      STRING COMMENT '根会话ID'
    ,layer              BIGINT COMMENT '用户分享层级'
    ,channel            STRING COMMENT '渠道名称'
    ,channel_shortname  STRING COMMENT '渠道简称'
    ,push_type          STRING COMMENT '推送类型（承接/日常/未知）'
    ,sub_channel        STRING COMMENT '子渠道标识'
    ,channel_type       STRING COMMENT '渠道类型分类'
    ,channel_type_frist STRING COMMENT '渠道类型前缀'
    ,channel_id         BIGINT COMMENT '渠道类型ID'
    ,level_type         STRING COMMENT '外部首层、非外部首层'
    ,user_share_depth   BIGINT COMMENT '分享层级'
    ,browse_time STRING COMMENT '访问时间'

)
PARTITIONED BY 
(
    dt                  STRING COMMENT '数据日期'
)
STORED AS ALIORC
TBLPROPERTIES ('columnar.nested.type' = 'true','comment' = 'mid区分渠道承接和日常的明细数据表')
;
--ALTER TABLE loghubods.ods_user_active_log_info_day ADD COLUMNS (channel_type_okr string )
--ALTER TABLE loghubods.ods_user_active_log_info_day ADD COLUMNS (browse_time string COMMENT '访问时间');
--ALTER TABLE loghubods.ods_user_active_log_info_day ADD COLUMNS (real_user_share_depth string --COMMENT '真实层级');
--ALTER TABLE loghubods.ods_user_active_log_info_day ADD COLUMNS (clienttimestamp string );
INSERT OVERWRITE TABLE loghubods.ods_user_active_log_info_day PARTITION (dt = '${day}')
WITH -- 1. 基础用户活跃日志CTE：解析JSON字段，过滤公共条件
base_useractive AS 
(
    SELECT  dt
            ,apptype
            ,machinecode
            ,subsessionid
            ,sessionid
            ,businesstype
            ,GET_JSON_OBJECT(extparams,'$.rootSourceId') AS root_source_id
            ,GET_JSON_OBJECT(extparams,'$.rootSessionId') AS root_session_id
            ,GET_JSON_OBJECT(extparams,'$.userShareDepth') AS user_share_depth
            ,extparams
            ,loginuid
            ,opengid
            ,CASE   WHEN opengid = '-1' THEN '单聊'
                    WHEN opengid = '-2' THEN '获取失败'
                    WHEN LENGTH(opengid) > 4 THEN '群聊'
            END AS type -- ,pagesource
            ,senceType AS sencetype
            ,GET_JSON_OBJECT(extParams,'$.hotSenceType') AS hotsencetype
            ,from_unixtime(cast(clienttimestamp as BIGINT )/1000) browse_time
            ,clienttimestamp
    FROM    loghubods.useractive_log
    WHERE   dt = '${day}'
    AND     businesstype = 'path'
) -- 2. 公众号买号分组表
,gzh_buy_group AS 
(
    SELECT  root_source_id AS gzh_buy_root
    FROM    loghubods.changwen_rootsourceid_group
    WHERE   dt = MAX_PT('loghubods.changwen_rootsourceid_group')
    AND     group_name = '公众号买号'
) -- 3. 服务号信息表（用于服务号代运营场景）
,service_account AS 
(
    SELECT  gzh.gh_id
            ,account.channel
            ,CASE   WHEN gzh.type = 0 THEN '公众号'
                    ELSE '服务号'
            END AS account_type
    FROM    loghubods.content_platform_account account
    LEFT JOIN loghubods.content_platform_gzh_account gzh
    ON      account.id = gzh.create_account_id
    WHERE   gzh.type != 0 -- 仅保留服务号
    GROUP BY gzh.gh_id
             ,account.channel
             ,CASE   WHEN gzh.type = 0 THEN '公众号'
                     ELSE '服务号'
             END
) -- 4. 长文数据与分组关联表
,long_articles_info AS 
(
    SELECT  a.rootsourceid
            ,a.ghid
            ,c.group_source_name
            ,a.push_type
    FROM    loghubods.long_articles_root_source_id a
    LEFT JOIN loghubods.publish_account b
    ON      a.ghid = b.gh_id
    LEFT JOIN loghubods.wx_statistics_group_source_account c
    ON      b.id = c.account_id
    WHERE   a.dt = MAX_PT('loghubods.long_articles_root_source_id')
    GROUP BY a.rootsourceid
             ,a.ghid
             ,c.group_source_name
             ,a.push_type
) -- 5. 腾讯投放记录表
,ad_put_flow_tencent AS 
(
    SELECT  root_source_id
            ,put_carrier_id
            ,put_type_one
            ,put_type_two
            ,channel
    FROM    loghubods.ad_put_flow_record_tencent_day
    WHERE   dt = '${day}'
    GROUP BY root_source_id
             ,put_carrier_id
             ,put_type_one
             ,put_type_two
             ,channel
) -- 主查询：通过CASE WHEN和LEFT JOIN实现所有渠道类型的统一处理
SELECT  b.apptype
        ,b.loginuid
        ,b.opengid
        ,b.type
        ,b.sencetype
        ,b.hotsencetype
        ,b.machinecode
        ,b.subsessionid
        ,b.sessionid
        ,b.root_source_id AS rootsourceid
        ,b.root_session_id AS rootsessionid -- 计算层级layer
        ,CASE   WHEN b.root_source_id REGEXP 'touliu_tencentwbqw_|dyyqw_' AND t9.channel REGEXP 'xycsd|csaq|shy|jxjx|gzcr|xyjj|jxatm|xjcy|yqyx|hbwq|jxxm|gzmy|cdjh|gzjr|gzxts|twhc|qdjdz|sjzyd|gzyhc|djh|gzlx|yywl|szjn|gzdd1|cqqd|cqslh|hzjy|hzjh|sclh|xyhc|snss' --AND t9.layer_type ='特殊首层' 
                THEN IF(CAST(b.user_share_depth AS INT) <= 1,1,2)
                ELSE IF(CAST(b.user_share_depth AS INT) = 0,1,2)
        END AS layer -- 计算渠道名称、简称
        ,CASE
                 -- 公众号即时回复
                   WHEN b.root_source_id REGEXP 'dyyjs_'
                    AND SUBSTR(b.root_source_id,7,4) = 'null'
                    AND t3.ghid_list = 'gh_580589404a08' THEN '浩讯'
                WHEN b.root_source_id REGEXP 'dyyjs_'
                    AND SUBSTR(b.root_source_id,7,4) = 'null'
                    AND t3.ghid_list = 'gh_505269831752' THEN '恒创'
                WHEN b.root_source_id REGEXP 'dyyjs_' THEN t4.company -- 企微外部合作
                WHEN b.root_source_id REGEXP 'touliu_tencentwbqw_|dyyqw_' THEN SPLIT_PART(b.root_source_id,'_',2) -- 服务号代运营
                WHEN b.root_source_id REGEXP 'fwhhzdyy_|fwhdyy_' THEN COALESCE(s.channel,'') -- 公众号买号/长文
                WHEN b.root_source_id REGEXP 'longArticles_' THEN COALESCE(l.group_source_name,'') -- 其他渠道
                ELSE ''
        END AS channel -- 渠道简称（逻辑与channel一致，仅部分场景有简写）
        ,CASE   WHEN b.root_source_id REGEXP 'dyyjs_'
                    AND SUBSTR(b.root_source_id,7,4) = 'null'
                    AND t3.ghid_list = 'gh_580589404a08' THEN 'hx'
                WHEN b.root_source_id REGEXP 'dyyjs_'
                    AND SUBSTR(b.root_source_id,7,4) = 'null'
                    AND t3.ghid_list = 'gh_505269831752' THEN 'hc'
                WHEN b.root_source_id REGEXP 'dyyjs_' THEN t4.company_code
                WHEN b.root_source_id REGEXP 'touliu_tencentwbqw_|dyyqw_' THEN SPLIT_PART(b.root_source_id,'_',2)
                ELSE ''
        END AS channel_shortname -- 推送类型
        ,CASE
                 -- 公众号/服务号投流、企微投流：从长文数据或投放记录获取
                WHEN b.root_source_id REGEXP 'touliu_tencentgzh_|touliu_tencentGzhArticle_|GzhTouLiu_Articles_gh|fwhtouliu_|touliu_tencentqw_|WeCom_|daitou_tencentgzh|DaiTou_gh' THEN COALESCE(t5.push_type,'未知') -- 公众号即时回复、小程序投流：固定为承接
                WHEN b.root_source_id REGEXP 'dyyjs_|touliu_tencent_' THEN '承接' -- 其他渠道：固定为日常
                ELSE '日常'
        END AS push_type -- 子渠道
        ,CASE   WHEN b.root_source_id REGEXP 'dyyjs_' THEN t3.ghid_list
                WHEN b.root_source_id REGEXP 'touliu_tencentwbqw_|dyyqw_' THEN t6.put_carrier_id
                WHEN b.root_source_id REGEXP 'fwhhzdyy_|fwhdyy_' THEN COALESCE(s.gh_id,'')
                WHEN b.root_source_id REGEXP 'longArticles_' THEN COALESCE(l.ghid,'')
                WHEN b.root_source_id REGEXP 'touliu_tencentgzh_|touliu_tencentGzhArticle_|GzhTouLiu_Articles_gh' AND b.root_source_id NOT REGEXP 'fwhtouliu_' THEN COALESCE(t5.ghid,'')
                WHEN b.root_source_id REGEXP 'fwhtouliu_' THEN COALESCE(t5.ghid,'')
                ELSE ''
        END AS sub_channel -- 渠道类型（统一映射）
        ,CASE   WHEN b.root_source_id REGEXP 'longArticles_' AND g.gzh_buy_root IS NOT NULL THEN '公众号买号'
                WHEN b.root_source_id REGEXP 'longArticles_'
                    AND g.gzh_buy_root IS NULL
                    AND t8.rootsourceid IS NULL THEN '公众号代运营-Daily-系统'
                WHEN b.root_source_id REGEXP 'dyyjs_' THEN '公众号合作-即转-稳定'
                WHEN b.root_source_id REGEXP 'fwhtouliu_' THEN '服务号投流'
                WHEN b.root_source_id REGEXP 'touliu_tencent_' THEN '小程序投流-稳定'
                WHEN b.root_source_id REGEXP 'touliu_tencentgzh_|touliu_tencentGzhArticle_|GzhTouLiu_Articles_gh' AND b.root_source_id NOT REGEXP 'fwhtouliu_' THEN '公众号投流-稳定'
                WHEN b.root_source_id REGEXP 'touliu_tencentqw_|WeCom_' AND t7.rootsourceid IS NULL THEN '企微投放'
                WHEN b.root_source_id REGEXP 'touliu_tencentwbqw_|dyyqw_' AND t6.root_source_id IS NOT NULL THEN '群/企微合作-稳定' --WHEN b.root_source_id REGEXP 'daitou_tencentgzh|DaiTou_gh' THEN '90公众号代投'
                WHEN b.root_source_id REGEXP 'qwsq_' AND t7.rootsourceid IS NOT NULL THEN '企微投放-存量拉群'
                WHEN b.root_source_id REGEXP 'fwhhzdyy_|fwhdyy_' THEN '服务号合作-Daily-自选' --WHEN b.root_source_id REGEXP 'fwhmh_' THEN '92服务号买号'
                WHEN b.root_source_id REGEXP 'longArticles_'
                    AND g.gzh_buy_root IS NULL
                    AND t8.rootsourceid IS NOT NULL THEN '公众号合作-Daily-自选'
                WHEN b.root_source_id REGEXP 'touliu_tencentqw_|WeCom_|qwmf_' AND t7.rootsourceid IS NOT NULL THEN '微信群买粉'
                WHEN b.root_source_id REGEXP 'daitou_tencentgzh|DaiTou_gh' THEN '公众号完全代投放'
                WHEN b.root_source_id REGEXP 'fwhmh_' THEN '服务号买号'
                ELSE '内部'
        END AS channel_type
        ,CASE   WHEN b.root_source_id REGEXP 'longArticles_' AND g.gzh_buy_root IS NOT NULL THEN 'longArticles_'
                WHEN b.root_source_id REGEXP 'longArticles_'
                    AND g.gzh_buy_root IS NULL
                    AND t8.rootsourceid IS NULL THEN 'longArticles_'
                WHEN b.root_source_id REGEXP 'dyyjs_' THEN 'dyyjs_'
                WHEN b.root_source_id REGEXP 'fwhtouliu_' THEN 'fwhtouliu_'
                WHEN b.root_source_id REGEXP 'touliu_tencent_' THEN 'touliu_tencent_'
                WHEN b.root_source_id REGEXP 'touliu_tencentgzh_|touliu_tencentGzhArticle_|GzhTouLiu_Articles_gh' AND b.root_source_id NOT REGEXP 'fwhtouliu_' THEN 'touliu_tencentgzh_'
                WHEN b.root_source_id REGEXP 'touliu_tencentqw_|WeCom_' AND t7.rootsourceid IS NULL THEN 'touliu_tencentqw_'
                WHEN b.root_source_id REGEXP 'touliu_tencentwbqw_|dyyqw_' AND t6.root_source_id IS NOT NULL THEN 'touliu_tencentwbqw_' --WHEN b.root_source_id REGEXP 'daitou_tencentgzh|DaiTou_gh' THEN '90公众号代投'
                WHEN b.root_source_id REGEXP 'qwsq_' AND t7.rootsourceid IS NOT NULL THEN 'qwsq_'
                WHEN b.root_source_id REGEXP 'fwhhzdyy_|fwhdyy_' THEN 'fwhhzdyy_' --WHEN b.root_source_id REGEXP 'fwhmh_' THEN '92服务号买号'
                WHEN b.root_source_id REGEXP 'longArticles_'
                    AND g.gzh_buy_root IS NULL
                    AND t8.rootsourceid IS NOT NULL THEN 'longArticles_'
                WHEN b.root_source_id REGEXP 'touliu_tencentqw_|WeCom_|qwmf_' AND t7.rootsourceid IS NOT NULL THEN 'WeCom_'
                WHEN b.root_source_id REGEXP 'daitou_tencentgzh|DaiTou_gh' THEN 'DaiTou_gh'
                WHEN b.root_source_id REGEXP 'fwhmh_' THEN 'fwhmh_'
                ELSE '内部'
        END AS channel_type_frist -- 渠道ID（与channel_type对应）
        ,CASE   WHEN b.root_source_id REGEXP 'longArticles_' AND g.gzh_buy_root IS NOT NULL THEN 3
                WHEN b.root_source_id REGEXP 'longArticles_' AND g.gzh_buy_root IS NULL THEN 1
                WHEN b.root_source_id REGEXP 'dyyjs_' THEN 2
                WHEN b.root_source_id REGEXP 'fwhtouliu_' THEN 4
                WHEN b.root_source_id REGEXP 'touliu_tencent_' THEN 5
                WHEN b.root_source_id REGEXP 'touliu_tencentgzh_|touliu_tencentGzhArticle_|GzhTouLiu_Articles_gh' AND b.root_source_id NOT REGEXP 'fwhtouliu_' THEN 6
                WHEN b.root_source_id REGEXP 'touliu_tencentqw_|WeCom_' AND t7.rootsourceid IS NULL THEN 7
                WHEN b.root_source_id REGEXP 'touliu_tencentwbqw_|dyyqw_' AND t6.root_source_id IS NOT NULL THEN 8 --WHEN b.root_source_id REGEXP 'daitou_tencentgzh|DaiTou_gh' THEN 90
                WHEN b.root_source_id REGEXP 'qwsq_' AND t7.rootsourceid IS NOT NULL THEN 90
                WHEN b.root_source_id REGEXP 'fwhhzdyy_|fwhdyy_' THEN 91 --WHEN b.root_source_id REGEXP 'fwhmh_' THEN 92
                WHEN b.root_source_id REGEXP 'longArticles_'
                    AND g.gzh_buy_root IS NULL
                    AND t8.rootsourceid IS NOT NULL THEN 92
                WHEN b.root_source_id REGEXP 'touliu_tencentqw_|WeCom_|qwmf_' AND t7.rootsourceid IS NOT NULL THEN 93
                WHEN b.root_source_id REGEXP 'daitou_tencentgzh|DaiTou_gh' THEN 94
                WHEN b.root_source_id REGEXP 'fwhmh_' THEN 95
                ELSE 9999999
        END AS channel_id
        ,CASE   WHEN b.root_source_id REGEXP 'touliu_tencentwbqw_|dyyqw_' --AND t9.layer_type ='特殊首层'
                    AND t9.channel REGEXP 'xycsd|csaq|shy|jxjx|gzcr|xyjj|jxatm|xjcy|yqyx|hbwq|jxxm|gzmy|cdjh|gzjr|gzxts|twhc|qdjdz|sjzyd|gzyhc|djh|gzlx|yywl|szjn|gzdd1|cqqd|cqslh|hzjy|hzjh|sclh|xyhc|snss'
                    AND b.user_share_depth <= 1 THEN '外部首层'
                WHEN b.root_source_id REGEXP 'touliu_tencentwbqw_|dyyqw_' --AND t9.layer_type ='非特殊首层'
                    AND t9.channel NOT REGEXP 'xycsd|csaq|shy|jxjx|gzcr|xyjj|jxatm|xjcy|yqyx|hbwq|jxxm|gzmy|cdjh|gzjr|gzxts|twhc|qdjdz|sjzyd|gzyhc|djh|gzlx|yywl|szjn|gzdd1|cqqd|cqslh|hzjy|hzjh|sclh|xyhc|snss'
                    AND b.user_share_depth = 0 THEN '外部首层'
                WHEN b.root_source_id NOT REGEXP 'touliu_tencentwbqw_|dyyqw_' AND b.user_share_depth = 0 THEN '外部首层'
                ELSE '非外部首层'
        END AS level_type
        ,CASE   WHEN b.root_source_id REGEXP 'touliu_tencentwbqw_|dyyqw_' AND t9.channel REGEXP 'xycsd|csaq|shy|jxjx|gzcr|xyjj|jxatm|xjcy|yqyx|hbwq|jxxm|gzmy|cdjh|gzjr|gzxts|twhc|qdjdz|sjzyd|gzyhc|djh|gzlx|yywl|szjn|gzdd1|cqqd|cqslh|hzjy|hzjh|sclh|xyhc|snss' --AND t9.layer_type ='特殊首层' 
                THEN IF(CAST(b.user_share_depth AS INT) <= 1,0,b.user_share_depth)
                ELSE b.user_share_depth
        END user_share_depth
        ,CASE   WHEN b.root_source_id REGEXP 'longArticles_' AND g.gzh_buy_root IS NOT NULL THEN '3公众号买号'
                WHEN b.root_source_id REGEXP 'longArticles_'
                    AND g.gzh_buy_root IS NULL
                    AND t8.rootsourceid IS NULL THEN '1公众号代运营-Daily'
                WHEN b.root_source_id REGEXP 'dyyjs_' THEN '2公众号代运营-即转'
                WHEN b.root_source_id REGEXP 'fwhtouliu_' THEN '4服务号投流'
                WHEN b.root_source_id REGEXP 'touliu_tencent_' THEN '5小程序投流'
                WHEN b.root_source_id REGEXP 'touliu_tencentgzh_|touliu_tencentGzhArticle_|GzhTouLiu_Articles_gh' AND b.root_source_id NOT REGEXP 'fwhtouliu_' THEN '6公众号投流'
                WHEN b.root_source_id REGEXP 'touliu_tencentqw_|WeCom_' AND t7.rootsourceid IS NULL THEN '7企微'
                WHEN b.root_source_id REGEXP 'touliu_tencentwbqw_|dyyqw_' AND t6.root_source_id IS NOT NULL THEN '8企微外部合作' --WHEN b.root_source_id REGEXP 'daitou_tencentgzh|DaiTou_gh' THEN '90公众号代投'
                WHEN b.root_source_id REGEXP 'qwsq_' AND t7.rootsourceid IS NOT NULL THEN '90企微投放-人群包-存量拉群'
                WHEN b.root_source_id REGEXP 'fwhhzdyy_|fwhdyy_' THEN '91服务号代运营-Daily' --WHEN b.root_source_id REGEXP 'fwhmh_' THEN '92服务号买号'
                WHEN b.root_source_id REGEXP 'longArticles_'
                    AND g.gzh_buy_root IS NULL
                    AND t8.rootsourceid IS NOT NULL THEN '92公众号代运营-Daily-合作'
                WHEN b.root_source_id REGEXP 'touliu_tencentqw_|WeCom_|qwmf_' AND t7.rootsourceid IS NOT NULL THEN '93企微买群粉'
                WHEN b.root_source_id REGEXP 'daitou_tencentgzh|DaiTou_gh' THEN '94公众号代投'
                WHEN b.root_source_id REGEXP 'fwhmh_' THEN '95服务号买号'
                ELSE '内部'
        END AS channel_type_okr
        ,b.browse_time
        ,b.user_share_depth
        ,b.clienttimestamp
FROM    base_useractive b -- 关联公众号买号分组表
LEFT JOIN gzh_buy_group g
ON      b.root_source_id = g.gzh_buy_root -- 关联公众号即时回复所需的ghid信息
LEFT JOIN   (
                SELECT  root_source_id
                        ,CONCAT_WS(',',COLLECT_SET(put_carrier_id)) AS ghid_list
                FROM    ad_put_flow_tencent
                WHERE   root_source_id REGEXP 'dyyjs_'
                GROUP BY root_source_id
            ) t3
ON      b.root_source_id = t3.root_source_id -- 关联公众号即时回复所需的公司信息
LEFT JOIN loghubods.dim_company_price_info t4
ON      b.root_source_id = t4.root_source_id
AND     t4.dt = '${day}' -- 关联投流相关的推送类型信息
LEFT JOIN   (
                SELECT  rootsourceid AS root_source_id
                        ,CASE   WHEN MAX(push_type) = '1' THEN '承接'
                                WHEN MAX(push_type) = '2' THEN '日常'
                                WHEN MAX(push_type) REGEXP '菜单|自动回复|即时欢迎语' THEN '承接'
                                WHEN MAX(push_type) REGEXP '日常推送|社群' THEN '日常'
                                WHEN MAX(push_type) REGEXP '对外信息展示' THEN '承接'
                                WHEN MAX(push_type) REGEXP '朋友圈' THEN '日常'
                                ELSE '未知'
                        END AS push_type
                        ,ghid
                FROM    (
                            SELECT  rootsourceid
                                    ,CAST(push_type AS STRING) AS push_type
                                    ,ghid
                            FROM    loghubods.long_articles_root_source_id
                            WHERE   dt = MAX_PT("loghubods.long_articles_root_source_id")
                            UNION
                            SELECT  root_source_id AS rootsourceid
                                    ,put_type_two AS push_type
                                    ,put_carrier_id
                            FROM    loghubods.ad_put_flow_record_tencent_day a
                            WHERE   a.dt = '${day}'
                        ) 
                GROUP BY rootsourceid
                         ,ghid
            ) t5
ON      b.root_source_id = t5.root_source_id -- 关联企微外部合作的put_carrier_id
LEFT JOIN   (
                SELECT  root_source_id
                        ,put_carrier_id
                FROM    ad_put_flow_tencent
                WHERE   put_type_one = '企微'
                AND     root_source_id REGEXP 'touliu_tencentwbqw_|dyyqw_'
                GROUP BY root_source_id
                         ,put_carrier_id
            ) t6
ON      b.root_source_id = t6.root_source_id -- 关联服务号信息
LEFT JOIN   (
                SELECT  rootsourceid
                        ,s.gh_id
                        ,s.channel
                FROM    loghubods.long_articles_root_source_id a
                LEFT JOIN service_account s
                ON      a.ghid = s.gh_id
                WHERE   a.dt = MAX_PT('loghubods.long_articles_root_source_id')
                UNION
                SELECT  root_source_id AS rootsourceid
                        ,a.put_carrier_id
                        ,d.channel
                FROM    loghubods.ad_put_flow_record_tencent_day a
                LEFT JOIN   (
                                SELECT  account.channel
                                        ,gzh.gh_id
                                        ,gzh.`name`
                                        ,(CASE    gzh.type
                                                WHEN 0 THEN '公众号'
                                                ELSE '服务号'
                                        END) AS type
                                FROM    loghubods.content_platform_account account
                                LEFT JOIN loghubods.content_platform_gzh_account gzh
                                ON      account.id = gzh.create_account_id
                            ) d
                ON      a.put_carrier_id = d.gh_id
                AND     d.type = '服务号'
                WHERE   a.dt = '${day}'
                AND     root_source_id REGEXP 'fwhhzdyy_|fwhdyy_'
            ) s
ON      b.root_source_id = s.rootsourceid
LEFT JOIN   (
                SELECT  root_source_id AS rootsourceid
                        ,put_type_two AS push_type --,b.name AS accountname
                        ,COALESCE(b.name,a.remark) AS accountname
                FROM    loghubods.ad_put_flow_record_tencent_day a
                LEFT JOIN   (
                                SELECT  user_id
                                        ,name
                                FROM    loghubods.reply_staff
                                WHERE   dt = MAX_PT('loghubods.reply_staff')
                                AND     is_delete = 0
                            ) b
                ON      a.put_carrier_id = b.user_id
                WHERE   dt = '${day}'
                AND     put_type_one = '企微'
                AND     root_source_id REGEXP 'touliu_tencentqw_|WeCom_|qwmf_|qwsq_'
                AND     (
                            COALESCE(b.name,a.remark) IN ('热点视频推荐','情感专家-月亮姐姐')
                            OR      root_source_id REGEXP 'qwmf_|qwsq_'
                )
                GROUP BY root_source_id
                         ,put_type_two
                         ,COALESCE(b.name,a.remark)
            ) t7
ON      b.root_source_id = t7.rootsourceid -- 关联长文信息
LEFT JOIN long_articles_info l
ON      b.root_source_id = l.rootsourceid
LEFT JOIN   (
                SELECT  rootsourceid
                        ,channel
                        ,channel_name
                        ,ghid
                        ,gzh_name
                        ,type
                FROM    loghubods.rootsourceid_ghid_channel_mapping
                WHERE   type = '公众号'
                GROUP BY rootsourceid
                         ,channel
                         ,channel_name
                         ,ghid
                         ,gzh_name
                         ,type
            ) t8
ON      b.root_source_id = t8.rootsourceid
LEFT JOIN   (
                SELECT  t1.root_source_id AS rootsourceid
                        ,t1.put_type_two AS push_type
                        ,t1.channel
                        ,t2.layer_type
                FROM    loghubods.ad_put_flow_record_tencent_day t1
                LEFT JOIN loghubods.content_platform_account t2
                ON      t1.channel = t2.channel
                WHERE   t1.dt = MAX_PT('loghubods.ad_put_flow_record_tencent_day')
                AND     t1.put_type_one = '企微'
                AND     t1.root_source_id REGEXP 'touliu_tencentwbqw_|dyyqw_|dyycd_'
                GROUP BY t1.root_source_id
                         ,t1.put_type_two
                         ,t1.channel
                         ,t2.layer_type
            ) t9
ON      b.root_source_id = t9.rootsourceid
;