#!/bin/bash

# # # # # # # # # # # # # # # # # # # # # #
# @file    :dsp_app_count.sh
# @author  :liushuai
# @revision:2017-03-14 20:09
# @desc    :查询dsp top流量数据
# # # # # # # # # # # # # # # # # # # # # #

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}


hive_cmd "
use dev;
select app_name,app.platform,url,category,sum(allNUM),
	concat(sum(man),'(',round(sum(man)/sum(allNUM)*100,2),'%',')') as man,
	concat(sum(female),'(',round(sum(female)/sum(allNUM)*100,2),'%',')') as female,
	concat(sum(other_gender),'(',round(sum(other_gender)/sum(allNUM)*100,2),'%',')') as other_gender,
    concat(sum(eighteen),'(',round(sum(eighteen)/sum(allNUM)*100,2),'%',')') as eighteen,
	concat(sum(twenty_five),'(',round(sum(twenty_five)/sum(allNUM)*100,2),'%',')') as twenty_five,
	concat(sum(forty_five),'(',round(sum(forty_five)/sum(allNUM)*100,2),'%',')') as forty_five,
	concat(sum(sixty),'(',round(sum(sixty)/sum(allNUM)*100,2),'%',')') as sixty,
    concat(sum(above_sixty),'(',round(sum(above_sixty)/sum(allNUM)*100,2),'%',')') as above_sixty,
	concat(sum(other_age),'(',round(sum(other_age)/sum(allNUM)*100,2),'%',')') as other_age,dsp.country from
        (select package_name,url,category,platform,count(*) as allNum,
            sum(if(gender='m',1,0)) as man,
            sum(if(gender='f',1,0)) as female,
            sum(if(gender='o',1,0))+sum(if(gender is null,1,0)) as other_gender,
            sum(if(age='0-17',1,0)) as eighteen,
            sum(if(age='18-24',1,0)) as twenty_five,
            sum(if(age='25-44',1,0)) as forty_five,
            sum(if(age='45-59',1,0)) as sixty,
            sum(if(age='60+',1,0)) as above_sixty,
            sum(if(age='unknown',1,0))+sum(if(age is null,1,0)) as other_age,country
            from dsp_app_gender_age group by package_name,url,category,platform,country) dsp
		join
        (select country,package_name,app_name,platform from dsp_app_id_name
            where year='${year}' and month='${month}' and day='${day}') app
        on app.package_name=dsp.package_name and app.country=dsp.country
	group by app_name,app.platform,dsp.country,url,category;
" > dspAppCountNum.txt
if [ $? -ne 0 ];then
  exit 255
fi


result_path=s3://mob-emr-test/liushuai/prd/${year}/${month}/${day}/table
hadoop fs -mkdir -p ${result_path}
hadoop fs -rm ${result_path}/dspAppCountNum.txt
hadoop fs -put dspAppCountNum.txt ${result_path}