1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
#!/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/