《Hive编程指南》最后一章的Outbrain案例中，有一个把访问网络流量会话化的简单实现，但按照它的查询出来的结果是错的，于是自己重写了一个。 一、问题提出（摘自书中原文） 为了分析网络流量，我们常常希望能够基于各种各样的标准来测量热度。一种方法就是将用户行为分解到会话中，一次会话代表单一的一次“使用”所包含的一系列操作。一个用户在一天内或者一个月中的某几天可以多次访问某个网站，但每一次访问肯定是不一样的。 那么，什么是一个会话呢？一种定义是指相隔不超过30分钟的一连串的页面活动就是一个会话。也就是说，如果你去你的第1个页面，等待5分钟，然后去第2个页面，那么这是相同的会话。又等待25分钟后再到第3页，仍然是相同的会话。再等待1分钟跳转到第4页，这次会话将被打破了，这将不是第4个访问页面了，而是第2个会话中的第一个页面。 一旦我们获得这些中断信息，我们就可以查看会话的属性信息，来看看发生了什么事而导致中断的。常规的方式就是通过会话长度来对链入的页面进行比较。 乍一看，这似乎是一个完美的迭代过程。对于每个页面，保持倒计数，直到你找到第1个页面。但Hive是不支持迭代的。不过，还是可以解决这个问题。可以将这个过程分为4个阶段。 1. 识别哪些页面浏览是会话的初始者，或“起源”页面。 2. 对于每个页面，将其划分到正确的来源页面。 3. 将所有的页面浏览聚合到每个来源页面。 4. 对每个来源页面进行标记，然后计算每个会话的热度。 这种方式将产生一个表，其中每一行都表示一个完整的会话，然后用户就可以查询想知道的信息了。 二、实现过程 1. 设置 首先定义表session_test：
代码语言：javascript复制CREATE TABLE session_test
(
st_user_id STRING,
st_pageview_id STRING,
st_page_url STRING,
st_referrer_url STRING,
st_timestamp DOUBLE
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';然后准备测试数据的CSV文件session_test.txt
代码语言：javascript复制user_1,2184914255, http://baike.baidu.com/link?url=stnTAgubmqJuhKT4Kf-P1wXR0rkNyuN1THCwLblz4eWqWxWly5kgTFtbBYVt8BCspvPj1BQNS9MoDyTcegvgjq#3,http://ssx4501.blog.sohu.com/321517032.html,1459070426.712
user_1,2489633785,http://blog.sohu.com/,http://news.sohu.com/20160327/n442361110.shtml,1459070327.691
user_1,9638816121,http://www.sohu.com/,https://bj.122.gov.cn/views/inquiry.html,1459070227.725
user_1,7730804743,http://www.bjjtgl.gov.cn/wanchengyemian2/hlwptzcts.html,http://www.bjjtgl.gov.cn/,1459070127.563
user_1,6135947520,http://mail.sohu.com/bapp/36/main#mailList_-2,http://baike.baidu.com/link?url=stnTAgubmqJuhKT4Kf-P1wXR0rkNyuN1THCwLblz4eWqWxWly5kgTFtbBYVt8BCspvPj1BQNS9MoDyTcegvgjq#3,1459070027.165
user_1,2005762692,http://www.56.com/u63/v_MTIxOTAzMTk2.html,http://www.56.com/u63/v_MTIxOTAzMTk2.html,1459069427.065
user_1,9885965958,http://v.baidu.com/movie/32210.htm?fr=open_bdps&video_uri=movie.basic.002707.1372413037.2,https://www.baidu.com/s?ie=utf-8&f=8&rsv_bp=0&rsv_idx=1&tn=baidu&wd=蔡李佛小子&rsv_pq=da0a1d6e001a2f26&rsv_t=ae7cF77GAFjqvIjZZAoitr6QZQEZHLOS5jaOILlrGrSf82o0ewqe3XyjTLQ&rsv_enter=1&rsv_n=2&rsv_sug3=1,1459060427.743
user_1,7781491477,https://www.baidu.com/,http://www.youku.com/,1459060227.538
user_1,4874646952,http://stswordman.cnblogs.com/archive/2006/06/12/423910.html,http://stswordman.cnblogs.com/archive/2006/06/12/423910.html,1459060127.538
user_1,4584871291,https://www.baidu.com/s?ie=utf-8&f=8&rsv_bp=1&rsv_idx=1&tn=baidu&wd=referrer_url&oq=mysql 多行uuid&rsv_pq=b37dd6bf000f9361&rsv_t=de34O52vUqUHuV5xSBtPC8H8TCY7QosiYwdWioYH04hjx/IyZ7PBogibMlA&rsv_enter=1&inputT=5220&rsv_sug3=33&rsv_sug1=23&rsv_sug7=000&rsv_n=2&bs=mysql 多行uuid,https://www.baidu.com/s?ie=utf-8&f=8&rsv_bp=1&rsv_idx=1&tn=baidu&wd=mysql 多行uuid&oq=mysql 逐行uuid&rsv_pq=bd5a2a29000e7681&rsv_t=0eb2z5Jt3mUBueH+93/DJMIOS9KNaP85ERoqiRFfSyIYOh4cSFNOwnRh/cw&rsv_enter=1&inputT=5220&rsv_sug3=29&rsv_sug1=20&rsv_sug7=100&rsv_sug2=0&rsv_sug4=5943,1459060027.378
user_1,7240734452,http://zhidao.baidu.com/link?url=_Cw4-_vo4XVI1lHfPuSDo7rRPeyDThHzietAOWHR52H_G1FlV8piTWbzNCDHQTGaxjSRHIox_DttBWLtmzS-8HFydpxG_IjUnbjryjASWay,http://zhidao.baidu.com/link?url=_Cw4-_vo4XVI1lHfPuSDo7rRPeyDThHzietAOWHR52H_G1FlV8piTWbzNCDHQTGaxjSRHIox_DttBWLtmzS-8HFydpxG_IjUnbjryjASWay,1459059427.334
user_1,6310115480,https://www.baidu.com/s?ie=utf-8&f=8&rsv_bp=1&rsv_idx=1&tn=baidu&wd=mysql 逐行uuid&oq=mysql uuid&rsv_pq=db45a04e000eb323&rsv_t=a2cexGBrnrvDtSsubhPDhgtu0+mpbceYUGEqfhh49O3STPQfBGFTvQmx3LM&rsv_enter=1&inputT=23761&rsv_sug3=24&rsv_sug1=17&rsv_sug7=100&rsv_sug2=0&rsv_sug4=25872,https://www.baidu.com/s?ie=utf-8&f=8&rsv_bp=1&rsv_idx=1&tn=baidu&wd=mysql uuid&oq=时间戳 毫秒&rsv_pq=bd2966c9000d954a&rsv_t=2591i8XuLiiMERZ/caOuYiLPWymHp4R4Z+JV5/4/k3JbvShjVSiL0kDX09w&rsv_enter=1&rsv_sug3=16&rsv_sug1=11&rsv_sug7=100&bs=时间戳 毫秒,1459057527.948
user_1,9725586547,http://bbs.aardio.com/forum.php/forum.php?mod=viewthread&tid=8236,http://blog.csdn.net/runming918/article/details/7231259,1459057427.004
user_1,1995107575,http://blog.csdn.net/superhosts/article/details/26054997,http://www.xue163.com/19990/519968/519968758926117085.html,1459055428.541
user_1,4376767486,http://www.xue163.com/36678/22365/223657734/,http://v.youku.com/v_show/id_XMTUxMTg4MjQ3Ng==_ev_4.html?from=y1.3-idx-uhome-1519-20887.205805-205902.7-1,1459053428.252
user_2,2202616558, http://baike.baidu.com/link?url=stnTAgubmqJuhKT4Kf-P1wXR0rkNyuN1THCwLblz4eWqWxWly5kgTFtbBYVt8BCspvPj1BQNS9MoDyTcegvgjq#3,http://ssx4501.blog.sohu.com/321517032.html,1459070426.913
user_2,2458809722,http://blog.sohu.com/,http://news.sohu.com/20160327/n442361110.shtml,1459070327.073
user_2,8893838191,http://www.sohu.com/,https://bj.122.gov.cn/views/inquiry.html,1459070227.167
user_2,2853640637,http://www.bjjtgl.gov.cn/wanchengyemian2/hlwptzcts.html,http://www.bjjtgl.gov.cn/,1459070127.527
user_2,1174630884,http://mail.sohu.com/bapp/36/main#mailList_-2,http://baike.baidu.com/link?url=stnTAgubmqJuhKT4Kf-P1wXR0rkNyuN1THCwLblz4eWqWxWly5kgTFtbBYVt8BCspvPj1BQNS9MoDyTcegvgjq#3,1459070027.515
user_2,5635223546,http://www.56.com/u63/v_MTIxOTAzMTk2.html,http://www.56.com/u63/v_MTIxOTAzMTk2.html,1459069427.031
user_2,6497571917,http://v.baidu.com/movie/32210.htm?fr=open_bdps&video_uri=movie.basic.002707.1372413037.2,https://www.baidu.com/s?ie=utf-8&f=8&rsv_bp=0&rsv_idx=1&tn=baidu&wd=蔡李佛小子&rsv_pq=da0a1d6e001a2f26&rsv_t=ae7cF77GAFjqvIjZZAoitr6QZQEZHLOS5jaOILlrGrSf82o0ewqe3XyjTLQ&rsv_enter=1&rsv_n=2&rsv_sug3=1,1459060427.961
user_2,9736857005,https://www.baidu.com/,http://www.youku.com/,1459060227.972
user_2,9522138344,http://stswordman.cnblogs.com/archive/2006/06/12/423910.html,http://stswordman.cnblogs.com/archive/2006/06/12/423910.html,1459060127.819
user_2,3288917999,https://www.baidu.com/s?ie=utf-8&f=8&rsv_bp=1&rsv_idx=1&tn=baidu&wd=referrer_url&oq=mysql 多行uuid&rsv_pq=b37dd6bf000f9361&rsv_t=de34O52vUqUHuV5xSBtPC8H8TCY7QosiYwdWioYH04hjx/IyZ7PBogibMlA&rsv_enter=1&inputT=5220&rsv_sug3=33&rsv_sug1=23&rsv_sug7=000&rsv_n=2&bs=mysql 多行uuid,https://www.baidu.com/s?ie=utf-8&f=8&rsv_bp=1&rsv_idx=1&tn=baidu&wd=mysql 多行uuid&oq=mysql 逐行uuid&rsv_pq=bd5a2a29000e7681&rsv_t=0eb2z5Jt3mUBueH+93/DJMIOS9KNaP85ERoqiRFfSyIYOh4cSFNOwnRh/cw&rsv_enter=1&inputT=5220&rsv_sug3=29&rsv_sug1=20&rsv_sug7=100&rsv_sug2=0&rsv_sug4=5943,1459060027.815
user_2,3790199378,http://zhidao.baidu.com/link?url=_Cw4-_vo4XVI1lHfPuSDo7rRPeyDThHzietAOWHR52H_G1FlV8piTWbzNCDHQTGaxjSRHIox_DttBWLtmzS-8HFydpxG_IjUnbjryjASWay,http://zhidao.baidu.com/link?url=_Cw4-_vo4XVI1lHfPuSDo7rRPeyDThHzietAOWHR52H_G1FlV8piTWbzNCDHQTGaxjSRHIox_DttBWLtmzS-8HFydpxG_IjUnbjryjASWay,1459059427.106
user_2,6413528694,https://www.baidu.com/s?ie=utf-8&f=8&rsv_bp=1&rsv_idx=1&tn=baidu&wd=mysql 逐行uuid&oq=mysql uuid&rsv_pq=db45a04e000eb323&rsv_t=a2cexGBrnrvDtSsubhPDhgtu0+mpbceYUGEqfhh49O3STPQfBGFTvQmx3LM&rsv_enter=1&inputT=23761&rsv_sug3=24&rsv_sug1=17&rsv_sug7=100&rsv_sug2=0&rsv_sug4=25872,https://www.baidu.com/s?ie=utf-8&f=8&rsv_bp=1&rsv_idx=1&tn=baidu&wd=mysql uuid&oq=时间戳 毫秒&rsv_pq=bd2966c9000d954a&rsv_t=2591i8XuLiiMERZ/caOuYiLPWymHp4R4Z+JV5/4/k3JbvShjVSiL0kDX09w&rsv_enter=1&rsv_sug3=16&rsv_sug1=11&rsv_sug7=100&bs=时间戳 毫秒,1459057527.689
user_2,6745253283,http://bbs.aardio.com/forum.php/forum.php?mod=viewthread&tid=8236,http://blog.csdn.net/runming918/article/details/7231259,1459057427.126
user_2,7438056632,http://blog.csdn.net/superhosts/article/details/26054997,http://www.xue163.com/19990/519968/519968758926117085.html,1459055428.198
user_2,8615798962,http://www.xue163.com/36678/22365/223657734/,http://v.youku.com/v_show/id_XMTUxMTg4MjQ3Ng==_ev_4.html?from=y1.3-idx-uhome-1519-20887.205805-205902.7-1,1459053428.636最后向表中导入测试数据
代码语言：javascript复制LOAD DATA LOCAL INPATH '/home/grid/session_test.txt' INTO TABLE session_test;2. 找到每个会话的起始页
代码语言：javascript复制CREATE TABLE sessionization_step_one_origins
AS
SELECT t1.st_user_id AS ssoo_user_id,
t1.st_pageview_id ssoo_pageview_id,
t1.st_timestamp ssoo_timestamp
FROM session_test t1
LEFT JOIN
(SELECT DISTINCT st_pageview_id
FROM (SELECT b.st_pageview_id st_pageview_id,
IF (
b.st_timestamp > a.st_timestamp
AND b.st_timestamp - a.st_timestamp < 1800,
1,
0)
c_nonorigin_flags
FROM session_test a
INNER JOIN session_test b
ON a.st_user_id = b.st_user_id) t1
WHERE c_nonorigin_flags = 1) t2
ON t1.st_pageview_id = t2.st_pageview_id
WHERE t2.st_pageview_id IS NULL;在最内层子查询中用自关联查询出非起始页面的浏览ID，因为Hive目前还没有支持不等于的JOIN，所以用了一个IF判断给非起始页面做标记。最外层的查询使用外连接得到会话起始页。 查询sessionization_step_one_origins表的记录如图1所示
代码语言：javascript复制 SELECT ssoo_user_id,
ssoo_pageview_id,
CAST (ssoo_timestamp AS DECIMAL (13, 3))
FROM sessionization_step_one_origins
ORDER BY ssoo_user_id, ssoo_timestamp;
图1 3. 将PV分配到起始页面中
代码语言：javascript复制CREATE TABLE sessionization_step_two_origin_identification
AS
SELECT st_user_id sstoi_user_id,
st_pageview_id sstoi_pageview_id,
ssoo_pageview_id sstoi_origin_pageview_id
FROM (SELECT t2.st_user_id,
t2.st_pageview_id,
t1.ssoo_pageview_id,
IF (
t2.st_timestamp >= t1.current_ssoo_timestamp
AND t2.st_timestamp < t1.lead_ssoo_timestamp,
1,
0)
c_nonorigin_flags
FROM (SELECT ssoo_user_id,
ssoo_pageview_id,
NVL (lead_ssoo_timestamp, 9999999999)
lead_ssoo_timestamp,
current_ssoo_timestamp
FROM (SELECT ssoo_user_id,
ssoo_pageview_id,
LEAD (
ssoo_timestamp,
1)
OVER (PARTITION BY ssoo_user_id
ORDER BY ssoo_timestamp)
lead_ssoo_timestamp,
ssoo_timestamp current_ssoo_timestamp
FROM sessionization_step_one_origins) t0) t1
INNER JOIN
(SELECT st_user_id, st_pageview_id, st_timestamp
FROM session_test) t2
ON t1.ssoo_user_id = t2.st_user_id) t
WHERE t.c_nonorigin_flags = 1;最内层子查询使用LEAD窗口分析函数得到下一个时间戳，在当前时间戳和下一个时间戳之间的记录为当前会话起始页对应的PV。因为起始页对应的PV包含起始页自己，所以IF区间判断条件是左闭右开。 查询sessionization_step_two_origin_identification表的记录如图2所示
代码语言：javascript复制SELECT sstoi_user_id, sstoi_pageview_id, sstoi_origin_pageview_id
FROM sessionization_step_two_origin_identification;
图2 4. 按起始页聚合
代码语言：javascript复制CREATE TABLE sessionization_step_three_origin_aggregation AS
SELECT
sstoi_user_id sstoa_user_id,
sstoi_origin_pageview_id sstoa_origin_pageview_id,
count(1) sstoa_pageview_count
FROM
sessionization_step_two_origin_identification
GROUP BY sstoi_user_id , sstoi_origin_pageview_id;查询sessionization_step_three_origin_aggregation表的记录如图3所示
代码语言：javascript复制 SELECT sstoa_user_id, sstoa_origin_pageview_id, sstoa_pageview_count
FROM sessionization_step_three_origin_aggregation
ORDER BY sstoa_user_id, sstoa_pageview_count;
图3 5. 按起始页属性聚合
代码语言：javascript复制CREATE TABLE sessionization_step_four_qualitative_labeling
AS
SELECT a.a_user_id AS ssfql_user_id,
a.a_origin_pageview_id AS ssfql_origin_pageview_id,
b.b_timestamp AS ssfql_timestamp,
b.b_page_url AS ssfql_page_url,
b.b_referrer_url AS ssfql_referrer_url,
a.a_pageview_count AS ssqfl_pageview_count
FROM (SELECT sstoa_user_id AS a_user_id,
sstoa_origin_pageview_id AS a_origin_pageview_id,
sstoa_pageview_count AS a_pageview_count
FROM sessionization_step_three_origin_aggregation) a
JOIN
(SELECT st_user_id AS b_user_id,
st_pageview_id AS b_pageview_id,
st_page_url AS b_page_url,
st_referrer_url AS b_referrer_url,
st_timestamp AS b_timestamp
FROM session_test) b
ON a.a_user_id = b.b_user_id
AND a.a_origin_pageview_id = b.b_pageview_id;查询sessionization_step_four_qualitative_labeling表的记录如图4所示
代码语言：javascript复制 SELECT ssfql_user_id,
ssfql_origin_pageview_id,
ssfql_timestamp,
ssfql_page_url,
ssfql_referrer_url,
ssqfl_pageview_count
FROM sessionization_step_four_qualitative_labeling
ORDER BY ssfql_user_id, ssfql_timestamp;
图4 6. 衡量热度
代码语言：javascript复制 SELECT PARSE_URL (ssfql_referrer_url, 'HOST') AS referrer_host,
COUNT (1) AS session_count,
AVG (ssqfl_pageview_count) AS avg_pvs_per_session,
SUM (ssqfl_pageview_count) / COUNT (1) AS weighted_avg_pvs_per_session,
MAX (ssqfl_pageview_count) AS max_pvs_per_session,
MIN (ssqfl_pageview_count) AS min_pvs_per_session,
COUNT (DISTINCT ssfql_user_id) AS unique_users
FROM sessionization_step_four_qualitative_labeling
GROUP BY PARSE_URL (ssfql_referrer_url, 'HOST');查询结果如图5所示
图5
