#!/bin/bash . /opt/app/bi-app/energy/dayTask/config.sh if [ -n "$2" ] ;then echo "如果是输入的日期按照取输入日期" do_date=$2 else echo "====没有输入数据的日期,取当前时间的前一天====" do_date=`date -d yesterday +"%Y-%m-%d"` fi echo $do_date env_config=" use saga_dw; SET mapreduce.job.queuename=default; " dwd_energy_15_min_hour=" insert overwrite table saga_dw.dwd_energy_15_min_hour partition (dt = '$do_date') select building, func_id, parent_code, branch_type, branch_type_name, use_range_type, cast(value_sum as decimal(30, 15)) as value_sum , hour_ as current_hour, current_year, year_mouth, week_of_year from ( select building, func_id, parent_code, branch_type, branch_type_name, use_range_type, dt, t1.hour_, (ap_sum - al_sum) value_sum from ( select building, func_id, 'AP1' parent_code, 'ALU' as branch_type, '其他' as branch_type_name, 'OTHER' use_range_type, sum(if((branch_type == 'AP' and use_range_type == '1'), data_value, 0)) ap_sum, sum(if((branch_type == 'AL' and use_range_type in (2, 3, 4, 6, 7)), data_value, 0)) al_sum, dt, o15mh.hour_ from ( select building, func_id, meter, data_time, substr(data_time, 0, 13) hour_, data_value, dt from ods_energy_15_min oe15m where dt = '$do_date' ) o15mh left join dim_office_meter dom on o15mh.meter = dom.meter where dt = '$do_date' and branch_type in ('AP', 'AL') group by building, func_id, dt, o15mh.hour_ ) t1 union select building, func_id, parent_code, branch_type, branch_type_name, use_range_type, dt, o15mh.hour_, sum(data_value) value_sum from ( select building, func_id, meter, data_time, substr(data_time, 0, 13) hour_, data_value, dt from ods_energy_15_min oe15m where dt = '$do_date' ) o15mh left join dim_office_meter dom2 on o15mh.meter = dom2.meter group by building, func_id, parent_code, branch_type, branch_type_name, use_range_type, dt, o15mh.hour_ ) teh left join dim_date_day ddd on dt = date_id order by current_hour;" insert_into_table(){ case $1 in "all") hive -e "$env_config$dwd_energy_15_min_hour" ;; esac } insert_into_table $1