#! /bin/bash

# # # # # # # # # # # # # # # # # # # # # #
# @file    :merge_dmp_dsp.sh
# @author  :liushuai
# @revision:2017-03-06 15:15
# @desc    ：两天前的dsp日活与3天前的全量join
# # # # # # # # # # # # # # # # # # # # # #

source ../prd_env.sh

LOG_TIME=$(date +%Y%m%d -d "-4 day $ScheduleTime")
year=${LOG_TIME:0:4}
month=${LOG_TIME:4:2}
day=${LOG_TIME:6:2}

OLD_LOG_TIME=$(date +%Y%m%d -d "-4 day $ScheduleTime")
old_year=${OLD_LOG_TIME:0:4}
old_month=${OLD_LOG_TIME:4:2}
old_day=${OLD_LOG_TIME:6:2}


hive_cmd "
use dev;
drop table tmp_dmp_and_dsp;
add file age.py;
create table if not exists tmp_dmp_and_dsp
as
	select dspanddmp.device_id, dspanddmp.device_type, dspanddmp.country,age,gender
	from (
		select distinct dsp.device_id,dsp.device_type,tmp_dmp.country country
		from (
			select device_id,device_type
			from
				dwh.etl_dsp_request_daily
			where date='${year}-${month}-${day}' and
				device_id is not null
		) dsp
		join
			tmp_dmp
		on
			dsp.device_id=tmp_dmp.device_id and dsp.device_type=tmp_dmp.device_type
		) dspanddmp
	left outer join (
		select transform(*)
			using 'python age.py' as (device_id,device_type,package_names,age,tag)
		from
			dwh.dm_device_age
		where year='${old_year}' and
			month='${old_month}' and
			day='${old_day}'
		) age
	on
		age.device_id=dspanddmp.device_id and age.device_type=dspanddmp.device_type
	left outer join (
		select device_id,device_type,gender
		from
			dwh.dm_device_gender
		where year='${old_year}' and
			month='${old_month}' and
			day='${old_day}'
		) gender
	on
		gender.device_id=dspanddmp.device_id and gender.device_type=dspanddmp.device_type
	group by dspanddmp.device_id,dspanddmp.device_type,dspanddmp.country,age,gender;
"
if [ $? -ne 0 ];then
  exit 255
fi