hive sql多表操作 秒懂否

--粉丝复购率（粉丝一定时间内第二次购买人数 与 支付用户比值 以天为维度）
select 
w.uid,
cast(avg(w.datacount) * 100 / sum(w.totalcount) as int)   as data_value,
from 
(select  z.uid as uid,z.datacount  as datacount ,x.totalcount as totalcount
         from
        (select 
        c.uid as uid,
        count(DISTINCT c.buyeruid) as datacount
        from 
        
        --SELECT  c.uid as uid ,  c.buyeruid as buyeruid FROM
        
        --c表
        (SELECT  uid, buyeruid from 
        
         (select  uid, datapool['opuid'] as buyeruid 
        from hdp_zhuanzhuan_dw_global.dw_log_server_action_1d  
        where  
         dt = '${yesDay}'
         and   action = 'tobInfoOrder'
         and region = 't'
         and datapool['status'] = 3) a
        
        LEFT SEMI JOIN
        
        (select  uid, datapool['opuid'] as buyeruid 
         table_order 
         WHERE
         dt BETWEEN  '${startDay}' and '${endDay}'
         and status = 3) b
        
        on a.buyeruid=b.buyeruid    ) c 
        LEFT semi  JOIN 
    ( SELECT
        cast(split(uid,
        '\\.')[0] AS BIGINT) AS uid,
        followeduid
             
    FROM
        table_fans  
    WHERE
        date='${yday}'             
        AND split(uid,'\\.')[1] IN (
            'following_1','fans_1'
        )             
        AND status = 0) y
        on c.uid=y.uid and c.buyeruid = y.followeduid
        
        group by c.uid) z
        left join 
        ( SELECT
        cast(split(uid,
        '\\.')[0] AS BIGINT) AS uid,
        count(followeduid) as totalcount
             
    FROM
        table_fans  
    WHERE
        date='${yday}'             
        AND split(uid,'\\.')[1] IN (
            'following_1','fans_1'
        )             
        AND status = 0 group by uid) x
    on z.uid=x.uid) w
    group by w.uid