#!/bin/bash # # # # # # # # # # # # # # # # # # # # # # # @file :create_end.sh # @author :liushuai # @revision:2017-03-14 20:09 # @desc :查询M系统的各个维度的数量,M系统相同app_id,不同包名的数量最大的包 # # # # # # # # # # # # # # # # # # # # # # 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_id,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,country,count(1) from ( select package_name,url,category,platform,count(1) 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 app_gender_age group by package_name,url,category,platform,country ) m join ( select app_id,package_name,app_name from app_id_name where year='${year}' and month='${month}' and day='${day}' ) app on app.package_name=m.package_name group by app_name,app_id,platform,country,url,category; ">appCountNum.txt if [ $? -ne 0 ];then exit 255 fi hive_cmd " use dev; select app.app_name,app.app_id,app.package_name,platform,url,category,max(allNum),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 app_gender_age group by package_name,url,category,platform,country) m join (select app_id,package_name,app_name from app_id_name where year='${year}' and month='${month}' and day='${day}') app on app.package_name=m.package_name group by app_name,app_id,platform,country,url,category,app.package_name; "> maxAppName.txt if [ $? -ne 0 ];then exit 255 fi hadoop fs -mkdir -p s3://mob-emr-test/liushuai/prd/${year}/${month}/${day}/table/ hadoop fs -rm s3://mob-emr-test/liushuai/prd/${year}/${month}/${day}/table/maxAppName.txt hadoop fs -rm s3://mob-emr-test/liushuai/prd/${year}/${month}/${day}/table/appCountNum.txt hadoop fs -put maxAppName.txt s3://mob-emr-test/liushuai/prd/${year}/${month}/${day}/table/ hadoop fs -put appCountNum.txt s3://mob-emr-test/liushuai/prd/${year}/${month}/${day}/table/