1 #!/bin/bash 2 /opt/module/hive/bin/hive -e " 3 with 4 a as ( 5 select 6 user_id, 7 count(0) order_count, 8 sum(final_total_amount) order_amount 9 from $1.dwd_order_info 10 group by user_id 11 ), 12 b as ( 13 select 14 user_id, 15 count(0) pay_count, 16 sum(total_amount) pay_amount 17 from $1.dwd_payment_info 18 group by user_id 19 ), 20 c as( 21 select 22 user_id, 23 count(appraise) comment_count 24 from $1.dwd_comment_info 25 group by user_id 26 ) 27 insert overwrite table $1.dws_user_action 28 select 29 user_id, 30 sum(d.order_count) order_count, 31 sum(d.order_amount) order_amount, 32 sum(d.pay_count) pay_count, 33 sum(d.pay_amount) pay_amount, 34 sum(d.comment_count) comment_count 35 from ( 36 select 37 user_id, 38 order_count, 39 order_amount, 40 0 pay_count, 41 0 pay_amount, 42 0 comment_count 43 from a 44 union all 45 select 46 user_id, 47 0, 48 0, 49 pay_count, 50 pay_amount, 51 0 52 from b 53 union all 54 select 55 user_id, 56 0, 57 0, 58 0 , 59 0 , 60 comment_count 61 from c 62 ) d 63 group by user_id;"