编写sql实现每个用户截止到每月为止的最大单月访问次数和累计到该月的总访问次数

编写sql实现每个用户截止到每月为止的最大单月访问次数和累计到该月的总访问次数

drop database if exists db_1;
create database db_1;
use db_1;
CREATE TABLE IF NOT EXISTS tb(
   id varchar(5),
   dates varchar(10),
   visits varchar(20)
);
INSERT INTO tb VALUES
('A','2015-01',5) ,
('A','2015-01',15),
('B','2015-01',5),
('A','2015-01',8),
('B','2015-01',25),
('A','2015-01',5),
('A','2015-02',4),
('A','2015-02',6),
('B','2015-02',10),
('B','2015-02',5),
('A','2015-03',16),
('A','2015-03',22),
('B','2015-03',23),
('B','2015-03',10),
('B','2015-03',11)
;

with t1 as (
    select id, dates, sum(visits) cs
    from tb
    group by id, dates
)
select id,dates,max(cs)over(partition by id order by dates) 最大单月,sum(cs)over(partition by id order by dates) 累计
from t1