#!/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}