进来闲来无事，总结一下数仓面试吧！希望对小白有点用！

有50W个某东网站店铺，每个顾客访客访问任何一个店铺的任何一个商品时都会产生一条访问日志，访问日志存储的表名为Visit，访客的用户id为user_id，被访问的店铺名称为shop，请统计：
1）每个店铺的UV（访客数）
2）每个店铺访问次数top3的访客信息。输出店铺名称、访客id、访问次数

---

1

---

首先，先明白什么是旁uv？
PV（Page View）访问量, 即页面浏览量或点击量，衡量网站用户访问的网页数量；在一定统计周期内用户每打开或刷新一个页面就记录1次，多次打开或刷新同一页面则浏览量累计。

即（独立的访客）Unique Visitor,访问您网站的一台电脑客户端为一个访客。在00:00-24:00内对相同的客户端的操作只会被计算一次。

建表语句

 create table second_visit (user_id string,shop string);

插入数据

insert into table second_visit values ('1','a');
insert into table second_visit values ('1','b');
insert into table second_visit values ('2','a');
insert into table second_visit values ('3','c');
insert into table second_visit values ('1','a');
insert into table second_visit values ('1','a');

---

首先，先去重（group by），首先按用户id去重，在按照门店去重。

---

select
    user_id,
    shop
from second_visit
group by user_id,shop;

结果为：
OK
user_id shop
1 a
1 b
2 a
3 c
Time taken: 23.059 seconds, Fetched: 4 row(s)

---

在计算uv的量

---

select
    tmp.shop,
    count(1) as uv
from
(
    select
	    user_id,
	    shop
    from second_visit
    group by user_id,shop
)tmp
group by shop;

结果为：
OK
tmp.shop uv
a 2
b 1
c 1
Time taken: 19.453 seconds, Fetched: 3 row(s)

---

2

---

数据太少。。。在插入点数据
insert into table second_visit values (‘4’,‘a’);
insert into table second_visit values (‘1’,‘b’);
insert into table second_visit values (‘2’,‘a’);
insert into table second_visit values (‘4’,‘c’);
insert into table second_visit values (‘4’,‘a’);
insert into table second_visit values (‘4’,‘a’);
insert into table second_visit values (‘5’,‘a’);
insert into table second_visit values (‘1’,‘b’);
insert into table second_visit values (‘2’,‘a’);
insert into table second_visit values (‘3’,‘c’);
insert into table second_visit values (‘5’,‘a’);
insert into table second_visit values (‘5’,‘a’);
insert into table second_visit values (‘5’,‘a’);
insert into table second_visit values (‘1’,‘b’);
insert into table second_visit values (‘4’,‘a’);
insert into table second_visit values (‘4’,‘c’);
insert into table second_visit values (‘2’,‘a’);
insert into table second_visit values (‘2’,‘a’);
insert into table second_visit values (‘4’,‘c’);
insert into table second_visit values (‘4’,‘c’);
insert into table second_visit values (‘3’,‘c’);
insert into table second_visit values (‘3’,‘c’);
insert into table second_visit values (‘5’,‘c’);
。。。。。。。
。。。。。。。

---

第一步也是首先进行去重并统计一个用户访问的次数。（先按店铺去重，在按用户id去重）

---

select
    user_id,
    shop,
	count(1) uvcount
from second_visit
group by shop,user_id；

结果为：

user_id	shop	uvcount
1	a	3
2	a	9
4	a	8
5	a	10
1	b	7
2	b	1
5	b	2
6	b	3
3	c	7
4	c	9
5	c	2
6	c	2

---

在利用开窗函数来进行排序 row_number() over()

---

select 
    tmp.user_id,
    tmp.shop,
    tmp.uvcount,
    row_number() over(partition by tmp.shop order by tmp.uvcount desc) rank
from
(
    select
	    user_id,
	    shop,
	    count(1) uvcount
    from second_visit
    group by shop,user_id
) tmp;

结果为：

tmp.user_id	tmp.shop	tmp.uvcount	rank
5	a	10	1
2	a	9	2
4	a	8	3
1	a	3	4
1	b	7	1
6	b	3	2
5	b	2	3
2	b	1	4
4	c	9	1
3	c	7	2
5	c	2	3
6	c	2	4

---

终极hivesql

---

select
    tmp1.shop,
    tmp1.user_id,
    tmp1.uvcount
from
(
    select 
	    tmp.user_id,
	    tmp.shop,
	    tmp.uvcount,
	    row_number() over(partition by tmp.shop order by tmp.uvcount desc) rank
    from
    (
	    select
		    user_id,
		    shop,
		    count(1) uvcount
	    from second_visit
	    group by shop,user_id
    ) tmp
)tmp1
where tmp1.rank<=3;

结果为：

tmp1.shop	tmp1.user_id	tmp1.uvcount
a	5	10
a	2	9
a	4	8
b	1	7
b	6	3
b	5	2
c	4	9
c	3	7
c	5	2

大牛路过留下点意见！万分感谢！