hive计算网页停留时长

hive表结构例如以下：

 

create table pv_user_info(
session_id string, 
user_id string,
url string,
starttime bigint
);

主要就是这几个字段实用。省略其它。

 

实现方式：userid和sessionid分组后并按时间降序排序，降序排序后。第一行就是该用户最后一次浏览的网页。最后一行是第一次浏览的网页，第一行的starttime和第二行的starttime相减就是第二行停留时间。

这里会有几个误差
用户最后一次浏览的网页时长：由于不知道用关闭浏览器的时间。所以没办法计算，就返回一个特定的值吧，我这里返回的是0。

跨天：任务是按天统计，还没有第二天的数据，所以不考虑跨天。
连续点开多个网页：按上面的实现方式就会有误差。这是没办法的。

按产生日志文件的结果看。仅仅能用这样的实现方式，除非专门写个js记录网页停留时长(不知是否可行)，那么就不须要用hive了。

详细实现：
1.要实现行与行之间的比較或算加减法hive sql是实现不了的，仅仅有自己定义函数UDF
2.定义全局变量，存上次浏览时间。sessionid。userid
3.第一次调用evaluate方法上次浏览时间肯定是空的。也就是说是用户在这一天里最后一次浏览网页的时间，直接返回0即可了。
4.再次调用evaluate方法要推断是不是同上一次调用evaluate方法是不是同一个用户同一个会话。假设是的话就相减。
5.调用evaluate方法假设和上一次调用不是一个用户。说明这个网页是还有一个用户最后一次浏览网页的时间。直接返回0。

 

hiveUDF代码

import org.apache.hadoop.hive.ql.exec.UDF;

public class CalcUDF extends UDF {

	// 记录上一次訪问信息
	private String[] lastLine = new String[2];;

	// 记录上次一次訪问时间
	private Long lastTime = null;

	public Long evaluate(Long currentTime, String[] currentLine) {
		if (lastTime == null) {

			lastTime = currentTime;
			for (int i = 0; i < currentLine.length; i++) {
				lastLine[i] = currentLine[i];
			}

			return 0L;
		} else {
			// 同一个用户sessionId 和 userId 相等
			if (currentLine[0].equals(lastLine[0])&& currentLine[1].equals(lastLine[1])) {
				Long useTime = lastTime - currentTime;

				lastTime = currentTime;

				for (int i = 0; i < currentLine.length; i++) {
					lastLine[i] = currentLine[i];
				}
				return useTime;
			} else { // 还有一个用户
				lastTime = currentTime;
				for (int i = 0; i < currentLine.length; i++) {
					lastLine[i] = currentLine[i];
				}
				return 0L;
			}
		}
	}
}

将代码打成jar包。上传到/home/hadoop/test/

进入hive命令行，运行：

add jar /home/hadoop/test/hiveUDF.jar; // 加入jar包 

create temporary function calc as 'com.orange.hadoop.CalcUDF';    //创建函数

//最后运行hive sql

select session_id,user_id,url,starttime, 
calc(starttime,user_id,session_id) 
from 
(select * from pv_user_info distribute by user_id,session_id sort by  user_id,session_id ,starttime desc) t;

 

 这是我的实现方式。有没有更好的解决方式？