create table action(
userId VARCHAR(10),
visitDate VARCHAR(10),
visitCount int );
insert into action 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 tab.users ‘用户‘,tab.date ‘日期‘,tab.su ‘小计‘,sum(tab.su) over(partition by tab.users ORDER BY tab.date)‘合计‘
from(
SELECT userId users,date_format(regexp_replace(visitDate,‘/‘,‘-‘),‘%Y-%m‘) date,sum(visitCount) su
FROM action
GROUP BY userId,date
ORDER BY userId,date
)tab
GROUP BY tab.users,tab.date