主要字段內(nèi)容dt表示日期,如20160510platform表示平臺,只有兩個選項,蘋果和安卓mid是用戶機(jī)器碼,類似于網(wǎng)卡MAC地址什么的pver是版本channel是分發(fā)渠道現(xiàn)在產(chǎn)品經(jīng)理需要統(tǒng)計每天用戶的新增,日活和留存率.其中留存率的概念是,如果用戶在5月1日第一次使用我們的產(chǎn)品。如果5月2日他還使用了,那么5月1日的“一日留存”加一.同理5月3日他又使用率，5月1日的“兩日留存”加一.5月1日的“一日留存率”=5月1日“一日留存” / 5月1日新增用戶數(shù)量.先創(chuàng)建一個表,記錄用戶首次使用的日期.
dt是用戶首次使用的日期,比如 20160510cver是版本pcid是用戶機(jī)器碼,就是原始日志表的mid然后創(chuàng)建一個每天數(shù)據(jù)的存放表,統(tǒng)計昨天一天的新增,激活和留存.
dt是日期type 1:新增 2:留存 3:日活num 是用戶數(shù)量,dtdiff僅僅用于計算留存,說明用戶使用和首次使用的日期間隔多少天.1.Hive統(tǒng)計每天新增用戶$dt是shell傳入的變量dt=$(date -d last-day +%Y%m%d)該腳本每天凌晨執(zhí)行,統(tǒng)計昨天的數(shù)據(jù).每次執(zhí)行,先清空report_userinfo表truncate table report_userinfo;insert into user_login_history select platform,min(dt),channel,cver,mid,1 from log_vvim where mid not in (select pcid from user_login_history where type=1) and mid is not null and dt=$dt group by platform,channel,cver,mid; 這個意思就是 原來沒有記錄在user_grouPRoom_login_history表中的pcid,java如果出現(xiàn)在昨天的日志表中,則說明用戶是新增的.然后將昨天新增的用戶數(shù)量寫入insert into report_userinfo select platform,dt,channel,cver,type,count(*) num,-1 from user_login_history where type=1 and dt=$dt group by platform,dt,channel,cver,type; 2.統(tǒng)計每天激活用戶數(shù)量insert into report_userinfo select platform,dt,channel,cver,3,count(distinct mid),-1 from log_vvim where mid is not null and dt=$dt group by dt,platform,channel,cver; 這個倒是簡單,根據(jù)原始的日志表,統(tǒng)計今天使用過的pcid,經(jīng)過去重,java培訓(xùn)中的用戶就是今天的日活用戶量.3.統(tǒng)計留存率.insert into report_userinfo select xinzeng.platform, xinzeng.dt, xinzeng.channel, xinzeng.cver, 2, count(distinct cunliu.pcid), datediff( from_unixtime(unix_timestamp(cast(cunliu.dt as string),'yyyyMMdd')), from_unixtime(unix_timestamp(cast(xinzeng.dt as string),'yyyyMMdd')) ) from ( select * from user_login_history where type=1 ) xinzeng inner join ( select platform, dt, channel, cver, mid pcid from log_vvim where mid is not null and dt=$dt group by platform,dt,channel,cver,mid ) cunliu on ( xinzeng.platform=cunliu.platform and xinzeng.channel=cunliu.channel and xinzeng.cver=cunliu.cver and xinzeng.pcid=cunliu.pcid ) where cunliu.dt>xinzeng.dt group by xinzeng.platform,xinzeng.dt,xinzeng.channel,xinzeng.cver, datediff( from_unixtime(unix_timestamp(cast(cunliu.dt as string),'yyyyMMdd')), from_unixtime(unix_timestamp(cast(xinzeng.dt as string),'yyyyMMdd')) ); 該SQL主要計算昨天使用過的用戶,他的首次使用日期,然后計算差值
表示安卓平臺,20160425那天首次使用的用戶,在8天之后,還使用過的用戶數(shù)量為20人。因為計算新增和日活在計算留存之前,cunliu.dt>xinzeng.dt主要是確定當(dāng)天新增的用戶不計入留存率計算.統(tǒng)計完成之后,將hive表導(dǎo)入MySQLsqoop export --connect jdbc:mysql://ip:端口/report --username uname --passWord "pwd" --table report_userinfo --export-dir '/user/hive/warehouse/logs.db/report_userinfo' --fields-terminated-by '/001'最終通過報表展現(xiàn)新聞熱點(diǎn)
疑難解答
圖片精選
