Hive基础知识之顾客访问店铺数据分析（UV、Top3）

已知顾客访问店铺的访问日志

user_id	shop
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

建表、导表

create table visit(user_id string,shop string) row format delimited fields terminated by '\t';
load data local inpath '/data/visit.dat' into table visit;

求每个店铺的UV（访客数）

select shop , count(distinct user_id) as uv from visit group by shop;

shop	UV
shop	uv
a	4
b	4
c	3

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

1.先求出每个店铺，每个访客的访问次数

select shop, user_id, count(*) as ct from visit group by shop, user_id;

shop	user_id	ct
a	u1	3
b	u1	2
a	u2	2
b	u2	1
c	u2	2
a	u3	1
c	u3	1
b	u4	2
a	u5	3
b	u5	1
c	u6	1

2.计算每个店铺被用户访问次数排名

select shop,user_id,ct,rank() over(partition by shop order by ct) rk
from (select shop, user_id, count(*) as ct from visit group by shop, user_id) as t1;

shop	user_id	ct	rk
a	u3	1	1
a	u2	2	2
a	u5	3	3
a	u1	3	3
b	u5	1	1
b	u2	1	1
b	u4	2	3
b	u1	2	3
c	u6	1	1
c	u3	1	1
c	u2	2	3

3.取每个店铺的前三名

select shop, user_id, ct from
(select shop,user_id,ct,rank() over(partition by shop order by ct) rk
from (select shop, user_id, count(*) as ct from visit group by shop, user_id) as t1) as t2 where rk <= 3;

shop	user_id	ct
a	u3	1
a	u2	2
a	u1	3
a	u5	3
b	u2	1
b	u5	1
b	u1	2
b	u4	2
c	u3	1
c	u6	1
c	u2	2

The End