#!/bin/bash


# # # # # # # # # # # # # # # # # # # # # #
# @file    :create_three.sh
# @author  :liushuai
# @revision:2017-03-14 20:09
# # # # # # # # # # # # # # # # # # # # # #

source ../prd_env.sh

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

file_dir="s3://mob-emr-test/liushuai/prd/${year}/${month}/${day}/four/three"
hadoop fs -mkdir -p $file_dir

hive_cmd "
use dev;
select count(*) as allNum,concat(sum(if(gender='m',1,0)),'(',round(sum(if(gender='m',1,0))/count(*)*100,2),'%',')'),
	concat(sum(if(gender='f',1,0)),'(',round(sum(if(gender='f',1,0))/count(*)*100,2),'%',')'),
	concat(sum(if(gender='o',1,0))+sum(if(gender is null,1,0)),'(',round((sum(if(gender='o',1,0))+sum(if(gender is null,1,0)))/count(*)*100,2),'%',')'),
	concat(sum(if(age='0-17',1,0)),'(',round(sum(if(age='0-17',1,0))/count(*)*100,2),'%',')'),
	concat(sum(if(age='18-24',1,0)),'(',round(sum(if(age='18-24',1,0))/count(*)*100,2),'%',')'),
	concat(sum(if(age='25-44',1,0)),'(',round(sum(if(age='25-44',1,0))/count(*)*100,2),'%',')'),
	concat(sum(if(age='45-59',1,0)),'(',round(sum(if(age='45-59',1,0))/count(*)*100,2),'%',')'),
	concat(sum(if(age='60+',1,0)),'(',round(sum(if(age='60+',1,0))/count(*)*100,2),'%',')'),
	concat(sum(if(age='unknown',1,0))+sum(if(age is null,1,0)),'(',round((sum(if(age='unknown',1,0))+sum(if(age is null,1,0)))/count(*)*100,2),'%',')')
	from tmp_dmp_and_m;
" > mCount.txt
if [ $? -ne 0 ];then
  exit 255
fi
hadoop fs -rm $file_dir/mCount.txt
hadoop fs -put mCount.txt $file_dir
if [ $? -ne 0 ];then
  exit 255
fi


hive_cmd "
use dev;
select count(*) as allNum,concat(sum(if(gender='m',1,0)),'(',round(sum(if(gender='m',1,0))/count(*)*100,2),'%',')'),
	concat(sum(if(gender='f',1,0)),'(',round(sum(if(gender='f',1,0))/count(*)*100,2),'%',')'),
	concat(sum(if(gender='o',1,0))+sum(if(gender is null,1,0)),'(',round((sum(if(gender='o',1,0))+sum(if(gender is null,1,0)))/count(*)*100,2),'%',')'),
	concat(sum(if(age='0-17',1,0)),'(',round(sum(if(age='0-17',1,0))/count(*)*100,2),'%',')'),
	concat(sum(if(age='18-24',1,0)),'(',round(sum(if(age='18-24',1,0))/count(*)*100,2),'%',')'),
	concat(sum(if(age='25-44',1,0)),'(',round(sum(if(age='25-44',1,0))/count(*)*100,2),'%',')'),
	concat(sum(if(age='45-59',1,0)),'(',round(sum(if(age='45-59',1,0))/count(*)*100,2),'%',')'),
	concat(sum(if(age='60+',1,0)),'(',round(sum(if(age='60+',1,0))/count(*)*100,2),'%',')'),
	concat(sum(if(age='unknown',1,0))+sum(if(age is null,1,0)),'(',round((sum(if(age='unknown',1,0))+sum(if(age is null,1,0)))/count(*)*100,2),'%',')')
	from tmp_dmp_and_dsp;
" > dspCount.txt
if [ $? -ne 0 ];then
  exit 255
fi

hadoop fs -rm $file_dir/dspCount.txt
hadoop fs -put dspCount.txt $file_dir
if [ $? -ne 0 ];then
  exit 255
fi


hive_cmd "
use dev;
select country,count(*) as allNum,concat(sum(if(gender='m',1,0)),'(',round(sum(if(gender='m',1,0))/count(*)*100,2),'%',')'),
	concat(sum(if(gender='f',1,0)),'(',round(sum(if(gender='f',1,0))/count(*)*100,2),'%',')'),
	concat(sum(if(gender='o',1,0))+sum(if(gender is null,1,0)),'(',round((sum(if(gender='o',1,0))+sum(if(gender is null,1,0)))/count(*)*100,2),'%',')'),
	concat(sum(if(age='0-17',1,0)),'(',round(sum(if(age='0-17',1,0))/count(*)*100,2),'%',')'),
	concat(sum(if(age='18-24',1,0)),'(',round(sum(if(age='18-24',1,0))/count(*)*100,2),'%',')'),
	concat(sum(if(age='25-44',1,0)),'(',round(sum(if(age='25-44',1,0))/count(*)*100,2),'%',')'),
	concat(sum(if(age='45-59',1,0)),'(',round(sum(if(age='45-59',1,0))/count(*)*100,2),'%',')'),
	concat(sum(if(age='60+',1,0)),'(',round(sum(if(age='60+',1,0))/count(*)*100,2),'%',')'),
	concat(sum(if(age='unknown',1,0))+sum(if(age is null,1,0)),'(',round((sum(if(age='unknown',1,0))+sum(if(age is null,1,0)))/count(*)*100,2),'%',')')
    from tmp_dmp_and_dsp group by country order by allNum desc limit 30;
" >dspCountryCount.txt
if [ $? -ne 0 ];then
  exit 255
fi

hadoop fs -rm $file_dir/dspCountryCount.txt
hadoop fs -put dspCountryCount.txt $file_dir
if [ $? -ne 0 ];then
  exit 255
fi


hive_cmd "
use dev;
select country,count(*) as allNum,concat(sum(if(gender='m',1,0)),'(',round(sum(if(gender='m',1,0))/count(*)*100,2),'%',')'),
	concat(sum(if(gender='f',1,0)),'(',round(sum(if(gender='f',1,0))/count(*)*100,2),'%',')'),
	concat(sum(if(gender='o',1,0))+sum(if(gender is null,1,0)),'(',round((sum(if(gender='o',1,0))+sum(if(gender is null,1,0)))/count(*)*100,2),'%',')'),
	concat(sum(if(age='0-17',1,0)),'(',round(sum(if(age='0-17',1,0))/count(*)*100,2),'%',')'),
	concat(sum(if(age='18-24',1,0)),'(',round(sum(if(age='18-24',1,0))/count(*)*100,2),'%',')'),
	concat(sum(if(age='25-44',1,0)),'(',round(sum(if(age='25-44',1,0))/count(*)*100,2),'%',')'),
	concat(sum(if(age='45-59',1,0)),'(',round(sum(if(age='45-59',1,0))/count(*)*100,2),'%',')'),
	concat(sum(if(age='60+',1,0)),'(',round(sum(if(age='60+',1,0))/count(*)*100,2),'%',')'),
	concat(sum(if(age='unknown',1,0))+sum(if(age is null,1,0)),'(',round((sum(if(age='unknown',1,0))+sum(if(age is null,1,0)))/count(*)*100,2),'%',')')
	from tmp_dmp_and_m group by country order by allNum desc limit 30;
" > mCountryCount.txt
if [ $? -ne 0 ];then
  exit 255
fi

hadoop fs -rm $file_dir/mCountryCount.txt
hadoop fs -put mCountryCount.txt $file_dir
if [ $? -ne 0 ];then
  exit 255
fi