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

yaong
阅读 1 分钟

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

0 条评论