count_country_interest.sh 1.38 KB
#! /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