SQL查询-窗口函数

第一题

创建表格

create tabel test1
(userId varchar(10) not null primary key,
visitDate varchar(10) not null,
visitCount int)

插入数据

insert into test1
(userId,`visitDate`,`visitCount`)values
( '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 * from test1

求每个访客每个月访问次数，和累计访问次数。

select a.*,
sum(a.visitCount)over(partition by a.userId order by a.month) as '累计'
 from    
(select userId,
month(`visitDate`) as month,
sum(`visitCount`) as visitCount 
from test1
group by `userId`,month(`visitDate`)
)a
order by a.userId

第二题

创建表格

CREATE  test2
(user_id varchar(20),
shop varchar(10))

插入数据

insert into test2(user_id,shop)
values( 'u1', 'a' ),
            ( 'u2', 'b' ),
            ( 'u1', 'b' ),
            ( 'u1', 'a' ),
            ( 'u3', 'c' ),
            ( 'u4', 'b' ),
            ( 'u1', 'a' ),
            ( 'u2', 'c' ),
            ( 'u5', 'b' ),
            ( 'u4', 'b' ),
            ( 'u6', 'c' ),
            ( 'u2', 'c' ),
            ( 'u1', 'b' ),
            ( 'u2', 'a' ),
            ( 'u2', 'a' ),
            ( 'u3', 'a' ),
            ( 'u5', 'a' ),
            ( 'u5', 'a' ),
            ( 'u5', 'a' ); 
            

查看数据

 select * from test2

1:每个店铺的UV（访客数）

select shop,count(distinct user_id) as user_cnt 
from test2
group by shop
order by user_cnt desc;

2:每个店铺访问次数top3的访客信息。输出店铺名称、访客id、访问次数

select b.* from 
(select a.*,row_number()over(partition by shop order by cnt desc) as rn from 
(select shop,
user_id,count(1) as cnt 
from test2
group by shop,user_id
order by shop,cnt desc
)a
)b
where b.rn<=3

第三题

有一个充值日志表credit_log，字段如下：

dist_id int ‘区组id’,
account string ‘账号’,
money int ‘充值金额’,
create_time string ‘订单时间’

CREATE TABLE test9(
            dist_id varchar(20) COMMENT '区