0: jdbc:hive2://node03:10000> select * from ods_click_stream_visit limit 2;+---------------------------------------+-------------------------------------+--------------------------------+---------------------------------+--------------------------------+---------------------------------+------------------------------------------------+------------------------------------+---------------------------------+--+| ods_click_stream_visit.session | ods_click_stream_visit.remote_addr | ods_click_stream_visit.intime | ods_click_stream_visit.outtime | ods_click_stream_visit.inpage | ods_click_stream_visit.outpage | ods_click_stream_visit.referal | ods_click_stream_visit.pagevisits | ods_click_stream_visit.datestr |+---------------------------------------+-------------------------------------+--------------------------------+---------------------------------+--------------------------------+---------------------------------+------------------------------------------------+------------------------------------+---------------------------------+--+| 01c87dee-e91c-4ad7-b3cc-0c121bc03806 | 113.107.237.31 | 2013-09-18 09:06:46 | 2013-09-18 09:06:46 | /finance-rhive-repurchase/ | /finance-rhive-repurchase/ | "-" | 1 | 20130918 || 06bb3ecb-f2b1-4b83-ba30-cf0ed51dd5e3 | 115.236.11.194 | 2013-09-18 12:25:57 | 2013-09-18 12:25:57 | /hadoop-mahout-roadmap/ | /hadoop-mahout-roadmap/ | "http://f.dataguru.cn/thread-177375-1-1.html" | 1 | 20130918 |+---------------------------------------+-------------------------------------+--------------------------------+---------------------------------+--------------------------------+---------------------------------+------------------------------------------------+------------------------------------+---------------------------------+--+
-- 回头/单次访客统计
select remote_addr,count(remote_addr) ipcountfrom ods_click_stream_visitgroup by remote_addrhaving ipcount > 1;
查询今日所有回头访客及其访问次数。
drop table dw_user_returning;create table dw_user_returning(day string,remote_addr string,acc_cnt string)partitioned by (datestr string);
insert overwrite table dw_user_returning partition(datestr='20130918')select '20130918' as day,remote_addr,count(session) as acc_cnt from ods_click_stream_visit group by remote_addr having acc_cnt > 1;
-------------------------------------------------------------------------------------- 人均访问频次,使用所有的独立访问的人，即独立的session个数除以所有的去重IP即可
-- 人均访问的频次，频次表示我们来了多少个session-- 次数都是使用session来进行区分，一个session就是表示一次select count(session)/count(distinct remote_addr) from ods_click_stream_visit where datestr='20130918';
select count(1) from ods_click_stream_visitwhere datestr ='20130918';
-- 人均页面浏览量，所有的页面点击次数累加除以所有的独立去重IP总和即可select sum(pagevisits)/count(distinct remote_addr) from ods_click_stream_visit where datestr='20130918';
转载于:https://www.cnblogs.com/mediocreWorld/p/11108321.html
相关资源：JAVA上百实例源码以及开源项目