A hive interview question: Cumulative report __hive

We have the following user access data:

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

Requires that you use SQL to count the cumulative number of accesses per user, as shown in the following table:

Subtotal

User ID	Month		Cumulative
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

Analysis

You can first count the results of each user's monthly visit, as shown in the following table:

Subtotal

User ID	Month
U01	2017-01	11
U01	2017-02	12
U02	2017-01	12
u03	2017-01	8
U04	2017-01	3

Then, with userid equality as a condition for self connection, you can have a result similar to the following:

U01     2017-01 u01 2017-01 u01 2017-01 one      u01     2017-02     2017-02 12      u01     2017-01
u01     2017-02      u01     2017-02 12

To the left of the Userid,vmonth,vcount group, and then ask the rightmost column and on it, of course, add a present condition, that is the right vmonth to be less than the left side of the vmonth. SQL command

The first step is to create the monthly access statistics:

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);

The second step, cumulative addition:

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;