Hive面试题 累积报表

如下的用户访问数据

userId  visitDate   visitCount
u01 	2017/1/21   5
u02 	2017/1/23   6
u03 	2017/1/22   8
u04 	2017/1/20   3
u01 	2017/1/23   6
u01 	2017/2/21   8
U02 	2017/1/23   6
U01 	2017/2/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

1.创建表，添加数据

##创建表
CREATE TABLE visitTable(userId  VARCHAR(20),visitDate  DATE,visitCount INT);
##添加数据
INSERT INTO visitTable VALUES('u01','2017/1/21','5');
INSERT INTO visitTable VALUES('u02','2017/1/23','6');
INSERT INTO visitTable VALUES('u03','2017/1/22','8');
INSERT INTO visitTable VALUES('u04','2017/1/20','3');
INSERT INTO visitTable VALUES('u01','2017/1/23','6');
INSERT INTO visitTable VALUES('u01','2017/2/21','8');
INSERT INTO visitTable VALUES('u02','2017/1/23','6');
INSERT INTO visitTable VALUES('u01','2017/2/22','4');

2.获取每个用户每个月得访问总数并生产表

##获取每个用户每个月得总数
##SUBSTR：截取
##获取用户截取后的月份信息，用户id，月份访问总数信息，通过截取后的月份信息与用户id进行分组生成用户月份总访问表
CREATE TABLE myMonthCount AS  
SELECT userId,SUBSTR(visitDate,1,7)  AS mydate,SUM(visitCount) AS mycount 
FROM visitTable 
GROUP BY SUBSTR(visitDate,1,7),userId;

查询的结果

3.获取用户每月访问表后，进行自查询

##使用自查询，显示右边表的id，日期，左边表的月份访问量，总访问量
##使用两张表（用户每月访问表）的id进行关联并且左表的月份要小于等于右表的月份
##按照右表的id与时间进行分组
SELECT  m2.userId,m2.mydate,MAX(m1.mycount),SUM(m1.mycount) 
FROM  myMonthCount m1 
JOIN myMonthCount m2 
ON m1.userId = m2.userId 
WHERE m1.mydate <= m2.mydate 
GROUP  BY m2.userId,m2.mydate;

查询的结果