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
#! /bin/bash
# # # # # # # # # # # # # # # # # # # # # #
# @file :count_country_interest.sh
# @author :liushuai
# @revision:2017-03-06 10:45
# @desc :统计分国家的兴趣
# # # # # # # # # # # # # # # # # # # # # #
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;
drop table dsp_country_interest_count;
drop table m_country_interest_count;
create table if not exists dsp_country_interest_count
as
select dsp_interest.country,interestOne,interestTwo,count(*) as num from
(select tmp_dmp_dsp_interest.country,interestOne,interestTwo from tmp_dmp_dsp_interest
join
(select country,count(*) as allNum from tmp_dmp_and_m group by country order by allNum desc limit 30) country
on tmp_dmp_dsp_interest.country=country.country
) dsp_interest group by interestOne,dsp_interest.country,interestTwo;
create table if not exists m_country_interest_count
as
select m_interest.country,interestOne,interestTwo,count(*) as num from
(select tmp_dmp_m_interest.country,interestOne,interestTwo from tmp_dmp_m_interest
join
(select country,count(*) allNum from tmp_dmp_and_m group by country order by allNum desc limit 30) country
on tmp_dmp_m_interest.country=country.country
) m_interest group by interestOne,m_interest.country,interestTwo;
"
if [ $? -ne 0 ];then
exit 255
fi