Hive sql - 多表关联不同时间维度计算数据

多表关联创建临时表，计算按日，按周，按月维度数据

select uid,
       nvl(sum(case when t1.dt>=date_sub(current_date,1) and t1.dt<current_date then delta*price end),0) as daily_fee,
       nvl(sum(case when t1.dt>=date_sub(current_date,pmod(dayofweek(current_date)+4, 7) + 1) and t1.dt<current_date then delta*price end),0) as weekly_fee,
       nvl(sum(case when t1.dt>=date_format(date_sub(current_date,1),'yyyy-MM-01') and t1.dt<current_date then delta*price end),0) as monthly_fee,
       nvl(sum(case when t1.dt<current_date then delta*price end),0) as all_fee
from
       (select user_id as future_uid,to_date(modify_time) as dt,sum(delta) as delta
        from fee_dw.user_balance_log_day 
        where asset='RMB' group by to_date(modify_time),user_id) t1
  left join 
       (select dt,price from fee_dm.d_asset_rmb_price where asset='RMB' and dt>='2020-01-01') t2
  on t1.dt=t2.dt
  group by uid