mysql--统计每个用户的累计访问次数
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);
--统计每个用户的累计访问次数
1 SELECT tab.users '用户',tab.date '日期',tab.su '小计',sum(tab.su) over(partition by tab.users ORDER BY tab.date)'合计' 2 from( 3 SELECT userId users,date_format(regexp_replace(visitDate,'/','-'),'%Y-%m') date,sum(visitCount) su 4 FROM action 5 GROUP BY userId,date 6 ORDER BY userId,date 7 )tab 8 GROUP BY tab.users,tab.date
注：这里用到over函数--
