手写sql,分组求和

 

 1.时间格式转换

select DATE_FORMAT(regexp_replace(visit_date,'/','-'),'%Y-%m')from visitor;

 

 2.计算每人单月访问量

select user_id,mn,sum(visit_count)as count from (

select user_id,DATE_FORMAT(replace(visit_date,'/','-'),'%Y-%m')as mn,visit_count from visitor)as a GROUP BY user_id,mn

 

 3。按月累计访问量

select user_id,mn,count,sum(count)over(PARTITION by user_id ORDER BY mn) as accumulate from (
select user_id,mn,sum(visit_count)as count from (

select user_id,DATE_FORMAT(replace(visit_date,'/','-'),'%Y-%m')as mn,visit_count from visitor)as a GROUP BY user_id,mn)as b;