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

2021.09.18 14:41 579浏览

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

0人点赞

• 推荐
• 评论
• 收藏
• 共同学习，写下你的评论

0/150