--统计出每个用户的累积访问次数

 

CREATE TABLE test(
userId string,
visitDate string,
visitCount INT )
ROW format delimited FIELDS TERMINATED BY "\t";
INSERT INTO TABLE test.test1
VALUES
( '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 );

select userId,
       visitMonth,
       cnt,
       sum(cnt) over (partition by userId order by cnt) sum_
from (select userId, visitMonth, sum(visitCount) cnt
      from (select userId, date_format(regexp_replace(visitDate, '/', '-'), 'yyyy-MM') visitMonth, visitCount
            from test1) t1
      group by userId, visitMonth) t2
order by userId;

查询结果: