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

yaong

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

user_idshop
u1a
u2b
u1b
u1a
u3c
u4b
u1a
u2c
u5b
u4b
u6c
u2c
u1b
u2a
u2a
u3a
u5a
u5a
u5a

建表、导表

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;
shopUV
shopuv
a4
b4
c3

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

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

select shop, user_id, count(*) as ct from visit group by shop, user_id;
shopuser_idct
au13
bu12
au22
bu21
cu22
au31
cu31
bu42
au53
bu51
cu61

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;
shopuser_idctrk
au311
au222
au533
au133
bu511
bu211
bu423
bu123
cu611
cu311
cu223

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;
shopuser_idct
au31
au22
au13
au53
bu21
bu51
bu12
bu42
cu31
cu61
cu22

The End

阅读 123
1 声望
0 粉丝
0 条评论
你知道吗?

1 声望
0 粉丝
宣传栏