Hive基礎(chǔ)知識(shí)之顧客訪問店鋪數(shù)據(jù)分析（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
點(diǎn)擊查看更多內(nèi)容
為 TA 點(diǎn)贊
評論
評論
共同學(xué)習(xí)，寫下你的評論
評論加載中...
作者其他優(yōu)質(zhì)文章
正在加載中
感謝您的支持，我會(huì)繼續(xù)努力的～
掃碼打賞，你說多少就多少
贊賞金額會(huì)直接到老師賬戶
支付方式
打開微信掃一掃，即可進(jìn)行掃碼打賞哦