一道Hive面试题：累积报表

我们有如下的用户访问数据：

userId	visitDate	visitCount
u01	2017-01-21	5
u02	2017-01-23	6
u03	2017-01-22	8
u04	2017-01-20	3
u01	2017-01-23	6
u01	2017-02-21	8
u02	2017-01-23	6
u01	2017-02-22	4

要求使用SQL统计出每个用户的累积访问次数，如下表所示：

用户id	月份	小计	累积
u01	2017-01	11	11
u01	2017-02	12	23
u02	2017-01	12	12
u03	2017-01	8	8
u04	2017-01	3	3

分析

首先可以统计出每个用户每月访问次数的结果，如下表：

用户id	月份	小计
u01	2017-01	11
u01	2017-02	12
u02	2017-01	12
u03	2017-01	8
u04	2017-01	3

然后以userId相等为条件进行自连接，可以有类似下面的结果：

u01     2017-01 11      u01     2017-01 11
u01     2017-01 11      u01     2017-02 12
u01     2017-02 12      u01     2017-01 11
u01     2017-02 12      u01     2017-02 12

以左边的userId,vMonth,vCount进行分组，然后求最右边一列的和就可以了，当然要加一个现在条件，那就是右边的vMonth要小于等于左边的vMonth。

SQL命令

第一步，创建月份访问统计表：

create table t_vlog_month as
select userId,substr(vDate,1,7) as vMonth, sum(vCount) as mCount
from t_vlog
group by userId,substr(vDate,1,7);

第二步，累积相加：

select t1.userId,t1.vMonth,max(t1.mCount),sum(t2.mCount) as aCount
from t_vlog_month as t1 inner join t_vlog_month as t2 on (t1.userId=t2.userId)
where t2.vMonth <= t1.vMonth
group by t1.userId,t1.vMonth;