SQL练习题

userId visitDate visitCount
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
要求使用SQL统计出每个用户的累积访
问次数，如下表所示：
用户id 月份 小计 累积
u01 2017-01 11 110
u01 2017-02 12 23
u02 2017-01 12 12
u03 2017-01 8 8
u04 2017-01 3 3

建表：
	create table visit (userId String not null,
						visitDate String not null,
						visitCount int not null)
	row format delimited fields terminated by "\t";
插入数据：
	insert into table visit values('u01','2017/1/21',5)
	insert into table visit values('u02','2017/1/23',6)
	insert into table visit values('u03','2017/1/22',8)
	insert into table visit values('u04','2017/1/20',3)
	insert into table visit values('u01','2017/1/23',6)
	insert into table visit values('u01','2017/1/21',8)
	insert into table visit values('u02','2017/1/23',6)
	insert into table visit values('u01','2017/1/22',4);
查询数据：
	(1)修改数据格式
	select
		userId,
		regexp_replace(visitDaate,'/','-') dt,
		visitCount
	from
		visit;t1
	(2)	每个用户按照月份对范文次数进行聚合，即前三列内容
	select 
		userId,
		date_format(dt,'yyyy-mm') mm,
		sum(visitCount) sum_ct
	from
		t1
	group by 
		userId,date_format(dt,'yyyy-mm');t2
	(3)统计累计数据
	select
		userId,
		mm,
		sum_ct
		sum(sum_ct) over(partition by userId order by mm)
	from
		t2;